Postgres Pro
Downloads
Home   >   mailing lists

Thread: plpgsql array initialization, what's the story?

plpgsql array initialization, what's the story?

From
"Karl O. Pinc"
Date:
Postgresql 8.0.1

If I write the plpgsql:

declare
y int[];
begin
y[1] := 1;
y[2] := 2;
y[3] := 3;
...

All y[] array elements are NULL, as is array_dims(y).
But if I write:

declare
y int[] := '{}';
begin
y[1] := 1;
y[2] := 2;
y[3] := 3;
...

Then things work as expected.

What's going on?  (As in "Gosh, it looks like something's
happening here that I should know about.")

This behavior is not clear from the plpgsql documentation.

Regards,

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: plpgsql array initialization, what's the story?

From
Michael Fuhr
Date:
On Thu, Mar 31, 2005 at 07:13:30PM +0000, Karl O. Pinc wrote:
> Postgresql 8.0.1
>
> If I write the plpgsql:
>
> declare
> y int[];
> begin
> y[1] := 1;
> y[2] := 2;
> y[3] := 3;
> ...
>
> All y[] array elements are NULL, as is array_dims(y).

I think this has been fixed for 8.0.2:

http://archives.postgresql.org/pgsql-committers/2005-02/msg00012.php

Here's a test in 8.0.2beta1:

CREATE FUNCTION foo() RETURNS integer[] AS $$
DECLARE
    y  integer[];
BEGIN
    y[1] := 1;
    y[2] := 2;
    y[3] := 3;

    RETURN y;
END;
$$ LANGUAGE plpgsql;

SELECT foo();
   foo
---------
 {1,2,3}
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: plpgsql array initialization, what's the story?

From
"Karl O. Pinc"
Date:
On 03/31/2005 01:59:02 PM, Michael Fuhr wrote:

> I think this has been fixed for 8.0.2:
>
> http://archives.postgresql.org/pgsql-committers/2005-02/msg00012.php
>
> Here's a test in 8.0.2beta1:
>
> CREATE FUNCTION foo() RETURNS integer[] AS $$
> DECLARE
>     y  integer[];
> BEGIN
>     y[1] := 1;
>     y[2] := 2;
>     y[3] := 3;
>
>     RETURN y;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT foo();
>    foo
> ---------
>  {1,2,3}
> (1 row)
>

In the meantime, those who want arrays of non-integer datatypes
must cast to an array of the appropriate datatype (as suggested
by the documentation.)

create or replace function kop.bar()
returns date[]
language plpgsql
as $func$
declare
y date[] := CAST ('{}' AS date[]);

begin
y[1] := '1979-03-01';
y[2] := '1979-03-02';
y[3] := '1979-03-03';

return y;
end;
$func$;

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein