Re: How to write a function that manipulates a set of results
От | Stefan Berglund |
---|---|
Тема | Re: How to write a function that manipulates a set of results |
Дата | |
Msg-id | b0mgv2pu89mlvubrm31ouvu9oj9090mc4p@4ax.com обсуждение исходный текст |
Ответ на | How to write a function that manipulates a set of results (Ashley Moran <work@ashleymoran.me.uk>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: