Re: pass an array as parameter to a function - Mailing list pgsql-general

On 2004.09.11 12:18 Josué Maldonado wrote:
> Hello list,
>
> Is there a way to pass a collection of values (array) to a a function
> in
> plpgsql?
>
> Thanks in advance

Just declare the argument with [] after the datatype.

However, you won't be able to modify the array elements,
nor can you create your own array, unless you construct
the external representation in a string and then typecast
it for assignment into the appropriate array variable.

(Postgresql 7.3.)

-- Test for passing arrays.

CREATE FUNCTION calling_arrays()
   RETURNS INT
   LANGUAGE plpgsql
   AS '

   DECLARE
     a INT[];
     b TEXT;

   BEGIN
-- This does not work in 7.3.
--    a[1] := 1;
--    a[2] := 2;
     a := ''{3,4}'';
--    a[2] := 5;
     b := ''{6,7,8}'';
     a := b;
     PERFORM calling_arrays2(a);
     RETURN 0;
   END;
';



CREATE FUNCTION calling_arrays2(INT[])
   RETURNS INT
   LANGUAGE plpgsql
   AS '

   DECLARE
     nother ALIAS FOR $1;
     a INT;
     b INT;

   BEGIN
     a := nother[1];
     b := nother[2];
-- This does not work.
--    RAISE NOTICE ''first %; second %'', nother[1], nother[2];
     RAISE NOTICE ''first %; second %'', a, b;
     RETURN 0;
   END;
';

SELECT calling_arrays();

DROP FUNCTION calling_arrays();
DROP FUNCTION calling_arrays2(INT[]);



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