From 2378d79ab29865f59245744beb8f04a3ce56d2ae Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 26 Jan 2013 16:18:42 -0500 Subject: Make LATERAL implicit for functions in FROM. The SQL standard does not have general functions-in-FROM, but it does allow UNNEST() there (see the production), and the semantics of that are defined to include lateral references. So spec compliance requires allowing lateral references within UNNEST() even without an explicit LATERAL keyword. Rather than making UNNEST() a special case, it seems best to extend this flexibility to any function-in-FROM. We'll still allow LATERAL to be written explicitly for clarity's sake, but it's now a noise word in this context. In theory this change could result in a change in behavior of existing queries, by allowing what had been an outer reference in a function-in-FROM to be captured by an earlier FROM-item at the same level. However, all pre-9.3 PG releases have a bug that causes them to match variable references to earlier FROM-items in preference to outer references (and then throw an error). So no previously-working query could contain the type of ambiguity that would risk a change of behavior. Per a suggestion from Andrew Gierth, though I didn't use his patch. --- doc/src/sgml/queries.sgml | 20 ++++++++++++++++---- doc/src/sgml/ref/select.sgml | 41 ++++++++++++++++++++++++++++------------- 2 files changed, 44 insertions(+), 17 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index bcee9468240..caa9f1b3389 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -717,14 +717,24 @@ SELECT * - Subqueries and table functions appearing in FROM can be + Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. - (Without LATERAL, each FROM item is + (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) + + + + Table functions appearing in FROM can also be + preceded by the key word LATERAL, but for functions the + key word is optional; the function's arguments can contain references + to columns provided by preceding FROM items in any case. + + + A LATERAL item can appear at top level in the - FROM list, or within a JOIN tree; in the latter + FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of. @@ -770,7 +780,9 @@ FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1, polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id; - or in several other equivalent formulations. + or in several other equivalent formulations. (As already mentioned, + the LATERAL key word is unnecessary in this example, but + we use it for clarity.) diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 26d511fad8c..0f9d52753d8 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -504,18 +504,28 @@ TABLE [ ONLY ] table_name [ * ] LATERAL - The LATERAL key word can precede a - sub-SELECT or function-call FROM - item. This allows the sub-SELECT or function - expression to refer to columns of FROM items that appear - before it in the FROM list. (Without - LATERAL, each FROM item is evaluated - independently and so cannot cross-reference any other - FROM item.) A LATERAL item can - appear at top level in the FROM list, or within a - JOIN tree; in the latter case it can also refer to any - items that are on the left-hand side of a JOIN that it is - on the right-hand side of. + + The LATERAL key word can precede a + sub-SELECT FROM item. This allows the + sub-SELECT to refer to columns of FROM + items that appear before it in the FROM list. (Without + LATERAL, each sub-SELECT is + evaluated independently and so cannot cross-reference any other + FROM item.) + + + + LATERAL can also precede a function-call + FROM item, but in this case it is a noise word, because + the function expression can refer to earlier FROM items + in any case. + + + + A LATERAL item can appear at top level in the + FROM list, or within a JOIN tree. In the + latter case it can also refer to any items that are on the left-hand + side of a JOIN that it is on the right-hand side of. @@ -1738,7 +1748,12 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; sub-SELECT; that is, the syntax FROM func(...) alias is approximately equivalent to - FROM (SELECT func(...)) alias. + FROM LATERAL (SELECT func(...)) alias. + Note that LATERAL is considered to be implicit; this is + because the standard requires LATERAL semantics for an + UNNEST() item in FROM. + PostgreSQL treats UNNEST() the + same as other set-returning functions. -- cgit v1.2.3