Waiting for 9.4 – Support multi-argument UNNEST(), and TABLE() syntax for multiple functions.

On 22nd of November, Tom Lane committed patch:

Support multi-argument UNNEST(), and TABLE() syntax for multiple functions.
 
This patch adds the ability to write TABLE( function1(), function2(), ...)
as a single FROM-clause entry.  The result is the concatenation of the
first row from each function, followed by the second row from each
function, etc; with NULLs inserted if any function produces fewer rows than
others.  This is believed to be a much more useful behavior than what
Postgres currently does with multiple SRFs in a SELECT list.
 
This syntax also provides a reasonable way to combine use of column
definition lists with WITH ORDINALITY: put the column definition list
inside TABLE(), where it's clear that it doesn't control the ordinality
column as well.
 
Also implement SQL-compliant multiple-argument UNNEST(), by turning
UNNEST(a,b,c) into TABLE(unnest(a), unnest(b), unnest(c)).
 
The SQL standard specifies TABLE() with only a single function, not
multiple functions, and it seems to require an implicit UNNEST() which is
not what this patch does.  There may be something wrong with that reading
of the spec, though, because if it's right then the spec's TABLE() is just
a pointless alternative spelling of UNNEST().  After further review of
that, we might choose to adopt a different syntax for what this patch does,
but in any case this functionality seems clearly worthwhile.
 
Andrew Gierth, reviewed by Zoltán Böszörményi and Heikki Linnakangas, and
significantly revised by me

Continue reading Waiting for 9.4 – Support multi-argument UNNEST(), and TABLE() syntax for multiple functions.

Waiting for 9.4 – Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)

On 29th of July, Greg Stark committed patch:

Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)
 
Author: Andrew Gierth, David Fetter
Reviewers: Dean Rasheed, Jeevan Chalke, Stephen Frost

Continue reading Waiting for 9.4 – Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)

Waiting for 9.3 – Implement SQL-standard LATERAL subqueries.

On 7th of August, Tom Lane committed patch:

Implement SQL-standard LATERAL subqueries.
 
This patch implements the standard syntax of LATERAL attached to a
sub-SELECT in FROM, and also allows LATERAL attached to a function in FROM,
since set-returning function calls are expected to be one of the principal
use-cases.
 
The main change here is a rewrite of the mechanism for keeping track of
which relations are visible for column references while the FROM clause is
being scanned.  The parser "namespace" lists are no longer lists of bare
RTEs, but are lists of ParseNamespaceItem structs, which carry an RTE
pointer as well as some visibility-controlling flags.  Aside from
supporting LATERAL correctly, this lets us get rid of the ancient hacks
that required rechecking subqueries and JOIN/ON and function-in-FROM
expressions for invalid references after they were initially parsed.
Invalid column references are now always correctly detected on sight.
 
In passing, remove assorted parser error checks that are now dead code by
virtue of our having gotten rid of add_missing_from, as well as some
comments that are obsolete for the same reason.  (It was mainly
add_missing_from that caused so much fudging here in the first place.)
 
The planner support for this feature is very minimal, and will be improved
in future patches.  It works well enough for testing purposes, though.
 
catversion bump forced due to new field in RangeTblEntry.

Continue reading Waiting for 9.3 – Implement SQL-standard LATERAL subqueries.