Thread: ROWS FROM(): A Foolish (In)Consistency?
Folks, As I was learning how best to add native weighted statistics, coming soon, I noticed that our ROWS FROM() constructor takes only set-returning functions, gluing the outputs together side by side without a join condition of any kind. This is a handy capability, which I don't find elsewhere in our code, modulo horrible things involving WITH ORDINALITY and FULL JOIN. Did I miss something? If not, would it make sense to allow every set-returning construct inside ROWS FROM(), rather than just some of them? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Mon, Oct 19, 2015 at 10:24:37AM -0700, David Fetter wrote: > Folks, > > As I was learning how best to add native weighted statistics, coming > soon, I noticed that our ROWS FROM() constructor takes only > set-returning functions, gluing the outputs together side by side > without a join condition of any kind. This is a handy capability, > which I don't find elsewhere in our code, modulo horrible things > involving WITH ORDINALITY and FULL JOIN. > > Did I miss something? > > If not, would it make sense to allow every set-returning construct > inside ROWS FROM(), rather than just some of them? Based on off-list feedback, this proposal is not as clear as I would have liked. Here's what we get with SRFs: SELECT * FROM ROWS FROM (generate_series(1,5,2),generate_series(4,1,-1)) AS t(i,j);i | j ---+---1 | 43 | 35 | 2 | 1 (4 rows) Here's a similar construct, but not really, from LATERAL: SELECT * FROM generate_series(1,5,2) AS s(i) FULL JOIN LATERAL generate_series(4,1,-1) AS t(j) ON s.i=t.j;i | j ---+---1 | 1 | 23 | 3 | 45 | (5 rows) What I'd like to do is lift the restriction on ROWS FROM(), which currently requires that the stuff inside the parentheses set-returning functions, so constructs something like the following would actually work: SELECT * FROM ROWS FROM ( (VALUES (...), ..., (...)), (SELECT ... ), (INSERT ... RETURNING ... ), my_srf() ) AS t(...) would actually work. What say? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On 10/19/15 1:07 PM, David Fetter wrote: > What I'd like to do is lift the restriction on ROWS FROM(), which > currently requires that the stuff inside the parentheses set-returning > functions, so constructs something like the following would actually work: > > SELECT * > FROM > ROWS FROM ( > (VALUES (...), ..., (...)), > (SELECT ... ), > (INSERT ... RETURNING ... ), > my_srf() > ) > AS t(...) > > would actually work. There's been a few places where I would have found that handy. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
On Mon, Oct 19, 2015 at 8:02 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > On 10/19/15 1:07 PM, David Fetter wrote: >> >> What I'd like to do is lift the restriction on ROWS FROM(), which >> currently requires that the stuff inside the parentheses set-returning >> functions, so constructs something like the following would actually work: >> >> SELECT * >> FROM >> ROWS FROM ( >> (VALUES (...), ..., (...)), >> (SELECT ... ), >> (INSERT ... RETURNING ... ), >> my_srf() >> ) >> AS t(...) >> >> would actually work. > > > There's been a few places where I would have found that handy. Why not just use a subquery with UNION ALL? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Oct 20, 2015 at 10:52:05AM -0400, Robert Haas wrote: > On Mon, Oct 19, 2015 at 8:02 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > > On 10/19/15 1:07 PM, David Fetter wrote: > >> > >> What I'd like to do is lift the restriction on ROWS FROM(), which > >> currently requires that the stuff inside the parentheses set-returning > >> functions, so constructs something like the following would actually work: > >> > >> SELECT * > >> FROM > >> ROWS FROM ( > >> (VALUES (...), ..., (...)), > >> (SELECT ... ), > >> (INSERT ... RETURNING ... ), > >> my_srf() > >> ) > >> AS t(...) > >> > >> would actually work. > > > > > > There's been a few places where I would have found that handy. > > Why not just use a subquery with UNION ALL? Because UNION ALL glues the queries vertically, not horizontally. ROWS FROM() turns things like: A() -- 1 2 3 4 5 and B() -- a 10 b 9 c 8 d 7 e 6 f 5 g 4 into ROWS FROM(A(), B()) ------------------- 1 a 10 2 b 9 3 c 8 4 d 7 5 e 6 NULL f 5 NULL g 4 UNION ALL turns combining A() and B() into an error because the output row types don't match. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Oct 20, 2015 at 11:03 AM, David Fetter <david@fetter.org> wrote: > On Tue, Oct 20, 2015 at 10:52:05AM -0400, Robert Haas wrote: >> On Mon, Oct 19, 2015 at 8:02 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: >> > On 10/19/15 1:07 PM, David Fetter wrote: >> >> >> >> What I'd like to do is lift the restriction on ROWS FROM(), which >> >> currently requires that the stuff inside the parentheses set-returning >> >> functions, so constructs something like the following would actually work: >> >> >> >> SELECT * >> >> FROM >> >> ROWS FROM ( >> >> (VALUES (...), ..., (...)), >> >> (SELECT ... ), >> >> (INSERT ... RETURNING ... ), >> >> my_srf() >> >> ) >> >> AS t(...) >> >> >> >> would actually work. >> > >> > >> > There's been a few places where I would have found that handy. >> >> Why not just use a subquery with UNION ALL? > > Because UNION ALL glues the queries vertically, not horizontally. Ah. I get it now. Thanks for clarifying. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Oct 20, 2015 at 11:16:13AM -0400, Robert Haas wrote: > On Tue, Oct 20, 2015 at 11:03 AM, David Fetter <david@fetter.org> wrote: > > On Tue, Oct 20, 2015 at 10:52:05AM -0400, Robert Haas wrote: > >> On Mon, Oct 19, 2015 at 8:02 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > >> > On 10/19/15 1:07 PM, David Fetter wrote: > >> >> > >> >> What I'd like to do is lift the restriction on ROWS FROM(), which > >> >> currently requires that the stuff inside the parentheses set-returning > >> >> functions, so constructs something like the following would actually work: > >> >> > >> >> SELECT * > >> >> FROM > >> >> ROWS FROM ( > >> >> (VALUES (...), ..., (...)), > >> >> (SELECT ... ), > >> >> (INSERT ... RETURNING ... ), > >> >> my_srf() > >> >> ) > >> >> AS t(...) > >> >> > >> >> would actually work. > >> > > >> > > >> > There's been a few places where I would have found that handy. > >> > >> Why not just use a subquery with UNION ALL? > > > > Because UNION ALL glues the queries vertically, not horizontally. > > Ah. I get it now. Thanks for clarifying. Sorry that didn't start out clearer. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate