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?
Date: Thu, 29 Dec 2005 11:21:21 -0700
Message-ID: <20051229182120.GA53437@winnie.fuhr.org> (raw)
In-Reply-To: <005101c60c9f$cfb5f660$6603a8c0@kenxp>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, ken@sunward.org
Subject: Re: Arrays in PL/pgSQL routines?
In-Reply-To: <20051229182120.GA53437@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox