Обсуждение: Glitch: cannot use Arrays with Raise Notice

Bug:  Cannot Use Arrays with Raise Notice in PL/pgSQL.
Version Tested: 7.4.1
Severity:  Annoyance
Description:
Attempting to pass an array element to Raise Notice in PL/pgSQL will produce a
parse error:

CREATE OR REPLACE FUNCTION if_exec_to_text (
        TEXT, TEXT[], TEXT[] ) RETURNS text AS '
DECLARE qstring TEXT;
        r_params ALIAS for $2;
    r_values ALIAS for $3;
    param_loop INT;
    execrec RECORD;
    retval TEXT;
BEGIN
        -- swaps in parameters and executes a query returning a single
    -- text value
        qstring := $1;
        param_loop := 1;
    raise notice '' param 1 %'', r_params[param_loop];
        WHILE r_params[param_loop] <> '''' LOOP
           -- qstring := strswap(qstring, r_params[param_loop],
r_values[param_loop] );
        -- above line commented out for reproducability
            param_loop := param_loop + 1;
    END LOOP;
        FOR execrec IN EXECUTE qstring LOOP
                retval := execrec.col1;
    END LOOP;
    RETURN retval;
END;' LANGUAGE plpgsql;

Produces the following error:

jwnet_test=# select if_exec_to_text ( 'select to_char(''#VALUE#''::DATE,''MM/
DD/YYYY'') as col1;',
jwnet_test(# ARRAY[ '#VALUE#' ], ARRAY[ '2004-03-21' ]);
ERROR:  syntax error at or near "["
CONTEXT:  compile of PL/pgSQL function "if_exec_to_text" near line 12

Removal of the Raise Notice statement will cause the procedure to execute.

No biggie, just wanted to get it on the bug list.


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Josh Berkus wrote:
> Bug:  Cannot Use Arrays with Raise Notice in PL/pgSQL.
> Version Tested: 7.4.1
> Severity:  Annoyance
> Description:
> Attempting to pass an array element to Raise Notice in PL/pgSQL will produce a
> parse error:

I can reproduce this with cvs tip -- I'll check into it.

Thanks,

Joe

Joe Conway <mail@joeconway.com> writes:
> I can reproduce this with cvs tip -- I'll check into it.

It's no surprise --- plpgsql's RAISE doesn't take anything but a string
literal for the format, and unadorned variable names for the additional
parameters.  It ought to be generalized some time ...

            regards, tom lane