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 ] + Executable._executable_traverse_internals
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[Optional[_T]],
1051 *args: _ColumnExpressionOrLiteralArgument[Any],
1052 **kwargs: Any,
1053 ) -> coalesce[_T]: ...
1055 @overload
1056 def coalesce(
1057 self,
1058 col: Optional[_T],
1059 *args: _ColumnExpressionOrLiteralArgument[Any],
1060 **kwargs: Any,
1061 ) -> coalesce[_T]: ...
1063 def coalesce(
1064 self,
1065 col: _ColumnExpressionOrLiteralArgument[Optional[_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 ReturnTypeFromOptionalArgs(ReturnTypeFromArgs[_T]):
1665 inherit_cache = True
1667 @overload
1668 def __init__(
1669 self,
1670 col: ColumnElement[_T],
1671 *args: _ColumnExpressionOrLiteralArgument[Any],
1672 **kwargs: Any,
1673 ) -> None: ...
1675 @overload
1676 def __init__(
1677 self,
1678 col: _ColumnExpressionArgument[Optional[_T]],
1679 *args: _ColumnExpressionOrLiteralArgument[Any],
1680 **kwargs: Any,
1681 ) -> None: ...
1683 @overload
1684 def __init__(
1685 self,
1686 col: Optional[_T],
1687 *args: _ColumnExpressionOrLiteralArgument[Any],
1688 **kwargs: Any,
1689 ) -> None: ...
1691 def __init__(
1692 self,
1693 *args: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1694 **kwargs: Any,
1695 ) -> None:
1696 super().__init__(*args, **kwargs) # type: ignore
1699class coalesce(ReturnTypeFromOptionalArgs[_T]):
1700 _has_args = True
1701 inherit_cache = True
1704class max(ReturnTypeFromArgs[_T]): # noqa: A001
1705 """The SQL MAX() aggregate function."""
1707 inherit_cache = True
1710class min(ReturnTypeFromArgs[_T]): # noqa: A001
1711 """The SQL MIN() aggregate function."""
1713 inherit_cache = True
1716class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1717 """The SQL SUM() aggregate function."""
1719 inherit_cache = True
1722class now(GenericFunction[datetime.datetime]):
1723 """The SQL now() datetime function.
1725 SQLAlchemy dialects will usually render this particular function
1726 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1728 """
1730 type = sqltypes.DateTime()
1731 inherit_cache = True
1734class concat(GenericFunction[str]):
1735 """The SQL CONCAT() function, which concatenates strings.
1737 E.g.:
1739 .. sourcecode:: pycon+sql
1741 >>> print(select(func.concat("a", "b")))
1742 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1744 String concatenation in SQLAlchemy is more commonly available using the
1745 Python ``+`` operator with string datatypes, which will render a
1746 backend-specific concatenation operator, such as :
1748 .. sourcecode:: pycon+sql
1750 >>> print(select(literal("a") + "b"))
1751 {printsql}SELECT :param_1 || :param_2 AS anon_1
1754 """
1756 type = sqltypes.String()
1757 inherit_cache = True
1760class char_length(GenericFunction[int]):
1761 """The CHAR_LENGTH() SQL function."""
1763 type = sqltypes.Integer()
1764 inherit_cache = True
1766 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1767 # slight hack to limit to just one positional argument
1768 # not sure why this one function has this special treatment
1769 super().__init__(arg, **kw)
1772class random(GenericFunction[float]):
1773 """The RANDOM() SQL function."""
1775 _has_args = True
1776 inherit_cache = True
1779class count(GenericFunction[int]):
1780 r"""The ANSI COUNT aggregate function. With no arguments,
1781 emits COUNT \*.
1783 E.g.::
1785 from sqlalchemy import func
1786 from sqlalchemy import select
1787 from sqlalchemy import table, column
1789 my_table = table("some_table", column("id"))
1791 stmt = select(func.count()).select_from(my_table)
1793 Executing ``stmt`` would emit:
1795 .. sourcecode:: sql
1797 SELECT count(*) AS count_1
1798 FROM some_table
1801 """
1803 type = sqltypes.Integer()
1804 inherit_cache = True
1806 def __init__(
1807 self,
1808 expression: Union[
1809 _ColumnExpressionArgument[Any], _StarOrOne, None
1810 ] = None,
1811 **kwargs: Any,
1812 ) -> None:
1813 if expression is None:
1814 expression = literal_column("*")
1815 super().__init__(expression, **kwargs)
1818class current_date(AnsiFunction[datetime.date]):
1819 """The CURRENT_DATE() SQL function."""
1821 type = sqltypes.Date()
1822 inherit_cache = True
1825class current_time(AnsiFunction[datetime.time]):
1826 """The CURRENT_TIME() SQL function."""
1828 type = sqltypes.Time()
1829 inherit_cache = True
1832class current_timestamp(AnsiFunction[datetime.datetime]):
1833 """The CURRENT_TIMESTAMP() SQL function."""
1835 type = sqltypes.DateTime()
1836 inherit_cache = True
1839class current_user(AnsiFunction[str]):
1840 """The CURRENT_USER() SQL function."""
1842 type = sqltypes.String()
1843 inherit_cache = True
1846class localtime(AnsiFunction[datetime.datetime]):
1847 """The localtime() SQL function."""
1849 type = sqltypes.DateTime()
1850 inherit_cache = True
1853class localtimestamp(AnsiFunction[datetime.datetime]):
1854 """The localtimestamp() SQL function."""
1856 type = sqltypes.DateTime()
1857 inherit_cache = True
1860class session_user(AnsiFunction[str]):
1861 """The SESSION_USER() SQL function."""
1863 type = sqltypes.String()
1864 inherit_cache = True
1867class sysdate(AnsiFunction[datetime.datetime]):
1868 """The SYSDATE() SQL function."""
1870 type = sqltypes.DateTime()
1871 inherit_cache = True
1874class user(AnsiFunction[str]):
1875 """The USER() SQL function."""
1877 type = sqltypes.String()
1878 inherit_cache = True
1881class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1882 """Support for the ARRAY_AGG function.
1884 The ``func.array_agg(expr)`` construct returns an expression of
1885 type :class:`_types.ARRAY`.
1887 e.g.::
1889 stmt = select(func.array_agg(table.c.values)[2:5])
1891 .. seealso::
1893 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1894 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1895 added.
1897 """
1899 inherit_cache = True
1901 def __init__(
1902 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1903 ) -> None:
1904 fn_args: Sequence[ColumnElement[Any]] = [
1905 coercions.expect(
1906 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1907 )
1908 for c in args
1909 ]
1911 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1912 if "type_" not in kwargs:
1913 type_from_args = _type_from_args(fn_args)
1914 if isinstance(type_from_args, sqltypes.ARRAY):
1915 kwargs["type_"] = type_from_args
1916 else:
1917 kwargs["type_"] = default_array_type(
1918 type_from_args, dimensions=1
1919 )
1920 kwargs["_parsed_args"] = fn_args
1921 super().__init__(*fn_args, **kwargs)
1924class OrderedSetAgg(GenericFunction[_T]):
1925 """Define a function where the return type is based on the sort
1926 expression type as defined by the expression passed to the
1927 :meth:`.FunctionElement.within_group` method."""
1929 array_for_multi_clause = False
1930 inherit_cache = True
1932 def within_group_type(
1933 self, within_group: WithinGroup[Any]
1934 ) -> TypeEngine[Any]:
1935 func_clauses = cast(ClauseList, self.clause_expr.element)
1936 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
1937 within_group.order_by
1938 )
1939 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1940 return sqltypes.ARRAY(order_by[0].type)
1941 else:
1942 return order_by[0].type
1945class mode(OrderedSetAgg[_T]):
1946 """Implement the ``mode`` ordered-set aggregate function.
1948 This function must be used with the :meth:`.FunctionElement.within_group`
1949 modifier to supply a sort expression to operate upon.
1951 The return type of this function is the same as the sort expression.
1953 """
1955 inherit_cache = True
1958class percentile_cont(OrderedSetAgg[_T]):
1959 """Implement the ``percentile_cont`` ordered-set aggregate function.
1961 This function must be used with the :meth:`.FunctionElement.within_group`
1962 modifier to supply a sort expression to operate upon.
1964 The return type of this function is the same as the sort expression,
1965 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1966 expression's type.
1968 """
1970 array_for_multi_clause = True
1971 inherit_cache = True
1974class percentile_disc(OrderedSetAgg[_T]):
1975 """Implement the ``percentile_disc`` ordered-set aggregate function.
1977 This function must be used with the :meth:`.FunctionElement.within_group`
1978 modifier to supply a sort expression to operate upon.
1980 The return type of this function is the same as the sort expression,
1981 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1982 expression's type.
1984 """
1986 array_for_multi_clause = True
1987 inherit_cache = True
1990class rank(GenericFunction[int]):
1991 """Implement the ``rank`` hypothetical-set aggregate function.
1993 This function must be used with the :meth:`.FunctionElement.within_group`
1994 modifier to supply a sort expression to operate upon.
1996 The return type of this function is :class:`.Integer`.
1998 """
2000 type = sqltypes.Integer()
2001 inherit_cache = True
2004class dense_rank(GenericFunction[int]):
2005 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2007 This function must be used with the :meth:`.FunctionElement.within_group`
2008 modifier to supply a sort expression to operate upon.
2010 The return type of this function is :class:`.Integer`.
2012 """
2014 type = sqltypes.Integer()
2015 inherit_cache = True
2018class percent_rank(GenericFunction[decimal.Decimal]):
2019 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2021 This function must be used with the :meth:`.FunctionElement.within_group`
2022 modifier to supply a sort expression to operate upon.
2024 The return type of this function is :class:`.Numeric`.
2026 """
2028 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2029 inherit_cache = True
2032class cume_dist(GenericFunction[decimal.Decimal]):
2033 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2035 This function must be used with the :meth:`.FunctionElement.within_group`
2036 modifier to supply a sort expression to operate upon.
2038 The return type of this function is :class:`.Numeric`.
2040 """
2042 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2043 inherit_cache = True
2046class cube(GenericFunction[_T]):
2047 r"""Implement the ``CUBE`` grouping operation.
2049 This function is used as part of the GROUP BY of a statement,
2050 e.g. :meth:`_expression.Select.group_by`::
2052 stmt = select(
2053 func.sum(table.c.value), table.c.col_1, table.c.col_2
2054 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2056 .. versionadded:: 1.2
2058 """
2060 _has_args = True
2061 inherit_cache = True
2064class rollup(GenericFunction[_T]):
2065 r"""Implement the ``ROLLUP`` grouping operation.
2067 This function is used as part of the GROUP BY of a statement,
2068 e.g. :meth:`_expression.Select.group_by`::
2070 stmt = select(
2071 func.sum(table.c.value), table.c.col_1, table.c.col_2
2072 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2074 .. versionadded:: 1.2
2076 """
2078 _has_args = True
2079 inherit_cache = True
2082class grouping_sets(GenericFunction[_T]):
2083 r"""Implement the ``GROUPING SETS`` grouping operation.
2085 This function is used as part of the GROUP BY of a statement,
2086 e.g. :meth:`_expression.Select.group_by`::
2088 stmt = select(
2089 func.sum(table.c.value), table.c.col_1, table.c.col_2
2090 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2092 In order to group by multiple sets, use the :func:`.tuple_` construct::
2094 from sqlalchemy import tuple_
2096 stmt = select(
2097 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2098 ).group_by(
2099 func.grouping_sets(
2100 tuple_(table.c.col_1, table.c.col_2),
2101 tuple_(table.c.value, table.c.col_3),
2102 )
2103 )
2105 .. versionadded:: 1.2
2107 """ # noqa: E501
2109 _has_args = True
2110 inherit_cache = True
2113class aggregate_strings(GenericFunction[str]):
2114 """Implement a generic string aggregation function.
2116 This function will concatenate non-null values into a string and
2117 separate the values by a delimiter.
2119 This function is compiled on a per-backend basis, into functions
2120 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2122 e.g. Example usage with delimiter '.'::
2124 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2126 The return type of this function is :class:`.String`.
2128 .. versionadded: 2.0.21
2130 """
2132 type = sqltypes.String()
2133 _has_args = True
2134 inherit_cache = True
2136 def __init__(
2137 self, clause: _ColumnExpressionArgument[Any], separator: str
2138 ) -> None:
2139 super().__init__(clause, separator)