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, 343 insertions, 39 deletions

diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index a9ea98d04..78f7ead2e 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -17,6 +17,7 @@ from . import sqltypes
 from . import util as sqlutil
 from .base import ColumnCollection
 from .base import Executable
+from .base import Generative
 from .base import HasMemoized
 from .elements import _type_from_args
 from .elements import BinaryExpression
@@ -28,11 +29,12 @@ from .elements import Extract
 from .elements import FunctionFilter
 from .elements import Grouping
 from .elements import literal_column
+from .elements import NamedColumn
 from .elements import Over
 from .elements import WithinGroup
-from .selectable import Alias
 from .selectable import FromClause
 from .selectable import Select
+from .selectable import TableValuedAlias
 from .visitors import InternalTraversal
 from .visitors import TraversibleType
 from .. import util
@@ -63,7 +65,7 @@ def register_function(identifier, fn, package="_default"):
     reg[identifier] = fn
 
 
-class FunctionElement(Executable, ColumnElement, FromClause):
+class FunctionElement(Executable, ColumnElement, FromClause, Generative):
     """Base for SQL function-oriented constructs.
 
     .. seealso::
@@ -80,11 +82,17 @@ class FunctionElement(Executable, ColumnElement, FromClause):
 
     """
 
-    _traverse_internals = [("clause_expr", InternalTraversal.dp_clauseelement)]
+    _traverse_internals = [
+        ("clause_expr", InternalTraversal.dp_clauseelement),
+        ("_with_ordinality", InternalTraversal.dp_boolean),
+        ("_table_value_type", InternalTraversal.dp_has_cache_key),
+    ]
 
     packagenames = ()
 
     _has_args = False
+    _with_ordinality = False
+    _table_value_type = None
 
     def __init__(self, *clauses, **kwargs):
         r"""Construct a :class:`.FunctionElement`.
@@ -123,27 +131,169 @@ class FunctionElement(Executable, ColumnElement, FromClause):
             self, multiparams, params, execution_options
         )
 
+    def scalar_table_valued(self, name, type_=None):
+        """Return a column expression that's against this
+        :class:`_functions.FunctionElement` as a scalar
+        table-valued expression.
+
+        The returned expression is similar to that returned by a single column
+        accessed off of a :meth:`_functions.FunctionElement.table_valued`
+        construct, except no FROM clause is generated; the function is rendered
+        in the similar way as a scalar subquery.
+
+        E.g.::
+
+            >>> from sqlalchemy import func, select
+            >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
+            >>> print(select(fn))
+            SELECT (jsonb_each(:jsonb_each_1)).key
+
+        .. versionadded:: 1.4.0b2
+
+        .. seealso::
+
+            :meth:`_functions.FunctionElement.table_valued`
+
+            :meth:`_functions.FunctionElement.alias`
+
+            :meth:`_functions.FunctionElement.column_valued`
+
+        """  # noqa E501
+
+        return ScalarFunctionColumn(self, name, type_)
+
+    def table_valued(self, *expr, **kw):
+        """Return a :class:`_sql.TableValuedAlias` representation of this
+        :class:`_functions.FunctionElement` with table-valued expressions added.
+
+        e.g.::
+
+            >>> fn = (
+            ...     func.generate_series(1, 5).
+            ...     table_valued("value", "start", "stop", "step")
+            ... )
+
+            >>> print(select(fn))
+            SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
+            FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
+
+            >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
+            SELECT anon_1.value, anon_1.stop
+            FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
+            WHERE anon_1.value > :value_1
+
+        A WITH ORDINALITY expression may be generated by passing the keyword
+        argument "with_ordinality"::
+
+            >>> fn = func.generate_series(4, 1, -1).table_valued("gen", with_ordinality="ordinality")
+            >>> print(select(fn))
+            SELECT anon_1.gen, anon_1.ordinality
+            FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1
+
+        :param \*expr: A series of string column names that will be added to the
+         ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
+         construct as columns.  :func:`_sql.column` objects with or without
+         datatypes may also be used.
+
+        :param name: optional name to assign to the alias name that's generated.
+         If omitted, a unique anonymizing name is used.
+
+        :param with_ordinality: string name that when present results in the
+         ``WITH ORDINALITY`` clause being added to the alias, and the given
+         string name will be added as a column to the .c collection
+         of the resulting :class:`_sql.TableValuedAlias`.
+
+        .. versionadded:: 1.4.0b2
+
+        .. seealso::
+
+            :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
+
+            :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
+
+            :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
+            :meth:`_functions.FunctionElement.table_valued` which delivers the
+            complete table valued expression as a scalar column expression
+
+            :meth:`_functions.FunctionElement.column_valued`
+
+            :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
+            using a derived column clause, e.g. ``AS name(col1, col2, ...)``
+
+        """  # noqa 501
+
+        new_func = self._generate()
+
+        with_ordinality = kw.pop("with_ordinality", None)
+        name = kw.pop("name", None)
+
+        if with_ordinality:
+            expr += (with_ordinality,)
+            new_func._with_ordinality = True
+
+        new_func.type = new_func._table_value_type = sqltypes.TableValueType(
+            *expr
+        )
+
+        return new_func.alias(name=name)
+
+    def column_valued(self, name=None):
+        """Return this :class:`_functions.FunctionElement` as a column expression that
+        selects from itself as a FROM clause.
+
+        E.g.::
+
+            >>> from sqlalchemy import select, func
+            >>> gs = func.generate_series(1, 5, -1).column_valued()
+            >>> print(select(gs))
+            SELECT anon_1
+            FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
+
+        This is shorthand for::
+
+            gs = func.generate_series(1, 5, -1).alias().column
+
+
+        .. seealso::
+
+            :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
+
+            :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
+
+            :meth:`_functions.FunctionElement.table_valued`
+
+        """  # noqa 501
+
+        return self.alias(name=name).column
+
     @property
     def columns(self):
         r"""The set of columns exported by this :class:`.FunctionElement`.
 
-        Function objects currently have no result column names built in;
-        this method returns a single-element column collection with
-        an anonymously named column.
+        This is a placeholder collection that allows the function to be
+        placed in the FROM clause of a statement::
 
-        An interim approach to providing named columns for a function
-        as a FROM clause is to build a :func:`_expression.select` with the
-        desired columns::
+            >>> from sqlalchemy import column, select, func
+            >>> stmt = select(column('x'), column('y')).select_from(func.myfunction())
+            >>> print(stmt)
+            SELECT x, y FROM myfunction()
 
-            from sqlalchemy.sql import column
+        The above form is a legacy feature that is now superseded by the
+        fully capable :meth:`_functions.FunctionElement.table_valued`
+        method; see that method for details.
 
-            stmt = select(column('x'), column('y')).\
-                select_from(func.myfunction())
+        .. seealso::
 
+            :meth:`_functions.FunctionElement.table_valued` - generates table-valued
+            SQL function expressions.
 
-        """
-        col = self.label(None)
-        return ColumnCollection(columns=[(col.key, col)])
+        """  # noqa E501
+        if self.type._is_table_value:
+            cols = self.type._elements
+        else:
+            cols = [self.label(None)]
+
+        return ColumnCollection(columns=[(col.key, col) for col in cols])
 
     @HasMemoized.memoized_attribute
     def clauses(self):
@@ -170,6 +320,12 @@ class FunctionElement(Executable, ColumnElement, FromClause):
 
         See :func:`_expression.over` for a full description.
 
+        .. seealso::
+
+            :func:`_expression.over`
+
+            :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
+
         """
         return Over(
             self,
@@ -191,6 +347,12 @@ class FunctionElement(Executable, ColumnElement, FromClause):
         .. versionadded:: 1.1
 
 
+        .. seealso::
+
+            :ref:`tutorial_functions_within_group` -
+            in the :ref:`unified_tutorial`
+
+
         """
         return WithinGroup(self, *order_by)
 
@@ -213,6 +375,9 @@ class FunctionElement(Executable, ColumnElement, FromClause):
 
         .. seealso::
 
+            :ref:`tutorial_functions_within_group` -
+            in the :ref:`unified_tutorial`
+
             :class:`.FunctionFilter`
 
             :func:`.funcfilter`
@@ -226,6 +391,9 @@ class FunctionElement(Executable, ColumnElement, FromClause):
     def as_comparison(self, left_index, right_index):
         """Interpret this expression as a boolean comparison between two values.
 
+        This method is used for an ORM use case described at
+        :ref:`relationship_custom_operator_sql_function`.
+
         A hypothetical SQL function "is_equal()" which compares to values
         for equality would be written in the Core expression language as::
 
@@ -250,9 +418,8 @@ class FunctionElement(Executable, ColumnElement, FromClause):
         to manipulate the "left" and "right" sides of the ON clause of a JOIN
         expression. The purpose of this method is to provide a SQL function
         construct that can also supply this information to the ORM, when used
-        with the :paramref:`_orm.relationship.primaryjoin` parameter.
-        The return
-        value is a containment object called :class:`.FunctionAsBinary`.
+        with the :paramref:`_orm.relationship.primaryjoin` parameter. The
+        return value is a containment object called :class:`.FunctionAsBinary`.
 
         An ORM example is as follows::
 
@@ -286,6 +453,11 @@ class FunctionElement(Executable, ColumnElement, FromClause):
 
         .. versionadded:: 1.3
 
+        .. seealso::
+
+            :ref:`relationship_custom_operator_sql_function` -
+            example use within the ORM
+
         """
         return FunctionAsBinary(self, left_index, right_index)
 
@@ -305,37 +477,65 @@ class FunctionElement(Executable, ColumnElement, FromClause):
 
         return None
 
-    def alias(self, name=None, flat=False):
+    def alias(self, name=None):
         r"""Produce a :class:`_expression.Alias` construct against this
         :class:`.FunctionElement`.
 
+        .. tip::
+
+            The :meth:`_functions.FunctionElement.alias` method is part of the
+            mechanism by which "table valued" SQL functions are created.
+            However, most use cases are covered by higher level methods on
+            :class:`_functions.FunctionElement` including
+            :meth:`_functions.FunctionElement.table_valued`, and
+            :meth:`_functions.FunctionElement.column_valued`.
+
         This construct wraps the function in a named alias which
         is suitable for the FROM clause, in the style accepted for example
-        by PostgreSQL.
+        by PostgreSQL.  A column expression is also provided using the
+        special ``.column`` attribute, which may
+        be used to refer to the output of the function as a scalar value
+        in the columns or where clause, for a backend such as PostgreSQL.
+
+        For a full table-valued expression, use the
+        :meth:`_function.FunctionElement.table_valued` method first to
+        establish named columns.
 
         e.g.::
 
-            from sqlalchemy.sql import column
+            >>> from sqlalchemy import func, select, column
+            >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
+            >>> print(select(data_view.column))
+            SELECT data_view
+            FROM unnest(:unnest_1) AS data_view
 
-            stmt = select(column('data_view')).\
-                select_from(SomeTable).\
-                select_from(func.unnest(SomeTable.data).alias('data_view')
-            )
+        The :meth:`_functions.FunctionElement.column_valued` method provides
+        a shortcut for the above pattern::
 
-        Would produce:
+            >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
+            >>> print(select(data_view))
+            SELECT data_view
+            FROM unnest(:unnest_1) AS data_view
 
-        .. sourcecode:: sql
+        .. versionadded:: 1.4.0b2  Added the ``.column`` accessor
 
-            SELECT data_view
-            FROM sometable, unnest(sometable.data) AS data_view
+        .. seealso::
+
+            :ref:`tutorial_functions_table_valued` -
+            in the :ref:`unified_tutorial`
+
+            :meth:`_functions.FunctionElement.table_valued`
+
+            :meth:`_functions.FunctionElement.scalar_table_valued`
+
+            :meth:`_functions.FunctionElement.column_valued`
 
-        .. versionadded:: 0.9.8 The :meth:`.FunctionElement.alias` method
-           is now supported.  Previously, this method's behavior was
-           undefined and did not behave consistently across versions.
 
         """
 
-        return Alias._construct(self, name)
+        return TableValuedAlias._construct(
+            self, name, table_value_type=self.type
+        )
 
     def select(self):
         """Produce a :func:`_expression.select` construct
@@ -351,6 +551,14 @@ class FunctionElement(Executable, ColumnElement, FromClause):
             s = s.execution_options(**self._execution_options)
         return s
 
+    @util.deprecated_20(
+        ":meth:`.FunctionElement.scalar`",
+        alternative="Scalar execution in SQLAlchemy 2.0 is performed "
+        "by the :meth:`_engine.Connection.scalar` method of "
+        ":class:`_engine.Connection`, "
+        "or in the ORM by the :meth:`.Session.scalar` method of "
+        ":class:`.Session`.",
+    )
     def scalar(self):
         """Execute this :class:`.FunctionElement` against an embedded
         'bind' and return a scalar value.
@@ -365,6 +573,14 @@ class FunctionElement(Executable, ColumnElement, FromClause):
         """
         return self.select().execute().scalar()
 
+    @util.deprecated_20(
+        ":meth:`.FunctionElement.execute`",
+        alternative="All statement execution in SQLAlchemy 2.0 is performed "
+        "by the :meth:`_engine.Connection.execute` method of "
+        ":class:`_engine.Connection`, "
+        "or in the ORM by the :meth:`.Session.execute` method of "
+        ":class:`.Session`.",
+    )
     def execute(self):
         """Execute this :class:`.FunctionElement` against an embedded
         'bind'.
@@ -441,6 +657,24 @@ class FunctionAsBinary(BinaryExpression):
         self.sql_function.clauses.clauses[self.right_index - 1] = value
 
 
+class ScalarFunctionColumn(NamedColumn):
+    __visit_name__ = "scalar_function_column"
+
+    _traverse_internals = [
+        ("name", InternalTraversal.dp_anon_name),
+        ("type", InternalTraversal.dp_type),
+        ("fn", InternalTraversal.dp_clauseelement),
+    ]
+
+    is_literal = False
+    table = None
+
+    def __init__(self, fn, name, type_=None):
+        self.fn = fn
+        self.name = name
+        self.type = sqltypes.to_instance(type_)
+
+
 class _FunctionGenerator(object):
     """Generate SQL function expressions.
 
@@ -586,10 +820,9 @@ class Function(FunctionElement):
 
         func.mypackage.some_function(col1, col2)
 
-
     .. seealso::
 
-        :ref:`coretutorial_functions`
+        :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
 
         :data:`.func` - namespace which produces registered or ad-hoc
         :class:`.Function` instances.
@@ -607,6 +840,23 @@ class Function(FunctionElement):
         ("type", InternalTraversal.dp_type),
     ]
 
+    type = sqltypes.NULLTYPE
+    """A :class:`_types.TypeEngine` object which refers to the SQL return
+    type represented by this SQL function.
+
+    This datatype may be configured when generating a
+    :class:`_functions.Function` object by passing the
+    :paramref:`_functions.Function.type_` parameter, e.g.::
+
+        >>> select(func.lower("some VALUE", type_=String))
+
+    The small number of built-in classes of :class:`_functions.Function` come
+    with a built-in datatype that's appropriate to the class of function and
+    its arguments. For functions that aren't known, the type defaults to the
+    "null type".
+
+    """
+
     @util.deprecated_params(
         bind=(
             "2.0",
@@ -825,6 +1075,8 @@ class next_value(GenericFunction):
 
 
 class AnsiFunction(GenericFunction):
+    """Define a function in "ansi" format, which doesn't render parenthesis."""
+
     inherit_cache = True
 
     def __init__(self, *args, **kwargs):
@@ -856,29 +1108,61 @@ class coalesce(ReturnTypeFromArgs):
     inherit_cache = True
 
 
-class max(ReturnTypeFromArgs):  # noqa
+class max(ReturnTypeFromArgs):  # noqa  A001
+    """The SQL MAX() aggregate function."""
+
     inherit_cache = True
 
 
-class min(ReturnTypeFromArgs):  # noqa
+class min(ReturnTypeFromArgs):  # noqa A001
+    """The SQL MIN() aggregate function."""
+
     inherit_cache = True
 
 
-class sum(ReturnTypeFromArgs):  # noqa
+class sum(ReturnTypeFromArgs):  # noqa A001
+    """The SQL SUM() aggregate function."""
+
     inherit_cache = True
 
 
-class now(GenericFunction):  # noqa
+class now(GenericFunction):
+    """The SQL now() datetime function.
+
+    SQLAlchemy dialects will usually render this particular function
+    in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
+
+    """
+
     type = sqltypes.DateTime
     inherit_cache = True
 
 
 class concat(GenericFunction):
+    """The SQL CONCAT() function, which concatenates strings.
+
+    E.g.::
+
+        >>> print(select(func.concat('a', 'b')))
+        SELECT concat(:concat_2, :concat_3) AS concat_1
+
+    String concatenation in SQLAlchemy is more commonly available using the
+    Python ``+`` operator with string datatypes, which will render a
+    backend-specific concatenation operator, such as ::
+
+        >>> print(select(literal("a") + "b"))
+        SELECT :param_1 || :param_2 AS anon_1
+
+
+    """
+
     type = sqltypes.String
     inherit_cache = True
 
 
 class char_length(GenericFunction):
+    """The CHAR_LENGTH() SQL function."""
+
     type = sqltypes.Integer
     inherit_cache = True
 
@@ -887,6 +1171,8 @@ class char_length(GenericFunction):
 
 
 class random(GenericFunction):
+    """The RANDOM() SQL function."""
+
     _has_args = True
     inherit_cache = True
 
@@ -922,46 +1208,64 @@ class count(GenericFunction):
 
 
 class current_date(AnsiFunction):
+    """The CURRENT_DATE() SQL function."""
+
     type = sqltypes.Date
     inherit_cache = True
 
 
 class current_time(AnsiFunction):
+    """The CURRENT_TIME() SQL function."""
+
     type = sqltypes.Time
     inherit_cache = True
 
 
 class current_timestamp(AnsiFunction):
+    """The CURRENT_TIMESTAMP() SQL function."""
+
     type = sqltypes.DateTime
     inherit_cache = True
 
 
 class current_user(AnsiFunction):
+    """The CURRENT_USER() SQL function."""
+
     type = sqltypes.String
     inherit_cache = True
 
 
 class localtime(AnsiFunction):
+    """The localtime() SQL function."""
+
     type = sqltypes.DateTime
     inherit_cache = True
 
 
 class localtimestamp(AnsiFunction):
+    """The localtimestamp() SQL function."""
+
     type = sqltypes.DateTime
     inherit_cache = True
 
 
 class session_user(AnsiFunction):
+    """The SESSION_USER() SQL function."""
+
     type = sqltypes.String
     inherit_cache = True
 
 
 class sysdate(AnsiFunction):
+    """The SYSDATE() SQL function."""
+
     type = sqltypes.DateTime
     inherit_cache = True
 
 
 class user(AnsiFunction):
+    """The USER() SQL function."""
+
     type = sqltypes.String
     inherit_cache = True