Thread: Function returning setof taking parameters from another table

Hello.

I've got a function which returns set of records:

select * from f_test(123);

param |  val1 |  val2
--------------------------- 123   |   1  |  17 123   |   2   |  18



I'd like to execute it multiple times with parameters from other
query, like (it doesn't work of course):

select * from f_test(x.id)
join x on (x.id in (123,124,125));

in order to get:

param |  val1 |  val2
--------------------------- 123   |   1   |  17 123   |   2   |  18 124   |   4   |  179 125   |   13 |  18 125   |
15|  12 125   |   14 |  112
 

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.

Is there another way?

Regards,
Marcin

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