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 monotonic: bool = False
151 _has_args = False
152 _with_ordinality = False
153 _table_value_type: Optional[TableValueType] = None
155 # some attributes that are defined between both ColumnElement and
156 # FromClause are set to Any here to avoid typing errors
157 primary_key: Any
158 _is_clone_of: Any
160 clause_expr: Grouping[Any]
162 def __init__(
163 self, *clauses: _ColumnExpressionOrLiteralArgument[Any]
164 ) -> None:
165 r"""Construct a :class:`.FunctionElement`.
167 :param \*clauses: list of column expressions that form the arguments
168 of the SQL function call.
170 :param \**kwargs: additional kwargs are typically consumed by
171 subclasses.
173 .. seealso::
175 :data:`.func`
177 :class:`.Function`
179 """
180 args: Sequence[_ColumnExpressionArgument[Any]] = [
181 coercions.expect(
182 roles.ExpressionElementRole,
183 c,
184 name=getattr(self, "name", None),
185 apply_propagate_attrs=self,
186 )
187 for c in clauses
188 ]
189 self._has_args = self._has_args or bool(args)
190 self.clause_expr = Grouping(
191 ClauseList(operator=operators.comma_op, group_contents=True, *args)
192 )
194 _non_anon_label = None
196 @property
197 def _proxy_key(self) -> Any:
198 return super()._proxy_key or getattr(self, "name", None)
200 def _execute_on_connection(
201 self,
202 connection: Connection,
203 distilled_params: _CoreMultiExecuteParams,
204 execution_options: CoreExecuteOptionsParameter,
205 ) -> CursorResult[Any]:
206 return connection._execute_function(
207 self, distilled_params, execution_options
208 )
210 def scalar_table_valued(
211 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
212 ) -> ScalarFunctionColumn[_T]:
213 """Return a column expression that's against this
214 :class:`_functions.FunctionElement` as a scalar
215 table-valued expression.
217 The returned expression is similar to that returned by a single column
218 accessed off of a :meth:`_functions.FunctionElement.table_valued`
219 construct, except no FROM clause is generated; the function is rendered
220 in the similar way as a scalar subquery.
222 E.g.:
224 .. sourcecode:: pycon+sql
226 >>> from sqlalchemy import func, select
227 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
228 >>> print(select(fn))
229 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
231 .. versionadded:: 1.4.0b2
233 .. seealso::
235 :meth:`_functions.FunctionElement.table_valued`
237 :meth:`_functions.FunctionElement.alias`
239 :meth:`_functions.FunctionElement.column_valued`
241 """ # noqa: E501
243 return ScalarFunctionColumn(self, name, type_)
245 def table_valued(
246 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
247 ) -> TableValuedAlias:
248 r"""Return a :class:`_sql.TableValuedAlias` representation of this
249 :class:`_functions.FunctionElement` with table-valued expressions added.
251 e.g.:
253 .. sourcecode:: pycon+sql
255 >>> fn = func.generate_series(1, 5).table_valued(
256 ... "value", "start", "stop", "step"
257 ... )
259 >>> print(select(fn))
260 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
261 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
263 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
264 {printsql}SELECT anon_1.value, anon_1.stop
265 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
266 WHERE anon_1.value > :value_1{stop}
268 A WITH ORDINALITY expression may be generated by passing the keyword
269 argument "with_ordinality":
271 .. sourcecode:: pycon+sql
273 >>> fn = func.generate_series(4, 1, -1).table_valued(
274 ... "gen", with_ordinality="ordinality"
275 ... )
276 >>> print(select(fn))
277 {printsql}SELECT anon_1.gen, anon_1.ordinality
278 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1
280 :param \*expr: A series of string column names that will be added to the
281 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
282 construct as columns. :func:`_sql.column` objects with or without
283 datatypes may also be used.
285 :param name: optional name to assign to the alias name that's generated.
286 If omitted, a unique anonymizing name is used.
288 :param with_ordinality: string name that when present results in the
289 ``WITH ORDINALITY`` clause being added to the alias, and the given
290 string name will be added as a column to the .c collection
291 of the resulting :class:`_sql.TableValuedAlias`.
293 :param joins_implicitly: when True, the table valued function may be
294 used in the FROM clause without any explicit JOIN to other tables
295 in the SQL query, and no "cartesian product" warning will be generated.
296 May be useful for SQL functions such as ``func.json_each()``.
298 .. versionadded:: 1.4.33
300 .. versionadded:: 1.4.0b2
303 .. seealso::
305 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
307 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
309 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
310 :meth:`_functions.FunctionElement.table_valued` which delivers the
311 complete table valued expression as a scalar column expression
313 :meth:`_functions.FunctionElement.column_valued`
315 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
316 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
318 """ # noqa: 501
320 new_func = self._generate()
322 with_ordinality = kw.pop("with_ordinality", None)
323 joins_implicitly = kw.pop("joins_implicitly", None)
324 name = kw.pop("name", None)
326 if with_ordinality:
327 expr += (with_ordinality,)
328 new_func._with_ordinality = True
330 new_func.type = new_func._table_value_type = TableValueType(*expr)
332 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
334 def column_valued(
335 self, name: Optional[str] = None, joins_implicitly: bool = False
336 ) -> TableValuedColumn[_T]:
337 """Return this :class:`_functions.FunctionElement` as a column expression that
338 selects from itself as a FROM clause.
340 E.g.:
342 .. sourcecode:: pycon+sql
344 >>> from sqlalchemy import select, func
345 >>> gs = func.generate_series(1, 5, -1).column_valued()
346 >>> print(select(gs))
347 {printsql}SELECT anon_1
348 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
350 This is shorthand for::
352 gs = func.generate_series(1, 5, -1).alias().column
354 :param name: optional name to assign to the alias name that's generated.
355 If omitted, a unique anonymizing name is used.
357 :param joins_implicitly: when True, the "table" portion of the column
358 valued function may be a member of the FROM clause without any
359 explicit JOIN to other tables in the SQL query, and no "cartesian
360 product" warning will be generated. May be useful for SQL functions
361 such as ``func.json_array_elements()``.
363 .. versionadded:: 1.4.46
365 .. seealso::
367 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
369 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
371 :meth:`_functions.FunctionElement.table_valued`
373 """ # noqa: 501
375 return self.alias(name=name, joins_implicitly=joins_implicitly).column
377 @util.ro_non_memoized_property
378 def columns(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
379 r"""The set of columns exported by this :class:`.FunctionElement`.
381 This is a placeholder collection that allows the function to be
382 placed in the FROM clause of a statement:
384 .. sourcecode:: pycon+sql
386 >>> from sqlalchemy import column, select, func
387 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
388 >>> print(stmt)
389 {printsql}SELECT x, y FROM myfunction()
391 The above form is a legacy feature that is now superseded by the
392 fully capable :meth:`_functions.FunctionElement.table_valued`
393 method; see that method for details.
395 .. seealso::
397 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
398 SQL function expressions.
400 """ # noqa: E501
401 return self.c
403 @util.ro_memoized_property
404 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
405 """synonym for :attr:`.FunctionElement.columns`."""
407 return ColumnCollection(
408 columns=[(col.key, col) for col in self._all_selected_columns]
409 )
411 @property
412 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
413 if is_table_value_type(self.type):
414 # TODO: this might not be fully accurate
415 cols = cast(
416 "Sequence[KeyedColumnElement[Any]]", self.type._elements
417 )
418 else:
419 cols = [self.label(None)]
421 return cols
423 @property
424 def exported_columns( # type: ignore[override]
425 self,
426 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
427 return self.columns
429 @HasMemoized.memoized_attribute
430 def clauses(self) -> ClauseList:
431 """Return the underlying :class:`.ClauseList` which contains
432 the arguments for this :class:`.FunctionElement`.
434 """
435 return cast(ClauseList, self.clause_expr.element)
437 def over(
438 self,
439 *,
440 partition_by: _ByArgument | None = None,
441 order_by: _ByArgument | None = None,
442 rows: _FrameIntTuple | FrameClause | None = None,
443 range_: _FrameIntTuple | FrameClause | None = None,
444 groups: _FrameIntTuple | FrameClause | None = None,
445 ) -> Over[_T]:
446 """Produce an OVER clause against this function.
448 Used against aggregate or so-called "window" functions,
449 for database backends that support window functions.
451 The expression::
453 func.row_number().over(order_by="x")
455 is shorthand for::
457 from sqlalchemy import over
459 over(func.row_number(), order_by="x")
461 See :func:`_expression.over` for a full description.
463 .. seealso::
465 :func:`_expression.over`
467 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
469 """
470 return Over(
471 self,
472 partition_by=partition_by,
473 order_by=order_by,
474 rows=rows,
475 range_=range_,
476 groups=groups,
477 )
479 def aggregate_order_by(
480 self, *order_by: _ColumnExpressionArgument[Any]
481 ) -> AggregateOrderBy[_T]:
482 r"""Produce a :class:`.AggregateOrderBy` object against a function.
484 Used for aggregating functions such as :class:`_functions.array_agg`,
485 ``group_concat``, ``json_agg`` on backends that support ordering via an
486 embedded ORDER BY parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
487 When used on backends like Oracle and SQL Server, SQL compilation uses
488 that of :class:`.WithinGroup`.
490 See :func:`_expression.aggregate_order_by` for a full description.
492 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
493 :func:`_postgresql.aggregate_order_by` function to a method on
494 :class:`.Function` that is backend agnostic.
496 .. seealso::
498 :class:`_functions.aggregate_strings` - backend-agnostic string
499 concatenation function which also supports ORDER BY
501 """
503 return AggregateOrderBy(self, *order_by)
505 def within_group(
506 self, *order_by: _ColumnExpressionArgument[Any]
507 ) -> WithinGroup[_T]:
508 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
510 Used against so-called "ordered set aggregate" and "hypothetical
511 set aggregate" functions, including :class:`.percentile_cont`,
512 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
513 used by PostgreSQL, Oracle Database, and Microsoft SQL Server.
515 For simple ORDER BY expressions within aggregate functions on
516 PostgreSQL, MySQL/MariaDB, SQLite, see :func:`_sql.aggregate_order_by`.
518 See :func:`_expression.within_group` for a full description.
520 .. seealso::
522 :ref:`tutorial_functions_within_group` -
523 in the :ref:`unified_tutorial`
526 """
527 return WithinGroup(self, *order_by)
529 @overload
530 def filter(self) -> Self: ...
532 @overload
533 def filter(
534 self,
535 __criterion0: _ColumnExpressionArgument[bool],
536 *criterion: _ColumnExpressionArgument[bool],
537 ) -> FunctionFilter[_T]: ...
539 def filter(
540 self, *criterion: _ColumnExpressionArgument[bool]
541 ) -> Union[Self, FunctionFilter[_T]]:
542 """Produce a FILTER clause against this function.
544 Used against aggregate and window functions,
545 for database backends that support the "FILTER" clause.
547 The expression::
549 func.count(1).filter(True)
551 is shorthand for::
553 from sqlalchemy import funcfilter
555 funcfilter(func.count(1), True)
557 .. seealso::
559 :ref:`tutorial_functions_within_group` -
560 in the :ref:`unified_tutorial`
562 :class:`.FunctionFilter`
564 :func:`.funcfilter`
567 """
568 if not criterion:
569 return self
570 return FunctionFilter(self, *criterion)
572 def as_comparison(
573 self, left_index: int, right_index: int
574 ) -> FunctionAsBinary:
575 """Interpret this expression as a boolean comparison between two
576 values.
578 This method is used for an ORM use case described at
579 :ref:`relationship_custom_operator_sql_function`.
581 A hypothetical SQL function "is_equal()" which compares to values
582 for equality would be written in the Core expression language as::
584 expr = func.is_equal("a", "b")
586 If "is_equal()" above is comparing "a" and "b" for equality, the
587 :meth:`.FunctionElement.as_comparison` method would be invoked as::
589 expr = func.is_equal("a", "b").as_comparison(1, 2)
591 Where above, the integer value "1" refers to the first argument of the
592 "is_equal()" function and the integer value "2" refers to the second.
594 This would create a :class:`.BinaryExpression` that is equivalent to::
596 BinaryExpression("a", "b", operator=op.eq)
598 However, at the SQL level it would still render as
599 "is_equal('a', 'b')".
601 The ORM, when it loads a related object or collection, needs to be able
602 to manipulate the "left" and "right" sides of the ON clause of a JOIN
603 expression. The purpose of this method is to provide a SQL function
604 construct that can also supply this information to the ORM, when used
605 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
606 return value is a containment object called :class:`.FunctionAsBinary`.
608 An ORM example is as follows::
610 class Venue(Base):
611 __tablename__ = "venue"
612 id = Column(Integer, primary_key=True)
613 name = Column(String)
615 descendants = relationship(
616 "Venue",
617 primaryjoin=func.instr(
618 remote(foreign(name)), name + "/"
619 ).as_comparison(1, 2)
620 == 1,
621 viewonly=True,
622 order_by=name,
623 )
625 Above, the "Venue" class can load descendant "Venue" objects by
626 determining if the name of the parent Venue is contained within the
627 start of the hypothetical descendant value's name, e.g. "parent1" would
628 match up to "parent1/child1", but not to "parent2/child1".
630 Possible use cases include the "materialized path" example given above,
631 as well as making use of special SQL functions such as geometric
632 functions to create join conditions.
634 :param left_index: the integer 1-based index of the function argument
635 that serves as the "left" side of the expression.
636 :param right_index: the integer 1-based index of the function argument
637 that serves as the "right" side of the expression.
639 .. seealso::
641 :ref:`relationship_custom_operator_sql_function` -
642 example use within the ORM
644 """
645 return FunctionAsBinary(self, left_index, right_index)
647 @property
648 def _from_objects(self) -> Any:
649 return self.clauses._from_objects
651 def within_group_type(
652 self, within_group: WithinGroup[_S]
653 ) -> Optional[TypeEngine[_S]]:
654 """For types that define their return type as based on the criteria
655 within a WITHIN GROUP (ORDER BY) expression, called by the
656 :class:`.WithinGroup` construct.
658 Returns None by default, in which case the function's normal ``.type``
659 is used.
661 """
663 return None
665 def alias(
666 self, name: Optional[str] = None, joins_implicitly: bool = False
667 ) -> TableValuedAlias:
668 r"""Produce a :class:`_expression.Alias` construct against this
669 :class:`.FunctionElement`.
671 .. tip::
673 The :meth:`_functions.FunctionElement.alias` method is part of the
674 mechanism by which "table valued" SQL functions are created.
675 However, most use cases are covered by higher level methods on
676 :class:`_functions.FunctionElement` including
677 :meth:`_functions.FunctionElement.table_valued`, and
678 :meth:`_functions.FunctionElement.column_valued`.
680 This construct wraps the function in a named alias which
681 is suitable for the FROM clause, in the style accepted for example
682 by PostgreSQL. A column expression is also provided using the
683 special ``.column`` attribute, which may
684 be used to refer to the output of the function as a scalar value
685 in the columns or where clause, for a backend such as PostgreSQL.
687 For a full table-valued expression, use the
688 :meth:`_functions.FunctionElement.table_valued` method first to
689 establish named columns.
691 e.g.:
693 .. sourcecode:: pycon+sql
695 >>> from sqlalchemy import func, select, column
696 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
697 >>> print(select(data_view.column))
698 {printsql}SELECT data_view
699 FROM unnest(:unnest_1) AS data_view
701 The :meth:`_functions.FunctionElement.column_valued` method provides
702 a shortcut for the above pattern:
704 .. sourcecode:: pycon+sql
706 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
707 >>> print(select(data_view))
708 {printsql}SELECT data_view
709 FROM unnest(:unnest_1) AS data_view
711 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
713 :param name: alias name, will be rendered as ``AS <name>`` in the
714 FROM clause
716 :param joins_implicitly: when True, the table valued function may be
717 used in the FROM clause without any explicit JOIN to other tables
718 in the SQL query, and no "cartesian product" warning will be
719 generated. May be useful for SQL functions such as
720 ``func.json_each()``.
722 .. versionadded:: 1.4.33
724 .. seealso::
726 :ref:`tutorial_functions_table_valued` -
727 in the :ref:`unified_tutorial`
729 :meth:`_functions.FunctionElement.table_valued`
731 :meth:`_functions.FunctionElement.scalar_table_valued`
733 :meth:`_functions.FunctionElement.column_valued`
736 """
738 return TableValuedAlias._construct(
739 self,
740 name=name,
741 table_value_type=self.type,
742 joins_implicitly=joins_implicitly,
743 )
745 def select(self) -> Select[_T]:
746 """Produce a :func:`_expression.select` construct
747 against this :class:`.FunctionElement`.
749 This is shorthand for::
751 s = select(function_element)
753 """
754 s: Select[_T] = Select(self)
755 if self._execution_options:
756 s = s.execution_options(**self._execution_options)
757 return s
759 def _bind_param(
760 self,
761 operator: OperatorType,
762 obj: Any,
763 type_: Optional[TypeEngine[_T]] = None,
764 expanding: bool = False,
765 **kw: Any,
766 ) -> BindParameter[_T]:
767 return BindParameter(
768 None,
769 obj,
770 _compared_to_operator=operator,
771 _compared_to_type=self.type,
772 unique=True,
773 type_=type_,
774 expanding=expanding,
775 **kw,
776 )
778 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
779 # for the moment, we are parenthesizing all array-returning
780 # expressions against getitem. This may need to be made
781 # more portable if in the future we support other DBs
782 # besides postgresql.
783 if against is operators.getitem and isinstance(
784 self.type, sqltypes.ARRAY
785 ):
786 return Grouping(self)
787 else:
788 return super().self_group(against=against)
790 @property
791 def entity_namespace(self) -> _EntityNamespace:
792 """overrides FromClause.entity_namespace as functions are generally
793 column expressions and not FromClauses.
795 """
796 # ideally functions would not be fromclauses but we failed to make
797 # this adjustment in 1.4
798 return _entity_namespace(self.clause_expr)
801class FunctionAsBinary(BinaryExpression[Any]):
802 _traverse_internals = [
803 ("sql_function", InternalTraversal.dp_clauseelement),
804 ("left_index", InternalTraversal.dp_plain_obj),
805 ("right_index", InternalTraversal.dp_plain_obj),
806 ("modifiers", InternalTraversal.dp_plain_dict),
807 ]
809 sql_function: FunctionElement[Any]
810 left_index: int
811 right_index: int
813 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
814 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
816 def __init__(
817 self, fn: FunctionElement[Any], left_index: int, right_index: int
818 ) -> None:
819 self.sql_function = fn
820 self.left_index = left_index
821 self.right_index = right_index
823 self.operator = operators.function_as_comparison_op
824 self.type = sqltypes.BOOLEANTYPE
825 self.negate = None
826 self._is_implicitly_boolean = True
827 self.modifiers = util.immutabledict({})
829 @property
830 def left_expr(self) -> ColumnElement[Any]:
831 return self.sql_function.clauses.clauses[self.left_index - 1]
833 @left_expr.setter
834 def left_expr(self, value: ColumnElement[Any]) -> None:
835 self.sql_function.clauses.clauses[self.left_index - 1] = value
837 @property
838 def right_expr(self) -> ColumnElement[Any]:
839 return self.sql_function.clauses.clauses[self.right_index - 1]
841 @right_expr.setter
842 def right_expr(self, value: ColumnElement[Any]) -> None:
843 self.sql_function.clauses.clauses[self.right_index - 1] = value
845 if not TYPE_CHECKING:
846 # mypy can't accommodate @property to replace an instance
847 # variable
849 left = left_expr
850 right = right_expr
853class ScalarFunctionColumn(NamedColumn[_T]):
854 __visit_name__ = "scalar_function_column"
856 _traverse_internals = [
857 ("name", InternalTraversal.dp_anon_name),
858 ("type", InternalTraversal.dp_type),
859 ("fn", InternalTraversal.dp_clauseelement),
860 ]
862 is_literal = False
863 table = None
865 def __init__(
866 self,
867 fn: FunctionElement[_T],
868 name: str,
869 type_: Optional[_TypeEngineArgument[_T]] = None,
870 ) -> None:
871 self.fn = fn
872 self.name = name
874 # if type is None, we get NULLTYPE, which is our _T. But I don't
875 # know how to get the overloads to express that correctly
876 self.type = type_api.to_instance(type_) # type: ignore
879class _FunctionGenerator:
880 """Generate SQL function expressions.
882 :data:`.func` is a special object instance which generates SQL
883 functions based on name-based attributes, e.g.:
885 .. sourcecode:: pycon+sql
887 >>> print(func.count(1))
888 {printsql}count(:param_1)
890 The returned object is an instance of :class:`.Function`, and is a
891 column-oriented SQL element like any other, and is used in that way:
893 .. sourcecode:: pycon+sql
895 >>> print(select(func.count(table.c.id)))
896 {printsql}SELECT count(sometable.id) FROM sometable
898 Any name can be given to :data:`.func`. If the function name is unknown to
899 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
900 which SQLAlchemy is aware of, the name may be interpreted as a *generic
901 function* which will be compiled appropriately to the target database:
903 .. sourcecode:: pycon+sql
905 >>> print(func.current_timestamp())
906 {printsql}CURRENT_TIMESTAMP
908 To call functions which are present in dot-separated packages,
909 specify them in the same manner:
911 .. sourcecode:: pycon+sql
913 >>> print(func.stats.yield_curve(5, 10))
914 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
916 SQLAlchemy can be made aware of the return type of functions to enable
917 type-specific lexical and result-based behavior. For example, to ensure
918 that a string-based function returns a Unicode value and is similarly
919 treated as a string in expressions, specify
920 :class:`~sqlalchemy.types.Unicode` as the type:
922 .. sourcecode:: pycon+sql
924 >>> print(
925 ... func.my_string("hi", type_=Unicode)
926 ... + " "
927 ... + func.my_string("there", type_=Unicode)
928 ... )
929 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
931 The object returned by a :data:`.func` call is usually an instance of
932 :class:`.Function`.
933 This object meets the "column" interface, including comparison and labeling
934 functions. The object can also be passed the :meth:`~.Connectable.execute`
935 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
936 where it will be
937 wrapped inside of a SELECT statement first::
939 print(connection.execute(func.current_timestamp()).scalar())
941 In a few exception cases, the :data:`.func` accessor
942 will redirect a name to a built-in expression such as :func:`.cast`
943 or :func:`.extract`, as these names have well-known meaning
944 but are not exactly the same as "functions" from a SQLAlchemy
945 perspective.
947 Functions which are interpreted as "generic" functions know how to
948 calculate their return type automatically. For a listing of known generic
949 functions, see :ref:`generic_functions`.
951 .. note::
953 The :data:`.func` construct has only limited support for calling
954 standalone "stored procedures", especially those with special
955 parameterization concerns.
957 See the section :ref:`stored_procedures` for details on how to use
958 the DBAPI-level ``callproc()`` method for fully traditional stored
959 procedures.
961 .. seealso::
963 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
965 :class:`.Function`
967 """ # noqa
969 def __init__(self, **opts: Any) -> None:
970 self.__names: List[str] = []
971 self.opts = opts
973 def __getattr__(self, name: str) -> _FunctionGenerator:
974 # passthru __ attributes; fixes pydoc
975 if name.startswith("__"):
976 try:
977 return self.__dict__[name] # type: ignore
978 except KeyError:
979 raise AttributeError(name)
981 elif name.endswith("_"):
982 name = name[0:-1]
983 f = _FunctionGenerator(**self.opts)
984 f.__names = list(self.__names) + [name]
985 return f
987 @overload
988 def __call__(
989 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
990 ) -> Function[_T]: ...
992 @overload
993 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
995 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
996 o = self.opts.copy()
997 o.update(kwargs)
999 tokens = len(self.__names)
1001 if tokens == 2:
1002 package, fname = self.__names
1003 elif tokens == 1:
1004 package, fname = "_default", self.__names[0]
1005 else:
1006 package = None
1008 if package is not None:
1009 func = _registry[package].get(fname.lower())
1010 if func is not None:
1011 return func(*c, **o)
1013 return Function(
1014 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
1015 )
1017 if TYPE_CHECKING:
1018 # START GENERATED FUNCTION ACCESSORS
1020 # code within this block is **programmatically,
1021 # statically generated** by tools/generate_sql_functions.py
1023 @property
1024 def aggregate_strings(self) -> Type[aggregate_strings]: ...
1026 @property
1027 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
1029 # set ColumnElement[_T] as a separate overload, to appease
1030 # mypy which seems to not want to accept _T from
1031 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1032 # _HasClauseElement as of mypy 1.15
1034 @overload
1035 def array_agg(
1036 self,
1037 col: ColumnElement[_T],
1038 *args: _ColumnExpressionOrLiteralArgument[Any],
1039 **kwargs: Any,
1040 ) -> array_agg[_T]: ...
1042 @overload
1043 def array_agg(
1044 self,
1045 col: _ColumnExpressionArgument[_T],
1046 *args: _ColumnExpressionOrLiteralArgument[Any],
1047 **kwargs: Any,
1048 ) -> array_agg[_T]: ...
1050 @overload
1051 def array_agg(
1052 self,
1053 col: _T,
1054 *args: _ColumnExpressionOrLiteralArgument[Any],
1055 **kwargs: Any,
1056 ) -> array_agg[_T]: ...
1058 def array_agg(
1059 self,
1060 col: _ColumnExpressionOrLiteralArgument[_T],
1061 *args: _ColumnExpressionOrLiteralArgument[Any],
1062 **kwargs: Any,
1063 ) -> array_agg[_T]: ...
1065 @property
1066 def cast(self) -> Type[Cast[Any]]: ...
1068 @property
1069 def char_length(self) -> Type[char_length]: ...
1071 # set ColumnElement[_T] as a separate overload, to appease
1072 # mypy which seems to not want to accept _T from
1073 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1074 # _HasClauseElement as of mypy 1.15
1076 @overload
1077 def coalesce(
1078 self,
1079 col: ColumnElement[_T],
1080 *args: _ColumnExpressionOrLiteralArgument[Any],
1081 **kwargs: Any,
1082 ) -> coalesce[_T]: ...
1084 @overload
1085 def coalesce(
1086 self,
1087 col: _ColumnExpressionArgument[Optional[_T]],
1088 *args: _ColumnExpressionOrLiteralArgument[Any],
1089 **kwargs: Any,
1090 ) -> coalesce[_T]: ...
1092 @overload
1093 def coalesce(
1094 self,
1095 col: Optional[_T],
1096 *args: _ColumnExpressionOrLiteralArgument[Any],
1097 **kwargs: Any,
1098 ) -> coalesce[_T]: ...
1100 def coalesce(
1101 self,
1102 col: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1103 *args: _ColumnExpressionOrLiteralArgument[Any],
1104 **kwargs: Any,
1105 ) -> coalesce[_T]: ...
1107 @property
1108 def concat(self) -> Type[concat]: ...
1110 @property
1111 def count(self) -> Type[count]: ...
1113 @property
1114 def cube(self) -> Type[cube[Any]]: ...
1116 @property
1117 def cume_dist(self) -> Type[cume_dist]: ...
1119 @property
1120 def current_date(self) -> Type[current_date]: ...
1122 @property
1123 def current_time(self) -> Type[current_time]: ...
1125 @property
1126 def current_timestamp(self) -> Type[current_timestamp]: ...
1128 @property
1129 def current_user(self) -> Type[current_user]: ...
1131 @property
1132 def dense_rank(self) -> Type[dense_rank]: ...
1134 @property
1135 def extract(self) -> Type[Extract]: ...
1137 @property
1138 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1140 @property
1141 def localtime(self) -> Type[localtime]: ...
1143 @property
1144 def localtimestamp(self) -> Type[localtimestamp]: ...
1146 # set ColumnElement[_T] as a separate overload, to appease
1147 # mypy which seems to not want to accept _T from
1148 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1149 # _HasClauseElement as of mypy 1.15
1151 @overload
1152 def max( # noqa: A001
1153 self,
1154 col: ColumnElement[_T],
1155 *args: _ColumnExpressionOrLiteralArgument[Any],
1156 **kwargs: Any,
1157 ) -> max[_T]: ...
1159 @overload
1160 def max( # noqa: A001
1161 self,
1162 col: _ColumnExpressionArgument[_T],
1163 *args: _ColumnExpressionOrLiteralArgument[Any],
1164 **kwargs: Any,
1165 ) -> max[_T]: ...
1167 @overload
1168 def max( # noqa: A001
1169 self,
1170 col: _T,
1171 *args: _ColumnExpressionOrLiteralArgument[Any],
1172 **kwargs: Any,
1173 ) -> max[_T]: ...
1175 def max( # noqa: A001
1176 self,
1177 col: _ColumnExpressionOrLiteralArgument[_T],
1178 *args: _ColumnExpressionOrLiteralArgument[Any],
1179 **kwargs: Any,
1180 ) -> max[_T]: ...
1182 # set ColumnElement[_T] as a separate overload, to appease
1183 # mypy which seems to not want to accept _T from
1184 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1185 # _HasClauseElement as of mypy 1.15
1187 @overload
1188 def min( # noqa: A001
1189 self,
1190 col: ColumnElement[_T],
1191 *args: _ColumnExpressionOrLiteralArgument[Any],
1192 **kwargs: Any,
1193 ) -> min[_T]: ...
1195 @overload
1196 def min( # noqa: A001
1197 self,
1198 col: _ColumnExpressionArgument[_T],
1199 *args: _ColumnExpressionOrLiteralArgument[Any],
1200 **kwargs: Any,
1201 ) -> min[_T]: ...
1203 @overload
1204 def min( # noqa: A001
1205 self,
1206 col: _T,
1207 *args: _ColumnExpressionOrLiteralArgument[Any],
1208 **kwargs: Any,
1209 ) -> min[_T]: ...
1211 def min( # noqa: A001
1212 self,
1213 col: _ColumnExpressionOrLiteralArgument[_T],
1214 *args: _ColumnExpressionOrLiteralArgument[Any],
1215 **kwargs: Any,
1216 ) -> min[_T]: ...
1218 @property
1219 def mode(self) -> Type[mode[Any]]: ...
1221 @property
1222 def next_value(self) -> Type[next_value]: ...
1224 @property
1225 def now(self) -> Type[now]: ...
1227 @property
1228 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1230 @property
1231 def percent_rank(self) -> Type[percent_rank]: ...
1233 @property
1234 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1236 @property
1237 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1239 # set ColumnElement[_T] as a separate overload, to appease
1240 # mypy which seems to not want to accept _T from
1241 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1242 # _HasClauseElement as of mypy 1.15
1244 @overload
1245 def pow( # noqa: A001
1246 self,
1247 col: ColumnElement[_T],
1248 *args: _ColumnExpressionOrLiteralArgument[Any],
1249 **kwargs: Any,
1250 ) -> pow[_T]: ...
1252 @overload
1253 def pow( # noqa: A001
1254 self,
1255 col: _ColumnExpressionArgument[_T],
1256 *args: _ColumnExpressionOrLiteralArgument[Any],
1257 **kwargs: Any,
1258 ) -> pow[_T]: ...
1260 @overload
1261 def pow( # noqa: A001
1262 self,
1263 col: _T,
1264 *args: _ColumnExpressionOrLiteralArgument[Any],
1265 **kwargs: Any,
1266 ) -> pow[_T]: ...
1268 def pow( # noqa: A001
1269 self,
1270 col: _ColumnExpressionOrLiteralArgument[_T],
1271 *args: _ColumnExpressionOrLiteralArgument[Any],
1272 **kwargs: Any,
1273 ) -> pow[_T]: ...
1275 @property
1276 def random(self) -> Type[random]: ...
1278 @property
1279 def rank(self) -> Type[rank]: ...
1281 @property
1282 def rollup(self) -> Type[rollup[Any]]: ...
1284 @property
1285 def session_user(self) -> Type[session_user]: ...
1287 # set ColumnElement[_T] as a separate overload, to appease
1288 # mypy which seems to not want to accept _T from
1289 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1290 # _HasClauseElement as of mypy 1.15
1292 @overload
1293 def sum( # noqa: A001
1294 self,
1295 col: ColumnElement[_T],
1296 *args: _ColumnExpressionOrLiteralArgument[Any],
1297 **kwargs: Any,
1298 ) -> sum[_T]: ...
1300 @overload
1301 def sum( # noqa: A001
1302 self,
1303 col: _ColumnExpressionArgument[_T],
1304 *args: _ColumnExpressionOrLiteralArgument[Any],
1305 **kwargs: Any,
1306 ) -> sum[_T]: ...
1308 @overload
1309 def sum( # noqa: A001
1310 self,
1311 col: _T,
1312 *args: _ColumnExpressionOrLiteralArgument[Any],
1313 **kwargs: Any,
1314 ) -> sum[_T]: ...
1316 def sum( # noqa: A001
1317 self,
1318 col: _ColumnExpressionOrLiteralArgument[_T],
1319 *args: _ColumnExpressionOrLiteralArgument[Any],
1320 **kwargs: Any,
1321 ) -> sum[_T]: ...
1323 @property
1324 def sysdate(self) -> Type[sysdate]: ...
1326 @property
1327 def user(self) -> Type[user]: ...
1329 # END GENERATED FUNCTION ACCESSORS
1332func = _FunctionGenerator()
1333func.__doc__ = _FunctionGenerator.__doc__
1335modifier = _FunctionGenerator(group=False)
1338class Function(FunctionElement[_T]):
1339 r"""Describe a named SQL function.
1341 The :class:`.Function` object is typically generated from the
1342 :data:`.func` generation object.
1345 :param \*clauses: list of column expressions that form the arguments
1346 of the SQL function call.
1348 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1349 used as the return value of the column expression generated by this
1350 function call.
1352 :param packagenames: a string which indicates package prefix names
1353 to be prepended to the function name when the SQL is generated.
1354 The :data:`.func` generator creates these when it is called using
1355 dotted format, e.g.::
1357 func.mypackage.some_function(col1, col2)
1359 .. seealso::
1361 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1363 :data:`.func` - namespace which produces registered or ad-hoc
1364 :class:`.Function` instances.
1366 :class:`.GenericFunction` - allows creation of registered function
1367 types.
1369 """
1371 __visit_name__ = "function"
1373 _traverse_internals = FunctionElement._traverse_internals + [
1374 ("packagenames", InternalTraversal.dp_plain_obj),
1375 ("name", InternalTraversal.dp_string),
1376 ("type", InternalTraversal.dp_type),
1377 ]
1379 name: str
1381 identifier: str
1383 type: TypeEngine[_T]
1384 """A :class:`_types.TypeEngine` object which refers to the SQL return
1385 type represented by this SQL function.
1387 This datatype may be configured when generating a
1388 :class:`_functions.Function` object by passing the
1389 :paramref:`_functions.Function.type_` parameter, e.g.::
1391 >>> select(func.lower("some VALUE", type_=String))
1393 The small number of built-in classes of :class:`_functions.Function` come
1394 with a built-in datatype that's appropriate to the class of function and
1395 its arguments. For functions that aren't known, the type defaults to the
1396 "null type".
1398 """
1400 @overload
1401 def __init__(
1402 self,
1403 name: str,
1404 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1405 type_: None = ...,
1406 packagenames: Optional[Tuple[str, ...]] = ...,
1407 monotonic: bool = ...,
1408 ) -> None: ...
1410 @overload
1411 def __init__(
1412 self,
1413 name: str,
1414 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1415 type_: _TypeEngineArgument[_T] = ...,
1416 packagenames: Optional[Tuple[str, ...]] = ...,
1417 monotonic: bool = ...,
1418 ) -> None: ...
1420 def __init__(
1421 self,
1422 name: str,
1423 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1424 type_: Optional[_TypeEngineArgument[_T]] = None,
1425 packagenames: Optional[Tuple[str, ...]] = None,
1426 monotonic: bool = False,
1427 ) -> None:
1428 """Construct a :class:`.Function`.
1430 The :data:`.func` construct is normally used to construct
1431 new :class:`.Function` instances.
1433 """
1434 self.packagenames = packagenames or ()
1435 self.name = name
1436 self.monotonic = monotonic
1438 # if type is None, we get NULLTYPE, which is our _T. But I don't
1439 # know how to get the overloads to express that correctly
1440 self.type = type_api.to_instance(type_) # type: ignore
1442 FunctionElement.__init__(self, *clauses)
1444 def _bind_param(
1445 self,
1446 operator: OperatorType,
1447 obj: Any,
1448 type_: Optional[TypeEngine[_T]] = None,
1449 expanding: bool = False,
1450 **kw: Any,
1451 ) -> BindParameter[_T]:
1452 return BindParameter(
1453 self.name,
1454 obj,
1455 _compared_to_operator=operator,
1456 _compared_to_type=self.type,
1457 type_=type_,
1458 unique=True,
1459 expanding=expanding,
1460 **kw,
1461 )
1464class GenericFunction(Function[_T]):
1465 """Define a 'generic' function.
1467 A generic function is a pre-established :class:`.Function`
1468 class that is instantiated automatically when called
1469 by name from the :data:`.func` attribute. Note that
1470 calling any name from :data:`.func` has the effect that
1471 a new :class:`.Function` instance is created automatically,
1472 given that name. The primary use case for defining
1473 a :class:`.GenericFunction` class is so that a function
1474 of a particular name may be given a fixed return type.
1475 It can also include custom argument parsing schemes as well
1476 as additional methods.
1478 Subclasses of :class:`.GenericFunction` are automatically
1479 registered under the name of the class. For
1480 example, a user-defined function ``as_utc()`` would
1481 be available immediately::
1483 from sqlalchemy.sql.functions import GenericFunction
1484 from sqlalchemy.types import DateTime
1487 class as_utc(GenericFunction):
1488 type = DateTime()
1489 inherit_cache = True
1492 print(select(func.as_utc()))
1494 User-defined generic functions can be organized into
1495 packages by specifying the "package" attribute when defining
1496 :class:`.GenericFunction`. Third party libraries
1497 containing many functions may want to use this in order
1498 to avoid name conflicts with other systems. For example,
1499 if our ``as_utc()`` function were part of a package
1500 "time"::
1502 class as_utc(GenericFunction):
1503 type = DateTime()
1504 package = "time"
1505 inherit_cache = True
1507 The above function would be available from :data:`.func`
1508 using the package name ``time``::
1510 print(select(func.time.as_utc()))
1512 A final option is to allow the function to be accessed
1513 from one name in :data:`.func` but to render as a different name.
1514 The ``identifier`` attribute will override the name used to
1515 access the function as loaded from :data:`.func`, but will retain
1516 the usage of ``name`` as the rendered name::
1518 class GeoBuffer(GenericFunction):
1519 type = Geometry()
1520 package = "geo"
1521 name = "ST_Buffer"
1522 identifier = "buffer"
1523 inherit_cache = True
1525 The above function will render as follows:
1527 .. sourcecode:: pycon+sql
1529 >>> print(func.geo.buffer())
1530 {printsql}ST_Buffer()
1532 The name will be rendered as is, however without quoting unless the name
1533 contains special characters that require quoting. To force quoting
1534 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1535 construct::
1537 from sqlalchemy.sql import quoted_name
1540 class GeoBuffer(GenericFunction):
1541 type = Geometry()
1542 package = "geo"
1543 name = quoted_name("ST_Buffer", True)
1544 identifier = "buffer"
1545 inherit_cache = True
1547 The above function will render as:
1549 .. sourcecode:: pycon+sql
1551 >>> print(func.geo.buffer())
1552 {printsql}"ST_Buffer"()
1554 Type parameters for this class as a
1555 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1556 and should match the type seen in a :class:`_engine.Result`. For example::
1558 class as_utc(GenericFunction[datetime.datetime]):
1559 type = DateTime()
1560 inherit_cache = True
1562 The above indicates that the following expression returns a ``datetime``
1563 object::
1565 connection.scalar(select(func.as_utc()))
1567 """
1569 coerce_arguments = True
1570 inherit_cache = True
1572 _register: bool
1574 name = "GenericFunction"
1576 def __init_subclass__(cls) -> None:
1577 if annotation.Annotated not in cls.__mro__:
1578 cls._register_generic_function(cls.__name__, cls.__dict__)
1579 super().__init_subclass__()
1581 @classmethod
1582 def _register_generic_function(
1583 cls, clsname: str, clsdict: Mapping[str, Any]
1584 ) -> None:
1585 cls.name = name = clsdict.get("name", clsname)
1586 cls.identifier = identifier = clsdict.get("identifier", name)
1587 package = clsdict.get("package", "_default")
1588 # legacy
1589 if "__return_type__" in clsdict:
1590 cls.type = clsdict["__return_type__"]
1592 # Check _register attribute status
1593 cls._register = getattr(cls, "_register", True)
1595 # Register the function if required
1596 if cls._register:
1597 register_function(identifier, cls, package)
1598 else:
1599 # Set _register to True to register child classes by default
1600 cls._register = True
1602 def __init__(
1603 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1604 ) -> None:
1605 parsed_args = kwargs.pop("_parsed_args", None)
1606 if parsed_args is None:
1607 parsed_args = [
1608 coercions.expect(
1609 roles.ExpressionElementRole,
1610 c,
1611 name=self.name,
1612 apply_propagate_attrs=self,
1613 )
1614 for c in args
1615 ]
1616 self._has_args = self._has_args or bool(parsed_args)
1617 self.packagenames = ()
1619 self.clause_expr = Grouping(
1620 ClauseList(
1621 operator=operators.comma_op, group_contents=True, *parsed_args
1622 )
1623 )
1625 self.type = type_api.to_instance( # type: ignore
1626 kwargs.pop("type_", None) or getattr(self, "type", None)
1627 )
1630register_function("cast", Cast) # type: ignore
1631register_function("extract", Extract) # type: ignore
1634class next_value(GenericFunction[int]):
1635 """Represent the 'next value', given a :class:`.Sequence`
1636 as its single argument.
1638 Compiles into the appropriate function on each backend,
1639 or will raise NotImplementedError if used on a backend
1640 that does not provide support for sequences.
1642 """
1644 type = sqltypes.Integer()
1645 name = "next_value"
1647 _traverse_internals = [
1648 ("sequence", InternalTraversal.dp_named_ddl_element)
1649 ]
1651 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1652 assert isinstance(
1653 seq, schema.Sequence
1654 ), "next_value() accepts a Sequence object as input."
1655 self.sequence = seq
1656 self.type = sqltypes.to_instance( # type: ignore
1657 seq.data_type or getattr(self, "type", None)
1658 )
1660 def compare(self, other: Any, **kw: Any) -> bool:
1661 return (
1662 isinstance(other, next_value)
1663 and self.sequence.name == other.sequence.name
1664 )
1666 @property
1667 def _from_objects(self) -> Any:
1668 return []
1671class AnsiFunction(GenericFunction[_T]):
1672 """Define a function in "ansi" format, which doesn't render parenthesis."""
1674 inherit_cache = True
1676 def __init__(
1677 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1678 ) -> None:
1679 GenericFunction.__init__(self, *args, **kwargs)
1682class ReturnTypeFromArgs(GenericFunction[_T]):
1683 """Define a function whose return type is bound to the type of its
1684 arguments.
1685 """
1687 inherit_cache = True
1689 # set ColumnElement[_T] as a separate overload, to appease
1690 # mypy which seems to not want to accept _T from
1691 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1692 # _HasClauseElement as of mypy 1.15
1694 @overload
1695 def __init__(
1696 self,
1697 col: ColumnElement[_T],
1698 *args: _ColumnExpressionOrLiteralArgument[Any],
1699 **kwargs: Any,
1700 ) -> None: ...
1702 @overload
1703 def __init__(
1704 self,
1705 col: _ColumnExpressionArgument[_T],
1706 *args: _ColumnExpressionOrLiteralArgument[Any],
1707 **kwargs: Any,
1708 ) -> None: ...
1710 @overload
1711 def __init__(
1712 self,
1713 col: _T,
1714 *args: _ColumnExpressionOrLiteralArgument[Any],
1715 **kwargs: Any,
1716 ) -> None: ...
1718 def __init__(
1719 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1720 ) -> None:
1721 fn_args: Sequence[ColumnElement[Any]] = [
1722 coercions.expect(
1723 roles.ExpressionElementRole,
1724 c,
1725 name=self.name,
1726 apply_propagate_attrs=self,
1727 )
1728 for c in args
1729 ]
1730 kwargs.setdefault("type_", _type_from_args(fn_args))
1731 kwargs["_parsed_args"] = fn_args
1732 super().__init__(*fn_args, **kwargs)
1735class ReturnTypeFromOptionalArgs(ReturnTypeFromArgs[_T]):
1736 inherit_cache = True
1738 @overload
1739 def __init__(
1740 self,
1741 col: ColumnElement[_T],
1742 *args: _ColumnExpressionOrLiteralArgument[Any],
1743 **kwargs: Any,
1744 ) -> None: ...
1746 @overload
1747 def __init__(
1748 self,
1749 col: _ColumnExpressionArgument[Optional[_T]],
1750 *args: _ColumnExpressionOrLiteralArgument[Any],
1751 **kwargs: Any,
1752 ) -> None: ...
1754 @overload
1755 def __init__(
1756 self,
1757 col: Optional[_T],
1758 *args: _ColumnExpressionOrLiteralArgument[Any],
1759 **kwargs: Any,
1760 ) -> None: ...
1762 def __init__(
1763 self,
1764 *args: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1765 **kwargs: Any,
1766 ) -> None:
1767 super().__init__(*args, **kwargs) # type: ignore
1770class coalesce(ReturnTypeFromOptionalArgs[_T]):
1771 _has_args = True
1772 inherit_cache = True
1775class max(ReturnTypeFromArgs[_T]): # noqa: A001
1776 """The SQL MAX() aggregate function."""
1778 inherit_cache = True
1781class min(ReturnTypeFromArgs[_T]): # noqa: A001
1782 """The SQL MIN() aggregate function."""
1784 inherit_cache = True
1787class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1788 """The SQL SUM() aggregate function."""
1790 inherit_cache = True
1793class now(GenericFunction[datetime.datetime]):
1794 """The SQL now() datetime function.
1796 SQLAlchemy dialects will usually render this particular function
1797 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1799 """
1801 type = sqltypes.DateTime()
1802 inherit_cache = True
1805class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1806 """The SQL POW() function which performs the power operator.
1808 E.g.:
1810 .. sourcecode:: pycon+sql
1812 >>> print(select(func.pow(2, 8)))
1813 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1815 .. versionadded:: 2.1
1817 """
1819 inherit_cache = True
1822class concat(GenericFunction[str]):
1823 """The SQL CONCAT() function, which concatenates strings.
1825 E.g.:
1827 .. sourcecode:: pycon+sql
1829 >>> print(select(func.concat("a", "b")))
1830 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1832 String concatenation in SQLAlchemy is more commonly available using the
1833 Python ``+`` operator with string datatypes, which will render a
1834 backend-specific concatenation operator, such as :
1836 .. sourcecode:: pycon+sql
1838 >>> print(select(literal("a") + "b"))
1839 {printsql}SELECT :param_1 || :param_2 AS anon_1
1842 """
1844 type = sqltypes.String()
1845 inherit_cache = True
1848class char_length(GenericFunction[int]):
1849 """The CHAR_LENGTH() SQL function."""
1851 type = sqltypes.Integer()
1852 inherit_cache = True
1854 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1855 # slight hack to limit to just one positional argument
1856 # not sure why this one function has this special treatment
1857 super().__init__(arg, **kw)
1860class random(GenericFunction[float]):
1861 """The RANDOM() SQL function."""
1863 _has_args = True
1864 inherit_cache = True
1867class count(GenericFunction[int]):
1868 r"""The ANSI COUNT aggregate function. With no arguments,
1869 emits COUNT \*.
1871 E.g.::
1873 from sqlalchemy import func
1874 from sqlalchemy import select
1875 from sqlalchemy import table, column
1877 my_table = table("some_table", column("id"))
1879 stmt = select(func.count()).select_from(my_table)
1881 Executing ``stmt`` would emit:
1883 .. sourcecode:: sql
1885 SELECT count(*) AS count_1
1886 FROM some_table
1889 """
1891 type = sqltypes.Integer()
1892 inherit_cache = True
1894 def __init__(
1895 self,
1896 expression: Union[
1897 _ColumnExpressionArgument[Any], _StarOrOne, None
1898 ] = None,
1899 **kwargs: Any,
1900 ) -> None:
1901 if expression is None:
1902 expression = literal_column("*")
1903 super().__init__(expression, **kwargs)
1906class current_date(AnsiFunction[datetime.date]):
1907 """The CURRENT_DATE() SQL function."""
1909 type = sqltypes.Date()
1910 inherit_cache = True
1913class current_time(AnsiFunction[datetime.time]):
1914 """The CURRENT_TIME() SQL function."""
1916 type = sqltypes.Time()
1917 inherit_cache = True
1920class current_timestamp(AnsiFunction[datetime.datetime]):
1921 """The CURRENT_TIMESTAMP() SQL function."""
1923 type = sqltypes.DateTime()
1924 inherit_cache = True
1927class current_user(AnsiFunction[str]):
1928 """The CURRENT_USER() SQL function."""
1930 type = sqltypes.String()
1931 inherit_cache = True
1934class localtime(AnsiFunction[datetime.datetime]):
1935 """The localtime() SQL function."""
1937 type = sqltypes.DateTime()
1938 inherit_cache = True
1941class localtimestamp(AnsiFunction[datetime.datetime]):
1942 """The localtimestamp() SQL function."""
1944 type = sqltypes.DateTime()
1945 inherit_cache = True
1948class session_user(AnsiFunction[str]):
1949 """The SESSION_USER() SQL function."""
1951 type = sqltypes.String()
1952 inherit_cache = True
1955class sysdate(AnsiFunction[datetime.datetime]):
1956 """The SYSDATE() SQL function."""
1958 type = sqltypes.DateTime()
1959 inherit_cache = True
1962class user(AnsiFunction[str]):
1963 """The USER() SQL function."""
1965 type = sqltypes.String()
1966 inherit_cache = True
1969class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1970 """Support for the ARRAY_AGG function.
1972 The ``func.array_agg(expr)`` construct returns an expression of
1973 type :class:`_types.ARRAY`.
1975 e.g.::
1977 stmt = select(func.array_agg(table.c.values)[2:5])
1979 .. seealso::
1981 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1982 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1983 added.
1985 """
1987 inherit_cache = True
1989 def __init__(
1990 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1991 ) -> None:
1992 fn_args: Sequence[ColumnElement[Any]] = [
1993 coercions.expect(
1994 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1995 )
1996 for c in args
1997 ]
1999 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
2000 if "type_" not in kwargs:
2001 type_from_args = _type_from_args(fn_args)
2002 if isinstance(type_from_args, sqltypes.ARRAY):
2003 kwargs["type_"] = type_from_args
2004 else:
2005 kwargs["type_"] = default_array_type(
2006 type_from_args, dimensions=1
2007 )
2008 kwargs["_parsed_args"] = fn_args
2009 super().__init__(*fn_args, **kwargs)
2012class OrderedSetAgg(GenericFunction[_T]):
2013 """Define a function where the return type is based on the sort
2014 expression type as defined by the expression passed to the
2015 :meth:`.FunctionElement.within_group` method."""
2017 array_for_multi_clause = False
2018 inherit_cache = True
2020 def within_group_type(
2021 self, within_group: WithinGroup[Any]
2022 ) -> TypeEngine[Any]:
2023 func_clauses = cast(ClauseList, self.clause_expr.element)
2024 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
2025 within_group.order_by
2026 )
2027 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
2028 return sqltypes.ARRAY(order_by[0].type)
2029 else:
2030 return order_by[0].type
2033class mode(OrderedSetAgg[_T]):
2034 """Implement the ``mode`` ordered-set aggregate function.
2036 This function must be used with the :meth:`.FunctionElement.within_group`
2037 modifier to supply a sort expression to operate upon.
2039 The return type of this function is the same as the sort expression.
2041 """
2043 inherit_cache = True
2046class percentile_cont(OrderedSetAgg[_T]):
2047 """Implement the ``percentile_cont`` ordered-set aggregate function.
2049 This function must be used with the :meth:`.FunctionElement.within_group`
2050 modifier to supply a sort expression to operate upon.
2052 The return type of this function is the same as the sort expression,
2053 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2054 expression's type.
2056 """
2058 array_for_multi_clause = True
2059 inherit_cache = True
2062class percentile_disc(OrderedSetAgg[_T]):
2063 """Implement the ``percentile_disc`` ordered-set aggregate function.
2065 This function must be used with the :meth:`.FunctionElement.within_group`
2066 modifier to supply a sort expression to operate upon.
2068 The return type of this function is the same as the sort expression,
2069 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2070 expression's type.
2072 """
2074 array_for_multi_clause = True
2075 inherit_cache = True
2078class rank(GenericFunction[int]):
2079 """Implement the ``rank`` hypothetical-set aggregate function.
2081 This function must be used with the :meth:`.FunctionElement.within_group`
2082 modifier to supply a sort expression to operate upon.
2084 The return type of this function is :class:`.Integer`.
2086 """
2088 type = sqltypes.Integer()
2089 inherit_cache = True
2092class dense_rank(GenericFunction[int]):
2093 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2095 This function must be used with the :meth:`.FunctionElement.within_group`
2096 modifier to supply a sort expression to operate upon.
2098 The return type of this function is :class:`.Integer`.
2100 """
2102 type = sqltypes.Integer()
2103 inherit_cache = True
2106class percent_rank(GenericFunction[decimal.Decimal]):
2107 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2109 This function must be used with the :meth:`.FunctionElement.within_group`
2110 modifier to supply a sort expression to operate upon.
2112 The return type of this function is :class:`.Numeric`.
2114 """
2116 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2117 inherit_cache = True
2120class cume_dist(GenericFunction[decimal.Decimal]):
2121 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2123 This function must be used with the :meth:`.FunctionElement.within_group`
2124 modifier to supply a sort expression to operate upon.
2126 The return type of this function is :class:`.Numeric`.
2128 """
2130 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2131 inherit_cache = True
2134class cube(GenericFunction[_T]):
2135 r"""Implement the ``CUBE`` grouping operation.
2137 This function is used as part of the GROUP BY of a statement,
2138 e.g. :meth:`_expression.Select.group_by`::
2140 stmt = select(
2141 func.sum(table.c.value), table.c.col_1, table.c.col_2
2142 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2144 """
2146 _has_args = True
2147 inherit_cache = True
2150class rollup(GenericFunction[_T]):
2151 r"""Implement the ``ROLLUP`` grouping operation.
2153 This function is used as part of the GROUP BY of a statement,
2154 e.g. :meth:`_expression.Select.group_by`::
2156 stmt = select(
2157 func.sum(table.c.value), table.c.col_1, table.c.col_2
2158 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2160 """
2162 _has_args = True
2163 inherit_cache = True
2166class grouping_sets(GenericFunction[_T]):
2167 r"""Implement the ``GROUPING SETS`` grouping operation.
2169 This function is used as part of the GROUP BY of a statement,
2170 e.g. :meth:`_expression.Select.group_by`::
2172 stmt = select(
2173 func.sum(table.c.value), table.c.col_1, table.c.col_2
2174 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2176 In order to group by multiple sets, use the :func:`.tuple_` construct::
2178 from sqlalchemy import tuple_
2180 stmt = select(
2181 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2182 ).group_by(
2183 func.grouping_sets(
2184 tuple_(table.c.col_1, table.c.col_2),
2185 tuple_(table.c.value, table.c.col_3),
2186 )
2187 )
2189 """ # noqa: E501
2191 _has_args = True
2192 inherit_cache = True
2195class aggregate_strings(GenericFunction[str]):
2196 """Implement a generic string aggregation function.
2198 This function will concatenate non-null values into a string and
2199 separate the values by a delimiter.
2201 This function is compiled on a per-backend basis, into functions
2202 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2204 e.g. Example usage with delimiter '.'::
2206 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2208 .. versionadded:: 2.0.21
2210 To add ordering to the expression, use the
2211 :meth:`_functions.FunctionElement.aggregate_order_by` modifier method,
2212 which will emit ORDER BY within the appropriate part of the column
2213 expression (varies by backend)::
2215 stmt = select(
2216 func.aggregate_strings(table.c.str_col, ".").aggregate_order_by(
2217 table.c.str_col
2218 )
2219 )
2221 .. versionadded:: 2.1 added :meth:`_functions.FunctionElement.aggregate_order_by`
2222 for all aggregate functions.
2224 :param clause: the SQL expression to be concatenated
2226 :param separator: separator string
2229 """ # noqa: E501
2231 type = sqltypes.String()
2232 _has_args = True
2233 inherit_cache = True
2235 def __init__(
2236 self,
2237 clause: _ColumnExpressionArgument[Any],
2238 separator: str,
2239 ) -> None:
2240 super().__init__(clause, separator)