Skip site navigation (1) Skip section navigation (2)

Re: Function returning setof taking parameters from another table

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Marcin Stępnicki <mstepnicki(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Function returning setof taking parameters from another table
Date: 2008-08-01 08:47:28
Message-ID: 4892CDA0.5010304@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Anoop GDate: 2008-08-01 09:41:40
Subject: How to change a view's owner in postgres
Previous:From: Steve MidgleyDate: 2008-08-01 03:35:15
Subject: Re: Problem with ORDER BY and DISTINCT ON

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group