- Added :meth:`.Select.lateral` and related constructs to allow

for the SQL standard LATERAL keyword, currently only supported
by Postgresql.  fixes #2857

6 files changed, 79 insertions, 10 deletions

diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py
index 43b06edd0..1193a1b0b 100644
--- a/lib/sqlalchemy/__init__.py
+++ b/lib/sqlalchemy/__init__.py
@@ -32,6 +32,7 @@ from .sql import (
     intersect,
     intersect_all,
     join,
+    lateral,
     literal,
     literal_column,
     modifier,
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index c0a3708d4..eb3449e40 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -345,6 +345,7 @@ syntaxes. It uses SQLAlchemy's hints mechanism::
     # DELETE FROM ONLY ...
     table.delete().with_hint('ONLY', dialect_name='postgresql')
 
+
 .. _postgresql_indexes:
 
 Postgresql-Specific Index Options
diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py
index a56e29cf5..7f7abacc2 100644
--- a/lib/sqlalchemy/sql/__init__.py
+++ b/lib/sqlalchemy/sql/__init__.py
@@ -46,6 +46,7 @@ from .expression import (
     intersect_all,
     join,
     label,
+    lateral,
     literal,
     literal_column,
     modifier,
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index c9a649748..3d2f02006 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1320,6 +1320,10 @@ class SQLCompiler(Compiled):
         else:
             return alias.original._compiler_dispatch(self, **kwargs)
 
+    def visit_lateral(self, lateral, **kw):
+        kw['lateral'] = True
+        return "LATERAL %s" % self.visit_alias(lateral, **kw)
+
     def get_render_as_alias_suffix(self, alias_name_text):
         return " AS " + alias_name_text
 
@@ -1532,7 +1536,7 @@ class SQLCompiler(Compiled):
         ('asfrom_froms', frozenset())
     ])
 
-    def _display_froms_for_select(self, select, asfrom):
+    def _display_froms_for_select(self, select, asfrom, lateral=False):
         # utility method to help external dialects
         # get the correct from list for a select.
         # specifically the oracle dialect needs this feature
@@ -1543,7 +1547,7 @@ class SQLCompiler(Compiled):
         correlate_froms = entry['correlate_froms']
         asfrom_froms = entry['asfrom_froms']
 
-        if asfrom:
+        if asfrom and not lateral:
             froms = select._get_display_froms(
                 explicit_correlate_froms=correlate_froms.difference(
                     asfrom_froms),
@@ -1559,6 +1563,7 @@ class SQLCompiler(Compiled):
                      compound_index=0,
                      nested_join_translation=False,
                      select_wraps_for=None,
+                     lateral=False,
                      **kwargs):
 
         needs_nested_translation = \
@@ -1598,7 +1603,7 @@ class SQLCompiler(Compiled):
                     select, transformed_select)
             return text
 
-        froms = self._setup_select_stack(select, entry, asfrom)
+        froms = self._setup_select_stack(select, entry, asfrom, lateral)
 
         column_clause_args = kwargs.copy()
         column_clause_args.update({
@@ -1671,7 +1676,7 @@ class SQLCompiler(Compiled):
 
         self.stack.pop(-1)
 
-        if asfrom and parens:
+        if (asfrom or lateral) and parens:
             return "(" + text + ")"
         else:
             return text
@@ -1689,11 +1694,11 @@ class SQLCompiler(Compiled):
         hint_text = self.get_select_hint_text(byfrom)
         return hint_text, byfrom
 
-    def _setup_select_stack(self, select, entry, asfrom):
+    def _setup_select_stack(self, select, entry, asfrom, lateral):
         correlate_froms = entry['correlate_froms']
         asfrom_froms = entry['asfrom_froms']
 
-        if asfrom:
+        if asfrom and not lateral:
             froms = select._get_display_froms(
                 explicit_correlate_froms=correlate_froms.difference(
                     asfrom_froms),
@@ -1712,6 +1717,7 @@ class SQLCompiler(Compiled):
             'selectable': select,
         }
         self.stack.append(new_entry)
+
         return froms
 
     def _compose_select_body(
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 36f7f7fe1..97f74d4e4 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -16,12 +16,14 @@ class.
 
 __all__ = [
     'Alias', 'Any', 'All', 'ClauseElement', 'ColumnCollection', 'ColumnElement',
-    'CompoundSelect', 'Delete', 'FromClause', 'Insert', 'Join', 'Select',
+    'CompoundSelect', 'Delete', 'FromClause', 'Insert', 'Join', 'Lateral',
+    'Select',
     'Selectable', 'TableClause', 'Update', 'alias', 'and_', 'asc', 'between',
     'bindparam', 'case', 'cast', 'column', 'delete', 'desc', 'distinct',
     'except_', 'except_all', 'exists', 'extract', 'func', 'modifier',
     'collate', 'insert', 'intersect', 'intersect_all', 'join', 'label',
-    'literal', 'literal_column', 'not_', 'null', 'nullsfirst', 'nullslast',
+    'lateral', 'literal', 'literal_column', 'not_', 'null', 'nullsfirst',
+    'nullslast',
     'or_', 'outparam', 'outerjoin', 'over', 'select', 'subquery',
     'table', 'text',
     'tuple_', 'type_coerce', 'union', 'union_all', 'update', 'within_group']
@@ -45,9 +47,9 @@ from .base import ColumnCollection, Generative, Executable, \
     PARSE_AUTOCOMMIT
 
 from .selectable import Alias, Join, Select, Selectable, TableClause, \
-    CompoundSelect, CTE, FromClause, FromGrouping, SelectBase, \
+    CompoundSelect, CTE, FromClause, FromGrouping, Lateral, SelectBase, \
     alias, GenerativeSelect, subquery, HasCTE, HasPrefixes, HasSuffixes, \
-    Exists, ScalarSelect, TextAsFrom
+    lateral, Exists, ScalarSelect, TextAsFrom
 
 
 from .dml import Insert, Update, Delete, UpdateBase, ValuesBase
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 249d0c604..e299f067e 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -161,6 +161,28 @@ def alias(selectable, name=None, flat=False):
     return selectable.alias(name=name, flat=flat)
 
 
+def lateral(selectable, name=None):
+    """Return a :class:`.Lateral` object.
+
+    :class:`.Lateral` is an :class:`.Alias` subclass that represents
+    a subquery with the LATERAL keyword applied to it.
+
+    The special behavior of a LATERAL subquery is that it appears in the
+    FROM clause of an enclosing SELECT, but may correlate to other
+    FROM clauses of that SELECT.   It is a special case of subquery
+    only supported by a small number of backends, currently more recent
+    Postgresql versions.
+
+    .. versionadded:: 1.1
+
+    .. seealso::
+
+        :ref:`lateral_selects` -  overview of usage.
+
+    """
+    return selectable.lateral(name=name)
+
+
 class Selectable(ClauseElement):
     """mark a class as being selectable"""
     __visit_name__ = 'selectable'
@@ -413,6 +435,21 @@ class FromClause(Selectable):
 
         return Alias(self, name)
 
+    def lateral(self, name=None):
+        """Return a LATERAL alias of this :class:`.FromClause`.
+
+        The return value is the :class:`.Lateral` construct also
+        provided by the top-level :func:`~.expression.lateral` function.
+
+        .. versionadded:: 1.1
+
+        .. seealso::
+
+            :ref:`lateral_selects` -  overview of usage.
+
+        """
+        return Lateral(self, name)
+
     def is_derived_from(self, fromclause):
         """Return True if this FromClause is 'derived' from the given
         FromClause.
@@ -1186,6 +1223,27 @@ class Alias(FromClause):
         return self.element.bind
 
 
+class Lateral(Alias):
+    """Represent a LATERAL subquery.
+
+    This object is constructed from the :func:`~.expression.lateral` module
+    level function as well as the :meth:`.FromClause.lateral` method available
+    on all :class:`.FromClause` subclasses.
+
+    While LATERAL is part of the SQL standard, curently only more recent
+    Postgresql versions provide support for this keyword.
+
+    .. versionadded:: 1.1
+
+    .. seealso::
+
+        :ref:`lateral_selects` -  overview of usage.
+
+    """
+
+    __visit_name__ = 'lateral'
+
+
 class CTE(Generative, HasSuffixes, Alias):
     """Represent a Common Table Expression.