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

On Wed, 14 Mar 2007 18:50:27 +0000, work(at)ashleymoran(dot)me(dot)uk (Ashley
Moran) wrote:
in <B5B55C8F-8C52-48A5-B8D9-8B071681299D(at)ashleymoran(dot)me(dot)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