pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedArrays in PL/pgSQL routines?
6+ messages / 4 participants
[nested] [flat]
* Arrays in PL/pgSQL routines?
@ 2005-12-29 17:46 Ken Winter <ken@sunward.org>
2005-12-29 17:52 ` Re: Arrays in PL/pgSQL routines? Bricklen Anderson <banderson@presinet.com>
2005-12-29 18:21 ` Re: Arrays in PL/pgSQL routines? Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 6+ messages in thread
From: Ken Winter @ 2005-12-29 17:46 UTC (permalink / raw)
To: pgsql-sql
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 [];
.
~ TIA
~ Ken
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Arrays in PL/pgSQL routines?
2005-12-29 17:46 Arrays in PL/pgSQL routines? Ken Winter <ken@sunward.org>
@ 2005-12-29 17:52 ` Bricklen Anderson <banderson@presinet.com>
2005-12-29 18:23 ` Re: Arrays in PL/pgSQL routines? Ken Winter <ken@sunward.org>
1 sibling, 1 reply; 6+ messages in thread
From: Bricklen Anderson @ 2005-12-29 17:52 UTC (permalink / raw)
To: Ken Winter <ken@sunward.org>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Arrays in PL/pgSQL routines?
2005-12-29 17:46 Arrays in PL/pgSQL routines? Ken Winter <ken@sunward.org>
2005-12-29 17:52 ` Re: Arrays in PL/pgSQL routines? Bricklen Anderson <banderson@presinet.com>
@ 2005-12-29 18:23 ` Ken Winter <ken@sunward.org>
2005-12-29 18:42 ` Re: Arrays in PL/pgSQL routines? Tom Lane <tgl@sss.pgh.pa.us>
2005-12-29 18:46 ` Re: Arrays in PL/pgSQL routines? Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 6+ messages in thread
From: Ken Winter @ 2005-12-29 18:23 UTC (permalink / raw)
To: 'Bricklen Anderson' <banderson@presinet.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Arrays in PL/pgSQL routines?
2005-12-29 17:46 Arrays in PL/pgSQL routines? Ken Winter <ken@sunward.org>
2005-12-29 17:52 ` Re: Arrays in PL/pgSQL routines? Bricklen Anderson <banderson@presinet.com>
2005-12-29 18:23 ` Re: Arrays in PL/pgSQL routines? Ken Winter <ken@sunward.org>
@ 2005-12-29 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-12-29 18:42 UTC (permalink / raw)
To: Ken Winter <ken@sunward.org>; +Cc: 'Bricklen Anderson' <banderson@presinet.com>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Arrays in PL/pgSQL routines?
2005-12-29 17:46 Arrays in PL/pgSQL routines? Ken Winter <ken@sunward.org>
2005-12-29 17:52 ` Re: Arrays in PL/pgSQL routines? Bricklen Anderson <banderson@presinet.com>
2005-12-29 18:23 ` Re: Arrays in PL/pgSQL routines? Ken Winter <ken@sunward.org>
@ 2005-12-29 18:46 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2005-12-29 18:46 UTC (permalink / raw)
To: Ken Winter <ken@sunward.org>; +Cc: 'Bricklen Anderson' <banderson@presinet.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Arrays in PL/pgSQL routines?
2005-12-29 17:46 Arrays in PL/pgSQL routines? Ken Winter <ken@sunward.org>
@ 2005-12-29 18:21 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2005-12-29 18:21 UTC (permalink / raw)
To: Ken Winter <ken@sunward.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-12-29 18:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-29 17:46 Arrays in PL/pgSQL routines? Ken Winter <ken@sunward.org>
2005-12-29 17:52 ` Bricklen Anderson <banderson@presinet.com>
2005-12-29 18:23 ` Ken Winter <ken@sunward.org>
2005-12-29 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-29 18:46 ` Michael Fuhr <mike@fuhr.org>
2005-12-29 18:21 ` Michael Fuhr <mike@fuhr.org>
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