Re: Plpgsql function with unknown number of args - Mailing list pgsql-general

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