From: Kai Hessing <kai.hessing@hobsons.de>
To: pgsql-sql@postgresql.org
Subject: Re: can a function return a virtual table?
Date: Wed, 20 Apr 2005 09:23:24 +0200
Message-ID: <3cmebiF6n5ocpU1@individual.net> (raw)
In-Reply-To: <20050419024313.GA75425@winnie.fuhr.org>
References: <3c4m1vF6b6e6aU1@individual.net>
<20050419024313.GA75425@winnie.fuhr.org>
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...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kai.hessing@hobsons.de
Subject: Re: can a function return a virtual table?
In-Reply-To: <3cmebiF6n5ocpU1@individual.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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