Re: Plpgsql function with unknown number of args

[Tony Caduto <tony_caduto@xxxxxxxxxxxxxxxxxxxx>]

you coud pass in criteria as a delimted string, then
pull out each arg something like this


CREATE or REPLACE FUNCTION test_func( varchar)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
IN_ARRAY text[] ;
arg1 varchar;
arg2 varchar;
arg3 varchar


begin
IN_ARRAY = string_to_array($1,'~^~');

arg1    = IN_ARRAY[1]
arg2    = IN_ARRAY[2]
arg3    = IN_ARRAY[3]

Then call the function like this:

select test_func('bla^~^bla~^~yada');

This example does not return anything, but you could build a select from the args you passed in then return a cursor.

normally PG is limited to 32 args (unless special compiled to support more), but with this technique you can pass in as many as you want.

hope this helps.

Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com


> CREATE FUNCTION myfunc(criteria) RETURNS ???? AS $$
>
> BEGIN
>
>     BEGIN
>     FOREACH crit IN criteria
> 	critsql := "b = 'crit' OR "
>     NEXT crit
>     END;
>     
>     PERFORM "SELECT a, b FROM mytable WHERE (" critsql ");"
>
> END;
> $$ LANGUAGE plpgsql;

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match