pass an array as parameter to a function

Hello list,

Is there a way to pass a collection of values (array) to a a function in
plpgsql?

Thanks in advance


--
Sinceramente,
Josué Maldonado.
"Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado
yo" (Barón Vladimir Harkonnen)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

re: pass an array as parameter to a function

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

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend