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
