Date: Thu, 29 Dec 2005 11:21:21 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Ken Winter <ken@sunward.org>
Cc: PostgreSQL pg-sql list <pgsql-sql@postgresql.org>
Subject: Re: Arrays in PL/pgSQL routines?
Message-ID: <20051229182120.GA53437@winnie.fuhr.org>
References: <005101c60c9f$cfb5f660$6603a8c0@kenxp>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <005101c60c9f$cfb5f660$6603a8c0@kenxp>

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