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(dot)Nasby(at)bluetreble(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate