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']);
