Thread: Arrays in pl/pgsql functions

PostgreSQL version 7.3.3

With reference to pl/pgsql functions.
I can declare a function to return an array type and I get no complaints when
defining or executing it.
I can declare a variable within the function as an array type and I don't get
any complaints defining or executing the function.
When I try to access the array I have problems.
I cannot find any documentation on accessing array types so I assumed they
would follow the pgSQL notation of for example:

CREATE OR REPLACE FUNCTION test_arrays() RETURNS int4[] AS '
DECLARE
     test int4[];
BEGIN
    test[1] := 1;
    RETURN test;
END ' LANGUAGE 'plpgsql';

If I try to execute this function I get:
WARNING:  plpgsql: ERROR during compile of test_arrays near line 5
ERROR:  syntax error at or near "["

If I comment out the line:
test[1] := 1;
then the function executes ok.

Could someone be please inform me what the correct syntax for accessing arrays
is or tell me if its not possible with pl/pgSQL functions.

Thanks in advance,
Donald Fraser.

Donald Fraser wrote:
> Could someone be please inform me what the correct syntax for accessing arrays
> is or tell me if its not possible with pl/pgSQL functions.
>

It won't work in 7.3.x or before, as you've noted. In 7.4 this will work:

CREATE OR REPLACE FUNCTION test_arrays() RETURNS int4[] AS '
DECLARE
      test int4[] := ''{}'';
BEGIN
     test[1] := 1;
     RETURN test;
END ' LANGUAGE 'plpgsql';

regression=# select test_arrays();
  test_arrays
-------------
  {1}
(1 row)

Note that you have to initialize "test" to an empty array, because
otherwise you are trying to add an element to a NULL::int4[], the result
of which is still NULL.

Joe

Many thanks for clearing that one up.
I have avoided using arrays for the mean time and gone for the more elegant
solution of using an additional table. More flexible and powerful but, in the
simple case that I needed, meant more work... Roll on 7.4 !

Regards
Donald Fraser.

----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "Donald Fraser" <demolish@cwgsy.net>
Cc: "[ADMIN]" <pgsql-admin@postgresql.org>
Sent: Thursday, July 17, 2003 6:50 PM
Subject: Re: [ADMIN] Arrays in pl/pgsql functions


> Donald Fraser wrote:
> > Could someone be please inform me what the correct syntax for accessing
arrays
> > is or tell me if its not possible with pl/pgSQL functions.
> >
>
> It won't work in 7.3.x or before, as you've noted. In 7.4 this will work:
>
> CREATE OR REPLACE FUNCTION test_arrays() RETURNS int4[] AS '
> DECLARE
>       test int4[] := ''{}'';
> BEGIN
>      test[1] := 1;
>      RETURN test;
> END ' LANGUAGE 'plpgsql';
>
> regression=# select test_arrays();
>   test_arrays
> -------------
>   {1}
> (1 row)
>
> Note that you have to initialize "test" to an empty array, because
> otherwise you are trying to add an element to a NULL::int4[], the result
> of which is still NULL.
>
> Joe
>
>

"Donald Fraser" <demolish@cwgsy.net> writes:
> CREATE OR REPLACE FUNCTION test_arrays() RETURNS int4[] AS '
> DECLARE
>      test int4[];
> BEGIN
>     test[1] := 1;
>     RETURN test;
> END ' LANGUAGE 'plpgsql';

> If I try to execute this function I get:
> WARNING:  plpgsql: ERROR during compile of test_arrays near line 5
> ERROR:  syntax error at or near "["

Existing releases of plpgsql don't support assignment to array elements.
I believe Joe Conway has fixed this for 7.4, though.

            regards, tom lane