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