Thread: passing array as argument and returning an array in plpgsql

Hi all,
       I am using postgresql7.4. How to handle arrays in plpgsql. How can
I pass an array. Is it possible to retrieve values from an array by
indexing it like

argument : '{1,2,3}'
Return value : varchar array

Variables :
---------

a alias for $1
b _varchar

Usage :
-----

b[1] = a[1];
b[2] = a[2];

return b;

Is it possible.

TIA,

--
regards,
Deepa K

hello

It is possible

CREATE OR REPLACE FUNCTION foo(anyarray) RETURNS anyarray AS '
DECLARE b integer[];
BEGIN b := $1; b[1] := b[1] + 1;
  RETURN b;
END;
' LANGUAGE plpgsql;

testdb011=> select foo(ARRAY[1,2,3]);
   foo
---------
 {2,2,3}
(1 řádka)

Regards
Pavel

I got this when I was searching for something else.  I will forward this
to you.
http://archives.postgresql.org/pgsql-general/2003-11/msg00852.php
CREATE or REPLACE FUNCTION foo(integer[]) RETURNS int AS
'DECLARE
        a alias for $1;
        index   integer := 1;
        total   integer := 0;
BEGIN
        WHILE a[index] > 0
        LOOP
                total := total + a[index];
                index := index + 1;
        END LOOP;

        RETURN total;
        END;
' LANGUAGE 'plpgsql';



test=> select foo('{1,2}');
 foo
-----
   3
(1 row)
On Tue, 2003-12-16 at 03:25, K. Deepa wrote:
> Hi all,
>        I am using postgresql7.4. How to handle arrays in plpgsql. How can
> I pass an array. Is it possible to retrieve values from an array by
> indexing it like
>
> argument : '{1,2,3}'
> Return value : varchar array
>
> Variables :
> ---------
>
> a alias for $1
> b _varchar
>
> Usage :
> -----
>
> b[1] = a[1];
> b[2] = a[2];
>
> return b;
>
> Is it possible.
>
> TIA,
--
Jenny Zhang
Open Source Development Lab
12725 SW Millikan Way, Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31

Jenny Zhang wrote:


Jenny,

although this is old but i find it worth mentioning tom's
comment on it. I hit your comment on facing similar issue.

The cited example is pretty iffy since it assumes that the valid array
entries are all > 0.  In recent PG version you can use the array_upper
and array_lower functions instead:

    for i in array_lower(a,1) .. array_upper(a,1) loop
        -- do something with a[i]
    end loop;

            regards, tom lane





>I got this when I was searching for something else.  I will forward this
>to you.
>http://archives.postgresql.org/pgsql-general/2003-11/msg00852.php
>CREATE or REPLACE FUNCTION foo(integer[]) RETURNS int AS
>'DECLARE
>        a alias for $1;
>        index   integer := 1;
>        total   integer := 0;
>BEGIN
>        WHILE a[index] > 0
>        LOOP
>                total := total + a[index];
>                index := index + 1;
>        END LOOP;
>
>        RETURN total;
>        END;
>' LANGUAGE 'plpgsql';
>
>
>
>test=> select foo('{1,2}');
> foo
>-----
>   3
>(1 row)
>On Tue, 2003-12-16 at 03:25, K. Deepa wrote:
>
>
>>Hi all,
>>       I am using postgresql7.4. How to handle arrays in plpgsql. How can
>>I pass an array. Is it possible to retrieve values from an array by
>>indexing it like
>>
>>argument : '{1,2,3}'
>>Return value : varchar array
>>
>>Variables :
>>---------
>>
>>a alias for $1
>>b _varchar
>>
>>Usage :
>>-----
>>
>>b[1] = a[1];
>>b[2] = a[2];
>>
>>return b;
>>
>>Is it possible.
>>
>>TIA,
>>
>>