From: Kai Hessing <kai.hessing@hobsons.de>
Subject: Re: can a function return a virtual table?
Date: Wed, 20 Apr 2005 09:23:24 +0200
Lines: 36
Message-ID: <3cmebiF6n5ocpU1@individual.net>
References: <3c4m1vF6b6e6aU1@individual.net>
	<20050419024313.GA75425@winnie.fuhr.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206)
In-Reply-To: <20050419024313.GA75425@winnie.fuhr.org>
To: pgsql-sql@postgresql.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...