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
