- implement LATERAL, needs tests, references #2857ticket_2587

3 files changed, 60 insertions, 7 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 52116a231..ebd975df5 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1276,6 +1276,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
 
@@ -1488,7 +1492,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
@@ -1499,7 +1503,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),
@@ -1515,6 +1519,7 @@ class SQLCompiler(Compiled):
                      compound_index=0,
                      nested_join_translation=False,
                      select_wraps_for=None,
+                     lateral=False,
                      **kwargs):
 
         needs_nested_translation = \
@@ -1554,7 +1559,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({
@@ -1661,11 +1666,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),
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 79d25a39e..044375e37 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -21,7 +21,8 @@ __all__ = [
     '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']
@@ -46,7 +47,7 @@ from .base import ColumnCollection, Generative, Executable, \
 
 from .selectable import Alias, Join, Select, Selectable, TableClause, \
     CompoundSelect, CTE, FromClause, FromGrouping, SelectBase, \
-    alias, GenerativeSelect, \
+    alias, lateral, GenerativeSelect, \
     subquery, HasPrefixes, HasSuffixes, Exists, ScalarSelect, TextAsFrom
 
 
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 73341053d..0b72c668f 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -161,6 +161,23 @@ 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 Postgresql.
+
+    .. versionadded:: 1.1
+
+    """
+    return selectable.lateral(name=name)
+
+
 class Selectable(ClauseElement):
     """mark a class as being selectable"""
     __visit_name__ = 'selectable'
@@ -403,6 +420,16 @@ class FromClause(Selectable):
 
         return Alias(self, name)
 
+    def lateral(self, name=None):
+        """Return a LATERAL alias of this :class:`.FromClause`.
+
+        See :func:`~.expression.lateral` for details.
+
+        .. versionadded:: 1.1
+
+        """
+        return Lateral(self, name)
+
     def is_derived_from(self, fromclause):
         """Return True if this FromClause is 'derived' from the given
         FromClause.
@@ -1170,6 +1197,26 @@ 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.
+
+    .. versionadded:: 1.1
+
+    .. seealso::
+
+        :func:`.expression.lateral`
+
+        :meth:`.FromClause.lateral`
+
+    """
+
+    __visit_name__ = 'lateral'
+
+
 class CTE(Generative, HasSuffixes, Alias):
     """Represent a Common Table Expression.