Re: Arrays in PL/pgSQL routines?

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