X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 1529C9DC83B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Mar 2006 10:48:48 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 80214-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Mar 2006 10:48:52 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id EA2769DC821
	for <pgsql-sql@postgresql.org>; Fri, 10 Mar 2006 10:48:45 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k2AEmm82005143;
	Fri, 10 Mar 2006 09:48:48 -0500 (EST)
To: Markus Schaber <schabi@logix-tt.com>
cc: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: Re: Set generating functions and subqueries 
In-reply-to: <44116ED2.9060901@logix-tt.com> 
References: <44116ED2.9060901@logix-tt.com>
Comments: In-reply-to Markus Schaber <schabi@logix-tt.com>
	message dated "Fri, 10 Mar 2006 13:19:30 +0100"
Date: Fri, 10 Mar 2006 09:48:48 -0500
Message-ID: <5142.1142002128@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.112 required=5 tests=[AWL=0.112]
X-Spam-Score: 0.112
X-Spam-Level: 
X-Archive-Number: 200603/150
X-Sequence-Number: 24496

Markus Schaber <schabi@logix-tt.com> writes:
> postgres=# select (select generate_series(1,2)),'othercol';
> ERROR:  more than one row returned by a subquery used as an expression

> So it seems that set-returning functions "blow up" the resultset by
> duplicating rows - so why is this not allowed for subqueries?

The behavior of the subquery expression is dictated by the SQL spec:

         1) If the cardinality of a <scalar subquery> or a <row subquery> is
            greater than 1, then an exception condition is raised: cardinal-
            ity violation.

The fact that the other form is even allowed is more of a holdover from
PostQUEL than something we have consciously decided is a good idea.
(IMHO it's actually a fairly *bad* idea, because it does not work nicely
when there's more than one SRF in the same targetlist.)  It'll probably
get phased out someday, if we can find a way to replace the
functionality.  I seem to recall speculating that SQL2003's LATERAL
tables might do the job.

> Btw, having several set-returning functions with equal or different set
> lengths produce interesting results:

No kidding.

			regards, tom lane