That was a nice answer - rather compleete. However at least I am questioning myself for a long time about what happens if one does a select from a SRF. The function may return millions of records (i.e. select * from x where a>1). Is this data streamed through the query process or does postgres create a temporary table.
An "explain select * from srf()" just returns a function invocation. :-/ How does this work? |-----Original Message----- |From: Michael Fuhr [mailto:[EMAIL PROTECTED] |Sent: Dienstag, 19. April 2005 04:43 |To: Kai Hessing |Cc: pgsql-sql@postgresql.org |Subject: Re: [SQL] can a function return a virtual table? | | |On Wed, Apr 13, 2005 at 03:44:25PM +0200, Kai Hessing wrote: |> |> This is the question i'm telling myself. It is because we |don't really |> delete table entries, just setting a status field to '-1'. So a valid |> select would look like: SELECT xyz, abc FROM (SELECT * FROM |tablex WHERE |> status > -1); | |I'll pick a nit and point out that the above isn't a valid query: | |test=> SELECT xyz, abc FROM (SELECT * FROM tablex WHERE status > -1); |ERROR: subquery in FROM must have an alias |HINT: For example, FROM (SELECT ...) [AS] foo. | |In this simple example you could omit the subquery altogether: | |SELECT xyz, abc FROM tablex WHERE status > -1; | |> It would be much nicer to have to write something like: |SELECT xyz, abc |> FROM active(tablex); where the function 'active(x)' returns a virtual |> table with all entries from table x where status is > -1. But sadly I |> have no idea how write such a function. Good old O'reilly |can't help (or |> i'm to dumb *g*). | |See the documentation for writing set-returning functions (SRFs). |The following links should get you started (if you're using a version |of PostgreSQL older than 8.0, then see the documentation for that |version): | |http://www.postgresql.org/docs/8.0/interactive/queries-table-ex |pressions.html#QUERIES-TABLEFUNCTIONS |http://www.postgresql.org/docs/8.0/interactive/xfunc-sql.html#AEN29503 |http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- |structures.html#AEN32823 | |Another possibility would be to use views. You'd need to create a |view on each table. | |http://www.postgresql.org/docs/8.0/interactive/tutorial-views.html |http://www.postgresql.org/docs/8.0/interactive/sql-createview.html | |Yet another possibility would be to move the inactive rows to a |separate table. You could reconstruct the original table with a |UNION of the active and inactive tables. | |http://www.postgresql.org/docs/8.0/interactive/queries-union.html | |-- |Michael Fuhr |http://www.fuhr.org/~mfuhr/ | |---------------------------(end of |broadcast)--------------------------- |TIP 8: explain analyze is your friend | ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
