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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match