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-2025 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 .elements import _type_from_args
43from .elements import AggregateOrderBy
44from .elements import BinaryExpression
45from .elements import BindParameter
46from .elements import Cast
47from .elements import ClauseList
48from .elements import ColumnElement
49from .elements import Extract
50from .elements import FunctionFilter
51from .elements import Grouping
52from .elements import literal_column
53from .elements import NamedColumn
54from .elements import Over
55from .elements import WithinGroup
56from .selectable import FromClause
57from .selectable import Select
58from .selectable import TableValuedAlias
59from .sqltypes import TableValueType
60from .type_api import TypeEngine
61from .visitors import InternalTraversal
62from .. import util
65if TYPE_CHECKING:
66 from ._typing import _ByArgument
67 from ._typing import _ColumnExpressionArgument
68 from ._typing import _ColumnExpressionOrLiteralArgument
69 from ._typing import _ColumnExpressionOrStrLabelArgument
70 from ._typing import _StarOrOne
71 from ._typing import _TypeEngineArgument
72 from .base import _EntityNamespace
73 from .elements import _FrameIntTuple
74 from .elements import ClauseElement
75 from .elements import FrameClause
76 from .elements import KeyedColumnElement
77 from .elements import TableValuedColumn
78 from .operators import OperatorType
79 from ..engine.base import Connection
80 from ..engine.cursor import CursorResult
81 from ..engine.interfaces import _CoreMultiExecuteParams
82 from ..engine.interfaces import CoreExecuteOptionsParameter
83 from ..util.typing import Self
85_T = TypeVar("_T", bound=Any)
86_S = TypeVar("_S", bound=Any)
88_registry: util.defaultdict[str, Dict[str, Type[Function[Any]]]] = (
89 util.defaultdict(dict)
90)
93def register_function(
94 identifier: str, fn: Type[Function[Any]], package: str = "_default"
95) -> None:
96 """Associate a callable with a particular func. name.
98 This is normally called by GenericFunction, but is also
99 available by itself so that a non-Function construct
100 can be associated with the :data:`.func` accessor (i.e.
101 CAST, EXTRACT).
103 """
104 reg = _registry[package]
106 identifier = str(identifier).lower()
108 # Check if a function with the same identifier is registered.
109 if identifier in reg:
110 util.warn(
111 "The GenericFunction '{}' is already registered and "
112 "is going to be overridden.".format(identifier)
113 )
114 reg[identifier] = fn
117class FunctionElement(
118 ColumnElement[_T], ExecutableStatement, FromClause, Generative
119):
120 """Base for SQL function-oriented constructs.
122 This is a `generic type <https://peps.python.org/pep-0484/#generics>`_,
123 meaning that type checkers and IDEs can be instructed on the types to
124 expect in a :class:`_engine.Result` for this function. See
125 :class:`.GenericFunction` for an example of how this is done.
127 .. seealso::
129 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
131 :class:`.Function` - named SQL function.
133 :data:`.func` - namespace which produces registered or ad-hoc
134 :class:`.Function` instances.
136 :class:`.GenericFunction` - allows creation of registered function
137 types.
139 """
141 _traverse_internals = [
142 ("clause_expr", InternalTraversal.dp_clauseelement),
143 ("_with_ordinality", InternalTraversal.dp_boolean),
144 ("_table_value_type", InternalTraversal.dp_has_cache_key),
145 ] + ExecutableStatement._executable_traverse_internals
147 packagenames: Tuple[str, ...] = ()
149 _has_args = False
150 _with_ordinality = False
151 _table_value_type: Optional[TableValueType] = None
153 # some attributes that are defined between both ColumnElement and
154 # FromClause are set to Any here to avoid typing errors
155 primary_key: Any
156 _is_clone_of: Any
158 clause_expr: Grouping[Any]
160 def __init__(
161 self, *clauses: _ColumnExpressionOrLiteralArgument[Any]
162 ) -> None:
163 r"""Construct a :class:`.FunctionElement`.
165 :param \*clauses: list of column expressions that form the arguments
166 of the SQL function call.
168 :param \**kwargs: additional kwargs are typically consumed by
169 subclasses.
171 .. seealso::
173 :data:`.func`
175 :class:`.Function`
177 """
178 args: Sequence[_ColumnExpressionArgument[Any]] = [
179 coercions.expect(
180 roles.ExpressionElementRole,
181 c,
182 name=getattr(self, "name", None),
183 apply_propagate_attrs=self,
184 )
185 for c in clauses
186 ]
187 self._has_args = self._has_args or bool(args)
188 self.clause_expr = Grouping(
189 ClauseList(operator=operators.comma_op, group_contents=True, *args)
190 )
192 _non_anon_label = None
194 @property
195 def _proxy_key(self) -> Any:
196 return super()._proxy_key or getattr(self, "name", None)
198 def _execute_on_connection(
199 self,
200 connection: Connection,
201 distilled_params: _CoreMultiExecuteParams,
202 execution_options: CoreExecuteOptionsParameter,
203 ) -> CursorResult[Any]:
204 return connection._execute_function(
205 self, distilled_params, execution_options
206 )
208 def scalar_table_valued(
209 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
210 ) -> ScalarFunctionColumn[_T]:
211 """Return a column expression that's against this
212 :class:`_functions.FunctionElement` as a scalar
213 table-valued expression.
215 The returned expression is similar to that returned by a single column
216 accessed off of a :meth:`_functions.FunctionElement.table_valued`
217 construct, except no FROM clause is generated; the function is rendered
218 in the similar way as a scalar subquery.
220 E.g.:
222 .. sourcecode:: pycon+sql
224 >>> from sqlalchemy import func, select
225 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
226 >>> print(select(fn))
227 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
229 .. versionadded:: 1.4.0b2
231 .. seealso::
233 :meth:`_functions.FunctionElement.table_valued`
235 :meth:`_functions.FunctionElement.alias`
237 :meth:`_functions.FunctionElement.column_valued`
239 """ # noqa: E501
241 return ScalarFunctionColumn(self, name, type_)
243 def table_valued(
244 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
245 ) -> TableValuedAlias:
246 r"""Return a :class:`_sql.TableValuedAlias` representation of this
247 :class:`_functions.FunctionElement` with table-valued expressions added.
249 e.g.:
251 .. sourcecode:: pycon+sql
253 >>> fn = func.generate_series(1, 5).table_valued(
254 ... "value", "start", "stop", "step"
255 ... )
257 >>> print(select(fn))
258 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
259 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
261 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
262 {printsql}SELECT anon_1.value, anon_1.stop
263 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
264 WHERE anon_1.value > :value_1{stop}
266 A WITH ORDINALITY expression may be generated by passing the keyword
267 argument "with_ordinality":
269 .. sourcecode:: pycon+sql
271 >>> fn = func.generate_series(4, 1, -1).table_valued(
272 ... "gen", with_ordinality="ordinality"
273 ... )
274 >>> print(select(fn))
275 {printsql}SELECT anon_1.gen, anon_1.ordinality
276 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1
278 :param \*expr: A series of string column names that will be added to the
279 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
280 construct as columns. :func:`_sql.column` objects with or without
281 datatypes may also be used.
283 :param name: optional name to assign to the alias name that's generated.
284 If omitted, a unique anonymizing name is used.
286 :param with_ordinality: string name that when present results in the
287 ``WITH ORDINALITY`` clause being added to the alias, and the given
288 string name will be added as a column to the .c collection
289 of the resulting :class:`_sql.TableValuedAlias`.
291 :param joins_implicitly: when True, the table valued function may be
292 used in the FROM clause without any explicit JOIN to other tables
293 in the SQL query, and no "cartesian product" warning will be generated.
294 May be useful for SQL functions such as ``func.json_each()``.
296 .. versionadded:: 1.4.33
298 .. versionadded:: 1.4.0b2
301 .. seealso::
303 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
305 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
307 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
308 :meth:`_functions.FunctionElement.table_valued` which delivers the
309 complete table valued expression as a scalar column expression
311 :meth:`_functions.FunctionElement.column_valued`
313 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
314 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
316 """ # noqa: 501
318 new_func = self._generate()
320 with_ordinality = kw.pop("with_ordinality", None)
321 joins_implicitly = kw.pop("joins_implicitly", None)
322 name = kw.pop("name", None)
324 if with_ordinality:
325 expr += (with_ordinality,)
326 new_func._with_ordinality = True
328 new_func.type = new_func._table_value_type = TableValueType(*expr)
330 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
332 def column_valued(
333 self, name: Optional[str] = None, joins_implicitly: bool = False
334 ) -> TableValuedColumn[_T]:
335 """Return this :class:`_functions.FunctionElement` as a column expression that
336 selects from itself as a FROM clause.
338 E.g.:
340 .. sourcecode:: pycon+sql
342 >>> from sqlalchemy import select, func
343 >>> gs = func.generate_series(1, 5, -1).column_valued()
344 >>> print(select(gs))
345 {printsql}SELECT anon_1
346 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
348 This is shorthand for::
350 gs = func.generate_series(1, 5, -1).alias().column
352 :param name: optional name to assign to the alias name that's generated.
353 If omitted, a unique anonymizing name is used.
355 :param joins_implicitly: when True, the "table" portion of the column
356 valued function may be a member of the FROM clause without any
357 explicit JOIN to other tables in the SQL query, and no "cartesian
358 product" warning will be generated. May be useful for SQL functions
359 such as ``func.json_array_elements()``.
361 .. versionadded:: 1.4.46
363 .. seealso::
365 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
367 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
369 :meth:`_functions.FunctionElement.table_valued`
371 """ # noqa: 501
373 return self.alias(name=name, joins_implicitly=joins_implicitly).column
375 @util.ro_non_memoized_property
376 def columns(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
377 r"""The set of columns exported by this :class:`.FunctionElement`.
379 This is a placeholder collection that allows the function to be
380 placed in the FROM clause of a statement:
382 .. sourcecode:: pycon+sql
384 >>> from sqlalchemy import column, select, func
385 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
386 >>> print(stmt)
387 {printsql}SELECT x, y FROM myfunction()
389 The above form is a legacy feature that is now superseded by the
390 fully capable :meth:`_functions.FunctionElement.table_valued`
391 method; see that method for details.
393 .. seealso::
395 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
396 SQL function expressions.
398 """ # noqa: E501
399 return self.c
401 @util.ro_memoized_property
402 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
403 """synonym for :attr:`.FunctionElement.columns`."""
405 return ColumnCollection(
406 columns=[(col.key, col) for col in self._all_selected_columns]
407 )
409 @property
410 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
411 if is_table_value_type(self.type):
412 # TODO: this might not be fully accurate
413 cols = cast(
414 "Sequence[KeyedColumnElement[Any]]", self.type._elements
415 )
416 else:
417 cols = [self.label(None)]
419 return cols
421 @property
422 def exported_columns( # type: ignore[override]
423 self,
424 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
425 return self.columns
427 @HasMemoized.memoized_attribute
428 def clauses(self) -> ClauseList:
429 """Return the underlying :class:`.ClauseList` which contains
430 the arguments for this :class:`.FunctionElement`.
432 """
433 return cast(ClauseList, self.clause_expr.element)
435 def over(
436 self,
437 *,
438 partition_by: _ByArgument | None = None,
439 order_by: _ByArgument | None = None,
440 rows: _FrameIntTuple | FrameClause | None = None,
441 range_: _FrameIntTuple | FrameClause | None = None,
442 groups: _FrameIntTuple | FrameClause | None = None,
443 ) -> Over[_T]:
444 """Produce an OVER clause against this function.
446 Used against aggregate or so-called "window" functions,
447 for database backends that support window functions.
449 The expression::
451 func.row_number().over(order_by="x")
453 is shorthand for::
455 from sqlalchemy import over
457 over(func.row_number(), order_by="x")
459 See :func:`_expression.over` for a full description.
461 .. seealso::
463 :func:`_expression.over`
465 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
467 """
468 return Over(
469 self,
470 partition_by=partition_by,
471 order_by=order_by,
472 rows=rows,
473 range_=range_,
474 groups=groups,
475 )
477 def aggregate_order_by(
478 self, *order_by: _ColumnExpressionArgument[Any]
479 ) -> AggregateOrderBy[_T]:
480 r"""Produce a :class:`.AggregateOrderBy` object against a function.
482 Used for aggregating functions such as :class:`_functions.array_agg`,
483 ``group_concat``, ``json_agg`` on backends that support ordering via an
484 embedded ORDER BY parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
485 When used on backends like Oracle and SQL Server, SQL compilation uses
486 that of :class:`.WithinGroup`.
488 See :func:`_expression.aggregate_order_by` for a full description.
490 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
491 :func:`_postgresql.aggregate_order_by` function to a method on
492 :class:`.Function` that is backend agnostic.
494 .. seealso::
496 :class:`_functions.aggregate_strings` - backend-agnostic string
497 concatenation function which also supports ORDER BY
499 """
501 return AggregateOrderBy(self, *order_by)
503 def within_group(
504 self, *order_by: _ColumnExpressionArgument[Any]
505 ) -> WithinGroup[_T]:
506 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
508 Used against so-called "ordered set aggregate" and "hypothetical
509 set aggregate" functions, including :class:`.percentile_cont`,
510 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
511 used by PostgreSQL, Oracle Database, and Microsoft SQL Server.
513 For simple ORDER BY expressions within aggregate functions on
514 PostgreSQL, MySQL/MariaDB, SQLite, see :func:`_sql.aggregate_order_by`.
516 See :func:`_expression.within_group` for a full description.
518 .. seealso::
520 :ref:`tutorial_functions_within_group` -
521 in the :ref:`unified_tutorial`
524 """
525 return WithinGroup(self, *order_by)
527 @overload
528 def filter(self) -> Self: ...
530 @overload
531 def filter(
532 self,
533 __criterion0: _ColumnExpressionArgument[bool],
534 *criterion: _ColumnExpressionArgument[bool],
535 ) -> FunctionFilter[_T]: ...
537 def filter(
538 self, *criterion: _ColumnExpressionArgument[bool]
539 ) -> Union[Self, FunctionFilter[_T]]:
540 """Produce a FILTER clause against this function.
542 Used against aggregate and window functions,
543 for database backends that support the "FILTER" clause.
545 The expression::
547 func.count(1).filter(True)
549 is shorthand for::
551 from sqlalchemy import funcfilter
553 funcfilter(func.count(1), True)
555 .. seealso::
557 :ref:`tutorial_functions_within_group` -
558 in the :ref:`unified_tutorial`
560 :class:`.FunctionFilter`
562 :func:`.funcfilter`
565 """
566 if not criterion:
567 return self
568 return FunctionFilter(self, *criterion)
570 def as_comparison(
571 self, left_index: int, right_index: int
572 ) -> FunctionAsBinary:
573 """Interpret this expression as a boolean comparison between two
574 values.
576 This method is used for an ORM use case described at
577 :ref:`relationship_custom_operator_sql_function`.
579 A hypothetical SQL function "is_equal()" which compares to values
580 for equality would be written in the Core expression language as::
582 expr = func.is_equal("a", "b")
584 If "is_equal()" above is comparing "a" and "b" for equality, the
585 :meth:`.FunctionElement.as_comparison` method would be invoked as::
587 expr = func.is_equal("a", "b").as_comparison(1, 2)
589 Where above, the integer value "1" refers to the first argument of the
590 "is_equal()" function and the integer value "2" refers to the second.
592 This would create a :class:`.BinaryExpression` that is equivalent to::
594 BinaryExpression("a", "b", operator=op.eq)
596 However, at the SQL level it would still render as
597 "is_equal('a', 'b')".
599 The ORM, when it loads a related object or collection, needs to be able
600 to manipulate the "left" and "right" sides of the ON clause of a JOIN
601 expression. The purpose of this method is to provide a SQL function
602 construct that can also supply this information to the ORM, when used
603 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
604 return value is a containment object called :class:`.FunctionAsBinary`.
606 An ORM example is as follows::
608 class Venue(Base):
609 __tablename__ = "venue"
610 id = Column(Integer, primary_key=True)
611 name = Column(String)
613 descendants = relationship(
614 "Venue",
615 primaryjoin=func.instr(
616 remote(foreign(name)), name + "/"
617 ).as_comparison(1, 2)
618 == 1,
619 viewonly=True,
620 order_by=name,
621 )
623 Above, the "Venue" class can load descendant "Venue" objects by
624 determining if the name of the parent Venue is contained within the
625 start of the hypothetical descendant value's name, e.g. "parent1" would
626 match up to "parent1/child1", but not to "parent2/child1".
628 Possible use cases include the "materialized path" example given above,
629 as well as making use of special SQL functions such as geometric
630 functions to create join conditions.
632 :param left_index: the integer 1-based index of the function argument
633 that serves as the "left" side of the expression.
634 :param right_index: the integer 1-based index of the function argument
635 that serves as the "right" side of the expression.
637 .. seealso::
639 :ref:`relationship_custom_operator_sql_function` -
640 example use within the ORM
642 """
643 return FunctionAsBinary(self, left_index, right_index)
645 @property
646 def _from_objects(self) -> Any:
647 return self.clauses._from_objects
649 def within_group_type(
650 self, within_group: WithinGroup[_S]
651 ) -> Optional[TypeEngine[_S]]:
652 """For types that define their return type as based on the criteria
653 within a WITHIN GROUP (ORDER BY) expression, called by the
654 :class:`.WithinGroup` construct.
656 Returns None by default, in which case the function's normal ``.type``
657 is used.
659 """
661 return None
663 def alias(
664 self, name: Optional[str] = None, joins_implicitly: bool = False
665 ) -> TableValuedAlias:
666 r"""Produce a :class:`_expression.Alias` construct against this
667 :class:`.FunctionElement`.
669 .. tip::
671 The :meth:`_functions.FunctionElement.alias` method is part of the
672 mechanism by which "table valued" SQL functions are created.
673 However, most use cases are covered by higher level methods on
674 :class:`_functions.FunctionElement` including
675 :meth:`_functions.FunctionElement.table_valued`, and
676 :meth:`_functions.FunctionElement.column_valued`.
678 This construct wraps the function in a named alias which
679 is suitable for the FROM clause, in the style accepted for example
680 by PostgreSQL. A column expression is also provided using the
681 special ``.column`` attribute, which may
682 be used to refer to the output of the function as a scalar value
683 in the columns or where clause, for a backend such as PostgreSQL.
685 For a full table-valued expression, use the
686 :meth:`_functions.FunctionElement.table_valued` method first to
687 establish named columns.
689 e.g.:
691 .. sourcecode:: pycon+sql
693 >>> from sqlalchemy import func, select, column
694 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
695 >>> print(select(data_view.column))
696 {printsql}SELECT data_view
697 FROM unnest(:unnest_1) AS data_view
699 The :meth:`_functions.FunctionElement.column_valued` method provides
700 a shortcut for the above pattern:
702 .. sourcecode:: pycon+sql
704 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
705 >>> print(select(data_view))
706 {printsql}SELECT data_view
707 FROM unnest(:unnest_1) AS data_view
709 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
711 :param name: alias name, will be rendered as ``AS <name>`` in the
712 FROM clause
714 :param joins_implicitly: when True, the table valued function may be
715 used in the FROM clause without any explicit JOIN to other tables
716 in the SQL query, and no "cartesian product" warning will be
717 generated. May be useful for SQL functions such as
718 ``func.json_each()``.
720 .. versionadded:: 1.4.33
722 .. seealso::
724 :ref:`tutorial_functions_table_valued` -
725 in the :ref:`unified_tutorial`
727 :meth:`_functions.FunctionElement.table_valued`
729 :meth:`_functions.FunctionElement.scalar_table_valued`
731 :meth:`_functions.FunctionElement.column_valued`
734 """
736 return TableValuedAlias._construct(
737 self,
738 name=name,
739 table_value_type=self.type,
740 joins_implicitly=joins_implicitly,
741 )
743 def select(self) -> Select[_T]:
744 """Produce a :func:`_expression.select` construct
745 against this :class:`.FunctionElement`.
747 This is shorthand for::
749 s = select(function_element)
751 """
752 s: Select[_T] = Select(self)
753 if self._execution_options:
754 s = s.execution_options(**self._execution_options)
755 return s
757 def _bind_param(
758 self,
759 operator: OperatorType,
760 obj: Any,
761 type_: Optional[TypeEngine[_T]] = None,
762 expanding: bool = False,
763 **kw: Any,
764 ) -> BindParameter[_T]:
765 return BindParameter(
766 None,
767 obj,
768 _compared_to_operator=operator,
769 _compared_to_type=self.type,
770 unique=True,
771 type_=type_,
772 expanding=expanding,
773 **kw,
774 )
776 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
777 # for the moment, we are parenthesizing all array-returning
778 # expressions against getitem. This may need to be made
779 # more portable if in the future we support other DBs
780 # besides postgresql.
781 if against in (operators.getitem, operators.json_getitem_op):
782 return Grouping(self)
783 else:
784 return super().self_group(against=against)
786 @property
787 def entity_namespace(self) -> _EntityNamespace:
788 """overrides FromClause.entity_namespace as functions are generally
789 column expressions and not FromClauses.
791 """
792 # ideally functions would not be fromclauses but we failed to make
793 # this adjustment in 1.4
794 return _entity_namespace(self.clause_expr)
797class FunctionAsBinary(BinaryExpression[Any]):
798 _traverse_internals = [
799 ("sql_function", InternalTraversal.dp_clauseelement),
800 ("left_index", InternalTraversal.dp_plain_obj),
801 ("right_index", InternalTraversal.dp_plain_obj),
802 ("modifiers", InternalTraversal.dp_plain_dict),
803 ]
805 sql_function: FunctionElement[Any]
806 left_index: int
807 right_index: int
809 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
810 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
812 def __init__(
813 self, fn: FunctionElement[Any], left_index: int, right_index: int
814 ) -> None:
815 self.sql_function = fn
816 self.left_index = left_index
817 self.right_index = right_index
819 self.operator = operators.function_as_comparison_op
820 self.type = sqltypes.BOOLEANTYPE
821 self.negate = None
822 self._is_implicitly_boolean = True
823 self.modifiers = util.immutabledict({})
825 @property
826 def left_expr(self) -> ColumnElement[Any]:
827 return self.sql_function.clauses.clauses[self.left_index - 1]
829 @left_expr.setter
830 def left_expr(self, value: ColumnElement[Any]) -> None:
831 self.sql_function.clauses.clauses[self.left_index - 1] = value
833 @property
834 def right_expr(self) -> ColumnElement[Any]:
835 return self.sql_function.clauses.clauses[self.right_index - 1]
837 @right_expr.setter
838 def right_expr(self, value: ColumnElement[Any]) -> None:
839 self.sql_function.clauses.clauses[self.right_index - 1] = value
841 if not TYPE_CHECKING:
842 # mypy can't accommodate @property to replace an instance
843 # variable
845 left = left_expr
846 right = right_expr
849class ScalarFunctionColumn(NamedColumn[_T]):
850 __visit_name__ = "scalar_function_column"
852 _traverse_internals = [
853 ("name", InternalTraversal.dp_anon_name),
854 ("type", InternalTraversal.dp_type),
855 ("fn", InternalTraversal.dp_clauseelement),
856 ]
858 is_literal = False
859 table = None
861 def __init__(
862 self,
863 fn: FunctionElement[_T],
864 name: str,
865 type_: Optional[_TypeEngineArgument[_T]] = None,
866 ) -> None:
867 self.fn = fn
868 self.name = name
870 # if type is None, we get NULLTYPE, which is our _T. But I don't
871 # know how to get the overloads to express that correctly
872 self.type = type_api.to_instance(type_) # type: ignore
875class _FunctionGenerator:
876 """Generate SQL function expressions.
878 :data:`.func` is a special object instance which generates SQL
879 functions based on name-based attributes, e.g.:
881 .. sourcecode:: pycon+sql
883 >>> print(func.count(1))
884 {printsql}count(:param_1)
886 The returned object is an instance of :class:`.Function`, and is a
887 column-oriented SQL element like any other, and is used in that way:
889 .. sourcecode:: pycon+sql
891 >>> print(select(func.count(table.c.id)))
892 {printsql}SELECT count(sometable.id) FROM sometable
894 Any name can be given to :data:`.func`. If the function name is unknown to
895 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
896 which SQLAlchemy is aware of, the name may be interpreted as a *generic
897 function* which will be compiled appropriately to the target database:
899 .. sourcecode:: pycon+sql
901 >>> print(func.current_timestamp())
902 {printsql}CURRENT_TIMESTAMP
904 To call functions which are present in dot-separated packages,
905 specify them in the same manner:
907 .. sourcecode:: pycon+sql
909 >>> print(func.stats.yield_curve(5, 10))
910 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
912 SQLAlchemy can be made aware of the return type of functions to enable
913 type-specific lexical and result-based behavior. For example, to ensure
914 that a string-based function returns a Unicode value and is similarly
915 treated as a string in expressions, specify
916 :class:`~sqlalchemy.types.Unicode` as the type:
918 .. sourcecode:: pycon+sql
920 >>> print(
921 ... func.my_string("hi", type_=Unicode)
922 ... + " "
923 ... + func.my_string("there", type_=Unicode)
924 ... )
925 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
927 The object returned by a :data:`.func` call is usually an instance of
928 :class:`.Function`.
929 This object meets the "column" interface, including comparison and labeling
930 functions. The object can also be passed the :meth:`~.Connectable.execute`
931 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
932 where it will be
933 wrapped inside of a SELECT statement first::
935 print(connection.execute(func.current_timestamp()).scalar())
937 In a few exception cases, the :data:`.func` accessor
938 will redirect a name to a built-in expression such as :func:`.cast`
939 or :func:`.extract`, as these names have well-known meaning
940 but are not exactly the same as "functions" from a SQLAlchemy
941 perspective.
943 Functions which are interpreted as "generic" functions know how to
944 calculate their return type automatically. For a listing of known generic
945 functions, see :ref:`generic_functions`.
947 .. note::
949 The :data:`.func` construct has only limited support for calling
950 standalone "stored procedures", especially those with special
951 parameterization concerns.
953 See the section :ref:`stored_procedures` for details on how to use
954 the DBAPI-level ``callproc()`` method for fully traditional stored
955 procedures.
957 .. seealso::
959 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
961 :class:`.Function`
963 """ # noqa
965 def __init__(self, **opts: Any) -> None:
966 self.__names: List[str] = []
967 self.opts = opts
969 def __getattr__(self, name: str) -> _FunctionGenerator:
970 # passthru __ attributes; fixes pydoc
971 if name.startswith("__"):
972 try:
973 return self.__dict__[name] # type: ignore
974 except KeyError:
975 raise AttributeError(name)
977 elif name.endswith("_"):
978 name = name[0:-1]
979 f = _FunctionGenerator(**self.opts)
980 f.__names = list(self.__names) + [name]
981 return f
983 @overload
984 def __call__(
985 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
986 ) -> Function[_T]: ...
988 @overload
989 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
991 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
992 o = self.opts.copy()
993 o.update(kwargs)
995 tokens = len(self.__names)
997 if tokens == 2:
998 package, fname = self.__names
999 elif tokens == 1:
1000 package, fname = "_default", self.__names[0]
1001 else:
1002 package = None
1004 if package is not None:
1005 func = _registry[package].get(fname.lower())
1006 if func is not None:
1007 return func(*c, **o)
1009 return Function(
1010 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
1011 )
1013 if TYPE_CHECKING:
1014 # START GENERATED FUNCTION ACCESSORS
1016 # code within this block is **programmatically,
1017 # statically generated** by tools/generate_sql_functions.py
1019 @property
1020 def aggregate_strings(self) -> Type[aggregate_strings]: ...
1022 @property
1023 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
1025 # set ColumnElement[_T] as a separate overload, to appease
1026 # mypy which seems to not want to accept _T from
1027 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1028 # _HasClauseElement as of mypy 1.15
1030 @overload
1031 def array_agg(
1032 self,
1033 col: ColumnElement[_T],
1034 *args: _ColumnExpressionOrLiteralArgument[Any],
1035 **kwargs: Any,
1036 ) -> array_agg[_T]: ...
1038 @overload
1039 def array_agg(
1040 self,
1041 col: _ColumnExpressionArgument[_T],
1042 *args: _ColumnExpressionOrLiteralArgument[Any],
1043 **kwargs: Any,
1044 ) -> array_agg[_T]: ...
1046 @overload
1047 def array_agg(
1048 self,
1049 col: _T,
1050 *args: _ColumnExpressionOrLiteralArgument[Any],
1051 **kwargs: Any,
1052 ) -> array_agg[_T]: ...
1054 def array_agg(
1055 self,
1056 col: _ColumnExpressionOrLiteralArgument[_T],
1057 *args: _ColumnExpressionOrLiteralArgument[Any],
1058 **kwargs: Any,
1059 ) -> array_agg[_T]: ...
1061 @property
1062 def cast(self) -> Type[Cast[Any]]: ...
1064 @property
1065 def char_length(self) -> Type[char_length]: ...
1067 # set ColumnElement[_T] as a separate overload, to appease
1068 # mypy which seems to not want to accept _T from
1069 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1070 # _HasClauseElement as of mypy 1.15
1072 @overload
1073 def coalesce(
1074 self,
1075 col: ColumnElement[_T],
1076 *args: _ColumnExpressionOrLiteralArgument[Any],
1077 **kwargs: Any,
1078 ) -> coalesce[_T]: ...
1080 @overload
1081 def coalesce(
1082 self,
1083 col: _ColumnExpressionArgument[Optional[_T]],
1084 *args: _ColumnExpressionOrLiteralArgument[Any],
1085 **kwargs: Any,
1086 ) -> coalesce[_T]: ...
1088 @overload
1089 def coalesce(
1090 self,
1091 col: Optional[_T],
1092 *args: _ColumnExpressionOrLiteralArgument[Any],
1093 **kwargs: Any,
1094 ) -> coalesce[_T]: ...
1096 def coalesce(
1097 self,
1098 col: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1099 *args: _ColumnExpressionOrLiteralArgument[Any],
1100 **kwargs: Any,
1101 ) -> coalesce[_T]: ...
1103 @property
1104 def concat(self) -> Type[concat]: ...
1106 @property
1107 def count(self) -> Type[count]: ...
1109 @property
1110 def cube(self) -> Type[cube[Any]]: ...
1112 @property
1113 def cume_dist(self) -> Type[cume_dist]: ...
1115 @property
1116 def current_date(self) -> Type[current_date]: ...
1118 @property
1119 def current_time(self) -> Type[current_time]: ...
1121 @property
1122 def current_timestamp(self) -> Type[current_timestamp]: ...
1124 @property
1125 def current_user(self) -> Type[current_user]: ...
1127 @property
1128 def dense_rank(self) -> Type[dense_rank]: ...
1130 @property
1131 def extract(self) -> Type[Extract]: ...
1133 @property
1134 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1136 @property
1137 def localtime(self) -> Type[localtime]: ...
1139 @property
1140 def localtimestamp(self) -> Type[localtimestamp]: ...
1142 # set ColumnElement[_T] as a separate overload, to appease
1143 # mypy which seems to not want to accept _T from
1144 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1145 # _HasClauseElement as of mypy 1.15
1147 @overload
1148 def max( # noqa: A001
1149 self,
1150 col: ColumnElement[_T],
1151 *args: _ColumnExpressionOrLiteralArgument[Any],
1152 **kwargs: Any,
1153 ) -> max[_T]: ...
1155 @overload
1156 def max( # noqa: A001
1157 self,
1158 col: _ColumnExpressionArgument[_T],
1159 *args: _ColumnExpressionOrLiteralArgument[Any],
1160 **kwargs: Any,
1161 ) -> max[_T]: ...
1163 @overload
1164 def max( # noqa: A001
1165 self,
1166 col: _T,
1167 *args: _ColumnExpressionOrLiteralArgument[Any],
1168 **kwargs: Any,
1169 ) -> max[_T]: ...
1171 def max( # noqa: A001
1172 self,
1173 col: _ColumnExpressionOrLiteralArgument[_T],
1174 *args: _ColumnExpressionOrLiteralArgument[Any],
1175 **kwargs: Any,
1176 ) -> max[_T]: ...
1178 # set ColumnElement[_T] as a separate overload, to appease
1179 # mypy which seems to not want to accept _T from
1180 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1181 # _HasClauseElement as of mypy 1.15
1183 @overload
1184 def min( # noqa: A001
1185 self,
1186 col: ColumnElement[_T],
1187 *args: _ColumnExpressionOrLiteralArgument[Any],
1188 **kwargs: Any,
1189 ) -> min[_T]: ...
1191 @overload
1192 def min( # noqa: A001
1193 self,
1194 col: _ColumnExpressionArgument[_T],
1195 *args: _ColumnExpressionOrLiteralArgument[Any],
1196 **kwargs: Any,
1197 ) -> min[_T]: ...
1199 @overload
1200 def min( # noqa: A001
1201 self,
1202 col: _T,
1203 *args: _ColumnExpressionOrLiteralArgument[Any],
1204 **kwargs: Any,
1205 ) -> min[_T]: ...
1207 def min( # noqa: A001
1208 self,
1209 col: _ColumnExpressionOrLiteralArgument[_T],
1210 *args: _ColumnExpressionOrLiteralArgument[Any],
1211 **kwargs: Any,
1212 ) -> min[_T]: ...
1214 @property
1215 def mode(self) -> Type[mode[Any]]: ...
1217 @property
1218 def next_value(self) -> Type[next_value]: ...
1220 @property
1221 def now(self) -> Type[now]: ...
1223 @property
1224 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1226 @property
1227 def percent_rank(self) -> Type[percent_rank]: ...
1229 @property
1230 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1232 @property
1233 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1235 # set ColumnElement[_T] as a separate overload, to appease
1236 # mypy which seems to not want to accept _T from
1237 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1238 # _HasClauseElement as of mypy 1.15
1240 @overload
1241 def pow( # noqa: A001
1242 self,
1243 col: ColumnElement[_T],
1244 *args: _ColumnExpressionOrLiteralArgument[Any],
1245 **kwargs: Any,
1246 ) -> pow[_T]: ...
1248 @overload
1249 def pow( # noqa: A001
1250 self,
1251 col: _ColumnExpressionArgument[_T],
1252 *args: _ColumnExpressionOrLiteralArgument[Any],
1253 **kwargs: Any,
1254 ) -> pow[_T]: ...
1256 @overload
1257 def pow( # noqa: A001
1258 self,
1259 col: _T,
1260 *args: _ColumnExpressionOrLiteralArgument[Any],
1261 **kwargs: Any,
1262 ) -> pow[_T]: ...
1264 def pow( # noqa: A001
1265 self,
1266 col: _ColumnExpressionOrLiteralArgument[_T],
1267 *args: _ColumnExpressionOrLiteralArgument[Any],
1268 **kwargs: Any,
1269 ) -> pow[_T]: ...
1271 @property
1272 def random(self) -> Type[random]: ...
1274 @property
1275 def rank(self) -> Type[rank]: ...
1277 @property
1278 def rollup(self) -> Type[rollup[Any]]: ...
1280 @property
1281 def session_user(self) -> Type[session_user]: ...
1283 # set ColumnElement[_T] as a separate overload, to appease
1284 # mypy which seems to not want to accept _T from
1285 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1286 # _HasClauseElement as of mypy 1.15
1288 @overload
1289 def sum( # noqa: A001
1290 self,
1291 col: ColumnElement[_T],
1292 *args: _ColumnExpressionOrLiteralArgument[Any],
1293 **kwargs: Any,
1294 ) -> sum[_T]: ...
1296 @overload
1297 def sum( # noqa: A001
1298 self,
1299 col: _ColumnExpressionArgument[_T],
1300 *args: _ColumnExpressionOrLiteralArgument[Any],
1301 **kwargs: Any,
1302 ) -> sum[_T]: ...
1304 @overload
1305 def sum( # noqa: A001
1306 self,
1307 col: _T,
1308 *args: _ColumnExpressionOrLiteralArgument[Any],
1309 **kwargs: Any,
1310 ) -> sum[_T]: ...
1312 def sum( # noqa: A001
1313 self,
1314 col: _ColumnExpressionOrLiteralArgument[_T],
1315 *args: _ColumnExpressionOrLiteralArgument[Any],
1316 **kwargs: Any,
1317 ) -> sum[_T]: ...
1319 @property
1320 def sysdate(self) -> Type[sysdate]: ...
1322 @property
1323 def user(self) -> Type[user]: ...
1325 # END GENERATED FUNCTION ACCESSORS
1328func = _FunctionGenerator()
1329func.__doc__ = _FunctionGenerator.__doc__
1331modifier = _FunctionGenerator(group=False)
1334class Function(FunctionElement[_T]):
1335 r"""Describe a named SQL function.
1337 The :class:`.Function` object is typically generated from the
1338 :data:`.func` generation object.
1341 :param \*clauses: list of column expressions that form the arguments
1342 of the SQL function call.
1344 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1345 used as the return value of the column expression generated by this
1346 function call.
1348 :param packagenames: a string which indicates package prefix names
1349 to be prepended to the function name when the SQL is generated.
1350 The :data:`.func` generator creates these when it is called using
1351 dotted format, e.g.::
1353 func.mypackage.some_function(col1, col2)
1355 .. seealso::
1357 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1359 :data:`.func` - namespace which produces registered or ad-hoc
1360 :class:`.Function` instances.
1362 :class:`.GenericFunction` - allows creation of registered function
1363 types.
1365 """
1367 __visit_name__ = "function"
1369 _traverse_internals = FunctionElement._traverse_internals + [
1370 ("packagenames", InternalTraversal.dp_plain_obj),
1371 ("name", InternalTraversal.dp_string),
1372 ("type", InternalTraversal.dp_type),
1373 ]
1375 name: str
1377 identifier: str
1379 type: TypeEngine[_T]
1380 """A :class:`_types.TypeEngine` object which refers to the SQL return
1381 type represented by this SQL function.
1383 This datatype may be configured when generating a
1384 :class:`_functions.Function` object by passing the
1385 :paramref:`_functions.Function.type_` parameter, e.g.::
1387 >>> select(func.lower("some VALUE", type_=String))
1389 The small number of built-in classes of :class:`_functions.Function` come
1390 with a built-in datatype that's appropriate to the class of function and
1391 its arguments. For functions that aren't known, the type defaults to the
1392 "null type".
1394 """
1396 @overload
1397 def __init__(
1398 self,
1399 name: str,
1400 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1401 type_: None = ...,
1402 packagenames: Optional[Tuple[str, ...]] = ...,
1403 ) -> None: ...
1405 @overload
1406 def __init__(
1407 self,
1408 name: str,
1409 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1410 type_: _TypeEngineArgument[_T] = ...,
1411 packagenames: Optional[Tuple[str, ...]] = ...,
1412 ) -> None: ...
1414 def __init__(
1415 self,
1416 name: str,
1417 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1418 type_: Optional[_TypeEngineArgument[_T]] = None,
1419 packagenames: Optional[Tuple[str, ...]] = None,
1420 ) -> None:
1421 """Construct a :class:`.Function`.
1423 The :data:`.func` construct is normally used to construct
1424 new :class:`.Function` instances.
1426 """
1427 self.packagenames = packagenames or ()
1428 self.name = name
1430 # if type is None, we get NULLTYPE, which is our _T. But I don't
1431 # know how to get the overloads to express that correctly
1432 self.type = type_api.to_instance(type_) # type: ignore
1434 FunctionElement.__init__(self, *clauses)
1436 def _bind_param(
1437 self,
1438 operator: OperatorType,
1439 obj: Any,
1440 type_: Optional[TypeEngine[_T]] = None,
1441 expanding: bool = False,
1442 **kw: Any,
1443 ) -> BindParameter[_T]:
1444 return BindParameter(
1445 self.name,
1446 obj,
1447 _compared_to_operator=operator,
1448 _compared_to_type=self.type,
1449 type_=type_,
1450 unique=True,
1451 expanding=expanding,
1452 **kw,
1453 )
1456class GenericFunction(Function[_T]):
1457 """Define a 'generic' function.
1459 A generic function is a pre-established :class:`.Function`
1460 class that is instantiated automatically when called
1461 by name from the :data:`.func` attribute. Note that
1462 calling any name from :data:`.func` has the effect that
1463 a new :class:`.Function` instance is created automatically,
1464 given that name. The primary use case for defining
1465 a :class:`.GenericFunction` class is so that a function
1466 of a particular name may be given a fixed return type.
1467 It can also include custom argument parsing schemes as well
1468 as additional methods.
1470 Subclasses of :class:`.GenericFunction` are automatically
1471 registered under the name of the class. For
1472 example, a user-defined function ``as_utc()`` would
1473 be available immediately::
1475 from sqlalchemy.sql.functions import GenericFunction
1476 from sqlalchemy.types import DateTime
1479 class as_utc(GenericFunction):
1480 type = DateTime()
1481 inherit_cache = True
1484 print(select(func.as_utc()))
1486 User-defined generic functions can be organized into
1487 packages by specifying the "package" attribute when defining
1488 :class:`.GenericFunction`. Third party libraries
1489 containing many functions may want to use this in order
1490 to avoid name conflicts with other systems. For example,
1491 if our ``as_utc()`` function were part of a package
1492 "time"::
1494 class as_utc(GenericFunction):
1495 type = DateTime()
1496 package = "time"
1497 inherit_cache = True
1499 The above function would be available from :data:`.func`
1500 using the package name ``time``::
1502 print(select(func.time.as_utc()))
1504 A final option is to allow the function to be accessed
1505 from one name in :data:`.func` but to render as a different name.
1506 The ``identifier`` attribute will override the name used to
1507 access the function as loaded from :data:`.func`, but will retain
1508 the usage of ``name`` as the rendered name::
1510 class GeoBuffer(GenericFunction):
1511 type = Geometry()
1512 package = "geo"
1513 name = "ST_Buffer"
1514 identifier = "buffer"
1515 inherit_cache = True
1517 The above function will render as follows:
1519 .. sourcecode:: pycon+sql
1521 >>> print(func.geo.buffer())
1522 {printsql}ST_Buffer()
1524 The name will be rendered as is, however without quoting unless the name
1525 contains special characters that require quoting. To force quoting
1526 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1527 construct::
1529 from sqlalchemy.sql import quoted_name
1532 class GeoBuffer(GenericFunction):
1533 type = Geometry()
1534 package = "geo"
1535 name = quoted_name("ST_Buffer", True)
1536 identifier = "buffer"
1537 inherit_cache = True
1539 The above function will render as:
1541 .. sourcecode:: pycon+sql
1543 >>> print(func.geo.buffer())
1544 {printsql}"ST_Buffer"()
1546 Type parameters for this class as a
1547 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1548 and should match the type seen in a :class:`_engine.Result`. For example::
1550 class as_utc(GenericFunction[datetime.datetime]):
1551 type = DateTime()
1552 inherit_cache = True
1554 The above indicates that the following expression returns a ``datetime``
1555 object::
1557 connection.scalar(select(func.as_utc()))
1559 """
1561 coerce_arguments = True
1562 inherit_cache = True
1564 _register: bool
1566 name = "GenericFunction"
1568 def __init_subclass__(cls) -> None:
1569 if annotation.Annotated not in cls.__mro__:
1570 cls._register_generic_function(cls.__name__, cls.__dict__)
1571 super().__init_subclass__()
1573 @classmethod
1574 def _register_generic_function(
1575 cls, clsname: str, clsdict: Mapping[str, Any]
1576 ) -> None:
1577 cls.name = name = clsdict.get("name", clsname)
1578 cls.identifier = identifier = clsdict.get("identifier", name)
1579 package = clsdict.get("package", "_default")
1580 # legacy
1581 if "__return_type__" in clsdict:
1582 cls.type = clsdict["__return_type__"]
1584 # Check _register attribute status
1585 cls._register = getattr(cls, "_register", True)
1587 # Register the function if required
1588 if cls._register:
1589 register_function(identifier, cls, package)
1590 else:
1591 # Set _register to True to register child classes by default
1592 cls._register = True
1594 def __init__(
1595 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1596 ) -> None:
1597 parsed_args = kwargs.pop("_parsed_args", None)
1598 if parsed_args is None:
1599 parsed_args = [
1600 coercions.expect(
1601 roles.ExpressionElementRole,
1602 c,
1603 name=self.name,
1604 apply_propagate_attrs=self,
1605 )
1606 for c in args
1607 ]
1608 self._has_args = self._has_args or bool(parsed_args)
1609 self.packagenames = ()
1611 self.clause_expr = Grouping(
1612 ClauseList(
1613 operator=operators.comma_op, group_contents=True, *parsed_args
1614 )
1615 )
1617 self.type = type_api.to_instance( # type: ignore
1618 kwargs.pop("type_", None) or getattr(self, "type", None)
1619 )
1622register_function("cast", Cast) # type: ignore
1623register_function("extract", Extract) # type: ignore
1626class next_value(GenericFunction[int]):
1627 """Represent the 'next value', given a :class:`.Sequence`
1628 as its single argument.
1630 Compiles into the appropriate function on each backend,
1631 or will raise NotImplementedError if used on a backend
1632 that does not provide support for sequences.
1634 """
1636 type = sqltypes.Integer()
1637 name = "next_value"
1639 _traverse_internals = [
1640 ("sequence", InternalTraversal.dp_named_ddl_element)
1641 ]
1643 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1644 assert isinstance(
1645 seq, schema.Sequence
1646 ), "next_value() accepts a Sequence object as input."
1647 self.sequence = seq
1648 self.type = sqltypes.to_instance( # type: ignore
1649 seq.data_type or getattr(self, "type", None)
1650 )
1652 def compare(self, other: Any, **kw: Any) -> bool:
1653 return (
1654 isinstance(other, next_value)
1655 and self.sequence.name == other.sequence.name
1656 )
1658 @property
1659 def _from_objects(self) -> Any:
1660 return []
1663class AnsiFunction(GenericFunction[_T]):
1664 """Define a function in "ansi" format, which doesn't render parenthesis."""
1666 inherit_cache = True
1668 def __init__(
1669 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1670 ) -> None:
1671 GenericFunction.__init__(self, *args, **kwargs)
1674class ReturnTypeFromArgs(GenericFunction[_T]):
1675 """Define a function whose return type is bound to the type of its
1676 arguments.
1677 """
1679 inherit_cache = True
1681 # set ColumnElement[_T] as a separate overload, to appease
1682 # mypy which seems to not want to accept _T from
1683 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1684 # _HasClauseElement as of mypy 1.15
1686 @overload
1687 def __init__(
1688 self,
1689 col: ColumnElement[_T],
1690 *args: _ColumnExpressionOrLiteralArgument[Any],
1691 **kwargs: Any,
1692 ) -> None: ...
1694 @overload
1695 def __init__(
1696 self,
1697 col: _ColumnExpressionArgument[_T],
1698 *args: _ColumnExpressionOrLiteralArgument[Any],
1699 **kwargs: Any,
1700 ) -> None: ...
1702 @overload
1703 def __init__(
1704 self,
1705 col: _T,
1706 *args: _ColumnExpressionOrLiteralArgument[Any],
1707 **kwargs: Any,
1708 ) -> None: ...
1710 def __init__(
1711 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1712 ) -> None:
1713 fn_args: Sequence[ColumnElement[Any]] = [
1714 coercions.expect(
1715 roles.ExpressionElementRole,
1716 c,
1717 name=self.name,
1718 apply_propagate_attrs=self,
1719 )
1720 for c in args
1721 ]
1722 kwargs.setdefault("type_", _type_from_args(fn_args))
1723 kwargs["_parsed_args"] = fn_args
1724 super().__init__(*fn_args, **kwargs)
1727class ReturnTypeFromOptionalArgs(ReturnTypeFromArgs[_T]):
1728 inherit_cache = True
1730 @overload
1731 def __init__(
1732 self,
1733 col: ColumnElement[_T],
1734 *args: _ColumnExpressionOrLiteralArgument[Any],
1735 **kwargs: Any,
1736 ) -> None: ...
1738 @overload
1739 def __init__(
1740 self,
1741 col: _ColumnExpressionArgument[Optional[_T]],
1742 *args: _ColumnExpressionOrLiteralArgument[Any],
1743 **kwargs: Any,
1744 ) -> None: ...
1746 @overload
1747 def __init__(
1748 self,
1749 col: Optional[_T],
1750 *args: _ColumnExpressionOrLiteralArgument[Any],
1751 **kwargs: Any,
1752 ) -> None: ...
1754 def __init__(
1755 self,
1756 *args: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1757 **kwargs: Any,
1758 ) -> None:
1759 super().__init__(*args, **kwargs) # type: ignore
1762class coalesce(ReturnTypeFromOptionalArgs[_T]):
1763 _has_args = True
1764 inherit_cache = True
1767class max(ReturnTypeFromArgs[_T]): # noqa: A001
1768 """The SQL MAX() aggregate function."""
1770 inherit_cache = True
1773class min(ReturnTypeFromArgs[_T]): # noqa: A001
1774 """The SQL MIN() aggregate function."""
1776 inherit_cache = True
1779class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1780 """The SQL SUM() aggregate function."""
1782 inherit_cache = True
1785class now(GenericFunction[datetime.datetime]):
1786 """The SQL now() datetime function.
1788 SQLAlchemy dialects will usually render this particular function
1789 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1791 """
1793 type = sqltypes.DateTime()
1794 inherit_cache = True
1797class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1798 """The SQL POW() function which performs the power operator.
1800 E.g.:
1802 .. sourcecode:: pycon+sql
1804 >>> print(select(func.pow(2, 8)))
1805 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1807 .. versionadded:: 2.1
1809 """
1811 inherit_cache = True
1814class concat(GenericFunction[str]):
1815 """The SQL CONCAT() function, which concatenates strings.
1817 E.g.:
1819 .. sourcecode:: pycon+sql
1821 >>> print(select(func.concat("a", "b")))
1822 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1824 String concatenation in SQLAlchemy is more commonly available using the
1825 Python ``+`` operator with string datatypes, which will render a
1826 backend-specific concatenation operator, such as :
1828 .. sourcecode:: pycon+sql
1830 >>> print(select(literal("a") + "b"))
1831 {printsql}SELECT :param_1 || :param_2 AS anon_1
1834 """
1836 type = sqltypes.String()
1837 inherit_cache = True
1840class char_length(GenericFunction[int]):
1841 """The CHAR_LENGTH() SQL function."""
1843 type = sqltypes.Integer()
1844 inherit_cache = True
1846 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1847 # slight hack to limit to just one positional argument
1848 # not sure why this one function has this special treatment
1849 super().__init__(arg, **kw)
1852class random(GenericFunction[float]):
1853 """The RANDOM() SQL function."""
1855 _has_args = True
1856 inherit_cache = True
1859class count(GenericFunction[int]):
1860 r"""The ANSI COUNT aggregate function. With no arguments,
1861 emits COUNT \*.
1863 E.g.::
1865 from sqlalchemy import func
1866 from sqlalchemy import select
1867 from sqlalchemy import table, column
1869 my_table = table("some_table", column("id"))
1871 stmt = select(func.count()).select_from(my_table)
1873 Executing ``stmt`` would emit:
1875 .. sourcecode:: sql
1877 SELECT count(*) AS count_1
1878 FROM some_table
1881 """
1883 type = sqltypes.Integer()
1884 inherit_cache = True
1886 def __init__(
1887 self,
1888 expression: Union[
1889 _ColumnExpressionArgument[Any], _StarOrOne, None
1890 ] = None,
1891 **kwargs: Any,
1892 ) -> None:
1893 if expression is None:
1894 expression = literal_column("*")
1895 super().__init__(expression, **kwargs)
1898class current_date(AnsiFunction[datetime.date]):
1899 """The CURRENT_DATE() SQL function."""
1901 type = sqltypes.Date()
1902 inherit_cache = True
1905class current_time(AnsiFunction[datetime.time]):
1906 """The CURRENT_TIME() SQL function."""
1908 type = sqltypes.Time()
1909 inherit_cache = True
1912class current_timestamp(AnsiFunction[datetime.datetime]):
1913 """The CURRENT_TIMESTAMP() SQL function."""
1915 type = sqltypes.DateTime()
1916 inherit_cache = True
1919class current_user(AnsiFunction[str]):
1920 """The CURRENT_USER() SQL function."""
1922 type = sqltypes.String()
1923 inherit_cache = True
1926class localtime(AnsiFunction[datetime.datetime]):
1927 """The localtime() SQL function."""
1929 type = sqltypes.DateTime()
1930 inherit_cache = True
1933class localtimestamp(AnsiFunction[datetime.datetime]):
1934 """The localtimestamp() SQL function."""
1936 type = sqltypes.DateTime()
1937 inherit_cache = True
1940class session_user(AnsiFunction[str]):
1941 """The SESSION_USER() SQL function."""
1943 type = sqltypes.String()
1944 inherit_cache = True
1947class sysdate(AnsiFunction[datetime.datetime]):
1948 """The SYSDATE() SQL function."""
1950 type = sqltypes.DateTime()
1951 inherit_cache = True
1954class user(AnsiFunction[str]):
1955 """The USER() SQL function."""
1957 type = sqltypes.String()
1958 inherit_cache = True
1961class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1962 """Support for the ARRAY_AGG function.
1964 The ``func.array_agg(expr)`` construct returns an expression of
1965 type :class:`_types.ARRAY`.
1967 e.g.::
1969 stmt = select(func.array_agg(table.c.values)[2:5])
1971 .. seealso::
1973 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1974 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1975 added.
1977 """
1979 inherit_cache = True
1981 def __init__(
1982 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1983 ) -> None:
1984 fn_args: Sequence[ColumnElement[Any]] = [
1985 coercions.expect(
1986 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1987 )
1988 for c in args
1989 ]
1991 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1992 if "type_" not in kwargs:
1993 type_from_args = _type_from_args(fn_args)
1994 if isinstance(type_from_args, sqltypes.ARRAY):
1995 kwargs["type_"] = type_from_args
1996 else:
1997 kwargs["type_"] = default_array_type(
1998 type_from_args, dimensions=1
1999 )
2000 kwargs["_parsed_args"] = fn_args
2001 super().__init__(*fn_args, **kwargs)
2004class OrderedSetAgg(GenericFunction[_T]):
2005 """Define a function where the return type is based on the sort
2006 expression type as defined by the expression passed to the
2007 :meth:`.FunctionElement.within_group` method."""
2009 array_for_multi_clause = False
2010 inherit_cache = True
2012 def within_group_type(
2013 self, within_group: WithinGroup[Any]
2014 ) -> TypeEngine[Any]:
2015 func_clauses = cast(ClauseList, self.clause_expr.element)
2016 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
2017 within_group.order_by
2018 )
2019 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
2020 return sqltypes.ARRAY(order_by[0].type)
2021 else:
2022 return order_by[0].type
2025class mode(OrderedSetAgg[_T]):
2026 """Implement the ``mode`` ordered-set aggregate function.
2028 This function must be used with the :meth:`.FunctionElement.within_group`
2029 modifier to supply a sort expression to operate upon.
2031 The return type of this function is the same as the sort expression.
2033 """
2035 inherit_cache = True
2038class percentile_cont(OrderedSetAgg[_T]):
2039 """Implement the ``percentile_cont`` ordered-set aggregate function.
2041 This function must be used with the :meth:`.FunctionElement.within_group`
2042 modifier to supply a sort expression to operate upon.
2044 The return type of this function is the same as the sort expression,
2045 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2046 expression's type.
2048 """
2050 array_for_multi_clause = True
2051 inherit_cache = True
2054class percentile_disc(OrderedSetAgg[_T]):
2055 """Implement the ``percentile_disc`` ordered-set aggregate function.
2057 This function must be used with the :meth:`.FunctionElement.within_group`
2058 modifier to supply a sort expression to operate upon.
2060 The return type of this function is the same as the sort expression,
2061 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2062 expression's type.
2064 """
2066 array_for_multi_clause = True
2067 inherit_cache = True
2070class rank(GenericFunction[int]):
2071 """Implement the ``rank`` hypothetical-set aggregate function.
2073 This function must be used with the :meth:`.FunctionElement.within_group`
2074 modifier to supply a sort expression to operate upon.
2076 The return type of this function is :class:`.Integer`.
2078 """
2080 type = sqltypes.Integer()
2081 inherit_cache = True
2084class dense_rank(GenericFunction[int]):
2085 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2087 This function must be used with the :meth:`.FunctionElement.within_group`
2088 modifier to supply a sort expression to operate upon.
2090 The return type of this function is :class:`.Integer`.
2092 """
2094 type = sqltypes.Integer()
2095 inherit_cache = True
2098class percent_rank(GenericFunction[decimal.Decimal]):
2099 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2101 This function must be used with the :meth:`.FunctionElement.within_group`
2102 modifier to supply a sort expression to operate upon.
2104 The return type of this function is :class:`.Numeric`.
2106 """
2108 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2109 inherit_cache = True
2112class cume_dist(GenericFunction[decimal.Decimal]):
2113 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2115 This function must be used with the :meth:`.FunctionElement.within_group`
2116 modifier to supply a sort expression to operate upon.
2118 The return type of this function is :class:`.Numeric`.
2120 """
2122 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2123 inherit_cache = True
2126class cube(GenericFunction[_T]):
2127 r"""Implement the ``CUBE`` grouping operation.
2129 This function is used as part of the GROUP BY of a statement,
2130 e.g. :meth:`_expression.Select.group_by`::
2132 stmt = select(
2133 func.sum(table.c.value), table.c.col_1, table.c.col_2
2134 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2136 """
2138 _has_args = True
2139 inherit_cache = True
2142class rollup(GenericFunction[_T]):
2143 r"""Implement the ``ROLLUP`` grouping operation.
2145 This function is used as part of the GROUP BY of a statement,
2146 e.g. :meth:`_expression.Select.group_by`::
2148 stmt = select(
2149 func.sum(table.c.value), table.c.col_1, table.c.col_2
2150 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2152 """
2154 _has_args = True
2155 inherit_cache = True
2158class grouping_sets(GenericFunction[_T]):
2159 r"""Implement the ``GROUPING SETS`` grouping operation.
2161 This function is used as part of the GROUP BY of a statement,
2162 e.g. :meth:`_expression.Select.group_by`::
2164 stmt = select(
2165 func.sum(table.c.value), table.c.col_1, table.c.col_2
2166 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2168 In order to group by multiple sets, use the :func:`.tuple_` construct::
2170 from sqlalchemy import tuple_
2172 stmt = select(
2173 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2174 ).group_by(
2175 func.grouping_sets(
2176 tuple_(table.c.col_1, table.c.col_2),
2177 tuple_(table.c.value, table.c.col_3),
2178 )
2179 )
2181 """ # noqa: E501
2183 _has_args = True
2184 inherit_cache = True
2187class aggregate_strings(GenericFunction[str]):
2188 """Implement a generic string aggregation function.
2190 This function will concatenate non-null values into a string and
2191 separate the values by a delimiter.
2193 This function is compiled on a per-backend basis, into functions
2194 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2196 e.g. Example usage with delimiter '.'::
2198 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2200 .. versionadded:: 2.0.21
2202 To add ordering to the expression, use the
2203 :meth:`_functions.FunctionElement.aggregate_order_by` modifier method,
2204 which will emit ORDER BY within the appropriate part of the column
2205 expression (varies by backend)::
2207 stmt = select(
2208 func.aggregate_strings(table.c.str_col, ".").aggregate_order_by(
2209 table.c.str_col
2210 )
2211 )
2213 .. versionadded:: 2.1 added :meth:`_functions.FunctionElement.aggregate_order_by`
2214 for all aggregate functions.
2216 :param clause: the SQL expression to be concatenated
2218 :param separator: separator string
2221 """ # noqa: E501
2223 type = sqltypes.String()
2224 _has_args = True
2225 inherit_cache = True
2227 def __init__(
2228 self,
2229 clause: _ColumnExpressionArgument[Any],
2230 separator: str,
2231 ) -> None:
2232 super().__init__(clause, separator)