Thread: Arrays in PL/pgSQL routines?

<div class="Section1"><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">Can arrays be declared in PL/pgSQL routines?  If so, how?</span></font><p class="MsoNormal"><font
face="Verdana"size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">Section 8.10 of the documentation (<a
href="http://www.postgresql.org/docs/7.4/static/arrays.html">http://www.postgresql.org/docs/7.4/static/arrays.html</a>)
tellshow to declare and use arrays as table columns.  But I don’t find any part of the documentation that says how to
declarea simple array local to a PL/pgSQL function.  I tried the following guess, but it only won me a “syntax error at
ornear VARCHAR:</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">DECLARE</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana">  my_array VARCHAR [];</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana">  …</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">~ TIA</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana">~ Ken</span></font></div>

Ken Winter wrote:
> Can arrays be declared in PL/pgSQL routines?  If so, how?
>  
<snip>
> DECLARE
> 
try:
my_array VARCHAR[] := '{}';

not sure if this works in 7.4 though, if that's the version that you are 
using.

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

Bricklen ~

That works.  (Odd that the initialization seems to be necessary to make it
work.)  Thanks!  Yes, I'm using version 7.4.

~ Ken

> -----Original Message-----
> From: Bricklen Anderson [mailto:banderson@presinet.com]
> Sent: Thursday, December 29, 2005 12:53 PM
> To: Ken Winter
> Cc: PostgreSQL pg-sql list
> Subject: Re: [SQL] Arrays in PL/pgSQL routines?
> 
> Ken Winter wrote:
> > Can arrays be declared in PL/pgSQL routines?  If so, how?
> >
> <snip>
> > DECLARE
> >
> try:
> my_array VARCHAR[] := '{}';
> 
> not sure if this works in 7.4 though, if that's the version that you are
> using.

"Ken Winter" <ken@sunward.org> writes:
> That works.  (Odd that the initialization seems to be necessary to make it
> work.)  Thanks!  Yes, I'm using version 7.4.

Possibly what you're running into is that 7.4 is restrictive about what
you can do with an array that's NULL.  Until pretty recently, assigning
to an element of a NULL array left the array still NULL --- this is an
artifact of the fact that the system sees the assignment as a binary
operation with the array and the new element as inputs.
        regards, tom lane

On Thu, Dec 29, 2005 at 01:23:28PM -0500, Ken Winter wrote:
> That works.  (Odd that the initialization seems to be necessary to make it
> work.)  Thanks!  Yes, I'm using version 7.4.

7.4.what?  Absence of initialization shouldn't cause a syntax error;
at least it doesn't in 7.4.10.  However, in 7.4 you do have to
initialize an array before assigning to its elements, else the array
will remain NULL (initialization isn't necessary in the latest 8.x
releases).

-- 
Michael Fuhr