Implement support for functions as FROM with columns clause support

Implemented support for "table valued functions" along with additional
syntaxes supported by PostgreSQL, one of the most commonly requested
features. Table valued functions are SQL functions that return lists of
values or rows, and are prevalent in PostgreSQL in the area of JSON
functions, where the "table value" is commonly referred towards as the
"record" datatype. Table valued functions are also supported by Oracle and
SQL Server.

Moved from I5b093b72533ef695293e737eb75850b9713e5e03 due
to accidental push

Fixes: #3566
Change-Id: Iea36d04c80a5ed3509dcdd9ebf0701687143fef5

1 files changed, 123 insertions, 52 deletions

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index a9f21cd5f..1bdef1932 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -3856,6 +3856,8 @@ class Over(ColumnElement):
 
         .. seealso::
 
+            :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
+
             :data:`.expression.func`
 
             :func:`_expression.within_group`
@@ -4007,6 +4009,9 @@ class WithinGroup(ColumnElement):
 
         .. seealso::
 
+            :ref:`tutorial_functions_within_group` - in the
+            :ref:`unified_tutorial`
+
             :data:`.expression.func`
 
             :func:`_expression.over`
@@ -4104,8 +4109,10 @@ class FunctionFilter(ColumnElement):
 
         .. seealso::
 
-            :meth:`.FunctionElement.filter`
+            :ref:`tutorial_functions_within_group` - in the
+            :ref:`unified_tutorial`
 
+            :meth:`.FunctionElement.filter`
 
         """
         self.func = func
@@ -4317,11 +4324,82 @@ class Label(roles.LabeledColumnExprRole, ColumnElement):
         return self.key, e
 
 
+class NamedColumn(ColumnElement):
+    is_literal = False
+    table = None
+
+    def _compare_name_for_result(self, other):
+        return (hasattr(other, "name") and self.name == other.name) or (
+            hasattr(other, "_label") and self._label == other._label
+        )
+
+    @util.memoized_property
+    def description(self):
+        if util.py3k:
+            return self.name
+        else:
+            return self.name.encode("ascii", "backslashreplace")
+
+    @HasMemoized.memoized_attribute
+    def _key_label(self):
+        if self.key != self.name:
+            return self._gen_label(self.key)
+        else:
+            return self._label
+
+    @HasMemoized.memoized_attribute
+    def _label(self):
+        return self._gen_label(self.name)
+
+    @HasMemoized.memoized_attribute
+    def _render_label_in_columns_clause(self):
+        return True
+
+    def _gen_label(self, name, dedupe_on_key=True):
+        return name
+
+    def _bind_param(self, operator, obj, type_=None, expanding=False):
+        return BindParameter(
+            self.key,
+            obj,
+            _compared_to_operator=operator,
+            _compared_to_type=self.type,
+            type_=type_,
+            unique=True,
+            expanding=expanding,
+        )
+
+    def _make_proxy(
+        self,
+        selectable,
+        name=None,
+        name_is_truncatable=False,
+        disallow_is_literal=False,
+        **kw
+    ):
+        c = ColumnClause(
+            coercions.expect(roles.TruncatedLabelRole, name or self.name)
+            if name_is_truncatable
+            else (name or self.name),
+            type_=self.type,
+            _selectable=selectable,
+            is_literal=False,
+        )
+        c._propagate_attrs = selectable._propagate_attrs
+        if name is None:
+            c.key = self.key
+        c._proxies = [self]
+        if selectable._is_clone_of is not None:
+            c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
+        return c.key, c
+
+
 class ColumnClause(
     roles.DDLReferredColumnRole,
     roles.LabeledColumnExprRole,
+    roles.StrAsPlainColumnRole,
     Immutable,
-    ColumnElement,
+    NamedColumn,
 ):
     """Represents a column expression from any textual string.
 
@@ -4360,6 +4438,9 @@ class ColumnClause(
 
     """
 
+    table = None
+    is_literal = False
+
     __visit_name__ = "column"
 
     _traverse_internals = [
@@ -4470,27 +4551,6 @@ class ColumnClause(
         self.type = type_api.to_instance(type_)
         self.is_literal = is_literal
 
-    def _compare_name_for_result(self, other):
-        if (
-            self.is_literal
-            or self.table is None
-            or self.table._is_textual
-            or not hasattr(other, "proxy_set")
-            or (
-                isinstance(other, ColumnClause)
-                and (
-                    other.is_literal
-                    or other.table is None
-                    or other.table._is_textual
-                )
-            )
-        ):
-            return (hasattr(other, "name") and self.name == other.name) or (
-                hasattr(other, "_label") and self._label == other._label
-            )
-        else:
-            return other.proxy_set.intersection(self.proxy_set)
-
     def get_children(self, column_tables=False, **kw):
         # override base get_children() to not return the Table
         # or selectable that is parent to this column.  Traversals
@@ -4505,24 +4565,6 @@ class ColumnClause(
         else:
             return []
 
-    @util.memoized_property
-    def description(self):
-        if util.py3k:
-            return self.name
-        else:
-            return self.name.encode("ascii", "backslashreplace")
-
-    @HasMemoized.memoized_attribute
-    def _key_label(self):
-        if self.key != self.name:
-            return self._gen_label(self.key)
-        else:
-            return self._label
-
-    @HasMemoized.memoized_attribute
-    def _label(self):
-        return self._gen_label(self.name)
-
     @HasMemoized.memoized_attribute
     def _render_label_in_columns_clause(self):
         return self.table is not None
@@ -4531,6 +4573,27 @@ class ColumnClause(
     def _ddl_label(self):
         return self._gen_label(self.name, dedupe_on_key=False)
 
+    def _compare_name_for_result(self, other):
+        if (
+            self.is_literal
+            or self.table is None
+            or self.table._is_textual
+            or not hasattr(other, "proxy_set")
+            or (
+                isinstance(other, ColumnClause)
+                and (
+                    other.is_literal
+                    or other.table is None
+                    or other.table._is_textual
+                )
+            )
+        ):
+            return (hasattr(other, "name") and self.name == other.name) or (
+                hasattr(other, "_label") and self._label == other._label
+            )
+        else:
+            return other.proxy_set.intersection(self.proxy_set)
+
     def _gen_label(self, name, dedupe_on_key=True):
         t = self.table
         if self.is_literal:
@@ -4575,17 +4638,6 @@ class ColumnClause(
         else:
             return name
 
-    def _bind_param(self, operator, obj, type_=None, expanding=False):
-        return BindParameter(
-            self.key,
-            obj,
-            _compared_to_operator=operator,
-            _compared_to_type=self.type,
-            type_=type_,
-            unique=True,
-            expanding=expanding,
-        )
-
     def _make_proxy(
         self,
         selectable,
@@ -4627,6 +4679,25 @@ class ColumnClause(
         return c.key, c
 
 
+class TableValuedColumn(NamedColumn):
+    __visit_name__ = "table_valued_column"
+
+    _traverse_internals = [
+        ("name", InternalTraversal.dp_anon_name),
+        ("type", InternalTraversal.dp_type),
+        ("scalar_alias", InternalTraversal.dp_clauseelement),
+    ]
+
+    def __init__(self, scalar_alias, type_):
+        self.scalar_alias = scalar_alias
+        self.key = self.name = scalar_alias.name
+        self.type = type_
+
+    @property
+    def _from_objects(self):
+        return [self.scalar_alias]
+
+
 class CollationClause(ColumnElement):
     __visit_name__ = "collation"