Re: [SQL] Function returning setof taking parameters from another table

Marcin Stępnicki wrote:

> So far the only method I can think of is to use union all with
> different parametrs, like:
>
> select * from f_test(123)
> union all
> select * from f_test(124)
> union all
> select * from f_test(125);
>
> But it is not flexible, I'd like to have parameters stored in another table.
>
> I can also write a pl/pg function, create a string like this with
> unions and EXECUTE it. However, it seems ugly to me.

One method is a PL/PgSQL set-returning function that loops over the
arguments, executes the function for each, and uses an inner loop to
RETURN NEXT the results.

Another way is to use Pg's support for expanding set-returning functions
in SELECT clauses. In simple cases you can get effects similar to the
use of a series of UNION operations. For example:

CREATE TYPE intpair AS (first INTEGER, last INTEGER);

--
-- Make a function that returns `$1' pairs of integers from 1 to $1
--
CREATE FUNCTION gsp (INTEGER) RETURNS SETOF intpair AS $$
SELECT generate_series(1,$1), generate_series(1,$1);
$$ LANGUAGE SQL;

--
-- Return a set of integer pairs from (1,1) to (4,4)
--
SELECT gsp(4);

--
-- Just run this one to see what it does.
--
SELECT x, gsp(x) FROM generate_series(1,10) as x;

--
Craig Ringer

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql