sqlalchemy.sql.functions.FunctionElement

Construct a .FunctionElement.

Produce a _expression.Alias construct against this .FunctionElement.

This construct wraps the function in a named alias which is suitable for the FROM clause, in the style accepted for example 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 _function.FunctionElement.table_valued method first to establish named columns.

e.g.:

>>> 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

The _functions.FunctionElement.column_valued method provides a shortcut for the above pattern:

>>> 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

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:

expr = func.is_equal("a", "b")

If "is_equal()" above is comparing "a" and "b" for equality, the .FunctionElement.as_comparison method would be invoked as:

expr = func.is_equal("a", "b").as_comparison(1, 2)

Where above, the integer value "1" refers to the first argument of the "is_equal()" function and the integer value "2" refers to the second.

This would create a .BinaryExpression that is equivalent to:

BinaryExpression("a", "b", operator=op.eq)

However, at the SQL level it would still render as "is_equal('a', 'b')".

The ORM, when it loads a related object or collection, needs to be able 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 .FunctionAsBinary.

An ORM example is as follows:

class Venue(Base):
    __tablename__ = 'venue'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    descendants = relationship(
        "Venue",
        primaryjoin=func.instr(
            remote(foreign(name)), name + "/"
        ).as_comparison(1, 2) == 1,
        viewonly=True,
        order_by=name
    )

Above, the "Venue" class can load descendant "Venue" objects by determining if the name of the parent Venue is contained within the start of the hypothetical descendant value's name, e.g. "parent1" would match up to "parent1/child1", but not to "parent2/child1".

Possible use cases include the "materialized path" example given above, as well as making use of special SQL functions such as geometric functions to create join conditions.

Return this _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

Execute this .FunctionElement against an embedded 'bind'.

This first calls ~.FunctionElement.select to produce a SELECT construct.

Note that .FunctionElement can be passed to the .Connectable.execute method of _engine.Connection or _engine.Engine.

Produce a FILTER clause against this function.

Used against aggregate and window functions, for database backends that support the "FILTER" clause.

The expression:

func.count(1).filter(True)

is shorthand for:

from sqlalchemy import funcfilter
funcfilter(func.count(1), True)

Produce an OVER clause against this function.

Used against aggregate or so-called "window" functions, for database backends that support window functions.

The expression:

func.row_number().over(order_by='x')

is shorthand for:

from sqlalchemy import over
over(func.row_number(), order_by='x')

See _expression.over for a full description.

Execute this .FunctionElement against an embedded 'bind' and return a scalar value.

This first calls ~.FunctionElement.select to produce a SELECT construct.

Note that .FunctionElement can be passed to the .Connectable.scalar method of _engine.Connection or _engine.Engine.

Return a column expression that's against this _functions.FunctionElement as a scalar table-valued expression.

The returned expression is similar to that returned by a single column accessed off of a _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

Produce a _expression.select construct against this .FunctionElement.

This is shorthand for:

s = select(function_element)

Apply a 'grouping' to this _expression.ClauseElement.

This method is overridden by subclasses to return a "grouping" construct, i.e. parenthesis. In particular it's used by "binary" expressions to provide a grouping around themselves when placed into a larger expression, as well as by _expression.select constructs when placed into the FROM clause of another _expression.select. (Note that subqueries should be normally created using the _expression.Select.alias method, as many platforms require nested SELECT statements to be named).

As expressions are composed together, the application of self_group is automatic - end-user code should never need to use this method directly. Note that SQLAlchemy's clause constructs take operator precedence into account - so parenthesis might not be needed, for example, in an expression like x OR (y AND z) - AND takes precedence over OR.

The base self_group method of _expression.ClauseElement just returns self.

Return a _sql.TableValuedAlias representation of this _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

Produce a WITHIN GROUP (ORDER BY expr) clause against this function.

Used against so-called "ordered set aggregate" and "hypothetical set aggregate" functions, including .percentile_cont, .rank, .dense_rank, etc.

See _expression.within_group for a full description.

For types that define their return type as based on the criteria within a WITHIN GROUP (ORDER BY) expression, called by the .WithinGroup construct.

Returns None by default, in which case the function's normal .type is used.