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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 <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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers