Re: How to write a function that manipulates a set of results

On Wed, 14 Mar 2007 18:50:27 +0000, work@ashleymoran.me.uk (Ashley
Moran) wrote:
 in <B5B55C8F-8C52-48A5-B8D9-8B071681299D@ashleymoran.me.uk>

>It's more complicated than that.  What we need to do is something
>along the lines of:
>
>results = SELECT * FROM foo();
>DELETE FROM results WHERE (some condition involving results);
>some_value = SELECT value FROM results WHERE (etc);
>
>and so on...
>
>All of which is easy with table variable, but I can't see how to
>translate it to PL/pgsql.  Is there any way to manipulate result sets
>in a set-based manner like this?

A table returning function or SRF can be used in joins with other tables
or subqueries.  In fact, you can use it in either of two formats:

If the SRF returns a native data type then you can use just the function
name.  Consider the function foo() which returns INTEGER.

CREATE OR REPLACE FUNCTION foo () RETURNS SETOF INTEGER AS

SELECT *
FROM
  foo() F INNER JOIN
  some_table T ON F=T.id;

If the SRF returns a composite type then you can use the function name
qualified by any of the members of the list of types.

SELECT *
FROM
  foo() F INNER JOIN
  some_table T ON F.num=T.id;

---
Stefan Berglund