Обсуждение: Set generating functions and subqueries
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
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 conditionis 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
Hi, Tom, Tom Lane wrote: > 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. That's interesting to know, and it seems to be a clean design. > 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. AFAICS, it can be replaced with JOINs: select * FROM (SELECT 'othercol' AS other) as foo CROSS JOIN (SELECT generate_series(1,2) as a) as fop CROSS JOIN (SELECT generate_series(3,4) as b) AS foq; other | a | b ----------+---+---othercol | 1 | 3othercol | 2 | 3othercol | 1 | 4othercol | 2 | 4 (4 rows) > No kidding. I wasn't kidding, I just wanted to point out the different behaviour between equal-length and inequal-length sequences. Thanks, 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
Markus Schaber <schabi@logix-tt.com> writes: > Tom Lane wrote: >> 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. > AFAICS, it can be replaced with JOINs: Not really; the case where joins don't help is where you want to generate the SRF's output for each of the values appearing in a table. There's an example in "SQL Functions Returning Sets" in the manual: http://www.postgresql.org/docs/8.1/static/xfunc-sql.html#AEN31646 regards, tom lane