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 is operators.getitem and isinstance(
749 self.type, sqltypes.ARRAY
750 ):
751 return Grouping(self)
752 else:
753 return super().self_group(against=against)
755 @property
756 def entity_namespace(self) -> _EntityNamespace:
757 """overrides FromClause.entity_namespace as functions are generally
758 column expressions and not FromClauses.
760 """
761 # ideally functions would not be fromclauses but we failed to make
762 # this adjustment in 1.4
763 return _entity_namespace(self.clause_expr)
766class FunctionAsBinary(BinaryExpression[Any]):
767 _traverse_internals = [
768 ("sql_function", InternalTraversal.dp_clauseelement),
769 ("left_index", InternalTraversal.dp_plain_obj),
770 ("right_index", InternalTraversal.dp_plain_obj),
771 ("modifiers", InternalTraversal.dp_plain_dict),
772 ]
774 sql_function: FunctionElement[Any]
775 left_index: int
776 right_index: int
778 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
779 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
781 def __init__(
782 self, fn: FunctionElement[Any], left_index: int, right_index: int
783 ) -> None:
784 self.sql_function = fn
785 self.left_index = left_index
786 self.right_index = right_index
788 self.operator = operators.function_as_comparison_op
789 self.type = sqltypes.BOOLEANTYPE
790 self.negate = None
791 self._is_implicitly_boolean = True
792 self.modifiers = {}
794 @property
795 def left_expr(self) -> ColumnElement[Any]:
796 return self.sql_function.clauses.clauses[self.left_index - 1]
798 @left_expr.setter
799 def left_expr(self, value: ColumnElement[Any]) -> None:
800 self.sql_function.clauses.clauses[self.left_index - 1] = value
802 @property
803 def right_expr(self) -> ColumnElement[Any]:
804 return self.sql_function.clauses.clauses[self.right_index - 1]
806 @right_expr.setter
807 def right_expr(self, value: ColumnElement[Any]) -> None:
808 self.sql_function.clauses.clauses[self.right_index - 1] = value
810 if not TYPE_CHECKING:
811 # mypy can't accommodate @property to replace an instance
812 # variable
814 left = left_expr
815 right = right_expr
818class ScalarFunctionColumn(NamedColumn[_T]):
819 __visit_name__ = "scalar_function_column"
821 _traverse_internals = [
822 ("name", InternalTraversal.dp_anon_name),
823 ("type", InternalTraversal.dp_type),
824 ("fn", InternalTraversal.dp_clauseelement),
825 ]
827 is_literal = False
828 table = None
830 def __init__(
831 self,
832 fn: FunctionElement[_T],
833 name: str,
834 type_: Optional[_TypeEngineArgument[_T]] = None,
835 ) -> None:
836 self.fn = fn
837 self.name = name
839 # if type is None, we get NULLTYPE, which is our _T. But I don't
840 # know how to get the overloads to express that correctly
841 self.type = type_api.to_instance(type_) # type: ignore
844class _FunctionGenerator:
845 """Generate SQL function expressions.
847 :data:`.func` is a special object instance which generates SQL
848 functions based on name-based attributes, e.g.:
850 .. sourcecode:: pycon+sql
852 >>> print(func.count(1))
853 {printsql}count(:param_1)
855 The returned object is an instance of :class:`.Function`, and is a
856 column-oriented SQL element like any other, and is used in that way:
858 .. sourcecode:: pycon+sql
860 >>> print(select(func.count(table.c.id)))
861 {printsql}SELECT count(sometable.id) FROM sometable
863 Any name can be given to :data:`.func`. If the function name is unknown to
864 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
865 which SQLAlchemy is aware of, the name may be interpreted as a *generic
866 function* which will be compiled appropriately to the target database:
868 .. sourcecode:: pycon+sql
870 >>> print(func.current_timestamp())
871 {printsql}CURRENT_TIMESTAMP
873 To call functions which are present in dot-separated packages,
874 specify them in the same manner:
876 .. sourcecode:: pycon+sql
878 >>> print(func.stats.yield_curve(5, 10))
879 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
881 SQLAlchemy can be made aware of the return type of functions to enable
882 type-specific lexical and result-based behavior. For example, to ensure
883 that a string-based function returns a Unicode value and is similarly
884 treated as a string in expressions, specify
885 :class:`~sqlalchemy.types.Unicode` as the type:
887 .. sourcecode:: pycon+sql
889 >>> print(
890 ... func.my_string("hi", type_=Unicode)
891 ... + " "
892 ... + func.my_string("there", type_=Unicode)
893 ... )
894 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
896 The object returned by a :data:`.func` call is usually an instance of
897 :class:`.Function`.
898 This object meets the "column" interface, including comparison and labeling
899 functions. The object can also be passed the :meth:`~.Connectable.execute`
900 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
901 where it will be
902 wrapped inside of a SELECT statement first::
904 print(connection.execute(func.current_timestamp()).scalar())
906 In a few exception cases, the :data:`.func` accessor
907 will redirect a name to a built-in expression such as :func:`.cast`
908 or :func:`.extract`, as these names have well-known meaning
909 but are not exactly the same as "functions" from a SQLAlchemy
910 perspective.
912 Functions which are interpreted as "generic" functions know how to
913 calculate their return type automatically. For a listing of known generic
914 functions, see :ref:`generic_functions`.
916 .. note::
918 The :data:`.func` construct has only limited support for calling
919 standalone "stored procedures", especially those with special
920 parameterization concerns.
922 See the section :ref:`stored_procedures` for details on how to use
923 the DBAPI-level ``callproc()`` method for fully traditional stored
924 procedures.
926 .. seealso::
928 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
930 :class:`.Function`
932 """ # noqa
934 def __init__(self, **opts: Any) -> None:
935 self.__names: List[str] = []
936 self.opts = opts
938 def __getattr__(self, name: str) -> _FunctionGenerator:
939 # passthru __ attributes; fixes pydoc
940 if name.startswith("__"):
941 try:
942 return self.__dict__[name] # type: ignore
943 except KeyError:
944 raise AttributeError(name)
946 elif name.endswith("_"):
947 name = name[0:-1]
948 f = _FunctionGenerator(**self.opts)
949 f.__names = list(self.__names) + [name]
950 return f
952 @overload
953 def __call__(
954 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
955 ) -> Function[_T]: ...
957 @overload
958 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
960 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
961 o = self.opts.copy()
962 o.update(kwargs)
964 tokens = len(self.__names)
966 if tokens == 2:
967 package, fname = self.__names
968 elif tokens == 1:
969 package, fname = "_default", self.__names[0]
970 else:
971 package = None
973 if package is not None:
974 func = _registry[package].get(fname.lower())
975 if func is not None:
976 return func(*c, **o)
978 return Function(
979 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
980 )
982 if TYPE_CHECKING:
983 # START GENERATED FUNCTION ACCESSORS
985 # code within this block is **programmatically,
986 # statically generated** by tools/generate_sql_functions.py
988 @property
989 def aggregate_strings(self) -> Type[aggregate_strings]: ...
991 @property
992 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
994 # set ColumnElement[_T] as a separate overload, to appease
995 # mypy which seems to not want to accept _T from
996 # _ColumnExpressionArgument. Seems somewhat related to the covariant
997 # _HasClauseElement as of mypy 1.15
999 @overload
1000 def array_agg(
1001 self,
1002 col: ColumnElement[_T],
1003 *args: _ColumnExpressionOrLiteralArgument[Any],
1004 **kwargs: Any,
1005 ) -> array_agg[_T]: ...
1007 @overload
1008 def array_agg(
1009 self,
1010 col: _ColumnExpressionArgument[_T],
1011 *args: _ColumnExpressionOrLiteralArgument[Any],
1012 **kwargs: Any,
1013 ) -> array_agg[_T]: ...
1015 @overload
1016 def array_agg(
1017 self,
1018 col: _T,
1019 *args: _ColumnExpressionOrLiteralArgument[Any],
1020 **kwargs: Any,
1021 ) -> array_agg[_T]: ...
1023 def array_agg(
1024 self,
1025 col: _ColumnExpressionOrLiteralArgument[_T],
1026 *args: _ColumnExpressionOrLiteralArgument[Any],
1027 **kwargs: Any,
1028 ) -> array_agg[_T]: ...
1030 @property
1031 def cast(self) -> Type[Cast[Any]]: ...
1033 @property
1034 def char_length(self) -> Type[char_length]: ...
1036 # set ColumnElement[_T] as a separate overload, to appease
1037 # mypy which seems to not want to accept _T from
1038 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1039 # _HasClauseElement as of mypy 1.15
1041 @overload
1042 def coalesce(
1043 self,
1044 col: ColumnElement[_T],
1045 *args: _ColumnExpressionOrLiteralArgument[Any],
1046 **kwargs: Any,
1047 ) -> coalesce[_T]: ...
1049 @overload
1050 def coalesce(
1051 self,
1052 col: _ColumnExpressionArgument[_T],
1053 *args: _ColumnExpressionOrLiteralArgument[Any],
1054 **kwargs: Any,
1055 ) -> coalesce[_T]: ...
1057 @overload
1058 def coalesce(
1059 self,
1060 col: _T,
1061 *args: _ColumnExpressionOrLiteralArgument[Any],
1062 **kwargs: Any,
1063 ) -> coalesce[_T]: ...
1065 def coalesce(
1066 self,
1067 col: _ColumnExpressionOrLiteralArgument[_T],
1068 *args: _ColumnExpressionOrLiteralArgument[Any],
1069 **kwargs: Any,
1070 ) -> coalesce[_T]: ...
1072 @property
1073 def concat(self) -> Type[concat]: ...
1075 @property
1076 def count(self) -> Type[count]: ...
1078 @property
1079 def cube(self) -> Type[cube[Any]]: ...
1081 @property
1082 def cume_dist(self) -> Type[cume_dist]: ...
1084 @property
1085 def current_date(self) -> Type[current_date]: ...
1087 @property
1088 def current_time(self) -> Type[current_time]: ...
1090 @property
1091 def current_timestamp(self) -> Type[current_timestamp]: ...
1093 @property
1094 def current_user(self) -> Type[current_user]: ...
1096 @property
1097 def dense_rank(self) -> Type[dense_rank]: ...
1099 @property
1100 def extract(self) -> Type[Extract]: ...
1102 @property
1103 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1105 @property
1106 def localtime(self) -> Type[localtime]: ...
1108 @property
1109 def localtimestamp(self) -> Type[localtimestamp]: ...
1111 # set ColumnElement[_T] as a separate overload, to appease
1112 # mypy which seems to not want to accept _T from
1113 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1114 # _HasClauseElement as of mypy 1.15
1116 @overload
1117 def max( # noqa: A001
1118 self,
1119 col: ColumnElement[_T],
1120 *args: _ColumnExpressionOrLiteralArgument[Any],
1121 **kwargs: Any,
1122 ) -> max[_T]: ...
1124 @overload
1125 def max( # noqa: A001
1126 self,
1127 col: _ColumnExpressionArgument[_T],
1128 *args: _ColumnExpressionOrLiteralArgument[Any],
1129 **kwargs: Any,
1130 ) -> max[_T]: ...
1132 @overload
1133 def max( # noqa: A001
1134 self,
1135 col: _T,
1136 *args: _ColumnExpressionOrLiteralArgument[Any],
1137 **kwargs: Any,
1138 ) -> max[_T]: ...
1140 def max( # noqa: A001
1141 self,
1142 col: _ColumnExpressionOrLiteralArgument[_T],
1143 *args: _ColumnExpressionOrLiteralArgument[Any],
1144 **kwargs: Any,
1145 ) -> max[_T]: ...
1147 # set ColumnElement[_T] as a separate overload, to appease
1148 # mypy which seems to not want to accept _T from
1149 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1150 # _HasClauseElement as of mypy 1.15
1152 @overload
1153 def min( # noqa: A001
1154 self,
1155 col: ColumnElement[_T],
1156 *args: _ColumnExpressionOrLiteralArgument[Any],
1157 **kwargs: Any,
1158 ) -> min[_T]: ...
1160 @overload
1161 def min( # noqa: A001
1162 self,
1163 col: _ColumnExpressionArgument[_T],
1164 *args: _ColumnExpressionOrLiteralArgument[Any],
1165 **kwargs: Any,
1166 ) -> min[_T]: ...
1168 @overload
1169 def min( # noqa: A001
1170 self,
1171 col: _T,
1172 *args: _ColumnExpressionOrLiteralArgument[Any],
1173 **kwargs: Any,
1174 ) -> min[_T]: ...
1176 def min( # noqa: A001
1177 self,
1178 col: _ColumnExpressionOrLiteralArgument[_T],
1179 *args: _ColumnExpressionOrLiteralArgument[Any],
1180 **kwargs: Any,
1181 ) -> min[_T]: ...
1183 @property
1184 def mode(self) -> Type[mode[Any]]: ...
1186 @property
1187 def next_value(self) -> Type[next_value]: ...
1189 @property
1190 def now(self) -> Type[now]: ...
1192 @property
1193 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1195 @property
1196 def percent_rank(self) -> Type[percent_rank]: ...
1198 @property
1199 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1201 @property
1202 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1204 @property
1205 def random(self) -> Type[random]: ...
1207 @property
1208 def rank(self) -> Type[rank]: ...
1210 @property
1211 def rollup(self) -> Type[rollup[Any]]: ...
1213 @property
1214 def session_user(self) -> Type[session_user]: ...
1216 # set ColumnElement[_T] as a separate overload, to appease
1217 # mypy which seems to not want to accept _T from
1218 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1219 # _HasClauseElement as of mypy 1.15
1221 @overload
1222 def sum( # noqa: A001
1223 self,
1224 col: ColumnElement[_T],
1225 *args: _ColumnExpressionOrLiteralArgument[Any],
1226 **kwargs: Any,
1227 ) -> sum[_T]: ...
1229 @overload
1230 def sum( # noqa: A001
1231 self,
1232 col: _ColumnExpressionArgument[_T],
1233 *args: _ColumnExpressionOrLiteralArgument[Any],
1234 **kwargs: Any,
1235 ) -> sum[_T]: ...
1237 @overload
1238 def sum( # noqa: A001
1239 self,
1240 col: _T,
1241 *args: _ColumnExpressionOrLiteralArgument[Any],
1242 **kwargs: Any,
1243 ) -> sum[_T]: ...
1245 def sum( # noqa: A001
1246 self,
1247 col: _ColumnExpressionOrLiteralArgument[_T],
1248 *args: _ColumnExpressionOrLiteralArgument[Any],
1249 **kwargs: Any,
1250 ) -> sum[_T]: ...
1252 @property
1253 def sysdate(self) -> Type[sysdate]: ...
1255 @property
1256 def user(self) -> Type[user]: ...
1258 # END GENERATED FUNCTION ACCESSORS
1261func = _FunctionGenerator()
1262func.__doc__ = _FunctionGenerator.__doc__
1264modifier = _FunctionGenerator(group=False)
1267class Function(FunctionElement[_T]):
1268 r"""Describe a named SQL function.
1270 The :class:`.Function` object is typically generated from the
1271 :data:`.func` generation object.
1274 :param \*clauses: list of column expressions that form the arguments
1275 of the SQL function call.
1277 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1278 used as the return value of the column expression generated by this
1279 function call.
1281 :param packagenames: a string which indicates package prefix names
1282 to be prepended to the function name when the SQL is generated.
1283 The :data:`.func` generator creates these when it is called using
1284 dotted format, e.g.::
1286 func.mypackage.some_function(col1, col2)
1288 .. seealso::
1290 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1292 :data:`.func` - namespace which produces registered or ad-hoc
1293 :class:`.Function` instances.
1295 :class:`.GenericFunction` - allows creation of registered function
1296 types.
1298 """
1300 __visit_name__ = "function"
1302 _traverse_internals = FunctionElement._traverse_internals + [
1303 ("packagenames", InternalTraversal.dp_plain_obj),
1304 ("name", InternalTraversal.dp_string),
1305 ("type", InternalTraversal.dp_type),
1306 ]
1308 name: str
1310 identifier: str
1312 type: TypeEngine[_T]
1313 """A :class:`_types.TypeEngine` object which refers to the SQL return
1314 type represented by this SQL function.
1316 This datatype may be configured when generating a
1317 :class:`_functions.Function` object by passing the
1318 :paramref:`_functions.Function.type_` parameter, e.g.::
1320 >>> select(func.lower("some VALUE", type_=String))
1322 The small number of built-in classes of :class:`_functions.Function` come
1323 with a built-in datatype that's appropriate to the class of function and
1324 its arguments. For functions that aren't known, the type defaults to the
1325 "null type".
1327 """
1329 @overload
1330 def __init__(
1331 self,
1332 name: str,
1333 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1334 type_: None = ...,
1335 packagenames: Optional[Tuple[str, ...]] = ...,
1336 ) -> None: ...
1338 @overload
1339 def __init__(
1340 self,
1341 name: str,
1342 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1343 type_: _TypeEngineArgument[_T] = ...,
1344 packagenames: Optional[Tuple[str, ...]] = ...,
1345 ) -> None: ...
1347 def __init__(
1348 self,
1349 name: str,
1350 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1351 type_: Optional[_TypeEngineArgument[_T]] = None,
1352 packagenames: Optional[Tuple[str, ...]] = None,
1353 ) -> None:
1354 """Construct a :class:`.Function`.
1356 The :data:`.func` construct is normally used to construct
1357 new :class:`.Function` instances.
1359 """
1360 self.packagenames = packagenames or ()
1361 self.name = name
1363 # if type is None, we get NULLTYPE, which is our _T. But I don't
1364 # know how to get the overloads to express that correctly
1365 self.type = type_api.to_instance(type_) # type: ignore
1367 FunctionElement.__init__(self, *clauses)
1369 def _bind_param(
1370 self,
1371 operator: OperatorType,
1372 obj: Any,
1373 type_: Optional[TypeEngine[_T]] = None,
1374 expanding: bool = False,
1375 **kw: Any,
1376 ) -> BindParameter[_T]:
1377 return BindParameter(
1378 self.name,
1379 obj,
1380 _compared_to_operator=operator,
1381 _compared_to_type=self.type,
1382 type_=type_,
1383 unique=True,
1384 expanding=expanding,
1385 **kw,
1386 )
1389class GenericFunction(Function[_T]):
1390 """Define a 'generic' function.
1392 A generic function is a pre-established :class:`.Function`
1393 class that is instantiated automatically when called
1394 by name from the :data:`.func` attribute. Note that
1395 calling any name from :data:`.func` has the effect that
1396 a new :class:`.Function` instance is created automatically,
1397 given that name. The primary use case for defining
1398 a :class:`.GenericFunction` class is so that a function
1399 of a particular name may be given a fixed return type.
1400 It can also include custom argument parsing schemes as well
1401 as additional methods.
1403 Subclasses of :class:`.GenericFunction` are automatically
1404 registered under the name of the class. For
1405 example, a user-defined function ``as_utc()`` would
1406 be available immediately::
1408 from sqlalchemy.sql.functions import GenericFunction
1409 from sqlalchemy.types import DateTime
1412 class as_utc(GenericFunction):
1413 type = DateTime()
1414 inherit_cache = True
1417 print(select(func.as_utc()))
1419 User-defined generic functions can be organized into
1420 packages by specifying the "package" attribute when defining
1421 :class:`.GenericFunction`. Third party libraries
1422 containing many functions may want to use this in order
1423 to avoid name conflicts with other systems. For example,
1424 if our ``as_utc()`` function were part of a package
1425 "time"::
1427 class as_utc(GenericFunction):
1428 type = DateTime()
1429 package = "time"
1430 inherit_cache = True
1432 The above function would be available from :data:`.func`
1433 using the package name ``time``::
1435 print(select(func.time.as_utc()))
1437 A final option is to allow the function to be accessed
1438 from one name in :data:`.func` but to render as a different name.
1439 The ``identifier`` attribute will override the name used to
1440 access the function as loaded from :data:`.func`, but will retain
1441 the usage of ``name`` as the rendered name::
1443 class GeoBuffer(GenericFunction):
1444 type = Geometry()
1445 package = "geo"
1446 name = "ST_Buffer"
1447 identifier = "buffer"
1448 inherit_cache = True
1450 The above function will render as follows:
1452 .. sourcecode:: pycon+sql
1454 >>> print(func.geo.buffer())
1455 {printsql}ST_Buffer()
1457 The name will be rendered as is, however without quoting unless the name
1458 contains special characters that require quoting. To force quoting
1459 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1460 construct::
1462 from sqlalchemy.sql import quoted_name
1465 class GeoBuffer(GenericFunction):
1466 type = Geometry()
1467 package = "geo"
1468 name = quoted_name("ST_Buffer", True)
1469 identifier = "buffer"
1470 inherit_cache = True
1472 The above function will render as:
1474 .. sourcecode:: pycon+sql
1476 >>> print(func.geo.buffer())
1477 {printsql}"ST_Buffer"()
1479 Type parameters for this class as a
1480 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1481 and should match the type seen in a :class:`_engine.Result`. For example::
1483 class as_utc(GenericFunction[datetime.datetime]):
1484 type = DateTime()
1485 inherit_cache = True
1487 The above indicates that the following expression returns a ``datetime``
1488 object::
1490 connection.scalar(select(func.as_utc()))
1492 .. versionadded:: 1.3.13 The :class:`.quoted_name` construct is now
1493 recognized for quoting when used with the "name" attribute of the
1494 object, so that quoting can be forced on or off for the function
1495 name.
1498 """
1500 coerce_arguments = True
1501 inherit_cache = True
1503 _register: bool
1505 name = "GenericFunction"
1507 def __init_subclass__(cls) -> None:
1508 if annotation.Annotated not in cls.__mro__:
1509 cls._register_generic_function(cls.__name__, cls.__dict__)
1510 super().__init_subclass__()
1512 @classmethod
1513 def _register_generic_function(
1514 cls, clsname: str, clsdict: Mapping[str, Any]
1515 ) -> None:
1516 cls.name = name = clsdict.get("name", clsname)
1517 cls.identifier = identifier = clsdict.get("identifier", name)
1518 package = clsdict.get("package", "_default")
1519 # legacy
1520 if "__return_type__" in clsdict:
1521 cls.type = clsdict["__return_type__"]
1523 # Check _register attribute status
1524 cls._register = getattr(cls, "_register", True)
1526 # Register the function if required
1527 if cls._register:
1528 register_function(identifier, cls, package)
1529 else:
1530 # Set _register to True to register child classes by default
1531 cls._register = True
1533 def __init__(
1534 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1535 ) -> None:
1536 parsed_args = kwargs.pop("_parsed_args", None)
1537 if parsed_args is None:
1538 parsed_args = [
1539 coercions.expect(
1540 roles.ExpressionElementRole,
1541 c,
1542 name=self.name,
1543 apply_propagate_attrs=self,
1544 )
1545 for c in args
1546 ]
1547 self._has_args = self._has_args or bool(parsed_args)
1548 self.packagenames = ()
1550 self.clause_expr = Grouping(
1551 ClauseList(
1552 operator=operators.comma_op, group_contents=True, *parsed_args
1553 )
1554 )
1556 self.type = type_api.to_instance( # type: ignore
1557 kwargs.pop("type_", None) or getattr(self, "type", None)
1558 )
1561register_function("cast", Cast) # type: ignore
1562register_function("extract", Extract) # type: ignore
1565class next_value(GenericFunction[int]):
1566 """Represent the 'next value', given a :class:`.Sequence`
1567 as its single argument.
1569 Compiles into the appropriate function on each backend,
1570 or will raise NotImplementedError if used on a backend
1571 that does not provide support for sequences.
1573 """
1575 type = sqltypes.Integer()
1576 name = "next_value"
1578 _traverse_internals = [
1579 ("sequence", InternalTraversal.dp_named_ddl_element)
1580 ]
1582 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1583 assert isinstance(
1584 seq, schema.Sequence
1585 ), "next_value() accepts a Sequence object as input."
1586 self.sequence = seq
1587 self.type = sqltypes.to_instance( # type: ignore
1588 seq.data_type or getattr(self, "type", None)
1589 )
1591 def compare(self, other: Any, **kw: Any) -> bool:
1592 return (
1593 isinstance(other, next_value)
1594 and self.sequence.name == other.sequence.name
1595 )
1597 @property
1598 def _from_objects(self) -> Any:
1599 return []
1602class AnsiFunction(GenericFunction[_T]):
1603 """Define a function in "ansi" format, which doesn't render parenthesis."""
1605 inherit_cache = True
1607 def __init__(
1608 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1609 ) -> None:
1610 GenericFunction.__init__(self, *args, **kwargs)
1613class ReturnTypeFromArgs(GenericFunction[_T]):
1614 """Define a function whose return type is bound to the type of its
1615 arguments.
1616 """
1618 inherit_cache = True
1620 # set ColumnElement[_T] as a separate overload, to appease
1621 # mypy which seems to not want to accept _T from
1622 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1623 # _HasClauseElement as of mypy 1.15
1625 @overload
1626 def __init__(
1627 self,
1628 col: ColumnElement[_T],
1629 *args: _ColumnExpressionOrLiteralArgument[Any],
1630 **kwargs: Any,
1631 ) -> None: ...
1633 @overload
1634 def __init__(
1635 self,
1636 col: _ColumnExpressionArgument[_T],
1637 *args: _ColumnExpressionOrLiteralArgument[Any],
1638 **kwargs: Any,
1639 ) -> None: ...
1641 @overload
1642 def __init__(
1643 self,
1644 col: _T,
1645 *args: _ColumnExpressionOrLiteralArgument[Any],
1646 **kwargs: Any,
1647 ) -> None: ...
1649 def __init__(
1650 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1651 ) -> None:
1652 fn_args: Sequence[ColumnElement[Any]] = [
1653 coercions.expect(
1654 roles.ExpressionElementRole,
1655 c,
1656 name=self.name,
1657 apply_propagate_attrs=self,
1658 )
1659 for c in args
1660 ]
1661 kwargs.setdefault("type_", _type_from_args(fn_args))
1662 kwargs["_parsed_args"] = fn_args
1663 super().__init__(*fn_args, **kwargs)
1666class coalesce(ReturnTypeFromArgs[_T]):
1667 _has_args = True
1668 inherit_cache = True
1671class max(ReturnTypeFromArgs[_T]): # noqa: A001
1672 """The SQL MAX() aggregate function."""
1674 inherit_cache = True
1677class min(ReturnTypeFromArgs[_T]): # noqa: A001
1678 """The SQL MIN() aggregate function."""
1680 inherit_cache = True
1683class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1684 """The SQL SUM() aggregate function."""
1686 inherit_cache = True
1689class now(GenericFunction[datetime.datetime]):
1690 """The SQL now() datetime function.
1692 SQLAlchemy dialects will usually render this particular function
1693 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1695 """
1697 type = sqltypes.DateTime()
1698 inherit_cache = True
1701class concat(GenericFunction[str]):
1702 """The SQL CONCAT() function, which concatenates strings.
1704 E.g.:
1706 .. sourcecode:: pycon+sql
1708 >>> print(select(func.concat("a", "b")))
1709 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1711 String concatenation in SQLAlchemy is more commonly available using the
1712 Python ``+`` operator with string datatypes, which will render a
1713 backend-specific concatenation operator, such as :
1715 .. sourcecode:: pycon+sql
1717 >>> print(select(literal("a") + "b"))
1718 {printsql}SELECT :param_1 || :param_2 AS anon_1
1721 """
1723 type = sqltypes.String()
1724 inherit_cache = True
1727class char_length(GenericFunction[int]):
1728 """The CHAR_LENGTH() SQL function."""
1730 type = sqltypes.Integer()
1731 inherit_cache = True
1733 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1734 # slight hack to limit to just one positional argument
1735 # not sure why this one function has this special treatment
1736 super().__init__(arg, **kw)
1739class random(GenericFunction[float]):
1740 """The RANDOM() SQL function."""
1742 _has_args = True
1743 inherit_cache = True
1746class count(GenericFunction[int]):
1747 r"""The ANSI COUNT aggregate function. With no arguments,
1748 emits COUNT \*.
1750 E.g.::
1752 from sqlalchemy import func
1753 from sqlalchemy import select
1754 from sqlalchemy import table, column
1756 my_table = table("some_table", column("id"))
1758 stmt = select(func.count()).select_from(my_table)
1760 Executing ``stmt`` would emit:
1762 .. sourcecode:: sql
1764 SELECT count(*) AS count_1
1765 FROM some_table
1768 """
1770 type = sqltypes.Integer()
1771 inherit_cache = True
1773 def __init__(
1774 self,
1775 expression: Union[
1776 _ColumnExpressionArgument[Any], _StarOrOne, None
1777 ] = None,
1778 **kwargs: Any,
1779 ) -> None:
1780 if expression is None:
1781 expression = literal_column("*")
1782 super().__init__(expression, **kwargs)
1785class current_date(AnsiFunction[datetime.date]):
1786 """The CURRENT_DATE() SQL function."""
1788 type = sqltypes.Date()
1789 inherit_cache = True
1792class current_time(AnsiFunction[datetime.time]):
1793 """The CURRENT_TIME() SQL function."""
1795 type = sqltypes.Time()
1796 inherit_cache = True
1799class current_timestamp(AnsiFunction[datetime.datetime]):
1800 """The CURRENT_TIMESTAMP() SQL function."""
1802 type = sqltypes.DateTime()
1803 inherit_cache = True
1806class current_user(AnsiFunction[str]):
1807 """The CURRENT_USER() SQL function."""
1809 type = sqltypes.String()
1810 inherit_cache = True
1813class localtime(AnsiFunction[datetime.datetime]):
1814 """The localtime() SQL function."""
1816 type = sqltypes.DateTime()
1817 inherit_cache = True
1820class localtimestamp(AnsiFunction[datetime.datetime]):
1821 """The localtimestamp() SQL function."""
1823 type = sqltypes.DateTime()
1824 inherit_cache = True
1827class session_user(AnsiFunction[str]):
1828 """The SESSION_USER() SQL function."""
1830 type = sqltypes.String()
1831 inherit_cache = True
1834class sysdate(AnsiFunction[datetime.datetime]):
1835 """The SYSDATE() SQL function."""
1837 type = sqltypes.DateTime()
1838 inherit_cache = True
1841class user(AnsiFunction[str]):
1842 """The USER() SQL function."""
1844 type = sqltypes.String()
1845 inherit_cache = True
1848class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1849 """Support for the ARRAY_AGG function.
1851 The ``func.array_agg(expr)`` construct returns an expression of
1852 type :class:`_types.ARRAY`.
1854 e.g.::
1856 stmt = select(func.array_agg(table.c.values)[2:5])
1858 .. seealso::
1860 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1861 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1862 added.
1864 """
1866 inherit_cache = True
1868 def __init__(
1869 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1870 ) -> None:
1871 fn_args: Sequence[ColumnElement[Any]] = [
1872 coercions.expect(
1873 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1874 )
1875 for c in args
1876 ]
1878 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1879 if "type_" not in kwargs:
1880 type_from_args = _type_from_args(fn_args)
1881 if isinstance(type_from_args, sqltypes.ARRAY):
1882 kwargs["type_"] = type_from_args
1883 else:
1884 kwargs["type_"] = default_array_type(
1885 type_from_args, dimensions=1
1886 )
1887 kwargs["_parsed_args"] = fn_args
1888 super().__init__(*fn_args, **kwargs)
1891class OrderedSetAgg(GenericFunction[_T]):
1892 """Define a function where the return type is based on the sort
1893 expression type as defined by the expression passed to the
1894 :meth:`.FunctionElement.within_group` method."""
1896 array_for_multi_clause = False
1897 inherit_cache = True
1899 def within_group_type(
1900 self, within_group: WithinGroup[Any]
1901 ) -> TypeEngine[Any]:
1902 func_clauses = cast(ClauseList, self.clause_expr.element)
1903 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
1904 within_group.order_by
1905 )
1906 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1907 return sqltypes.ARRAY(order_by[0].type)
1908 else:
1909 return order_by[0].type
1912class mode(OrderedSetAgg[_T]):
1913 """Implement the ``mode`` ordered-set aggregate function.
1915 This function must be used with the :meth:`.FunctionElement.within_group`
1916 modifier to supply a sort expression to operate upon.
1918 The return type of this function is the same as the sort expression.
1920 """
1922 inherit_cache = True
1925class percentile_cont(OrderedSetAgg[_T]):
1926 """Implement the ``percentile_cont`` ordered-set aggregate function.
1928 This function must be used with the :meth:`.FunctionElement.within_group`
1929 modifier to supply a sort expression to operate upon.
1931 The return type of this function is the same as the sort expression,
1932 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1933 expression's type.
1935 """
1937 array_for_multi_clause = True
1938 inherit_cache = True
1941class percentile_disc(OrderedSetAgg[_T]):
1942 """Implement the ``percentile_disc`` ordered-set aggregate function.
1944 This function must be used with the :meth:`.FunctionElement.within_group`
1945 modifier to supply a sort expression to operate upon.
1947 The return type of this function is the same as the sort expression,
1948 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1949 expression's type.
1951 """
1953 array_for_multi_clause = True
1954 inherit_cache = True
1957class rank(GenericFunction[int]):
1958 """Implement the ``rank`` hypothetical-set aggregate function.
1960 This function must be used with the :meth:`.FunctionElement.within_group`
1961 modifier to supply a sort expression to operate upon.
1963 The return type of this function is :class:`.Integer`.
1965 """
1967 type = sqltypes.Integer()
1968 inherit_cache = True
1971class dense_rank(GenericFunction[int]):
1972 """Implement the ``dense_rank`` hypothetical-set aggregate function.
1974 This function must be used with the :meth:`.FunctionElement.within_group`
1975 modifier to supply a sort expression to operate upon.
1977 The return type of this function is :class:`.Integer`.
1979 """
1981 type = sqltypes.Integer()
1982 inherit_cache = True
1985class percent_rank(GenericFunction[decimal.Decimal]):
1986 """Implement the ``percent_rank`` hypothetical-set aggregate function.
1988 This function must be used with the :meth:`.FunctionElement.within_group`
1989 modifier to supply a sort expression to operate upon.
1991 The return type of this function is :class:`.Numeric`.
1993 """
1995 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
1996 inherit_cache = True
1999class cume_dist(GenericFunction[decimal.Decimal]):
2000 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2002 This function must be used with the :meth:`.FunctionElement.within_group`
2003 modifier to supply a sort expression to operate upon.
2005 The return type of this function is :class:`.Numeric`.
2007 """
2009 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2010 inherit_cache = True
2013class cube(GenericFunction[_T]):
2014 r"""Implement the ``CUBE`` grouping operation.
2016 This function is used as part of the GROUP BY of a statement,
2017 e.g. :meth:`_expression.Select.group_by`::
2019 stmt = select(
2020 func.sum(table.c.value), table.c.col_1, table.c.col_2
2021 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2023 .. versionadded:: 1.2
2025 """
2027 _has_args = True
2028 inherit_cache = True
2031class rollup(GenericFunction[_T]):
2032 r"""Implement the ``ROLLUP`` grouping operation.
2034 This function is used as part of the GROUP BY of a statement,
2035 e.g. :meth:`_expression.Select.group_by`::
2037 stmt = select(
2038 func.sum(table.c.value), table.c.col_1, table.c.col_2
2039 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2041 .. versionadded:: 1.2
2043 """
2045 _has_args = True
2046 inherit_cache = True
2049class grouping_sets(GenericFunction[_T]):
2050 r"""Implement the ``GROUPING SETS`` grouping operation.
2052 This function is used as part of the GROUP BY of a statement,
2053 e.g. :meth:`_expression.Select.group_by`::
2055 stmt = select(
2056 func.sum(table.c.value), table.c.col_1, table.c.col_2
2057 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2059 In order to group by multiple sets, use the :func:`.tuple_` construct::
2061 from sqlalchemy import tuple_
2063 stmt = select(
2064 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2065 ).group_by(
2066 func.grouping_sets(
2067 tuple_(table.c.col_1, table.c.col_2),
2068 tuple_(table.c.value, table.c.col_3),
2069 )
2070 )
2072 .. versionadded:: 1.2
2074 """ # noqa: E501
2076 _has_args = True
2077 inherit_cache = True
2080class aggregate_strings(GenericFunction[str]):
2081 """Implement a generic string aggregation function.
2083 This function will concatenate non-null values into a string and
2084 separate the values by a delimiter.
2086 This function is compiled on a per-backend basis, into functions
2087 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2089 e.g. Example usage with delimiter '.'::
2091 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2093 The return type of this function is :class:`.String`.
2095 .. versionadded: 2.0.21
2097 """
2099 type = sqltypes.String()
2100 _has_args = True
2101 inherit_cache = True
2103 def __init__(
2104 self, clause: _ColumnExpressionArgument[Any], separator: str
2105 ) -> None:
2106 super().__init__(clause, separator)