Обсуждение: Plpgsql function with unknown number of args
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.
Mike
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
manyas 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;
>
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: >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. > >