Plpgsql function with unknown number of args

In article <1806D1F73FCB7F439F2C842EE0627B1801C32853@usa0300ms01.na.xerox.net>,
"Relyea, Mike" <Mike.Relyea@xerox.com> writes:

I need to create my very first function. I'm using 8.0.2 and I need a
function that I can call (from my client app) with an unknown number of
criteria for a select query. The function will then return the results
of the query. In my mind, it would go something like what I've outlined
below. I realize that there are syntax mistakes etc, but this is just
an example:

CREATE TABLE mytable (
a INTEGER UNIQUE PRIMARY KEY,
b VARCHAR(100) NOT NULL,
);

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;

Select * from myfunc(1, 2, 3, 4) would then equate to SELECT a, b FROM
mytable WHERE (b = '1' OR b = '2' OR b = '3' OR b = '4');

Select * from myfunc(9, 21) would equate to SELECT a, b FROM mytable
WHERE (b = '9' OR b = '21');

My question is how do I do that? I've looked through the docs and can't
find what I'm looking for. I'm assuming this is possible because it's a
relatively simple task.

You can't have a variable number of args, but since all args have the
same type you can use an array. The return type is a set of mytable
rows; thus myfunc becomes something like

CREATE FUNCTION myfunc (TEXT[]) RETURNS SETOF mytable AS $$
SELECT *
FROM mytable
WHERE b = ANY ($1)
$$ LANGUAGE sql;

This function can be called like that:

SELECT *
FROM myfunc (ARRAY ['1', '2', '3', '4']);

SELECT *
FROM myfunc (ARRAY ['9', '21']);

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

Thanks for the input. This looks very promising. I have one further
question. My SQL statement is going to pull data from more than one
table in a relatively complex query. How do I cast the RETURNS portion
of the function? Again, I can't find what I'm looking for in the docs.
I've included an actual sample SQL statement. I will only be changing
the first portion of the WHERE clause.

SELECT "PrintSamples"."MachineID", "PrintSamples"."PrintCopyID",
"tblColors"."Color", "tblBlockAC"."AreaCoverage",
"ParameterValues"."ParameterValue" AS "Mottle_NMF"
FROM ("AnalysisModules"
INNER JOIN ("tblColors"
INNER JOIN ("Targets"
INNER JOIN (("tblTPNamesAndColors"
INNER JOIN "PrintSamples"
ON "tblTPNamesAndColors"."TestPatternName" =
"PrintSamples"."TestPatternName")
INNER JOIN (("DigitalImages"
INNER JOIN "PrintSampleAnalyses"
ON "DigitalImages"."ImageID" = "PrintSampleAnalyses"."ImageID")
INNER JOIN ("ParameterNames"
INNER JOIN ("Measurements"
INNER JOIN "ParameterValues"
ON "Measurements"."MeasurementID" =
"ParameterValues"."MeasurementID")
ON "ParameterNames"."ParameterID" =
"ParameterValues"."ParameterID")
ON "PrintSampleAnalyses"."psaID" = "Measurements"."psaID")
ON "PrintSamples"."PrintSampleID" =
"DigitalImages"."PrintSampleID")
ON "Targets"."TargetID" = "Measurements"."TargetID")
ON "tblColors"."ColorID" = "tblTPNamesAndColors"."ColorID")
ON "AnalysisModules"."MetricID" = "Measurements"."MetricID")
INNER JOIN "tblBlockAC"
ON "Targets"."TargetID" = "tblBlockAC"."TargetID"
WHERE (("PrintSamples"."MachineID" = '2167' OR
"PrintSamples"."MachineID" = '2168' OR "PrintSamples"."MachineID" =
'2169')
AND (("tblBlockAC"."AreaCoverage")=100 Or
("tblBlockAC"."AreaCoverage")=60 Or ("tblBlockAC"."AreaCoverage")=40)
AND (("AnalysisModules"."AnalysisModuleName")='NMF')
AND (("ParameterNames"."ParameterName")='NMF'))
ORDER BY "PrintSamples"."MachineID", "PrintSamples"."PrintCopyID",
"tblColors"."ColorID";

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Harald Fuchs
Sent: Monday, April 18, 2005 3:49 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Plpgsql function with unknown number of args

In article
<1806D1F73FCB7F439F2C842EE0627B1801C32853@usa0300ms01.na.xerox.net>,
"Relyea, Mike" <Mike.Relyea@xerox.com> writes:

I need to create my very first function. I'm using 8.0.2 and I need a
function that I can call (from my client app) with an unknown number

of

criteria for a select query. The function will then return the

results

of the query. In my mind, it would go something like what I've

outlined

below. I realize that there are syntax mistakes etc, but this is just
an example:

CREATE TABLE mytable (
a INTEGER UNIQUE PRIMARY KEY,
b VARCHAR(100) NOT NULL,
);

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;

Select * from myfunc(1, 2, 3, 4) would then equate to SELECT a, b FROM
mytable WHERE (b = '1' OR b = '2' OR b = '3' OR b = '4');

Select * from myfunc(9, 21) would equate to SELECT a, b FROM mytable
WHERE (b = '9' OR b = '21');

My question is how do I do that? I've looked through the docs and

can't

find what I'm looking for. I'm assuming this is possible because it's

a

relatively simple task.

You can't have a variable number of args, but since all args have the
same type you can use an array. The return type is a set of mytable
rows; thus myfunc becomes something like

CREATE FUNCTION myfunc (TEXT[]) RETURNS SETOF mytable AS $$
SELECT *
FROM mytable
WHERE b = ANY ($1)
$$ LANGUAGE sql;

This function can be called like that:

SELECT *
FROM myfunc (ARRAY ['1', '2', '3', '4']);

SELECT *
FROM myfunc (ARRAY ['9', '21']);

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Relyea, Mike" <Mike.Relyea@xerox.com> writes:

Thanks for the input. This looks very promising. I have one further
question. My SQL statement is going to pull data from more than one
table in a relatively complex query. How do I cast the RETURNS portion
of the function?

In current releases, you'll have to create a named composite type that
matches what you want to return, and declare the function as returning
that type.

(PG 8.1 will have a facility for named OUT parameters that lets you
avoid the notational overhead of inventing a composite type, although
what happens under-the-hood is not really very different.)

regards, tom lane

You don't have to cast it as anything, just return a refcursor from your
function.

Say you return a refcursor called return_cursor

select myfunction(your_in_array);
fetch all from return_cursor;

If you are calling from a development environment, you put the return
value of the fuction (the refcursor name) into a variable, then
dynamicly build the fetch
all statement from return value of the function. This must be done in
the context of a transaction, i.e. both statements must must be executed
in the same transaction.

The refcursors work really well and are very flexible.

Relyea, Mike wrote: