Message-ID: <ED4E30DD9C43D5118DFB00508BBBA76EB166BC@neptun.sonorys.at>
From: =?ISO-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at>
To: 
Cc: pgsql-sql@postgresql.org
Subject: Re: can a function return a virtual table?
Date: Tue, 19 Apr 2005 09:34:43 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-1"

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:mike@fuhr.org]
|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
|