Обсуждение: can a function return a virtual table?
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); 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*). -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Bei seinen Handlungen ist vorzubedenken besser als nachzubedenken. (Demokrit, um 460 v. Chr.)
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-expressions.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/
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 |
On Tue, Apr 19, 2005 at 09:34:43 +0200, KÖPFERL Robert <robert.koepferl@sonorys.at> wrote: > 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? If the function isn't written using a language of SQL, it isn't going to optimize well when you only want to see part of the table.
Hi, and thanks for the answer ;) (*upps* just noticed, that I sent the answer accidently direct to poster^^ *sorry*) Michael Fuhr schrieb: >> 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. *yeah* sure you're right. I shortened it to much^^ >> In this simple example you could omit the subquery altogether: >> SELECT xyz, abc FROM tablex WHERE status > -1; It is not about such a simple example. When joining tables (especially with outer joins) it isn't trivial ;) >> 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): Thanks, I think this is what I've searched for (btw. we use 7.3). But what I want to do is: CREATE FUNCTION active(char) RETURNS setof $1 AS ' SELECT * FROM $1 WHERE status>-1; ' LANGUAGE SQL; But this does not work. So I'll use views instead ;) It also should be more performant. Thanks...
Kai, > 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*). http://techdocs.postgresql.org/guides/SetReturningFunctions Beware, though, that query plan estimation for SRFs is less accurate than for regular subqueries, so you could end up with unnecessarily slow query execution. Test! -- Josh Berkus Aglio Database Solutions San Francisco