pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcan a function return a virtual table?
6+ messages / 5 participants
[nested] [flat]
* can a function return a virtual table?
@ 2005-04-13 13:44 Kai Hessing <kai.hessing@hobsons.de>
0 siblings, 2 replies; 6+ messages in thread
From: Kai Hessing @ 2005-04-13 13:44 UTC (permalink / raw)
To: pgsql-sql
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.)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a function return a virtual table?
@ 2005-04-19 02:43 Michael Fuhr <mike@fuhr.org>
parent: Kai Hessing <kai.hessing@hobsons.de>
1 sibling, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2005-04-19 02:43 UTC (permalink / raw)
To: Kai Hessing <kai.hessing@hobsons.de>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a function return a virtual table?
@ 2005-04-19 07:34 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 1 reply; 6+ messages in thread
From: KÖPFERL Robert @ 2005-04-19 07:34 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
|
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a function return a virtual table?
@ 2005-04-19 11:11 Bruno Wolff III <bruno@wolff.to>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2005-04-19 11:11 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a function return a virtual table?
@ 2005-04-20 07:23 Kai Hessing <kai.hessing@hobsons.de>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 6+ messages in thread
From: Kai Hessing @ 2005-04-20 07:23 UTC (permalink / raw)
To: pgsql-sql
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...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a function return a virtual table?
@ 2005-04-23 18:00 Josh Berkus <josh@agliodbs.com>
parent: Kai Hessing <kai.hessing@hobsons.de>
1 sibling, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2005-04-23 18:00 UTC (permalink / raw)
To: Kai Hessing <kai.hessing@hobsons.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-04-23 18:00 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-13 13:44 can a function return a virtual table? Kai Hessing <kai.hessing@hobsons.de>
2005-04-19 02:43 ` Michael Fuhr <mike@fuhr.org>
2005-04-20 07:23 ` Kai Hessing <kai.hessing@hobsons.de>
2005-04-23 18:00 ` Josh Berkus <josh@agliodbs.com>
2005-04-19 07:34 Re: can a function return a virtual table? KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-04-19 11:11 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox