Re: 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 <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