Re: [HACKERS] ROWS FROM(): A Foolish (In)Consistency?
On Tue, Oct 20, 2015 at 11:16:13AM -0400, Robert Haas wrote: > On Tue, Oct 20, 2015 at 11:03 AM, David Fetter 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 > >> 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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ROWS FROM(): A Foolish (In)Consistency?
On Tue, Oct 20, 2015 at 11:03 AM, David Fetter 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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ROWS FROM(): A Foolish (In)Consistency?
On Tue, Oct 20, 2015 at 10:52:05AM -0400, Robert Haas wrote: > On Mon, Oct 19, 2015 at 8:02 PM, Jim Nasby 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() -- a10 b9 c8 d7 e6 f5 g4 into ROWS FROM(A(), B()) --- 1a 10 2b 9 3c 8 4d 7 5e 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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ROWS FROM(): A Foolish (In)Consistency?
On Mon, Oct 19, 2015 at 8:02 PM, Jim Nasby 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ROWS FROM(): A Foolish (In)Consistency?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ROWS FROM(): A Foolish (In)Consistency?
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 | 4 3 | 3 5 | 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 | 2 3 | 3 | 4 5 | (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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers