Message-ID: <44116ED2.9060901@logix-tt.com>
Date: Fri, 10 Mar 2006 13:19:30 +0100
From: Markus Schaber <schabi@logix-tt.com>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: Set generating functions and subqueries
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hello,

Today, I stumbled across the following:

postgres=# select generate_series(1,2),'othercol';
 generate_series | ?column?
-----------------+----------
               1 | othercol
               2 | othercol
(2 rows)

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?

It is easy to refactor a subquery into a set-returning function, so I
think this violates the principle of orthogonality.

But there may be subtle reasons of ambiguity here I don't see right now.

(I know that usually, a JOIN would be the right thing to do here, but
I'm just curious why multi-row subqueries are not allowed.)


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

postgres=# select generate_series(1,2),generate_series(3,4),'othercol';
         generate_series | generate_series | ?column?
-----------------+-----------------+----------
               1 |               3 | othercol
               2 |               4 | othercol
(2 rows)

postgres=# select generate_series(1,2),generate_series(3,5),'othercol';
 generate_series | generate_series | ?column?
-----------------+-----------------+----------
               1 |               3 | othercol
               2 |               4 | othercol
               1 |               5 | othercol
               2 |               3 | othercol
               1 |               4 | othercol
               2 |               5 | othercol
(6 rows)

Is there any way to indicate that I want the cross-product if both
set-returning functions have the same length? This could lead to strange
effects if the sets have varying lengths otherwhise.
(One workaround would be to join two selects, each one having one
set-returning function.)

Btw, it is not possible to trick PostgreSQL into accepting multi-row
selects this way:

postgres=# select (select generate_series(1,2)),generate_series(3,4),'';
ERROR:  more than one row returned by a subquery used as an expression


Have fun,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org