pass an array as parameter to a function

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