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 Executable
40from .base import Generative
41from .base import HasMemoized
42from .elements import _type_from_args
43from .elements import BinaryExpression
44from .elements import BindParameter
45from .elements import Cast
46from .elements import ClauseList
47from .elements import ColumnElement
48from .elements import Extract
49from .elements import FunctionFilter
50from .elements import Grouping
51from .elements import literal_column
52from .elements import NamedColumn
53from .elements import Over
54from .elements import WithinGroup
55from .selectable import FromClause
56from .selectable import Select
57from .selectable import TableValuedAlias
58from .sqltypes import TableValueType
59from .type_api import TypeEngine
60from .visitors import InternalTraversal
61from .. import util
64if TYPE_CHECKING:
65 from ._typing import _ByArgument
66 from ._typing import _ColumnExpressionArgument
67 from ._typing import _ColumnExpressionOrLiteralArgument
68 from ._typing import _ColumnExpressionOrStrLabelArgument
69 from ._typing import _StarOrOne
70 from ._typing import _TypeEngineArgument
71 from .base import _EntityNamespace
72 from .elements import ClauseElement
73 from .elements import KeyedColumnElement
74 from .elements import TableValuedColumn
75 from .operators import OperatorType
76 from ..engine.base import Connection
77 from ..engine.cursor import CursorResult
78 from ..engine.interfaces import _CoreMultiExecuteParams
79 from ..engine.interfaces import CoreExecuteOptionsParameter
80 from ..util.typing import Self
82_T = TypeVar("_T", bound=Any)
83_S = TypeVar("_S", bound=Any)
85_registry: util.defaultdict[str, Dict[str, Type[Function[Any]]]] = (
86 util.defaultdict(dict)
87)
90def register_function(
91 identifier: str, fn: Type[Function[Any]], package: str = "_default"
92) -> None:
93 """Associate a callable with a particular func. name.
95 This is normally called by GenericFunction, but is also
96 available by itself so that a non-Function construct
97 can be associated with the :data:`.func` accessor (i.e.
98 CAST, EXTRACT).
100 """
101 reg = _registry[package]
103 identifier = str(identifier).lower()
105 # Check if a function with the same identifier is registered.
106 if identifier in reg:
107 util.warn(
108 "The GenericFunction '{}' is already registered and "
109 "is going to be overridden.".format(identifier)
110 )
111 reg[identifier] = fn
114class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
115 """Base for SQL function-oriented constructs.
117 This is a `generic type <https://peps.python.org/pep-0484/#generics>`_,
118 meaning that type checkers and IDEs can be instructed on the types to
119 expect in a :class:`_engine.Result` for this function. See
120 :class:`.GenericFunction` for an example of how this is done.
122 .. seealso::
124 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
126 :class:`.Function` - named SQL function.
128 :data:`.func` - namespace which produces registered or ad-hoc
129 :class:`.Function` instances.
131 :class:`.GenericFunction` - allows creation of registered function
132 types.
134 """
136 _traverse_internals = [
137 ("clause_expr", InternalTraversal.dp_clauseelement),
138 ("_with_ordinality", InternalTraversal.dp_boolean),
139 ("_table_value_type", InternalTraversal.dp_has_cache_key),
140 ]
142 packagenames: Tuple[str, ...] = ()
144 _has_args = False
145 _with_ordinality = False
146 _table_value_type: Optional[TableValueType] = None
148 # some attributes that are defined between both ColumnElement and
149 # FromClause are set to Any here to avoid typing errors
150 primary_key: Any
151 _is_clone_of: Any
153 clause_expr: Grouping[Any]
155 def __init__(
156 self, *clauses: _ColumnExpressionOrLiteralArgument[Any]
157 ) -> None:
158 r"""Construct a :class:`.FunctionElement`.
160 :param \*clauses: list of column expressions that form the arguments
161 of the SQL function call.
163 :param \**kwargs: additional kwargs are typically consumed by
164 subclasses.
166 .. seealso::
168 :data:`.func`
170 :class:`.Function`
172 """
173 args: Sequence[_ColumnExpressionArgument[Any]] = [
174 coercions.expect(
175 roles.ExpressionElementRole,
176 c,
177 name=getattr(self, "name", None),
178 apply_propagate_attrs=self,
179 )
180 for c in clauses
181 ]
182 self._has_args = self._has_args or bool(args)
183 self.clause_expr = Grouping(
184 ClauseList(operator=operators.comma_op, group_contents=True, *args)
185 )
187 _non_anon_label = None
189 @property
190 def _proxy_key(self) -> Any:
191 return super()._proxy_key or getattr(self, "name", None)
193 def _execute_on_connection(
194 self,
195 connection: Connection,
196 distilled_params: _CoreMultiExecuteParams,
197 execution_options: CoreExecuteOptionsParameter,
198 ) -> CursorResult[Any]:
199 return connection._execute_function(
200 self, distilled_params, execution_options
201 )
203 def scalar_table_valued(
204 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
205 ) -> ScalarFunctionColumn[_T]:
206 """Return a column expression that's against this
207 :class:`_functions.FunctionElement` as a scalar
208 table-valued expression.
210 The returned expression is similar to that returned by a single column
211 accessed off of a :meth:`_functions.FunctionElement.table_valued`
212 construct, except no FROM clause is generated; the function is rendered
213 in the similar way as a scalar subquery.
215 E.g.:
217 .. sourcecode:: pycon+sql
219 >>> from sqlalchemy import func, select
220 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
221 >>> print(select(fn))
222 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
224 .. versionadded:: 1.4.0b2
226 .. seealso::
228 :meth:`_functions.FunctionElement.table_valued`
230 :meth:`_functions.FunctionElement.alias`
232 :meth:`_functions.FunctionElement.column_valued`
234 """ # noqa: E501
236 return ScalarFunctionColumn(self, name, type_)
238 def table_valued(
239 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
240 ) -> TableValuedAlias:
241 r"""Return a :class:`_sql.TableValuedAlias` representation of this
242 :class:`_functions.FunctionElement` with table-valued expressions added.
244 e.g.:
246 .. sourcecode:: pycon+sql
248 >>> fn = func.generate_series(1, 5).table_valued(
249 ... "value", "start", "stop", "step"
250 ... )
252 >>> print(select(fn))
253 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
254 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
256 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
257 {printsql}SELECT anon_1.value, anon_1.stop
258 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
259 WHERE anon_1.value > :value_1{stop}
261 A WITH ORDINALITY expression may be generated by passing the keyword
262 argument "with_ordinality":
264 .. sourcecode:: pycon+sql
266 >>> fn = func.generate_series(4, 1, -1).table_valued(
267 ... "gen", with_ordinality="ordinality"
268 ... )
269 >>> print(select(fn))
270 {printsql}SELECT anon_1.gen, anon_1.ordinality
271 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1
273 :param \*expr: A series of string column names that will be added to the
274 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
275 construct as columns. :func:`_sql.column` objects with or without
276 datatypes may also be used.
278 :param name: optional name to assign to the alias name that's generated.
279 If omitted, a unique anonymizing name is used.
281 :param with_ordinality: string name that when present results in the
282 ``WITH ORDINALITY`` clause being added to the alias, and the given
283 string name will be added as a column to the .c collection
284 of the resulting :class:`_sql.TableValuedAlias`.
286 :param joins_implicitly: when True, the table valued function may be
287 used in the FROM clause without any explicit JOIN to other tables
288 in the SQL query, and no "cartesian product" warning will be generated.
289 May be useful for SQL functions such as ``func.json_each()``.
291 .. versionadded:: 1.4.33
293 .. versionadded:: 1.4.0b2
296 .. seealso::
298 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
300 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
302 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
303 :meth:`_functions.FunctionElement.table_valued` which delivers the
304 complete table valued expression as a scalar column expression
306 :meth:`_functions.FunctionElement.column_valued`
308 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
309 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
311 """ # noqa: 501
313 new_func = self._generate()
315 with_ordinality = kw.pop("with_ordinality", None)
316 joins_implicitly = kw.pop("joins_implicitly", None)
317 name = kw.pop("name", None)
319 if with_ordinality:
320 expr += (with_ordinality,)
321 new_func._with_ordinality = True
323 new_func.type = new_func._table_value_type = TableValueType(*expr)
325 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
327 def column_valued(
328 self, name: Optional[str] = None, joins_implicitly: bool = False
329 ) -> TableValuedColumn[_T]:
330 """Return this :class:`_functions.FunctionElement` as a column expression that
331 selects from itself as a FROM clause.
333 E.g.:
335 .. sourcecode:: pycon+sql
337 >>> from sqlalchemy import select, func
338 >>> gs = func.generate_series(1, 5, -1).column_valued()
339 >>> print(select(gs))
340 {printsql}SELECT anon_1
341 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
343 This is shorthand for::
345 gs = func.generate_series(1, 5, -1).alias().column
347 :param name: optional name to assign to the alias name that's generated.
348 If omitted, a unique anonymizing name is used.
350 :param joins_implicitly: when True, the "table" portion of the column
351 valued function may be a member of the FROM clause without any
352 explicit JOIN to other tables in the SQL query, and no "cartesian
353 product" warning will be generated. May be useful for SQL functions
354 such as ``func.json_array_elements()``.
356 .. versionadded:: 1.4.46
358 .. seealso::
360 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
362 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
364 :meth:`_functions.FunctionElement.table_valued`
366 """ # noqa: 501
368 return self.alias(name=name, joins_implicitly=joins_implicitly).column
370 @util.ro_non_memoized_property
371 def columns(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
372 r"""The set of columns exported by this :class:`.FunctionElement`.
374 This is a placeholder collection that allows the function to be
375 placed in the FROM clause of a statement:
377 .. sourcecode:: pycon+sql
379 >>> from sqlalchemy import column, select, func
380 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
381 >>> print(stmt)
382 {printsql}SELECT x, y FROM myfunction()
384 The above form is a legacy feature that is now superseded by the
385 fully capable :meth:`_functions.FunctionElement.table_valued`
386 method; see that method for details.
388 .. seealso::
390 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
391 SQL function expressions.
393 """ # noqa: E501
394 return self.c
396 @util.ro_memoized_property
397 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
398 """synonym for :attr:`.FunctionElement.columns`."""
400 return ColumnCollection(
401 columns=[(col.key, col) for col in self._all_selected_columns]
402 )
404 @property
405 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
406 if is_table_value_type(self.type):
407 # TODO: this might not be fully accurate
408 cols = cast(
409 "Sequence[KeyedColumnElement[Any]]", self.type._elements
410 )
411 else:
412 cols = [self.label(None)]
414 return cols
416 @property
417 def exported_columns( # type: ignore[override]
418 self,
419 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
420 return self.columns
422 @HasMemoized.memoized_attribute
423 def clauses(self) -> ClauseList:
424 """Return the underlying :class:`.ClauseList` which contains
425 the arguments for this :class:`.FunctionElement`.
427 """
428 return cast(ClauseList, self.clause_expr.element)
430 def over(
431 self,
432 *,
433 partition_by: Optional[_ByArgument] = None,
434 order_by: Optional[_ByArgument] = None,
435 rows: Optional[Tuple[Optional[int], Optional[int]]] = None,
436 range_: Optional[Tuple[Optional[int], Optional[int]]] = None,
437 groups: Optional[Tuple[Optional[int], Optional[int]]] = None,
438 ) -> Over[_T]:
439 """Produce an OVER clause against this function.
441 Used against aggregate or so-called "window" functions,
442 for database backends that support window functions.
444 The expression::
446 func.row_number().over(order_by="x")
448 is shorthand for::
450 from sqlalchemy import over
452 over(func.row_number(), order_by="x")
454 See :func:`_expression.over` for a full description.
456 .. seealso::
458 :func:`_expression.over`
460 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
462 """
463 return Over(
464 self,
465 partition_by=partition_by,
466 order_by=order_by,
467 rows=rows,
468 range_=range_,
469 groups=groups,
470 )
472 def within_group(
473 self, *order_by: _ColumnExpressionArgument[Any]
474 ) -> WithinGroup[_T]:
475 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
477 Used against so-called "ordered set aggregate" and "hypothetical
478 set aggregate" functions, including :class:`.percentile_cont`,
479 :class:`.rank`, :class:`.dense_rank`, etc.
481 See :func:`_expression.within_group` for a full description.
483 .. seealso::
485 :ref:`tutorial_functions_within_group` -
486 in the :ref:`unified_tutorial`
489 """
490 return WithinGroup(self, *order_by)
492 @overload
493 def filter(self) -> Self: ...
495 @overload
496 def filter(
497 self,
498 __criterion0: _ColumnExpressionArgument[bool],
499 *criterion: _ColumnExpressionArgument[bool],
500 ) -> FunctionFilter[_T]: ...
502 def filter(
503 self, *criterion: _ColumnExpressionArgument[bool]
504 ) -> Union[Self, FunctionFilter[_T]]:
505 """Produce a FILTER clause against this function.
507 Used against aggregate and window functions,
508 for database backends that support the "FILTER" clause.
510 The expression::
512 func.count(1).filter(True)
514 is shorthand for::
516 from sqlalchemy import funcfilter
518 funcfilter(func.count(1), True)
520 .. seealso::
522 :ref:`tutorial_functions_within_group` -
523 in the :ref:`unified_tutorial`
525 :class:`.FunctionFilter`
527 :func:`.funcfilter`
530 """
531 if not criterion:
532 return self
533 return FunctionFilter(self, *criterion)
535 def as_comparison(
536 self, left_index: int, right_index: int
537 ) -> FunctionAsBinary:
538 """Interpret this expression as a boolean comparison between two
539 values.
541 This method is used for an ORM use case described at
542 :ref:`relationship_custom_operator_sql_function`.
544 A hypothetical SQL function "is_equal()" which compares to values
545 for equality would be written in the Core expression language as::
547 expr = func.is_equal("a", "b")
549 If "is_equal()" above is comparing "a" and "b" for equality, the
550 :meth:`.FunctionElement.as_comparison` method would be invoked as::
552 expr = func.is_equal("a", "b").as_comparison(1, 2)
554 Where above, the integer value "1" refers to the first argument of the
555 "is_equal()" function and the integer value "2" refers to the second.
557 This would create a :class:`.BinaryExpression` that is equivalent to::
559 BinaryExpression("a", "b", operator=op.eq)
561 However, at the SQL level it would still render as
562 "is_equal('a', 'b')".
564 The ORM, when it loads a related object or collection, needs to be able
565 to manipulate the "left" and "right" sides of the ON clause of a JOIN
566 expression. The purpose of this method is to provide a SQL function
567 construct that can also supply this information to the ORM, when used
568 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
569 return value is a containment object called :class:`.FunctionAsBinary`.
571 An ORM example is as follows::
573 class Venue(Base):
574 __tablename__ = "venue"
575 id = Column(Integer, primary_key=True)
576 name = Column(String)
578 descendants = relationship(
579 "Venue",
580 primaryjoin=func.instr(
581 remote(foreign(name)), name + "/"
582 ).as_comparison(1, 2)
583 == 1,
584 viewonly=True,
585 order_by=name,
586 )
588 Above, the "Venue" class can load descendant "Venue" objects by
589 determining if the name of the parent Venue is contained within the
590 start of the hypothetical descendant value's name, e.g. "parent1" would
591 match up to "parent1/child1", but not to "parent2/child1".
593 Possible use cases include the "materialized path" example given above,
594 as well as making use of special SQL functions such as geometric
595 functions to create join conditions.
597 :param left_index: the integer 1-based index of the function argument
598 that serves as the "left" side of the expression.
599 :param right_index: the integer 1-based index of the function argument
600 that serves as the "right" side of the expression.
602 .. versionadded:: 1.3
604 .. seealso::
606 :ref:`relationship_custom_operator_sql_function` -
607 example use within the ORM
609 """
610 return FunctionAsBinary(self, left_index, right_index)
612 @property
613 def _from_objects(self) -> Any:
614 return self.clauses._from_objects
616 def within_group_type(
617 self, within_group: WithinGroup[_S]
618 ) -> Optional[TypeEngine[_S]]:
619 """For types that define their return type as based on the criteria
620 within a WITHIN GROUP (ORDER BY) expression, called by the
621 :class:`.WithinGroup` construct.
623 Returns None by default, in which case the function's normal ``.type``
624 is used.
626 """
628 return None
630 def alias(
631 self, name: Optional[str] = None, joins_implicitly: bool = False
632 ) -> TableValuedAlias:
633 r"""Produce a :class:`_expression.Alias` construct against this
634 :class:`.FunctionElement`.
636 .. tip::
638 The :meth:`_functions.FunctionElement.alias` method is part of the
639 mechanism by which "table valued" SQL functions are created.
640 However, most use cases are covered by higher level methods on
641 :class:`_functions.FunctionElement` including
642 :meth:`_functions.FunctionElement.table_valued`, and
643 :meth:`_functions.FunctionElement.column_valued`.
645 This construct wraps the function in a named alias which
646 is suitable for the FROM clause, in the style accepted for example
647 by PostgreSQL. A column expression is also provided using the
648 special ``.column`` attribute, which may
649 be used to refer to the output of the function as a scalar value
650 in the columns or where clause, for a backend such as PostgreSQL.
652 For a full table-valued expression, use the
653 :meth:`_functions.FunctionElement.table_valued` method first to
654 establish named columns.
656 e.g.:
658 .. sourcecode:: pycon+sql
660 >>> from sqlalchemy import func, select, column
661 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
662 >>> print(select(data_view.column))
663 {printsql}SELECT data_view
664 FROM unnest(:unnest_1) AS data_view
666 The :meth:`_functions.FunctionElement.column_valued` method provides
667 a shortcut for the above pattern:
669 .. sourcecode:: pycon+sql
671 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
672 >>> print(select(data_view))
673 {printsql}SELECT data_view
674 FROM unnest(:unnest_1) AS data_view
676 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
678 :param name: alias name, will be rendered as ``AS <name>`` in the
679 FROM clause
681 :param joins_implicitly: when True, the table valued function may be
682 used in the FROM clause without any explicit JOIN to other tables
683 in the SQL query, and no "cartesian product" warning will be
684 generated. May be useful for SQL functions such as
685 ``func.json_each()``.
687 .. versionadded:: 1.4.33
689 .. seealso::
691 :ref:`tutorial_functions_table_valued` -
692 in the :ref:`unified_tutorial`
694 :meth:`_functions.FunctionElement.table_valued`
696 :meth:`_functions.FunctionElement.scalar_table_valued`
698 :meth:`_functions.FunctionElement.column_valued`
701 """
703 return TableValuedAlias._construct(
704 self,
705 name=name,
706 table_value_type=self.type,
707 joins_implicitly=joins_implicitly,
708 )
710 def select(self) -> Select[Tuple[_T]]:
711 """Produce a :func:`_expression.select` construct
712 against this :class:`.FunctionElement`.
714 This is shorthand for::
716 s = select(function_element)
718 """
719 s: Select[Any] = Select(self)
720 if self._execution_options:
721 s = s.execution_options(**self._execution_options)
722 return s
724 def _bind_param(
725 self,
726 operator: OperatorType,
727 obj: Any,
728 type_: Optional[TypeEngine[_T]] = None,
729 expanding: bool = False,
730 **kw: Any,
731 ) -> BindParameter[_T]:
732 return BindParameter(
733 None,
734 obj,
735 _compared_to_operator=operator,
736 _compared_to_type=self.type,
737 unique=True,
738 type_=type_,
739 expanding=expanding,
740 **kw,
741 )
743 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
744 # for the moment, we are parenthesizing all array-returning
745 # expressions against getitem. This may need to be made
746 # more portable if in the future we support other DBs
747 # besides postgresql.
748 if against in (operators.getitem, operators.json_getitem_op):
749 return Grouping(self)
750 else:
751 return super().self_group(against=against)
753 @property
754 def entity_namespace(self) -> _EntityNamespace:
755 """overrides FromClause.entity_namespace as functions are generally
756 column expressions and not FromClauses.
758 """
759 # ideally functions would not be fromclauses but we failed to make
760 # this adjustment in 1.4
761 return _entity_namespace(self.clause_expr)
764class FunctionAsBinary(BinaryExpression[Any]):
765 _traverse_internals = [
766 ("sql_function", InternalTraversal.dp_clauseelement),
767 ("left_index", InternalTraversal.dp_plain_obj),
768 ("right_index", InternalTraversal.dp_plain_obj),
769 ("modifiers", InternalTraversal.dp_plain_dict),
770 ]
772 sql_function: FunctionElement[Any]
773 left_index: int
774 right_index: int
776 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
777 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
779 def __init__(
780 self, fn: FunctionElement[Any], left_index: int, right_index: int
781 ) -> None:
782 self.sql_function = fn
783 self.left_index = left_index
784 self.right_index = right_index
786 self.operator = operators.function_as_comparison_op
787 self.type = sqltypes.BOOLEANTYPE
788 self.negate = None
789 self._is_implicitly_boolean = True
790 self.modifiers = util.immutabledict({})
792 @property
793 def left_expr(self) -> ColumnElement[Any]:
794 return self.sql_function.clauses.clauses[self.left_index - 1]
796 @left_expr.setter
797 def left_expr(self, value: ColumnElement[Any]) -> None:
798 self.sql_function.clauses.clauses[self.left_index - 1] = value
800 @property
801 def right_expr(self) -> ColumnElement[Any]:
802 return self.sql_function.clauses.clauses[self.right_index - 1]
804 @right_expr.setter
805 def right_expr(self, value: ColumnElement[Any]) -> None:
806 self.sql_function.clauses.clauses[self.right_index - 1] = value
808 if not TYPE_CHECKING:
809 # mypy can't accommodate @property to replace an instance
810 # variable
812 left = left_expr
813 right = right_expr
816class ScalarFunctionColumn(NamedColumn[_T]):
817 __visit_name__ = "scalar_function_column"
819 _traverse_internals = [
820 ("name", InternalTraversal.dp_anon_name),
821 ("type", InternalTraversal.dp_type),
822 ("fn", InternalTraversal.dp_clauseelement),
823 ]
825 is_literal = False
826 table = None
828 def __init__(
829 self,
830 fn: FunctionElement[_T],
831 name: str,
832 type_: Optional[_TypeEngineArgument[_T]] = None,
833 ) -> None:
834 self.fn = fn
835 self.name = name
837 # if type is None, we get NULLTYPE, which is our _T. But I don't
838 # know how to get the overloads to express that correctly
839 self.type = type_api.to_instance(type_) # type: ignore
842class _FunctionGenerator:
843 """Generate SQL function expressions.
845 :data:`.func` is a special object instance which generates SQL
846 functions based on name-based attributes, e.g.:
848 .. sourcecode:: pycon+sql
850 >>> print(func.count(1))
851 {printsql}count(:param_1)
853 The returned object is an instance of :class:`.Function`, and is a
854 column-oriented SQL element like any other, and is used in that way:
856 .. sourcecode:: pycon+sql
858 >>> print(select(func.count(table.c.id)))
859 {printsql}SELECT count(sometable.id) FROM sometable
861 Any name can be given to :data:`.func`. If the function name is unknown to
862 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
863 which SQLAlchemy is aware of, the name may be interpreted as a *generic
864 function* which will be compiled appropriately to the target database:
866 .. sourcecode:: pycon+sql
868 >>> print(func.current_timestamp())
869 {printsql}CURRENT_TIMESTAMP
871 To call functions which are present in dot-separated packages,
872 specify them in the same manner:
874 .. sourcecode:: pycon+sql
876 >>> print(func.stats.yield_curve(5, 10))
877 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
879 SQLAlchemy can be made aware of the return type of functions to enable
880 type-specific lexical and result-based behavior. For example, to ensure
881 that a string-based function returns a Unicode value and is similarly
882 treated as a string in expressions, specify
883 :class:`~sqlalchemy.types.Unicode` as the type:
885 .. sourcecode:: pycon+sql
887 >>> print(
888 ... func.my_string("hi", type_=Unicode)
889 ... + " "
890 ... + func.my_string("there", type_=Unicode)
891 ... )
892 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
894 The object returned by a :data:`.func` call is usually an instance of
895 :class:`.Function`.
896 This object meets the "column" interface, including comparison and labeling
897 functions. The object can also be passed the :meth:`~.Connectable.execute`
898 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
899 where it will be
900 wrapped inside of a SELECT statement first::
902 print(connection.execute(func.current_timestamp()).scalar())
904 In a few exception cases, the :data:`.func` accessor
905 will redirect a name to a built-in expression such as :func:`.cast`
906 or :func:`.extract`, as these names have well-known meaning
907 but are not exactly the same as "functions" from a SQLAlchemy
908 perspective.
910 Functions which are interpreted as "generic" functions know how to
911 calculate their return type automatically. For a listing of known generic
912 functions, see :ref:`generic_functions`.
914 .. note::
916 The :data:`.func` construct has only limited support for calling
917 standalone "stored procedures", especially those with special
918 parameterization concerns.
920 See the section :ref:`stored_procedures` for details on how to use
921 the DBAPI-level ``callproc()`` method for fully traditional stored
922 procedures.
924 .. seealso::
926 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
928 :class:`.Function`
930 """ # noqa
932 def __init__(self, **opts: Any) -> None:
933 self.__names: List[str] = []
934 self.opts = opts
936 def __getattr__(self, name: str) -> _FunctionGenerator:
937 # passthru __ attributes; fixes pydoc
938 if name.startswith("__"):
939 try:
940 return self.__dict__[name] # type: ignore
941 except KeyError:
942 raise AttributeError(name)
944 elif name.endswith("_"):
945 name = name[0:-1]
946 f = _FunctionGenerator(**self.opts)
947 f.__names = list(self.__names) + [name]
948 return f
950 @overload
951 def __call__(
952 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
953 ) -> Function[_T]: ...
955 @overload
956 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
958 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
959 o = self.opts.copy()
960 o.update(kwargs)
962 tokens = len(self.__names)
964 if tokens == 2:
965 package, fname = self.__names
966 elif tokens == 1:
967 package, fname = "_default", self.__names[0]
968 else:
969 package = None
971 if package is not None:
972 func = _registry[package].get(fname.lower())
973 if func is not None:
974 return func(*c, **o)
976 return Function(
977 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
978 )
980 if TYPE_CHECKING:
981 # START GENERATED FUNCTION ACCESSORS
983 # code within this block is **programmatically,
984 # statically generated** by tools/generate_sql_functions.py
986 @property
987 def aggregate_strings(self) -> Type[aggregate_strings]: ...
989 @property
990 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
992 # set ColumnElement[_T] as a separate overload, to appease
993 # mypy which seems to not want to accept _T from
994 # _ColumnExpressionArgument. Seems somewhat related to the covariant
995 # _HasClauseElement as of mypy 1.15
997 @overload
998 def array_agg(
999 self,
1000 col: ColumnElement[_T],
1001 *args: _ColumnExpressionOrLiteralArgument[Any],
1002 **kwargs: Any,
1003 ) -> array_agg[_T]: ...
1005 @overload
1006 def array_agg(
1007 self,
1008 col: _ColumnExpressionArgument[_T],
1009 *args: _ColumnExpressionOrLiteralArgument[Any],
1010 **kwargs: Any,
1011 ) -> array_agg[_T]: ...
1013 @overload
1014 def array_agg(
1015 self,
1016 col: _T,
1017 *args: _ColumnExpressionOrLiteralArgument[Any],
1018 **kwargs: Any,
1019 ) -> array_agg[_T]: ...
1021 def array_agg(
1022 self,
1023 col: _ColumnExpressionOrLiteralArgument[_T],
1024 *args: _ColumnExpressionOrLiteralArgument[Any],
1025 **kwargs: Any,
1026 ) -> array_agg[_T]: ...
1028 @property
1029 def cast(self) -> Type[Cast[Any]]: ...
1031 @property
1032 def char_length(self) -> Type[char_length]: ...
1034 # set ColumnElement[_T] as a separate overload, to appease
1035 # mypy which seems to not want to accept _T from
1036 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1037 # _HasClauseElement as of mypy 1.15
1039 @overload
1040 def coalesce(
1041 self,
1042 col: ColumnElement[_T],
1043 *args: _ColumnExpressionOrLiteralArgument[Any],
1044 **kwargs: Any,
1045 ) -> coalesce[_T]: ...
1047 @overload
1048 def coalesce(
1049 self,
1050 col: _ColumnExpressionArgument[_T],
1051 *args: _ColumnExpressionOrLiteralArgument[Any],
1052 **kwargs: Any,
1053 ) -> coalesce[_T]: ...
1055 @overload
1056 def coalesce(
1057 self,
1058 col: _T,
1059 *args: _ColumnExpressionOrLiteralArgument[Any],
1060 **kwargs: Any,
1061 ) -> coalesce[_T]: ...
1063 def coalesce(
1064 self,
1065 col: _ColumnExpressionOrLiteralArgument[_T],
1066 *args: _ColumnExpressionOrLiteralArgument[Any],
1067 **kwargs: Any,
1068 ) -> coalesce[_T]: ...
1070 @property
1071 def concat(self) -> Type[concat]: ...
1073 @property
1074 def count(self) -> Type[count]: ...
1076 @property
1077 def cube(self) -> Type[cube[Any]]: ...
1079 @property
1080 def cume_dist(self) -> Type[cume_dist]: ...
1082 @property
1083 def current_date(self) -> Type[current_date]: ...
1085 @property
1086 def current_time(self) -> Type[current_time]: ...
1088 @property
1089 def current_timestamp(self) -> Type[current_timestamp]: ...
1091 @property
1092 def current_user(self) -> Type[current_user]: ...
1094 @property
1095 def dense_rank(self) -> Type[dense_rank]: ...
1097 @property
1098 def extract(self) -> Type[Extract]: ...
1100 @property
1101 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1103 @property
1104 def localtime(self) -> Type[localtime]: ...
1106 @property
1107 def localtimestamp(self) -> Type[localtimestamp]: ...
1109 # set ColumnElement[_T] as a separate overload, to appease
1110 # mypy which seems to not want to accept _T from
1111 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1112 # _HasClauseElement as of mypy 1.15
1114 @overload
1115 def max( # noqa: A001
1116 self,
1117 col: ColumnElement[_T],
1118 *args: _ColumnExpressionOrLiteralArgument[Any],
1119 **kwargs: Any,
1120 ) -> max[_T]: ...
1122 @overload
1123 def max( # noqa: A001
1124 self,
1125 col: _ColumnExpressionArgument[_T],
1126 *args: _ColumnExpressionOrLiteralArgument[Any],
1127 **kwargs: Any,
1128 ) -> max[_T]: ...
1130 @overload
1131 def max( # noqa: A001
1132 self,
1133 col: _T,
1134 *args: _ColumnExpressionOrLiteralArgument[Any],
1135 **kwargs: Any,
1136 ) -> max[_T]: ...
1138 def max( # noqa: A001
1139 self,
1140 col: _ColumnExpressionOrLiteralArgument[_T],
1141 *args: _ColumnExpressionOrLiteralArgument[Any],
1142 **kwargs: Any,
1143 ) -> max[_T]: ...
1145 # set ColumnElement[_T] as a separate overload, to appease
1146 # mypy which seems to not want to accept _T from
1147 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1148 # _HasClauseElement as of mypy 1.15
1150 @overload
1151 def min( # noqa: A001
1152 self,
1153 col: ColumnElement[_T],
1154 *args: _ColumnExpressionOrLiteralArgument[Any],
1155 **kwargs: Any,
1156 ) -> min[_T]: ...
1158 @overload
1159 def min( # noqa: A001
1160 self,
1161 col: _ColumnExpressionArgument[_T],
1162 *args: _ColumnExpressionOrLiteralArgument[Any],
1163 **kwargs: Any,
1164 ) -> min[_T]: ...
1166 @overload
1167 def min( # noqa: A001
1168 self,
1169 col: _T,
1170 *args: _ColumnExpressionOrLiteralArgument[Any],
1171 **kwargs: Any,
1172 ) -> min[_T]: ...
1174 def min( # noqa: A001
1175 self,
1176 col: _ColumnExpressionOrLiteralArgument[_T],
1177 *args: _ColumnExpressionOrLiteralArgument[Any],
1178 **kwargs: Any,
1179 ) -> min[_T]: ...
1181 @property
1182 def mode(self) -> Type[mode[Any]]: ...
1184 @property
1185 def next_value(self) -> Type[next_value]: ...
1187 @property
1188 def now(self) -> Type[now]: ...
1190 @property
1191 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1193 @property
1194 def percent_rank(self) -> Type[percent_rank]: ...
1196 @property
1197 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1199 @property
1200 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1202 @property
1203 def random(self) -> Type[random]: ...
1205 @property
1206 def rank(self) -> Type[rank]: ...
1208 @property
1209 def rollup(self) -> Type[rollup[Any]]: ...
1211 @property
1212 def session_user(self) -> Type[session_user]: ...
1214 # set ColumnElement[_T] as a separate overload, to appease
1215 # mypy which seems to not want to accept _T from
1216 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1217 # _HasClauseElement as of mypy 1.15
1219 @overload
1220 def sum( # noqa: A001
1221 self,
1222 col: ColumnElement[_T],
1223 *args: _ColumnExpressionOrLiteralArgument[Any],
1224 **kwargs: Any,
1225 ) -> sum[_T]: ...
1227 @overload
1228 def sum( # noqa: A001
1229 self,
1230 col: _ColumnExpressionArgument[_T],
1231 *args: _ColumnExpressionOrLiteralArgument[Any],
1232 **kwargs: Any,
1233 ) -> sum[_T]: ...
1235 @overload
1236 def sum( # noqa: A001
1237 self,
1238 col: _T,
1239 *args: _ColumnExpressionOrLiteralArgument[Any],
1240 **kwargs: Any,
1241 ) -> sum[_T]: ...
1243 def sum( # noqa: A001
1244 self,
1245 col: _ColumnExpressionOrLiteralArgument[_T],
1246 *args: _ColumnExpressionOrLiteralArgument[Any],
1247 **kwargs: Any,
1248 ) -> sum[_T]: ...
1250 @property
1251 def sysdate(self) -> Type[sysdate]: ...
1253 @property
1254 def user(self) -> Type[user]: ...
1256 # END GENERATED FUNCTION ACCESSORS
1259func = _FunctionGenerator()
1260func.__doc__ = _FunctionGenerator.__doc__
1262modifier = _FunctionGenerator(group=False)
1265class Function(FunctionElement[_T]):
1266 r"""Describe a named SQL function.
1268 The :class:`.Function` object is typically generated from the
1269 :data:`.func` generation object.
1272 :param \*clauses: list of column expressions that form the arguments
1273 of the SQL function call.
1275 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1276 used as the return value of the column expression generated by this
1277 function call.
1279 :param packagenames: a string which indicates package prefix names
1280 to be prepended to the function name when the SQL is generated.
1281 The :data:`.func` generator creates these when it is called using
1282 dotted format, e.g.::
1284 func.mypackage.some_function(col1, col2)
1286 .. seealso::
1288 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1290 :data:`.func` - namespace which produces registered or ad-hoc
1291 :class:`.Function` instances.
1293 :class:`.GenericFunction` - allows creation of registered function
1294 types.
1296 """
1298 __visit_name__ = "function"
1300 _traverse_internals = FunctionElement._traverse_internals + [
1301 ("packagenames", InternalTraversal.dp_plain_obj),
1302 ("name", InternalTraversal.dp_string),
1303 ("type", InternalTraversal.dp_type),
1304 ]
1306 name: str
1308 identifier: str
1310 type: TypeEngine[_T]
1311 """A :class:`_types.TypeEngine` object which refers to the SQL return
1312 type represented by this SQL function.
1314 This datatype may be configured when generating a
1315 :class:`_functions.Function` object by passing the
1316 :paramref:`_functions.Function.type_` parameter, e.g.::
1318 >>> select(func.lower("some VALUE", type_=String))
1320 The small number of built-in classes of :class:`_functions.Function` come
1321 with a built-in datatype that's appropriate to the class of function and
1322 its arguments. For functions that aren't known, the type defaults to the
1323 "null type".
1325 """
1327 @overload
1328 def __init__(
1329 self,
1330 name: str,
1331 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1332 type_: None = ...,
1333 packagenames: Optional[Tuple[str, ...]] = ...,
1334 ) -> None: ...
1336 @overload
1337 def __init__(
1338 self,
1339 name: str,
1340 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1341 type_: _TypeEngineArgument[_T] = ...,
1342 packagenames: Optional[Tuple[str, ...]] = ...,
1343 ) -> None: ...
1345 def __init__(
1346 self,
1347 name: str,
1348 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1349 type_: Optional[_TypeEngineArgument[_T]] = None,
1350 packagenames: Optional[Tuple[str, ...]] = None,
1351 ) -> None:
1352 """Construct a :class:`.Function`.
1354 The :data:`.func` construct is normally used to construct
1355 new :class:`.Function` instances.
1357 """
1358 self.packagenames = packagenames or ()
1359 self.name = name
1361 # if type is None, we get NULLTYPE, which is our _T. But I don't
1362 # know how to get the overloads to express that correctly
1363 self.type = type_api.to_instance(type_) # type: ignore
1365 FunctionElement.__init__(self, *clauses)
1367 def _bind_param(
1368 self,
1369 operator: OperatorType,
1370 obj: Any,
1371 type_: Optional[TypeEngine[_T]] = None,
1372 expanding: bool = False,
1373 **kw: Any,
1374 ) -> BindParameter[_T]:
1375 return BindParameter(
1376 self.name,
1377 obj,
1378 _compared_to_operator=operator,
1379 _compared_to_type=self.type,
1380 type_=type_,
1381 unique=True,
1382 expanding=expanding,
1383 **kw,
1384 )
1387class GenericFunction(Function[_T]):
1388 """Define a 'generic' function.
1390 A generic function is a pre-established :class:`.Function`
1391 class that is instantiated automatically when called
1392 by name from the :data:`.func` attribute. Note that
1393 calling any name from :data:`.func` has the effect that
1394 a new :class:`.Function` instance is created automatically,
1395 given that name. The primary use case for defining
1396 a :class:`.GenericFunction` class is so that a function
1397 of a particular name may be given a fixed return type.
1398 It can also include custom argument parsing schemes as well
1399 as additional methods.
1401 Subclasses of :class:`.GenericFunction` are automatically
1402 registered under the name of the class. For
1403 example, a user-defined function ``as_utc()`` would
1404 be available immediately::
1406 from sqlalchemy.sql.functions import GenericFunction
1407 from sqlalchemy.types import DateTime
1410 class as_utc(GenericFunction):
1411 type = DateTime()
1412 inherit_cache = True
1415 print(select(func.as_utc()))
1417 User-defined generic functions can be organized into
1418 packages by specifying the "package" attribute when defining
1419 :class:`.GenericFunction`. Third party libraries
1420 containing many functions may want to use this in order
1421 to avoid name conflicts with other systems. For example,
1422 if our ``as_utc()`` function were part of a package
1423 "time"::
1425 class as_utc(GenericFunction):
1426 type = DateTime()
1427 package = "time"
1428 inherit_cache = True
1430 The above function would be available from :data:`.func`
1431 using the package name ``time``::
1433 print(select(func.time.as_utc()))
1435 A final option is to allow the function to be accessed
1436 from one name in :data:`.func` but to render as a different name.
1437 The ``identifier`` attribute will override the name used to
1438 access the function as loaded from :data:`.func`, but will retain
1439 the usage of ``name`` as the rendered name::
1441 class GeoBuffer(GenericFunction):
1442 type = Geometry()
1443 package = "geo"
1444 name = "ST_Buffer"
1445 identifier = "buffer"
1446 inherit_cache = True
1448 The above function will render as follows:
1450 .. sourcecode:: pycon+sql
1452 >>> print(func.geo.buffer())
1453 {printsql}ST_Buffer()
1455 The name will be rendered as is, however without quoting unless the name
1456 contains special characters that require quoting. To force quoting
1457 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1458 construct::
1460 from sqlalchemy.sql import quoted_name
1463 class GeoBuffer(GenericFunction):
1464 type = Geometry()
1465 package = "geo"
1466 name = quoted_name("ST_Buffer", True)
1467 identifier = "buffer"
1468 inherit_cache = True
1470 The above function will render as:
1472 .. sourcecode:: pycon+sql
1474 >>> print(func.geo.buffer())
1475 {printsql}"ST_Buffer"()
1477 Type parameters for this class as a
1478 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1479 and should match the type seen in a :class:`_engine.Result`. For example::
1481 class as_utc(GenericFunction[datetime.datetime]):
1482 type = DateTime()
1483 inherit_cache = True
1485 The above indicates that the following expression returns a ``datetime``
1486 object::
1488 connection.scalar(select(func.as_utc()))
1490 .. versionadded:: 1.3.13 The :class:`.quoted_name` construct is now
1491 recognized for quoting when used with the "name" attribute of the
1492 object, so that quoting can be forced on or off for the function
1493 name.
1496 """
1498 coerce_arguments = True
1499 inherit_cache = True
1501 _register: bool
1503 name = "GenericFunction"
1505 def __init_subclass__(cls) -> None:
1506 if annotation.Annotated not in cls.__mro__:
1507 cls._register_generic_function(cls.__name__, cls.__dict__)
1508 super().__init_subclass__()
1510 @classmethod
1511 def _register_generic_function(
1512 cls, clsname: str, clsdict: Mapping[str, Any]
1513 ) -> None:
1514 cls.name = name = clsdict.get("name", clsname)
1515 cls.identifier = identifier = clsdict.get("identifier", name)
1516 package = clsdict.get("package", "_default")
1517 # legacy
1518 if "__return_type__" in clsdict:
1519 cls.type = clsdict["__return_type__"]
1521 # Check _register attribute status
1522 cls._register = getattr(cls, "_register", True)
1524 # Register the function if required
1525 if cls._register:
1526 register_function(identifier, cls, package)
1527 else:
1528 # Set _register to True to register child classes by default
1529 cls._register = True
1531 def __init__(
1532 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1533 ) -> None:
1534 parsed_args = kwargs.pop("_parsed_args", None)
1535 if parsed_args is None:
1536 parsed_args = [
1537 coercions.expect(
1538 roles.ExpressionElementRole,
1539 c,
1540 name=self.name,
1541 apply_propagate_attrs=self,
1542 )
1543 for c in args
1544 ]
1545 self._has_args = self._has_args or bool(parsed_args)
1546 self.packagenames = ()
1548 self.clause_expr = Grouping(
1549 ClauseList(
1550 operator=operators.comma_op, group_contents=True, *parsed_args
1551 )
1552 )
1554 self.type = type_api.to_instance( # type: ignore
1555 kwargs.pop("type_", None) or getattr(self, "type", None)
1556 )
1559register_function("cast", Cast) # type: ignore
1560register_function("extract", Extract) # type: ignore
1563class next_value(GenericFunction[int]):
1564 """Represent the 'next value', given a :class:`.Sequence`
1565 as its single argument.
1567 Compiles into the appropriate function on each backend,
1568 or will raise NotImplementedError if used on a backend
1569 that does not provide support for sequences.
1571 """
1573 type = sqltypes.Integer()
1574 name = "next_value"
1576 _traverse_internals = [
1577 ("sequence", InternalTraversal.dp_named_ddl_element)
1578 ]
1580 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1581 assert isinstance(
1582 seq, schema.Sequence
1583 ), "next_value() accepts a Sequence object as input."
1584 self.sequence = seq
1585 self.type = sqltypes.to_instance( # type: ignore
1586 seq.data_type or getattr(self, "type", None)
1587 )
1589 def compare(self, other: Any, **kw: Any) -> bool:
1590 return (
1591 isinstance(other, next_value)
1592 and self.sequence.name == other.sequence.name
1593 )
1595 @property
1596 def _from_objects(self) -> Any:
1597 return []
1600class AnsiFunction(GenericFunction[_T]):
1601 """Define a function in "ansi" format, which doesn't render parenthesis."""
1603 inherit_cache = True
1605 def __init__(
1606 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1607 ) -> None:
1608 GenericFunction.__init__(self, *args, **kwargs)
1611class ReturnTypeFromArgs(GenericFunction[_T]):
1612 """Define a function whose return type is bound to the type of its
1613 arguments.
1614 """
1616 inherit_cache = True
1618 # set ColumnElement[_T] as a separate overload, to appease
1619 # mypy which seems to not want to accept _T from
1620 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1621 # _HasClauseElement as of mypy 1.15
1623 @overload
1624 def __init__(
1625 self,
1626 col: ColumnElement[_T],
1627 *args: _ColumnExpressionOrLiteralArgument[Any],
1628 **kwargs: Any,
1629 ) -> None: ...
1631 @overload
1632 def __init__(
1633 self,
1634 col: _ColumnExpressionArgument[_T],
1635 *args: _ColumnExpressionOrLiteralArgument[Any],
1636 **kwargs: Any,
1637 ) -> None: ...
1639 @overload
1640 def __init__(
1641 self,
1642 col: _T,
1643 *args: _ColumnExpressionOrLiteralArgument[Any],
1644 **kwargs: Any,
1645 ) -> None: ...
1647 def __init__(
1648 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1649 ) -> None:
1650 fn_args: Sequence[ColumnElement[Any]] = [
1651 coercions.expect(
1652 roles.ExpressionElementRole,
1653 c,
1654 name=self.name,
1655 apply_propagate_attrs=self,
1656 )
1657 for c in args
1658 ]
1659 kwargs.setdefault("type_", _type_from_args(fn_args))
1660 kwargs["_parsed_args"] = fn_args
1661 super().__init__(*fn_args, **kwargs)
1664class coalesce(ReturnTypeFromArgs[_T]):
1665 _has_args = True
1666 inherit_cache = True
1669class max(ReturnTypeFromArgs[_T]): # noqa: A001
1670 """The SQL MAX() aggregate function."""
1672 inherit_cache = True
1675class min(ReturnTypeFromArgs[_T]): # noqa: A001
1676 """The SQL MIN() aggregate function."""
1678 inherit_cache = True
1681class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1682 """The SQL SUM() aggregate function."""
1684 inherit_cache = True
1687class now(GenericFunction[datetime.datetime]):
1688 """The SQL now() datetime function.
1690 SQLAlchemy dialects will usually render this particular function
1691 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1693 """
1695 type = sqltypes.DateTime()
1696 inherit_cache = True
1699class concat(GenericFunction[str]):
1700 """The SQL CONCAT() function, which concatenates strings.
1702 E.g.:
1704 .. sourcecode:: pycon+sql
1706 >>> print(select(func.concat("a", "b")))
1707 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1709 String concatenation in SQLAlchemy is more commonly available using the
1710 Python ``+`` operator with string datatypes, which will render a
1711 backend-specific concatenation operator, such as :
1713 .. sourcecode:: pycon+sql
1715 >>> print(select(literal("a") + "b"))
1716 {printsql}SELECT :param_1 || :param_2 AS anon_1
1719 """
1721 type = sqltypes.String()
1722 inherit_cache = True
1725class char_length(GenericFunction[int]):
1726 """The CHAR_LENGTH() SQL function."""
1728 type = sqltypes.Integer()
1729 inherit_cache = True
1731 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1732 # slight hack to limit to just one positional argument
1733 # not sure why this one function has this special treatment
1734 super().__init__(arg, **kw)
1737class random(GenericFunction[float]):
1738 """The RANDOM() SQL function."""
1740 _has_args = True
1741 inherit_cache = True
1744class count(GenericFunction[int]):
1745 r"""The ANSI COUNT aggregate function. With no arguments,
1746 emits COUNT \*.
1748 E.g.::
1750 from sqlalchemy import func
1751 from sqlalchemy import select
1752 from sqlalchemy import table, column
1754 my_table = table("some_table", column("id"))
1756 stmt = select(func.count()).select_from(my_table)
1758 Executing ``stmt`` would emit:
1760 .. sourcecode:: sql
1762 SELECT count(*) AS count_1
1763 FROM some_table
1766 """
1768 type = sqltypes.Integer()
1769 inherit_cache = True
1771 def __init__(
1772 self,
1773 expression: Union[
1774 _ColumnExpressionArgument[Any], _StarOrOne, None
1775 ] = None,
1776 **kwargs: Any,
1777 ) -> None:
1778 if expression is None:
1779 expression = literal_column("*")
1780 super().__init__(expression, **kwargs)
1783class current_date(AnsiFunction[datetime.date]):
1784 """The CURRENT_DATE() SQL function."""
1786 type = sqltypes.Date()
1787 inherit_cache = True
1790class current_time(AnsiFunction[datetime.time]):
1791 """The CURRENT_TIME() SQL function."""
1793 type = sqltypes.Time()
1794 inherit_cache = True
1797class current_timestamp(AnsiFunction[datetime.datetime]):
1798 """The CURRENT_TIMESTAMP() SQL function."""
1800 type = sqltypes.DateTime()
1801 inherit_cache = True
1804class current_user(AnsiFunction[str]):
1805 """The CURRENT_USER() SQL function."""
1807 type = sqltypes.String()
1808 inherit_cache = True
1811class localtime(AnsiFunction[datetime.datetime]):
1812 """The localtime() SQL function."""
1814 type = sqltypes.DateTime()
1815 inherit_cache = True
1818class localtimestamp(AnsiFunction[datetime.datetime]):
1819 """The localtimestamp() SQL function."""
1821 type = sqltypes.DateTime()
1822 inherit_cache = True
1825class session_user(AnsiFunction[str]):
1826 """The SESSION_USER() SQL function."""
1828 type = sqltypes.String()
1829 inherit_cache = True
1832class sysdate(AnsiFunction[datetime.datetime]):
1833 """The SYSDATE() SQL function."""
1835 type = sqltypes.DateTime()
1836 inherit_cache = True
1839class user(AnsiFunction[str]):
1840 """The USER() SQL function."""
1842 type = sqltypes.String()
1843 inherit_cache = True
1846class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1847 """Support for the ARRAY_AGG function.
1849 The ``func.array_agg(expr)`` construct returns an expression of
1850 type :class:`_types.ARRAY`.
1852 e.g.::
1854 stmt = select(func.array_agg(table.c.values)[2:5])
1856 .. seealso::
1858 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1859 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1860 added.
1862 """
1864 inherit_cache = True
1866 def __init__(
1867 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1868 ) -> None:
1869 fn_args: Sequence[ColumnElement[Any]] = [
1870 coercions.expect(
1871 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1872 )
1873 for c in args
1874 ]
1876 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1877 if "type_" not in kwargs:
1878 type_from_args = _type_from_args(fn_args)
1879 if isinstance(type_from_args, sqltypes.ARRAY):
1880 kwargs["type_"] = type_from_args
1881 else:
1882 kwargs["type_"] = default_array_type(
1883 type_from_args, dimensions=1
1884 )
1885 kwargs["_parsed_args"] = fn_args
1886 super().__init__(*fn_args, **kwargs)
1889class OrderedSetAgg(GenericFunction[_T]):
1890 """Define a function where the return type is based on the sort
1891 expression type as defined by the expression passed to the
1892 :meth:`.FunctionElement.within_group` method."""
1894 array_for_multi_clause = False
1895 inherit_cache = True
1897 def within_group_type(
1898 self, within_group: WithinGroup[Any]
1899 ) -> TypeEngine[Any]:
1900 func_clauses = cast(ClauseList, self.clause_expr.element)
1901 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
1902 within_group.order_by
1903 )
1904 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1905 return sqltypes.ARRAY(order_by[0].type)
1906 else:
1907 return order_by[0].type
1910class mode(OrderedSetAgg[_T]):
1911 """Implement the ``mode`` ordered-set aggregate function.
1913 This function must be used with the :meth:`.FunctionElement.within_group`
1914 modifier to supply a sort expression to operate upon.
1916 The return type of this function is the same as the sort expression.
1918 """
1920 inherit_cache = True
1923class percentile_cont(OrderedSetAgg[_T]):
1924 """Implement the ``percentile_cont`` ordered-set aggregate function.
1926 This function must be used with the :meth:`.FunctionElement.within_group`
1927 modifier to supply a sort expression to operate upon.
1929 The return type of this function is the same as the sort expression,
1930 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1931 expression's type.
1933 """
1935 array_for_multi_clause = True
1936 inherit_cache = True
1939class percentile_disc(OrderedSetAgg[_T]):
1940 """Implement the ``percentile_disc`` ordered-set aggregate function.
1942 This function must be used with the :meth:`.FunctionElement.within_group`
1943 modifier to supply a sort expression to operate upon.
1945 The return type of this function is the same as the sort expression,
1946 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1947 expression's type.
1949 """
1951 array_for_multi_clause = True
1952 inherit_cache = True
1955class rank(GenericFunction[int]):
1956 """Implement the ``rank`` hypothetical-set aggregate function.
1958 This function must be used with the :meth:`.FunctionElement.within_group`
1959 modifier to supply a sort expression to operate upon.
1961 The return type of this function is :class:`.Integer`.
1963 """
1965 type = sqltypes.Integer()
1966 inherit_cache = True
1969class dense_rank(GenericFunction[int]):
1970 """Implement the ``dense_rank`` hypothetical-set aggregate function.
1972 This function must be used with the :meth:`.FunctionElement.within_group`
1973 modifier to supply a sort expression to operate upon.
1975 The return type of this function is :class:`.Integer`.
1977 """
1979 type = sqltypes.Integer()
1980 inherit_cache = True
1983class percent_rank(GenericFunction[decimal.Decimal]):
1984 """Implement the ``percent_rank`` hypothetical-set aggregate function.
1986 This function must be used with the :meth:`.FunctionElement.within_group`
1987 modifier to supply a sort expression to operate upon.
1989 The return type of this function is :class:`.Numeric`.
1991 """
1993 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
1994 inherit_cache = True
1997class cume_dist(GenericFunction[decimal.Decimal]):
1998 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2000 This function must be used with the :meth:`.FunctionElement.within_group`
2001 modifier to supply a sort expression to operate upon.
2003 The return type of this function is :class:`.Numeric`.
2005 """
2007 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2008 inherit_cache = True
2011class cube(GenericFunction[_T]):
2012 r"""Implement the ``CUBE`` grouping operation.
2014 This function is used as part of the GROUP BY of a statement,
2015 e.g. :meth:`_expression.Select.group_by`::
2017 stmt = select(
2018 func.sum(table.c.value), table.c.col_1, table.c.col_2
2019 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2021 .. versionadded:: 1.2
2023 """
2025 _has_args = True
2026 inherit_cache = True
2029class rollup(GenericFunction[_T]):
2030 r"""Implement the ``ROLLUP`` grouping operation.
2032 This function is used as part of the GROUP BY of a statement,
2033 e.g. :meth:`_expression.Select.group_by`::
2035 stmt = select(
2036 func.sum(table.c.value), table.c.col_1, table.c.col_2
2037 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2039 .. versionadded:: 1.2
2041 """
2043 _has_args = True
2044 inherit_cache = True
2047class grouping_sets(GenericFunction[_T]):
2048 r"""Implement the ``GROUPING SETS`` grouping operation.
2050 This function is used as part of the GROUP BY of a statement,
2051 e.g. :meth:`_expression.Select.group_by`::
2053 stmt = select(
2054 func.sum(table.c.value), table.c.col_1, table.c.col_2
2055 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2057 In order to group by multiple sets, use the :func:`.tuple_` construct::
2059 from sqlalchemy import tuple_
2061 stmt = select(
2062 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2063 ).group_by(
2064 func.grouping_sets(
2065 tuple_(table.c.col_1, table.c.col_2),
2066 tuple_(table.c.value, table.c.col_3),
2067 )
2068 )
2070 .. versionadded:: 1.2
2072 """ # noqa: E501
2074 _has_args = True
2075 inherit_cache = True
2078class aggregate_strings(GenericFunction[str]):
2079 """Implement a generic string aggregation function.
2081 This function will concatenate non-null values into a string and
2082 separate the values by a delimiter.
2084 This function is compiled on a per-backend basis, into functions
2085 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2087 e.g. Example usage with delimiter '.'::
2089 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2091 The return type of this function is :class:`.String`.
2093 .. versionadded: 2.0.21
2095 """
2097 type = sqltypes.String()
2098 _has_args = True
2099 inherit_cache = True
2101 def __init__(
2102 self, clause: _ColumnExpressionArgument[Any], separator: str
2103 ) -> None:
2104 super().__init__(clause, separator)