Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/functions.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8"""SQL function API, factories, and built-in functions."""
10from __future__ import annotations
12import datetime
13import decimal
14from typing import Any
15from typing import cast
16from typing import Dict
17from typing import List
18from typing import Mapping
19from typing import Optional
20from typing import overload
21from typing import Sequence
22from typing import Tuple
23from typing import Type
24from typing import TYPE_CHECKING
25from typing import TypeVar
26from typing import Union
28from . import annotation
29from . import coercions
30from . import operators
31from . import roles
32from . import schema
33from . import sqltypes
34from . import type_api
35from . import util as sqlutil
36from ._typing import is_table_value_type
37from .base import _entity_namespace
38from .base import ColumnCollection
39from .base import ExecutableStatement
40from .base import Generative
41from .base import HasMemoized
42from .base import WriteableColumnCollection
43from .elements import _type_from_args
44from .elements import AggregateOrderBy
45from .elements import BinaryExpression
46from .elements import BindParameter
47from .elements import Cast
48from .elements import ClauseList
49from .elements import ColumnElement
50from .elements import Extract
51from .elements import FunctionFilter
52from .elements import Grouping
53from .elements import literal_column
54from .elements import NamedColumn
55from .elements import Over
56from .elements import WithinGroup
57from .selectable import FromClause
58from .selectable import Select
59from .selectable import TableValuedAlias
60from .sqltypes import TableValueType
61from .type_api import TypeEngine
62from .visitors import InternalTraversal
63from .. import util
66if TYPE_CHECKING:
67 from ._typing import _ByArgument
68 from ._typing import _ColumnExpressionArgument
69 from ._typing import _ColumnExpressionOrLiteralArgument
70 from ._typing import _ColumnExpressionOrStrLabelArgument
71 from ._typing import _StarOrOne
72 from ._typing import _TypeEngineArgument
73 from .base import _EntityNamespace
74 from .elements import _FrameIntTuple
75 from .elements import ClauseElement
76 from .elements import FrameClause
77 from .elements import KeyedColumnElement
78 from .elements import TableValuedColumn
79 from .operators import OperatorType
80 from ..engine.base import Connection
81 from ..engine.cursor import CursorResult
82 from ..engine.interfaces import _CoreMultiExecuteParams
83 from ..engine.interfaces import CoreExecuteOptionsParameter
84 from ..util.typing import Self
86_T = TypeVar("_T", bound=Any)
87_S = TypeVar("_S", bound=Any)
89_registry: util.defaultdict[str, Dict[str, Type[Function[Any]]]] = (
90 util.defaultdict(dict)
91)
94def register_function(
95 identifier: str, fn: Type[Function[Any]], package: str = "_default"
96) -> None:
97 """Associate a callable with a particular func. name.
99 This is normally called by GenericFunction, but is also
100 available by itself so that a non-Function construct
101 can be associated with the :data:`.func` accessor (i.e.
102 CAST, EXTRACT).
104 """
105 reg = _registry[package]
107 identifier = str(identifier).lower()
109 # Check if a function with the same identifier is registered.
110 if identifier in reg:
111 util.warn(
112 "The GenericFunction '{}' is already registered and "
113 "is going to be overridden.".format(identifier)
114 )
115 reg[identifier] = fn
118class FunctionElement(
119 ColumnElement[_T], ExecutableStatement, FromClause, Generative
120):
121 """Base for SQL function-oriented constructs.
123 This is a `generic type <https://peps.python.org/pep-0484/#generics>`_,
124 meaning that type checkers and IDEs can be instructed on the types to
125 expect in a :class:`_engine.Result` for this function. See
126 :class:`.GenericFunction` for an example of how this is done.
128 .. seealso::
130 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
132 :class:`.Function` - named SQL function.
134 :data:`.func` - namespace which produces registered or ad-hoc
135 :class:`.Function` instances.
137 :class:`.GenericFunction` - allows creation of registered function
138 types.
140 """
142 _traverse_internals = [
143 ("clause_expr", InternalTraversal.dp_clauseelement),
144 ("_with_ordinality", InternalTraversal.dp_boolean),
145 ("_table_value_type", InternalTraversal.dp_has_cache_key),
146 ] + ExecutableStatement._executable_traverse_internals
148 packagenames: Tuple[str, ...] = ()
150 monotonic: bool = False
152 _has_args = False
153 _with_ordinality = False
154 _table_value_type: Optional[TableValueType] = None
156 # some attributes that are defined between both ColumnElement and
157 # FromClause are set to Any here to avoid typing errors
158 primary_key: Any
159 _is_clone_of: Any
161 clause_expr: Grouping[Any]
163 def __init__(
164 self, *clauses: _ColumnExpressionOrLiteralArgument[Any]
165 ) -> None:
166 r"""Construct a :class:`.FunctionElement`.
168 :param \*clauses: list of column expressions that form the arguments
169 of the SQL function call.
171 :param \**kwargs: additional kwargs are typically consumed by
172 subclasses.
174 .. seealso::
176 :data:`.func`
178 :class:`.Function`
180 """
181 args: Sequence[_ColumnExpressionArgument[Any]] = [
182 coercions.expect(
183 roles.ExpressionElementRole,
184 c,
185 name=getattr(self, "name", None),
186 apply_propagate_attrs=self,
187 )
188 for c in clauses
189 ]
190 self._has_args = self._has_args or bool(args)
191 self.clause_expr = Grouping(
192 ClauseList(operator=operators.comma_op, group_contents=True, *args)
193 )
195 _non_anon_label = None
197 @property
198 def _proxy_key(self) -> Any:
199 return super()._proxy_key or getattr(self, "name", None)
201 def _execute_on_connection(
202 self,
203 connection: Connection,
204 distilled_params: _CoreMultiExecuteParams,
205 execution_options: CoreExecuteOptionsParameter,
206 ) -> CursorResult[Any]:
207 return connection._execute_function(
208 self, distilled_params, execution_options
209 )
211 def scalar_table_valued(
212 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
213 ) -> ScalarFunctionColumn[_T]:
214 """Return a column expression that's against this
215 :class:`_functions.FunctionElement` as a scalar
216 table-valued expression.
218 The returned expression is similar to that returned by a single column
219 accessed off of a :meth:`_functions.FunctionElement.table_valued`
220 construct, except no FROM clause is generated; the function is rendered
221 in the similar way as a scalar subquery.
223 E.g.:
225 .. sourcecode:: pycon+sql
227 >>> from sqlalchemy import func, select
228 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
229 >>> print(select(fn))
230 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
232 .. versionadded:: 1.4.0b2
234 .. seealso::
236 :meth:`_functions.FunctionElement.table_valued`
238 :meth:`_functions.FunctionElement.alias`
240 :meth:`_functions.FunctionElement.column_valued`
242 """ # noqa: E501
244 return ScalarFunctionColumn(self, name, type_)
246 def table_valued(
247 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
248 ) -> TableValuedAlias:
249 r"""Return a :class:`_sql.TableValuedAlias` representation of this
250 :class:`_functions.FunctionElement` with table-valued expressions added.
252 e.g. to use the SQLite form of ``generate_series()`` (including
253 hidden columns "start", "stop", "step"):
255 .. sourcecode:: pycon+sql
257 >>> fn = func.generate_series(1, 5).table_valued(
258 ... "value", "start", "stop", "step"
259 ... )
261 >>> print(select(fn))
262 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
263 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
265 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
266 {printsql}SELECT anon_1.value, anon_1.stop
267 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
268 WHERE anon_1.value > :value_1{stop}
270 Backends like PostgreSQL need the accessed columns to be explicitly
271 named in "AS" clause. To achieve this, use
272 :meth:`_sql.TableValuedAlias.render_derived`; be sure to consult the
273 :ref:`PostgreSQL-specific documentation for table valued functions
274 <postgresql_table_valued>` for additional examples:
276 .. sourcecode:: pycon+sql
278 >>> fn = func.generate_series(1, 5).table_valued("value").render_derived()
280 >>> print(select(fn))
281 {printsql}SELECT anon_1.value FROM
282 generate_series(:generate_series_1, :generate_series_2) AS anon_1(value){stop}
284 A WITH ORDINALITY expression may be generated by passing the keyword
285 argument :paramref:`.FunctionElement.table_valued.with_ordinality`,
286 illustrated below using PostgreSQL's syntax:
288 .. sourcecode:: pycon+sql
290 >>> fn = func.generate_series(4, 1, -1).table_valued(
291 ... "gen", with_ordinality="ordinality"
292 ... )
293 >>> print(select(fn.render_derived()))
294 {printsql}SELECT anon_1.gen, anon_1.ordinality
295 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3)
296 WITH ORDINALITY AS anon_1(gen, ordinality)
298 :param \*expr: A series of string column names that will be added to the
299 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
300 construct as columns. :func:`_sql.column` objects with or without
301 datatypes may also be used.
303 :param name: optional name to assign to the alias name that's generated.
304 If omitted, a unique anonymizing name is used.
306 :param with_ordinality: string name that when present results in the
307 ``WITH ORDINALITY`` clause being added to the alias, and the given
308 string name will be added as a column to the .c collection
309 of the resulting :class:`_sql.TableValuedAlias`.
311 :param joins_implicitly: when True, the table valued function may be
312 used in the FROM clause without any explicit JOIN to other tables
313 in the SQL query, and no "cartesian product" warning will be generated.
314 May be useful for SQL functions such as ``func.json_each()``.
316 .. versionadded:: 1.4.33
318 .. versionadded:: 1.4.0b2
321 .. seealso::
323 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
325 :ref:`Table-Valued Functions on PostgreSQL <postgresql_table_valued>` - in the :ref:`postgresql_toplevel` documentation
327 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
328 :meth:`_functions.FunctionElement.table_valued` which delivers the
329 complete table valued expression as a scalar column expression
331 :meth:`_functions.FunctionElement.column_valued`
333 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
334 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
336 """ # noqa: 501
338 new_func = self._generate()
340 with_ordinality = kw.pop("with_ordinality", None)
341 joins_implicitly = kw.pop("joins_implicitly", None)
342 name = kw.pop("name", None)
344 if with_ordinality:
345 expr += (with_ordinality,)
346 new_func._with_ordinality = True
348 new_func.type = new_func._table_value_type = TableValueType(*expr)
350 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
352 def column_valued(
353 self, name: Optional[str] = None, joins_implicitly: bool = False
354 ) -> TableValuedColumn[_T]:
355 """Return this :class:`_functions.FunctionElement` as a column expression that
356 selects from itself as a FROM clause.
358 E.g.:
360 .. sourcecode:: pycon+sql
362 >>> from sqlalchemy import select, func
363 >>> gs = func.generate_series(1, 5, -1).column_valued()
364 >>> print(select(gs))
365 {printsql}SELECT anon_1
366 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
368 This is shorthand for::
370 gs = func.generate_series(1, 5, -1).alias().column
372 :param name: optional name to assign to the alias name that's generated.
373 If omitted, a unique anonymizing name is used.
375 :param joins_implicitly: when True, the "table" portion of the column
376 valued function may be a member of the FROM clause without any
377 explicit JOIN to other tables in the SQL query, and no "cartesian
378 product" warning will be generated. May be useful for SQL functions
379 such as ``func.json_array_elements()``.
381 .. versionadded:: 1.4.46
383 .. seealso::
385 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
387 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
389 :meth:`_functions.FunctionElement.table_valued`
391 """ # noqa: 501
393 return self.alias(name=name, joins_implicitly=joins_implicitly).column
395 @util.ro_non_memoized_property
396 def columns(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
397 r"""The set of columns exported by this :class:`.FunctionElement`.
399 This is a placeholder collection that allows the function to be
400 placed in the FROM clause of a statement:
402 .. sourcecode:: pycon+sql
404 >>> from sqlalchemy import column, select, func
405 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
406 >>> print(stmt)
407 {printsql}SELECT x, y FROM myfunction()
409 The above form is a legacy feature that is now superseded by the
410 fully capable :meth:`_functions.FunctionElement.table_valued`
411 method; see that method for details.
413 .. seealso::
415 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
416 SQL function expressions.
418 """ # noqa: E501
419 return self.c
421 @util.ro_memoized_property
422 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
423 """synonym for :attr:`.FunctionElement.columns`."""
425 return WriteableColumnCollection(
426 columns=[(col.key, col) for col in self._all_selected_columns]
427 )
429 @property
430 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
431 if is_table_value_type(self.type):
432 # TODO: this might not be fully accurate
433 cols = cast(
434 "Sequence[KeyedColumnElement[Any]]", self.type._elements
435 )
436 else:
437 cols = [self.label(None)]
439 return cols
441 @property
442 def exported_columns( # type: ignore[override]
443 self,
444 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
445 return self.columns
447 @HasMemoized.memoized_attribute
448 def clauses(self) -> ClauseList:
449 """Return the underlying :class:`.ClauseList` which contains
450 the arguments for this :class:`.FunctionElement`.
452 """
453 return cast(ClauseList, self.clause_expr.element)
455 def over(
456 self,
457 *,
458 partition_by: _ByArgument | None = None,
459 order_by: _ByArgument | None = None,
460 rows: _FrameIntTuple | FrameClause | None = None,
461 range_: _FrameIntTuple | FrameClause | None = None,
462 groups: _FrameIntTuple | FrameClause | None = None,
463 ) -> Over[_T]:
464 """Produce an OVER clause against this function.
466 Used against aggregate or so-called "window" functions,
467 for database backends that support window functions.
469 The expression::
471 func.row_number().over(order_by="x")
473 is shorthand for::
475 from sqlalchemy import over
477 over(func.row_number(), order_by="x")
479 See :func:`_expression.over` for a full description.
481 .. seealso::
483 :func:`_expression.over`
485 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
487 """
488 return Over(
489 self,
490 partition_by=partition_by,
491 order_by=order_by,
492 rows=rows,
493 range_=range_,
494 groups=groups,
495 )
497 def aggregate_order_by(
498 self, *order_by: _ColumnExpressionArgument[Any]
499 ) -> AggregateOrderBy[_T]:
500 r"""Produce a :class:`.AggregateOrderBy` object against a function.
502 Used for aggregating functions such as :class:`_functions.array_agg`,
503 ``group_concat``, ``json_agg`` on backends that support ordering via an
504 embedded ORDER BY parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
505 When used on backends like Oracle and SQL Server, SQL compilation uses
506 that of :class:`.WithinGroup`.
508 See :func:`_expression.aggregate_order_by` for a full description.
510 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
511 :func:`_postgresql.aggregate_order_by` function to a method on
512 :class:`.Function` that is backend agnostic.
514 .. seealso::
516 :class:`_functions.aggregate_strings` - backend-agnostic string
517 concatenation function which also supports ORDER BY
519 """
521 return AggregateOrderBy(self, *order_by)
523 def within_group(
524 self, *order_by: _ColumnExpressionArgument[Any]
525 ) -> WithinGroup[_T]:
526 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
528 Used against so-called "ordered set aggregate" and "hypothetical
529 set aggregate" functions, including :class:`.percentile_cont`,
530 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
531 used by PostgreSQL, Oracle Database, and Microsoft SQL Server.
533 For simple ORDER BY expressions within aggregate functions on
534 PostgreSQL, MySQL/MariaDB, SQLite, see :func:`_sql.aggregate_order_by`.
536 See :func:`_expression.within_group` for a full description.
538 .. seealso::
540 :ref:`tutorial_functions_within_group` -
541 in the :ref:`unified_tutorial`
544 """
545 return WithinGroup(self, *order_by)
547 @overload
548 def filter(self) -> Self: ...
550 @overload
551 def filter(
552 self,
553 __criterion0: _ColumnExpressionArgument[bool],
554 *criterion: _ColumnExpressionArgument[bool],
555 ) -> FunctionFilter[_T]: ...
557 def filter(
558 self, *criterion: _ColumnExpressionArgument[bool]
559 ) -> Union[Self, FunctionFilter[_T]]:
560 """Produce a FILTER clause against this function.
562 Used against aggregate and window functions,
563 for database backends that support the "FILTER" clause.
565 The expression::
567 func.count(1).filter(True)
569 is shorthand for::
571 from sqlalchemy import funcfilter
573 funcfilter(func.count(1), True)
575 .. seealso::
577 :ref:`tutorial_functions_within_group` -
578 in the :ref:`unified_tutorial`
580 :class:`.FunctionFilter`
582 :func:`.funcfilter`
585 """
586 if not criterion:
587 return self
588 return FunctionFilter(self, *criterion)
590 def as_comparison(
591 self, left_index: int, right_index: int
592 ) -> FunctionAsBinary:
593 """Interpret this expression as a boolean comparison between two
594 values.
596 This method is used for an ORM use case described at
597 :ref:`relationship_custom_operator_sql_function`.
599 A hypothetical SQL function "is_equal()" which compares to values
600 for equality would be written in the Core expression language as::
602 expr = func.is_equal("a", "b")
604 If "is_equal()" above is comparing "a" and "b" for equality, the
605 :meth:`.FunctionElement.as_comparison` method would be invoked as::
607 expr = func.is_equal("a", "b").as_comparison(1, 2)
609 Where above, the integer value "1" refers to the first argument of the
610 "is_equal()" function and the integer value "2" refers to the second.
612 This would create a :class:`.BinaryExpression` that is equivalent to::
614 BinaryExpression("a", "b", operator=op.eq)
616 However, at the SQL level it would still render as
617 "is_equal('a', 'b')".
619 The ORM, when it loads a related object or collection, needs to be able
620 to manipulate the "left" and "right" sides of the ON clause of a JOIN
621 expression. The purpose of this method is to provide a SQL function
622 construct that can also supply this information to the ORM, when used
623 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
624 return value is a containment object called :class:`.FunctionAsBinary`.
626 An ORM example is as follows::
628 class Venue(Base):
629 __tablename__ = "venue"
630 id = Column(Integer, primary_key=True)
631 name = Column(String)
633 descendants = relationship(
634 "Venue",
635 primaryjoin=func.instr(
636 remote(foreign(name)), name + "/"
637 ).as_comparison(1, 2)
638 == 1,
639 viewonly=True,
640 order_by=name,
641 )
643 Above, the "Venue" class can load descendant "Venue" objects by
644 determining if the name of the parent Venue is contained within the
645 start of the hypothetical descendant value's name, e.g. "parent1" would
646 match up to "parent1/child1", but not to "parent2/child1".
648 Possible use cases include the "materialized path" example given above,
649 as well as making use of special SQL functions such as geometric
650 functions to create join conditions.
652 :param left_index: the integer 1-based index of the function argument
653 that serves as the "left" side of the expression.
654 :param right_index: the integer 1-based index of the function argument
655 that serves as the "right" side of the expression.
657 .. seealso::
659 :ref:`relationship_custom_operator_sql_function` -
660 example use within the ORM
662 """
663 return FunctionAsBinary(self, left_index, right_index)
665 @property
666 def _from_objects(self) -> Any:
667 return self.clauses._from_objects
669 def within_group_type(
670 self, within_group: WithinGroup[_S]
671 ) -> Optional[TypeEngine[_S]]:
672 """For types that define their return type as based on the criteria
673 within a WITHIN GROUP (ORDER BY) expression, called by the
674 :class:`.WithinGroup` construct.
676 Returns None by default, in which case the function's normal ``.type``
677 is used.
679 """
681 return None
683 def alias(
684 self, name: Optional[str] = None, joins_implicitly: bool = False
685 ) -> TableValuedAlias:
686 r"""Produce a :class:`_expression.Alias` construct against this
687 :class:`.FunctionElement`.
689 .. tip::
691 The :meth:`_functions.FunctionElement.alias` method is part of the
692 mechanism by which "table valued" SQL functions are created.
693 However, most use cases are covered by higher level methods on
694 :class:`_functions.FunctionElement` including
695 :meth:`_functions.FunctionElement.table_valued`, and
696 :meth:`_functions.FunctionElement.column_valued`.
698 This construct wraps the function in a named alias which
699 is suitable for the FROM clause, in the style accepted for example
700 by PostgreSQL. A column expression is also provided using the
701 special ``.column`` attribute, which may
702 be used to refer to the output of the function as a scalar value
703 in the columns or where clause, for a backend such as PostgreSQL.
705 For a full table-valued expression, use the
706 :meth:`_functions.FunctionElement.table_valued` method first to
707 establish named columns.
709 e.g.:
711 .. sourcecode:: pycon+sql
713 >>> from sqlalchemy import func, select, column
714 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
715 >>> print(select(data_view.column))
716 {printsql}SELECT data_view
717 FROM unnest(:unnest_1) AS data_view
719 The :meth:`_functions.FunctionElement.column_valued` method provides
720 a shortcut for the above pattern:
722 .. sourcecode:: pycon+sql
724 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
725 >>> print(select(data_view))
726 {printsql}SELECT data_view
727 FROM unnest(:unnest_1) AS data_view
729 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
731 :param name: alias name, will be rendered as ``AS <name>`` in the
732 FROM clause
734 :param joins_implicitly: when True, the table valued function may be
735 used in the FROM clause without any explicit JOIN to other tables
736 in the SQL query, and no "cartesian product" warning will be
737 generated. May be useful for SQL functions such as
738 ``func.json_each()``.
740 .. versionadded:: 1.4.33
742 .. seealso::
744 :ref:`tutorial_functions_table_valued` -
745 in the :ref:`unified_tutorial`
747 :meth:`_functions.FunctionElement.table_valued`
749 :meth:`_functions.FunctionElement.scalar_table_valued`
751 :meth:`_functions.FunctionElement.column_valued`
754 """
756 return TableValuedAlias._construct(
757 self,
758 name=name,
759 table_value_type=self.type,
760 joins_implicitly=joins_implicitly,
761 )
763 def select(self) -> Select[_T]:
764 """Produce a :func:`_expression.select` construct
765 against this :class:`.FunctionElement`.
767 This is shorthand for::
769 s = select(function_element)
771 """
772 s: Select[_T] = Select(self)
773 if self._execution_options:
774 s = s.execution_options(**self._execution_options)
775 return s
777 def _bind_param(
778 self,
779 operator: OperatorType,
780 obj: Any,
781 type_: Optional[TypeEngine[_T]] = None,
782 expanding: bool = False,
783 **kw: Any,
784 ) -> BindParameter[_T]:
785 return BindParameter(
786 None,
787 obj,
788 _compared_to_operator=operator,
789 _compared_to_type=self.type,
790 unique=True,
791 type_=type_,
792 expanding=expanding,
793 **kw,
794 )
796 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
797 # for the moment, we are parenthesizing all array-returning
798 # expressions against getitem. This may need to be made
799 # more portable if in the future we support other DBs
800 # besides postgresql.
801 if against is operators.getitem and isinstance(
802 self.type, sqltypes.ARRAY
803 ):
804 return Grouping(self)
805 else:
806 return super().self_group(against=against)
808 @property
809 def entity_namespace(self) -> _EntityNamespace:
810 """overrides FromClause.entity_namespace as functions are generally
811 column expressions and not FromClauses.
813 """
814 # ideally functions would not be fromclauses but we failed to make
815 # this adjustment in 1.4
816 return _entity_namespace(self.clause_expr)
819class FunctionAsBinary(BinaryExpression[Any]):
820 _traverse_internals = [
821 ("sql_function", InternalTraversal.dp_clauseelement),
822 ("left_index", InternalTraversal.dp_plain_obj),
823 ("right_index", InternalTraversal.dp_plain_obj),
824 ("modifiers", InternalTraversal.dp_plain_dict),
825 ]
827 sql_function: FunctionElement[Any]
828 left_index: int
829 right_index: int
831 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
832 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
834 def __init__(
835 self, fn: FunctionElement[Any], left_index: int, right_index: int
836 ) -> None:
837 self.sql_function = fn
838 self.left_index = left_index
839 self.right_index = right_index
841 self.operator = operators.function_as_comparison_op
842 self.type = sqltypes.BOOLEANTYPE
843 self.negate = None
844 self._is_implicitly_boolean = True
845 self.modifiers = util.immutabledict({})
847 @property
848 def left_expr(self) -> ColumnElement[Any]:
849 return self.sql_function.clauses.clauses[self.left_index - 1]
851 @left_expr.setter
852 def left_expr(self, value: ColumnElement[Any]) -> None:
853 self.sql_function.clauses.clauses[self.left_index - 1] = value
855 @property
856 def right_expr(self) -> ColumnElement[Any]:
857 return self.sql_function.clauses.clauses[self.right_index - 1]
859 @right_expr.setter
860 def right_expr(self, value: ColumnElement[Any]) -> None:
861 self.sql_function.clauses.clauses[self.right_index - 1] = value
863 if not TYPE_CHECKING:
864 # mypy can't accommodate @property to replace an instance
865 # variable
867 left = left_expr
868 right = right_expr
871class ScalarFunctionColumn(NamedColumn[_T]):
872 __visit_name__ = "scalar_function_column"
874 _traverse_internals = [
875 ("name", InternalTraversal.dp_anon_name),
876 ("type", InternalTraversal.dp_type),
877 ("fn", InternalTraversal.dp_clauseelement),
878 ]
880 is_literal = False
881 table = None
883 def __init__(
884 self,
885 fn: FunctionElement[_T],
886 name: str,
887 type_: Optional[_TypeEngineArgument[_T]] = None,
888 ) -> None:
889 self.fn = fn
890 self.name = name
892 # if type is None, we get NULLTYPE, which is our _T. But I don't
893 # know how to get the overloads to express that correctly
894 self.type = type_api.to_instance(type_) # type: ignore
897class _FunctionGenerator:
898 """Generate SQL function expressions.
900 :data:`.func` is a special object instance which generates SQL
901 functions based on name-based attributes, e.g.:
903 .. sourcecode:: pycon+sql
905 >>> print(func.count(1))
906 {printsql}count(:param_1)
908 The returned object is an instance of :class:`.Function`, and is a
909 column-oriented SQL element like any other, and is used in that way:
911 .. sourcecode:: pycon+sql
913 >>> print(select(func.count(table.c.id)))
914 {printsql}SELECT count(sometable.id) FROM sometable
916 Any name can be given to :data:`.func`. If the function name is unknown to
917 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
918 which SQLAlchemy is aware of, the name may be interpreted as a *generic
919 function* which will be compiled appropriately to the target database:
921 .. sourcecode:: pycon+sql
923 >>> print(func.current_timestamp())
924 {printsql}CURRENT_TIMESTAMP
926 To call functions which are present in dot-separated packages,
927 specify them in the same manner:
929 .. sourcecode:: pycon+sql
931 >>> print(func.stats.yield_curve(5, 10))
932 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
934 SQLAlchemy can be made aware of the return type of functions to enable
935 type-specific lexical and result-based behavior. For example, to ensure
936 that a string-based function returns a Unicode value and is similarly
937 treated as a string in expressions, specify
938 :class:`~sqlalchemy.types.Unicode` as the type:
940 .. sourcecode:: pycon+sql
942 >>> print(
943 ... func.my_string("hi", type_=Unicode)
944 ... + " "
945 ... + func.my_string("there", type_=Unicode)
946 ... )
947 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
949 The object returned by a :data:`.func` call is usually an instance of
950 :class:`.Function`.
951 This object meets the "column" interface, including comparison and labeling
952 functions. The object can also be passed the :meth:`~.Connectable.execute`
953 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
954 where it will be
955 wrapped inside of a SELECT statement first::
957 print(connection.execute(func.current_timestamp()).scalar())
959 In a few exception cases, the :data:`.func` accessor
960 will redirect a name to a built-in expression such as :func:`.cast`
961 or :func:`.extract`, as these names have well-known meaning
962 but are not exactly the same as "functions" from a SQLAlchemy
963 perspective.
965 Functions which are interpreted as "generic" functions know how to
966 calculate their return type automatically. For a listing of known generic
967 functions, see :ref:`generic_functions`.
969 .. note::
971 The :data:`.func` construct has only limited support for calling
972 standalone "stored procedures", especially those with special
973 parameterization concerns.
975 See the section :ref:`stored_procedures` for details on how to use
976 the DBAPI-level ``callproc()`` method for fully traditional stored
977 procedures.
979 .. seealso::
981 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
983 :class:`.Function`
985 """ # noqa
987 def __init__(self, **opts: Any) -> None:
988 self.__names: List[str] = []
989 self.opts = opts
991 def __getattr__(self, name: str) -> _FunctionGenerator:
992 # passthru __ attributes; fixes pydoc
993 if name.startswith("__"):
994 try:
995 return self.__dict__[name] # type: ignore
996 except KeyError:
997 raise AttributeError(name)
999 elif name.endswith("_"):
1000 name = name[0:-1]
1001 f = _FunctionGenerator(**self.opts)
1002 f.__names = list(self.__names) + [name]
1003 return f
1005 @overload
1006 def __call__(
1007 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
1008 ) -> Function[_T]: ...
1010 @overload
1011 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
1013 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
1014 o = self.opts.copy()
1015 o.update(kwargs)
1017 tokens = len(self.__names)
1019 if tokens == 2:
1020 package, fname = self.__names
1021 elif tokens == 1:
1022 package, fname = "_default", self.__names[0]
1023 else:
1024 package = None
1026 if package is not None:
1027 func = _registry[package].get(fname.lower())
1028 if func is not None:
1029 return func(*c, **o)
1031 return Function(
1032 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
1033 )
1035 if TYPE_CHECKING:
1036 # START GENERATED FUNCTION ACCESSORS
1038 # code within this block is **programmatically,
1039 # statically generated** by tools/generate_sql_functions.py
1041 @property
1042 def aggregate_strings(self) -> Type[aggregate_strings]: ...
1044 @property
1045 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
1047 # set ColumnElement[_T] as a separate overload, to appease
1048 # mypy which seems to not want to accept _T from
1049 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1050 # _HasClauseElement as of mypy 1.15
1052 @overload
1053 def array_agg(
1054 self,
1055 col: ColumnElement[_T],
1056 *args: _ColumnExpressionOrLiteralArgument[Any],
1057 **kwargs: Any,
1058 ) -> array_agg[_T]: ...
1060 @overload
1061 def array_agg(
1062 self,
1063 col: _ColumnExpressionArgument[_T],
1064 *args: _ColumnExpressionOrLiteralArgument[Any],
1065 **kwargs: Any,
1066 ) -> array_agg[_T]: ...
1068 @overload
1069 def array_agg(
1070 self,
1071 col: _T,
1072 *args: _ColumnExpressionOrLiteralArgument[Any],
1073 **kwargs: Any,
1074 ) -> array_agg[_T]: ...
1076 def array_agg(
1077 self,
1078 col: _ColumnExpressionOrLiteralArgument[_T],
1079 *args: _ColumnExpressionOrLiteralArgument[Any],
1080 **kwargs: Any,
1081 ) -> array_agg[_T]: ...
1083 @property
1084 def cast(self) -> Type[Cast[Any]]: ...
1086 @property
1087 def char_length(self) -> Type[char_length]: ...
1089 # set ColumnElement[_T] as a separate overload, to appease
1090 # mypy which seems to not want to accept _T from
1091 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1092 # _HasClauseElement as of mypy 1.15
1094 @overload
1095 def coalesce(
1096 self,
1097 col: ColumnElement[_T],
1098 *args: _ColumnExpressionOrLiteralArgument[Any],
1099 **kwargs: Any,
1100 ) -> coalesce[_T]: ...
1102 @overload
1103 def coalesce(
1104 self,
1105 col: _ColumnExpressionArgument[Optional[_T]],
1106 *args: _ColumnExpressionOrLiteralArgument[Any],
1107 **kwargs: Any,
1108 ) -> coalesce[_T]: ...
1110 @overload
1111 def coalesce(
1112 self,
1113 col: Optional[_T],
1114 *args: _ColumnExpressionOrLiteralArgument[Any],
1115 **kwargs: Any,
1116 ) -> coalesce[_T]: ...
1118 def coalesce(
1119 self,
1120 col: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1121 *args: _ColumnExpressionOrLiteralArgument[Any],
1122 **kwargs: Any,
1123 ) -> coalesce[_T]: ...
1125 @property
1126 def concat(self) -> Type[concat]: ...
1128 @property
1129 def count(self) -> Type[count]: ...
1131 @property
1132 def cube(self) -> Type[cube[Any]]: ...
1134 @property
1135 def cume_dist(self) -> Type[cume_dist]: ...
1137 @property
1138 def current_date(self) -> Type[current_date]: ...
1140 @property
1141 def current_time(self) -> Type[current_time]: ...
1143 @property
1144 def current_timestamp(self) -> Type[current_timestamp]: ...
1146 @property
1147 def current_user(self) -> Type[current_user]: ...
1149 @property
1150 def dense_rank(self) -> Type[dense_rank]: ...
1152 @property
1153 def extract(self) -> Type[Extract]: ...
1155 @property
1156 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1158 @property
1159 def localtime(self) -> Type[localtime]: ...
1161 @property
1162 def localtimestamp(self) -> Type[localtimestamp]: ...
1164 # set ColumnElement[_T] as a separate overload, to appease
1165 # mypy which seems to not want to accept _T from
1166 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1167 # _HasClauseElement as of mypy 1.15
1169 @overload
1170 def max( # noqa: A001
1171 self,
1172 col: ColumnElement[_T],
1173 *args: _ColumnExpressionOrLiteralArgument[Any],
1174 **kwargs: Any,
1175 ) -> max[_T]: ...
1177 @overload
1178 def max( # noqa: A001
1179 self,
1180 col: _ColumnExpressionArgument[_T],
1181 *args: _ColumnExpressionOrLiteralArgument[Any],
1182 **kwargs: Any,
1183 ) -> max[_T]: ...
1185 @overload
1186 def max( # noqa: A001
1187 self,
1188 col: _T,
1189 *args: _ColumnExpressionOrLiteralArgument[Any],
1190 **kwargs: Any,
1191 ) -> max[_T]: ...
1193 def max( # noqa: A001
1194 self,
1195 col: _ColumnExpressionOrLiteralArgument[_T],
1196 *args: _ColumnExpressionOrLiteralArgument[Any],
1197 **kwargs: Any,
1198 ) -> max[_T]: ...
1200 # set ColumnElement[_T] as a separate overload, to appease
1201 # mypy which seems to not want to accept _T from
1202 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1203 # _HasClauseElement as of mypy 1.15
1205 @overload
1206 def min( # noqa: A001
1207 self,
1208 col: ColumnElement[_T],
1209 *args: _ColumnExpressionOrLiteralArgument[Any],
1210 **kwargs: Any,
1211 ) -> min[_T]: ...
1213 @overload
1214 def min( # noqa: A001
1215 self,
1216 col: _ColumnExpressionArgument[_T],
1217 *args: _ColumnExpressionOrLiteralArgument[Any],
1218 **kwargs: Any,
1219 ) -> min[_T]: ...
1221 @overload
1222 def min( # noqa: A001
1223 self,
1224 col: _T,
1225 *args: _ColumnExpressionOrLiteralArgument[Any],
1226 **kwargs: Any,
1227 ) -> min[_T]: ...
1229 def min( # noqa: A001
1230 self,
1231 col: _ColumnExpressionOrLiteralArgument[_T],
1232 *args: _ColumnExpressionOrLiteralArgument[Any],
1233 **kwargs: Any,
1234 ) -> min[_T]: ...
1236 @property
1237 def mode(self) -> Type[mode[Any]]: ...
1239 @property
1240 def next_value(self) -> Type[next_value]: ...
1242 @property
1243 def now(self) -> Type[now]: ...
1245 @property
1246 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1248 @property
1249 def percent_rank(self) -> Type[percent_rank]: ...
1251 @property
1252 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1254 @property
1255 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1257 # set ColumnElement[_T] as a separate overload, to appease
1258 # mypy which seems to not want to accept _T from
1259 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1260 # _HasClauseElement as of mypy 1.15
1262 @overload
1263 def pow( # noqa: A001
1264 self,
1265 col: ColumnElement[_T],
1266 *args: _ColumnExpressionOrLiteralArgument[Any],
1267 **kwargs: Any,
1268 ) -> pow[_T]: ...
1270 @overload
1271 def pow( # noqa: A001
1272 self,
1273 col: _ColumnExpressionArgument[_T],
1274 *args: _ColumnExpressionOrLiteralArgument[Any],
1275 **kwargs: Any,
1276 ) -> pow[_T]: ...
1278 @overload
1279 def pow( # noqa: A001
1280 self,
1281 col: _T,
1282 *args: _ColumnExpressionOrLiteralArgument[Any],
1283 **kwargs: Any,
1284 ) -> pow[_T]: ...
1286 def pow( # noqa: A001
1287 self,
1288 col: _ColumnExpressionOrLiteralArgument[_T],
1289 *args: _ColumnExpressionOrLiteralArgument[Any],
1290 **kwargs: Any,
1291 ) -> pow[_T]: ...
1293 @property
1294 def random(self) -> Type[random]: ...
1296 @property
1297 def rank(self) -> Type[rank]: ...
1299 @property
1300 def rollup(self) -> Type[rollup[Any]]: ...
1302 @property
1303 def session_user(self) -> Type[session_user]: ...
1305 # set ColumnElement[_T] as a separate overload, to appease
1306 # mypy which seems to not want to accept _T from
1307 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1308 # _HasClauseElement as of mypy 1.15
1310 @overload
1311 def sum( # noqa: A001
1312 self,
1313 col: ColumnElement[_T],
1314 *args: _ColumnExpressionOrLiteralArgument[Any],
1315 **kwargs: Any,
1316 ) -> sum[_T]: ...
1318 @overload
1319 def sum( # noqa: A001
1320 self,
1321 col: _ColumnExpressionArgument[_T],
1322 *args: _ColumnExpressionOrLiteralArgument[Any],
1323 **kwargs: Any,
1324 ) -> sum[_T]: ...
1326 @overload
1327 def sum( # noqa: A001
1328 self,
1329 col: _T,
1330 *args: _ColumnExpressionOrLiteralArgument[Any],
1331 **kwargs: Any,
1332 ) -> sum[_T]: ...
1334 def sum( # noqa: A001
1335 self,
1336 col: _ColumnExpressionOrLiteralArgument[_T],
1337 *args: _ColumnExpressionOrLiteralArgument[Any],
1338 **kwargs: Any,
1339 ) -> sum[_T]: ...
1341 @property
1342 def sysdate(self) -> Type[sysdate]: ...
1344 @property
1345 def user(self) -> Type[user]: ...
1347 # END GENERATED FUNCTION ACCESSORS
1350func = _FunctionGenerator()
1351func.__doc__ = _FunctionGenerator.__doc__
1353modifier = _FunctionGenerator(group=False)
1356class Function(FunctionElement[_T]):
1357 r"""Describe a named SQL function.
1359 The :class:`.Function` object is typically generated from the
1360 :data:`.func` generation object.
1363 :param \*clauses: list of column expressions that form the arguments
1364 of the SQL function call.
1366 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1367 used as the return value of the column expression generated by this
1368 function call.
1370 :param packagenames: a string which indicates package prefix names
1371 to be prepended to the function name when the SQL is generated.
1372 The :data:`.func` generator creates these when it is called using
1373 dotted format, e.g.::
1375 func.mypackage.some_function(col1, col2)
1377 .. seealso::
1379 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1381 :data:`.func` - namespace which produces registered or ad-hoc
1382 :class:`.Function` instances.
1384 :class:`.GenericFunction` - allows creation of registered function
1385 types.
1387 """
1389 __visit_name__ = "function"
1391 _traverse_internals = FunctionElement._traverse_internals + [
1392 ("packagenames", InternalTraversal.dp_plain_obj),
1393 ("name", InternalTraversal.dp_string),
1394 ("type", InternalTraversal.dp_type),
1395 ]
1397 name: str
1399 identifier: str
1401 type: TypeEngine[_T]
1402 """A :class:`_types.TypeEngine` object which refers to the SQL return
1403 type represented by this SQL function.
1405 This datatype may be configured when generating a
1406 :class:`_functions.Function` object by passing the
1407 :paramref:`_functions.Function.type_` parameter, e.g.::
1409 >>> select(func.lower("some VALUE", type_=String))
1411 The small number of built-in classes of :class:`_functions.Function` come
1412 with a built-in datatype that's appropriate to the class of function and
1413 its arguments. For functions that aren't known, the type defaults to the
1414 "null type".
1416 """
1418 @overload
1419 def __init__(
1420 self,
1421 name: str,
1422 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1423 type_: None = ...,
1424 packagenames: Optional[Tuple[str, ...]] = ...,
1425 monotonic: bool = ...,
1426 ) -> None: ...
1428 @overload
1429 def __init__(
1430 self,
1431 name: str,
1432 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1433 type_: _TypeEngineArgument[_T] = ...,
1434 packagenames: Optional[Tuple[str, ...]] = ...,
1435 monotonic: bool = ...,
1436 ) -> None: ...
1438 def __init__(
1439 self,
1440 name: str,
1441 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1442 type_: Optional[_TypeEngineArgument[_T]] = None,
1443 packagenames: Optional[Tuple[str, ...]] = None,
1444 monotonic: bool = False,
1445 ) -> None:
1446 """Construct a :class:`.Function`.
1448 The :data:`.func` construct is normally used to construct
1449 new :class:`.Function` instances.
1451 """
1452 self.packagenames = packagenames or ()
1453 self.name = name
1454 self.monotonic = monotonic
1456 # if type is None, we get NULLTYPE, which is our _T. But I don't
1457 # know how to get the overloads to express that correctly
1458 self.type = type_api.to_instance(type_) # type: ignore
1460 FunctionElement.__init__(self, *clauses)
1462 def _bind_param(
1463 self,
1464 operator: OperatorType,
1465 obj: Any,
1466 type_: Optional[TypeEngine[_T]] = None,
1467 expanding: bool = False,
1468 **kw: Any,
1469 ) -> BindParameter[_T]:
1470 return BindParameter(
1471 self.name,
1472 obj,
1473 _compared_to_operator=operator,
1474 _compared_to_type=self.type,
1475 type_=type_,
1476 unique=True,
1477 expanding=expanding,
1478 **kw,
1479 )
1482class GenericFunction(Function[_T]):
1483 """Define a 'generic' function.
1485 A generic function is a pre-established :class:`.Function`
1486 class that is instantiated automatically when called
1487 by name from the :data:`.func` attribute. Note that
1488 calling any name from :data:`.func` has the effect that
1489 a new :class:`.Function` instance is created automatically,
1490 given that name. The primary use case for defining
1491 a :class:`.GenericFunction` class is so that a function
1492 of a particular name may be given a fixed return type.
1493 It can also include custom argument parsing schemes as well
1494 as additional methods.
1496 Subclasses of :class:`.GenericFunction` are automatically
1497 registered under the name of the class. For
1498 example, a user-defined function ``as_utc()`` would
1499 be available immediately::
1501 from sqlalchemy.sql.functions import GenericFunction
1502 from sqlalchemy.types import DateTime
1505 class as_utc(GenericFunction):
1506 type = DateTime()
1507 inherit_cache = True
1510 print(select(func.as_utc()))
1512 User-defined generic functions can be organized into
1513 packages by specifying the "package" attribute when defining
1514 :class:`.GenericFunction`. Third party libraries
1515 containing many functions may want to use this in order
1516 to avoid name conflicts with other systems. For example,
1517 if our ``as_utc()`` function were part of a package
1518 "time"::
1520 class as_utc(GenericFunction):
1521 type = DateTime()
1522 package = "time"
1523 inherit_cache = True
1525 The above function would be available from :data:`.func`
1526 using the package name ``time``::
1528 print(select(func.time.as_utc()))
1530 A final option is to allow the function to be accessed
1531 from one name in :data:`.func` but to render as a different name.
1532 The ``identifier`` attribute will override the name used to
1533 access the function as loaded from :data:`.func`, but will retain
1534 the usage of ``name`` as the rendered name::
1536 class GeoBuffer(GenericFunction):
1537 type = Geometry()
1538 package = "geo"
1539 name = "ST_Buffer"
1540 identifier = "buffer"
1541 inherit_cache = True
1543 The above function will render as follows:
1545 .. sourcecode:: pycon+sql
1547 >>> print(func.geo.buffer())
1548 {printsql}ST_Buffer()
1550 The name will be rendered as is, however without quoting unless the name
1551 contains special characters that require quoting. To force quoting
1552 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1553 construct::
1555 from sqlalchemy.sql import quoted_name
1558 class GeoBuffer(GenericFunction):
1559 type = Geometry()
1560 package = "geo"
1561 name = quoted_name("ST_Buffer", True)
1562 identifier = "buffer"
1563 inherit_cache = True
1565 The above function will render as:
1567 .. sourcecode:: pycon+sql
1569 >>> print(func.geo.buffer())
1570 {printsql}"ST_Buffer"()
1572 Type parameters for this class as a
1573 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1574 and should match the type seen in a :class:`_engine.Result`. For example::
1576 class as_utc(GenericFunction[datetime.datetime]):
1577 type = DateTime()
1578 inherit_cache = True
1580 The above indicates that the following expression returns a ``datetime``
1581 object::
1583 connection.scalar(select(func.as_utc()))
1585 """
1587 coerce_arguments = True
1588 inherit_cache = True
1590 _register: bool
1592 name = "GenericFunction"
1594 def __init_subclass__(cls) -> None:
1595 if annotation.Annotated not in cls.__mro__:
1596 cls._register_generic_function(cls.__name__, cls.__dict__)
1597 super().__init_subclass__()
1599 @classmethod
1600 def _register_generic_function(
1601 cls, clsname: str, clsdict: Mapping[str, Any]
1602 ) -> None:
1603 cls.name = name = clsdict.get("name", clsname)
1604 cls.identifier = identifier = clsdict.get("identifier", name)
1605 package = clsdict.get("package", "_default")
1606 # legacy
1607 if "__return_type__" in clsdict:
1608 cls.type = clsdict["__return_type__"]
1610 # Check _register attribute status
1611 cls._register = getattr(cls, "_register", True)
1613 # Register the function if required
1614 if cls._register:
1615 register_function(identifier, cls, package)
1616 else:
1617 # Set _register to True to register child classes by default
1618 cls._register = True
1620 def __init__(
1621 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1622 ) -> None:
1623 parsed_args = kwargs.pop("_parsed_args", None)
1624 if parsed_args is None:
1625 parsed_args = [
1626 coercions.expect(
1627 roles.ExpressionElementRole,
1628 c,
1629 name=self.name,
1630 apply_propagate_attrs=self,
1631 )
1632 for c in args
1633 ]
1634 self._has_args = self._has_args or bool(parsed_args)
1635 self.packagenames = ()
1637 self.clause_expr = Grouping(
1638 ClauseList(
1639 operator=operators.comma_op, group_contents=True, *parsed_args
1640 )
1641 )
1643 self.type = type_api.to_instance( # type: ignore
1644 kwargs.pop("type_", None) or getattr(self, "type", None)
1645 )
1648register_function("cast", Cast) # type: ignore
1649register_function("extract", Extract) # type: ignore
1652class next_value(GenericFunction[int]):
1653 """Represent the 'next value', given a :class:`.Sequence`
1654 as its single argument.
1656 Compiles into the appropriate function on each backend,
1657 or will raise NotImplementedError if used on a backend
1658 that does not provide support for sequences.
1660 """
1662 type = sqltypes.Integer()
1663 name = "next_value"
1665 _traverse_internals = [
1666 ("sequence", InternalTraversal.dp_named_ddl_element)
1667 ]
1669 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1670 assert isinstance(
1671 seq, schema.Sequence
1672 ), "next_value() accepts a Sequence object as input."
1673 self.sequence = seq
1674 self.type = sqltypes.to_instance( # type: ignore
1675 seq.data_type or getattr(self, "type", None)
1676 )
1678 def compare(self, other: Any, **kw: Any) -> bool:
1679 return (
1680 isinstance(other, next_value)
1681 and self.sequence.name == other.sequence.name
1682 )
1684 @property
1685 def _from_objects(self) -> Any:
1686 return []
1689class AnsiFunction(GenericFunction[_T]):
1690 """Define a function in "ansi" format, which doesn't render parenthesis."""
1692 inherit_cache = True
1694 def __init__(
1695 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1696 ) -> None:
1697 GenericFunction.__init__(self, *args, **kwargs)
1700class ReturnTypeFromArgs(GenericFunction[_T]):
1701 """Define a function whose return type is bound to the type of its
1702 arguments.
1703 """
1705 inherit_cache = True
1707 # set ColumnElement[_T] as a separate overload, to appease
1708 # mypy which seems to not want to accept _T from
1709 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1710 # _HasClauseElement as of mypy 1.15
1712 @overload
1713 def __init__(
1714 self,
1715 col: ColumnElement[_T],
1716 *args: _ColumnExpressionOrLiteralArgument[Any],
1717 **kwargs: Any,
1718 ) -> None: ...
1720 @overload
1721 def __init__(
1722 self,
1723 col: _ColumnExpressionArgument[_T],
1724 *args: _ColumnExpressionOrLiteralArgument[Any],
1725 **kwargs: Any,
1726 ) -> None: ...
1728 @overload
1729 def __init__(
1730 self,
1731 col: _T,
1732 *args: _ColumnExpressionOrLiteralArgument[Any],
1733 **kwargs: Any,
1734 ) -> None: ...
1736 def __init__(
1737 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1738 ) -> None:
1739 fn_args: Sequence[ColumnElement[Any]] = [
1740 coercions.expect(
1741 roles.ExpressionElementRole,
1742 c,
1743 name=self.name,
1744 apply_propagate_attrs=self,
1745 )
1746 for c in args
1747 ]
1748 kwargs.setdefault("type_", _type_from_args(fn_args))
1749 kwargs["_parsed_args"] = fn_args
1750 super().__init__(*fn_args, **kwargs)
1753class ReturnTypeFromOptionalArgs(ReturnTypeFromArgs[_T]):
1754 inherit_cache = True
1756 @overload
1757 def __init__(
1758 self,
1759 col: ColumnElement[_T],
1760 *args: _ColumnExpressionOrLiteralArgument[Any],
1761 **kwargs: Any,
1762 ) -> None: ...
1764 @overload
1765 def __init__(
1766 self,
1767 col: _ColumnExpressionArgument[Optional[_T]],
1768 *args: _ColumnExpressionOrLiteralArgument[Any],
1769 **kwargs: Any,
1770 ) -> None: ...
1772 @overload
1773 def __init__(
1774 self,
1775 col: Optional[_T],
1776 *args: _ColumnExpressionOrLiteralArgument[Any],
1777 **kwargs: Any,
1778 ) -> None: ...
1780 def __init__(
1781 self,
1782 *args: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1783 **kwargs: Any,
1784 ) -> None:
1785 super().__init__(*args, **kwargs) # type: ignore
1788class coalesce(ReturnTypeFromOptionalArgs[_T]):
1789 _has_args = True
1790 inherit_cache = True
1793class max(ReturnTypeFromArgs[_T]): # noqa: A001
1794 """The SQL MAX() aggregate function."""
1796 inherit_cache = True
1799class min(ReturnTypeFromArgs[_T]): # noqa: A001
1800 """The SQL MIN() aggregate function."""
1802 inherit_cache = True
1805class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1806 """The SQL SUM() aggregate function."""
1808 inherit_cache = True
1811class now(GenericFunction[datetime.datetime]):
1812 """The SQL now() datetime function.
1814 SQLAlchemy dialects will usually render this particular function
1815 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1817 """
1819 type = sqltypes.DateTime()
1820 inherit_cache = True
1823class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1824 """The SQL POW() function which performs the power operator.
1826 E.g.:
1828 .. sourcecode:: pycon+sql
1830 >>> print(select(func.pow(2, 8)))
1831 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1833 .. versionadded:: 2.1
1835 """
1837 inherit_cache = True
1840class concat(GenericFunction[str]):
1841 """The SQL CONCAT() function, which concatenates strings.
1843 E.g.:
1845 .. sourcecode:: pycon+sql
1847 >>> print(select(func.concat("a", "b")))
1848 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1850 String concatenation in SQLAlchemy is more commonly available using the
1851 Python ``+`` operator with string datatypes, which will render a
1852 backend-specific concatenation operator, such as :
1854 .. sourcecode:: pycon+sql
1856 >>> print(select(literal("a") + "b"))
1857 {printsql}SELECT :param_1 || :param_2 AS anon_1
1860 """
1862 type = sqltypes.String()
1863 inherit_cache = True
1866class char_length(GenericFunction[int]):
1867 """The CHAR_LENGTH() SQL function."""
1869 type = sqltypes.Integer()
1870 inherit_cache = True
1872 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1873 # slight hack to limit to just one positional argument
1874 # not sure why this one function has this special treatment
1875 super().__init__(arg, **kw)
1878class random(GenericFunction[float]):
1879 """The RANDOM() SQL function."""
1881 _has_args = True
1882 inherit_cache = True
1885class count(GenericFunction[int]):
1886 r"""The ANSI COUNT aggregate function. With no arguments,
1887 emits COUNT \*.
1889 E.g.::
1891 from sqlalchemy import func
1892 from sqlalchemy import select
1893 from sqlalchemy import table, column
1895 my_table = table("some_table", column("id"))
1897 stmt = select(func.count()).select_from(my_table)
1899 Executing ``stmt`` would emit:
1901 .. sourcecode:: sql
1903 SELECT count(*) AS count_1
1904 FROM some_table
1907 """
1909 type = sqltypes.Integer()
1910 inherit_cache = True
1912 def __init__(
1913 self,
1914 expression: Union[
1915 _ColumnExpressionArgument[Any], _StarOrOne, None
1916 ] = None,
1917 **kwargs: Any,
1918 ) -> None:
1919 if expression is None:
1920 expression = literal_column("*")
1921 super().__init__(expression, **kwargs)
1924class current_date(AnsiFunction[datetime.date]):
1925 """The CURRENT_DATE() SQL function."""
1927 type = sqltypes.Date()
1928 inherit_cache = True
1931class current_time(AnsiFunction[datetime.time]):
1932 """The CURRENT_TIME() SQL function."""
1934 type = sqltypes.Time()
1935 inherit_cache = True
1938class current_timestamp(AnsiFunction[datetime.datetime]):
1939 """The CURRENT_TIMESTAMP() SQL function."""
1941 type = sqltypes.DateTime()
1942 inherit_cache = True
1945class current_user(AnsiFunction[str]):
1946 """The CURRENT_USER() SQL function."""
1948 type = sqltypes.String()
1949 inherit_cache = True
1952class localtime(AnsiFunction[datetime.datetime]):
1953 """The localtime() SQL function."""
1955 type = sqltypes.DateTime()
1956 inherit_cache = True
1959class localtimestamp(AnsiFunction[datetime.datetime]):
1960 """The localtimestamp() SQL function."""
1962 type = sqltypes.DateTime()
1963 inherit_cache = True
1966class session_user(AnsiFunction[str]):
1967 """The SESSION_USER() SQL function."""
1969 type = sqltypes.String()
1970 inherit_cache = True
1973class sysdate(AnsiFunction[datetime.datetime]):
1974 """The SYSDATE() SQL function."""
1976 type = sqltypes.DateTime()
1977 inherit_cache = True
1980class user(AnsiFunction[str]):
1981 """The USER() SQL function."""
1983 type = sqltypes.String()
1984 inherit_cache = True
1987class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1988 """Support for the ARRAY_AGG function.
1990 The ``func.array_agg(expr)`` construct returns an expression of
1991 type :class:`_types.ARRAY`.
1993 e.g.::
1995 stmt = select(func.array_agg(table.c.values)[2:5])
1997 .. seealso::
1999 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
2000 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
2001 added.
2003 """
2005 inherit_cache = True
2007 def __init__(
2008 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
2009 ) -> None:
2010 fn_args: Sequence[ColumnElement[Any]] = [
2011 coercions.expect(
2012 roles.ExpressionElementRole, c, apply_propagate_attrs=self
2013 )
2014 for c in args
2015 ]
2017 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
2018 if "type_" not in kwargs:
2019 type_from_args = _type_from_args(fn_args)
2020 if isinstance(type_from_args, sqltypes.ARRAY):
2021 kwargs["type_"] = type_from_args
2022 else:
2023 kwargs["type_"] = default_array_type(
2024 type_from_args, dimensions=1
2025 )
2026 kwargs["_parsed_args"] = fn_args
2027 super().__init__(*fn_args, **kwargs)
2030class OrderedSetAgg(GenericFunction[_T]):
2031 """Define a function where the return type is based on the sort
2032 expression type as defined by the expression passed to the
2033 :meth:`.FunctionElement.within_group` method."""
2035 array_for_multi_clause = False
2036 inherit_cache = True
2038 def within_group_type(
2039 self, within_group: WithinGroup[Any]
2040 ) -> TypeEngine[Any]:
2041 func_clauses = cast(ClauseList, self.clause_expr.element)
2042 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
2043 within_group.order_by
2044 )
2045 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
2046 return sqltypes.ARRAY(order_by[0].type)
2047 else:
2048 return order_by[0].type
2051class mode(OrderedSetAgg[_T]):
2052 """Implement the ``mode`` ordered-set aggregate function.
2054 This function must be used with the :meth:`.FunctionElement.within_group`
2055 modifier to supply a sort expression to operate upon.
2057 The return type of this function is the same as the sort expression.
2059 """
2061 inherit_cache = True
2064class percentile_cont(OrderedSetAgg[_T]):
2065 """Implement the ``percentile_cont`` ordered-set aggregate function.
2067 This function must be used with the :meth:`.FunctionElement.within_group`
2068 modifier to supply a sort expression to operate upon.
2070 The return type of this function is the same as the sort expression,
2071 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2072 expression's type.
2074 """
2076 array_for_multi_clause = True
2077 inherit_cache = True
2080class percentile_disc(OrderedSetAgg[_T]):
2081 """Implement the ``percentile_disc`` ordered-set aggregate function.
2083 This function must be used with the :meth:`.FunctionElement.within_group`
2084 modifier to supply a sort expression to operate upon.
2086 The return type of this function is the same as the sort expression,
2087 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2088 expression's type.
2090 """
2092 array_for_multi_clause = True
2093 inherit_cache = True
2096class rank(GenericFunction[int]):
2097 """Implement the ``rank`` hypothetical-set aggregate function.
2099 This function must be used with the :meth:`.FunctionElement.within_group`
2100 modifier to supply a sort expression to operate upon.
2102 The return type of this function is :class:`.Integer`.
2104 """
2106 type = sqltypes.Integer()
2107 inherit_cache = True
2110class dense_rank(GenericFunction[int]):
2111 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2113 This function must be used with the :meth:`.FunctionElement.within_group`
2114 modifier to supply a sort expression to operate upon.
2116 The return type of this function is :class:`.Integer`.
2118 """
2120 type = sqltypes.Integer()
2121 inherit_cache = True
2124class percent_rank(GenericFunction[decimal.Decimal]):
2125 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2127 This function must be used with the :meth:`.FunctionElement.within_group`
2128 modifier to supply a sort expression to operate upon.
2130 The return type of this function is :class:`.Numeric`.
2132 """
2134 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2135 inherit_cache = True
2138class cume_dist(GenericFunction[decimal.Decimal]):
2139 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2141 This function must be used with the :meth:`.FunctionElement.within_group`
2142 modifier to supply a sort expression to operate upon.
2144 The return type of this function is :class:`.Numeric`.
2146 """
2148 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2149 inherit_cache = True
2152class cube(GenericFunction[_T]):
2153 r"""Implement the ``CUBE`` grouping operation.
2155 This function is used as part of the GROUP BY of a statement,
2156 e.g. :meth:`_expression.Select.group_by`::
2158 stmt = select(
2159 func.sum(table.c.value), table.c.col_1, table.c.col_2
2160 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2162 """
2164 _has_args = True
2165 inherit_cache = True
2168class rollup(GenericFunction[_T]):
2169 r"""Implement the ``ROLLUP`` grouping operation.
2171 This function is used as part of the GROUP BY of a statement,
2172 e.g. :meth:`_expression.Select.group_by`::
2174 stmt = select(
2175 func.sum(table.c.value), table.c.col_1, table.c.col_2
2176 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2178 """
2180 _has_args = True
2181 inherit_cache = True
2184class grouping_sets(GenericFunction[_T]):
2185 r"""Implement the ``GROUPING SETS`` grouping operation.
2187 This function is used as part of the GROUP BY of a statement,
2188 e.g. :meth:`_expression.Select.group_by`::
2190 stmt = select(
2191 func.sum(table.c.value), table.c.col_1, table.c.col_2
2192 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2194 In order to group by multiple sets, use the :func:`.tuple_` construct::
2196 from sqlalchemy import tuple_
2198 stmt = select(
2199 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2200 ).group_by(
2201 func.grouping_sets(
2202 tuple_(table.c.col_1, table.c.col_2),
2203 tuple_(table.c.value, table.c.col_3),
2204 )
2205 )
2207 """ # noqa: E501
2209 _has_args = True
2210 inherit_cache = True
2213class aggregate_strings(GenericFunction[str]):
2214 """Implement a generic string aggregation function.
2216 This function will concatenate non-null values into a string and
2217 separate the values by a delimiter.
2219 This function is compiled on a per-backend basis, into functions
2220 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2222 e.g. Example usage with delimiter '.'::
2224 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2226 .. versionadded:: 2.0.21
2228 To add ordering to the expression, use the
2229 :meth:`_functions.FunctionElement.aggregate_order_by` modifier method,
2230 which will emit ORDER BY within the appropriate part of the column
2231 expression (varies by backend)::
2233 stmt = select(
2234 func.aggregate_strings(table.c.str_col, ".").aggregate_order_by(
2235 table.c.str_col
2236 )
2237 )
2239 .. versionadded:: 2.1 added :meth:`_functions.FunctionElement.aggregate_order_by`
2240 for all aggregate functions.
2242 :param clause: the SQL expression to be concatenated
2244 :param separator: separator string
2247 """ # noqa: E501
2249 type = sqltypes.String()
2250 _has_args = True
2251 inherit_cache = True
2253 def __init__(
2254 self,
2255 clause: _ColumnExpressionArgument[Any],
2256 separator: str,
2257 ) -> None:
2258 super().__init__(clause, separator)