On Thu, Dec 29, 2005 at 12:46:28PM -0500, Ken Winter wrote:
> Can arrays be declared in PL/pgSQL routines? If so, how?
>
> Section 8.10 of the documentation
> (http://www.postgresql.org/docs/7.4/static/arrays.html) tells how to declare
> and use arrays as table columns. But I don't find any part of the
> documentation that says how to declare a simple array local to a PL/pgSQL
> function. I tried the following guess, but it only won me a "syntax error
> at or near VARCHAR:
>
> DECLARE
>
> my_array VARCHAR [];
What version of PostgreSQL are you using? Could you post a complete
function instead of just an excerpt? The following works for me in
7.4.10 and later but not in 7.3.12:
CREATE FUNCTION foo(varchar, varchar, varchar) RETURNS varchar[] AS '
DECLARE my_array varchar[] := ''{}'';
BEGIN my_array[1] := $1; my_array[2] := $2; my_array[3] := $3; RETURN my_array;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;
SELECT foo('a', 'b', 'c'); foo
---------{a,b,c}
(1 row)
Array handling was improved in 7.4; in earlier versions you'll
probably get an error like the following after SELECT:
WARNING: plpgsql: ERROR during compile of foo near line 4
ERROR: syntax error at or near "["
That's a little different than your "syntax error at or near VARCHAR."
Was that the actual error message?
--
Michael Fuhr