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 .. seealso::
604 :ref:`relationship_custom_operator_sql_function` -
605 example use within the ORM
607 """
608 return FunctionAsBinary(self, left_index, right_index)
610 @property
611 def _from_objects(self) -> Any:
612 return self.clauses._from_objects
614 def within_group_type(
615 self, within_group: WithinGroup[_S]
616 ) -> Optional[TypeEngine[_S]]:
617 """For types that define their return type as based on the criteria
618 within a WITHIN GROUP (ORDER BY) expression, called by the
619 :class:`.WithinGroup` construct.
621 Returns None by default, in which case the function's normal ``.type``
622 is used.
624 """
626 return None
628 def alias(
629 self, name: Optional[str] = None, joins_implicitly: bool = False
630 ) -> TableValuedAlias:
631 r"""Produce a :class:`_expression.Alias` construct against this
632 :class:`.FunctionElement`.
634 .. tip::
636 The :meth:`_functions.FunctionElement.alias` method is part of the
637 mechanism by which "table valued" SQL functions are created.
638 However, most use cases are covered by higher level methods on
639 :class:`_functions.FunctionElement` including
640 :meth:`_functions.FunctionElement.table_valued`, and
641 :meth:`_functions.FunctionElement.column_valued`.
643 This construct wraps the function in a named alias which
644 is suitable for the FROM clause, in the style accepted for example
645 by PostgreSQL. A column expression is also provided using the
646 special ``.column`` attribute, which may
647 be used to refer to the output of the function as a scalar value
648 in the columns or where clause, for a backend such as PostgreSQL.
650 For a full table-valued expression, use the
651 :meth:`_functions.FunctionElement.table_valued` method first to
652 establish named columns.
654 e.g.:
656 .. sourcecode:: pycon+sql
658 >>> from sqlalchemy import func, select, column
659 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
660 >>> print(select(data_view.column))
661 {printsql}SELECT data_view
662 FROM unnest(:unnest_1) AS data_view
664 The :meth:`_functions.FunctionElement.column_valued` method provides
665 a shortcut for the above pattern:
667 .. sourcecode:: pycon+sql
669 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
670 >>> print(select(data_view))
671 {printsql}SELECT data_view
672 FROM unnest(:unnest_1) AS data_view
674 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
676 :param name: alias name, will be rendered as ``AS <name>`` in the
677 FROM clause
679 :param joins_implicitly: when True, the table valued function may be
680 used in the FROM clause without any explicit JOIN to other tables
681 in the SQL query, and no "cartesian product" warning will be
682 generated. May be useful for SQL functions such as
683 ``func.json_each()``.
685 .. versionadded:: 1.4.33
687 .. seealso::
689 :ref:`tutorial_functions_table_valued` -
690 in the :ref:`unified_tutorial`
692 :meth:`_functions.FunctionElement.table_valued`
694 :meth:`_functions.FunctionElement.scalar_table_valued`
696 :meth:`_functions.FunctionElement.column_valued`
699 """
701 return TableValuedAlias._construct(
702 self,
703 name=name,
704 table_value_type=self.type,
705 joins_implicitly=joins_implicitly,
706 )
708 def select(self) -> Select[_T]:
709 """Produce a :func:`_expression.select` construct
710 against this :class:`.FunctionElement`.
712 This is shorthand for::
714 s = select(function_element)
716 """
717 s: Select[_T] = Select(self)
718 if self._execution_options:
719 s = s.execution_options(**self._execution_options)
720 return s
722 def _bind_param(
723 self,
724 operator: OperatorType,
725 obj: Any,
726 type_: Optional[TypeEngine[_T]] = None,
727 expanding: bool = False,
728 **kw: Any,
729 ) -> BindParameter[_T]:
730 return BindParameter(
731 None,
732 obj,
733 _compared_to_operator=operator,
734 _compared_to_type=self.type,
735 unique=True,
736 type_=type_,
737 expanding=expanding,
738 **kw,
739 )
741 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
742 # for the moment, we are parenthesizing all array-returning
743 # expressions against getitem. This may need to be made
744 # more portable if in the future we support other DBs
745 # besides postgresql.
746 if against is operators.getitem and isinstance(
747 self.type, sqltypes.ARRAY
748 ):
749 return Grouping(self)
750 else:
751 return super().self_group(against=against)
753 @property
754 def entity_namespace(self) -> _EntityNamespace:
755 """overrides FromClause.entity_namespace as functions are generally
756 column expressions and not FromClauses.
758 """
759 # ideally functions would not be fromclauses but we failed to make
760 # this adjustment in 1.4
761 return _entity_namespace(self.clause_expr)
764class FunctionAsBinary(BinaryExpression[Any]):
765 _traverse_internals = [
766 ("sql_function", InternalTraversal.dp_clauseelement),
767 ("left_index", InternalTraversal.dp_plain_obj),
768 ("right_index", InternalTraversal.dp_plain_obj),
769 ("modifiers", InternalTraversal.dp_plain_dict),
770 ]
772 sql_function: FunctionElement[Any]
773 left_index: int
774 right_index: int
776 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
777 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
779 def __init__(
780 self, fn: FunctionElement[Any], left_index: int, right_index: int
781 ) -> None:
782 self.sql_function = fn
783 self.left_index = left_index
784 self.right_index = right_index
786 self.operator = operators.function_as_comparison_op
787 self.type = sqltypes.BOOLEANTYPE
788 self.negate = None
789 self._is_implicitly_boolean = True
790 self.modifiers = util.immutabledict({})
792 @property
793 def left_expr(self) -> ColumnElement[Any]:
794 return self.sql_function.clauses.clauses[self.left_index - 1]
796 @left_expr.setter
797 def left_expr(self, value: ColumnElement[Any]) -> None:
798 self.sql_function.clauses.clauses[self.left_index - 1] = value
800 @property
801 def right_expr(self) -> ColumnElement[Any]:
802 return self.sql_function.clauses.clauses[self.right_index - 1]
804 @right_expr.setter
805 def right_expr(self, value: ColumnElement[Any]) -> None:
806 self.sql_function.clauses.clauses[self.right_index - 1] = value
808 if not TYPE_CHECKING:
809 # mypy can't accommodate @property to replace an instance
810 # variable
812 left = left_expr
813 right = right_expr
816class ScalarFunctionColumn(NamedColumn[_T]):
817 __visit_name__ = "scalar_function_column"
819 _traverse_internals = [
820 ("name", InternalTraversal.dp_anon_name),
821 ("type", InternalTraversal.dp_type),
822 ("fn", InternalTraversal.dp_clauseelement),
823 ]
825 is_literal = False
826 table = None
828 def __init__(
829 self,
830 fn: FunctionElement[_T],
831 name: str,
832 type_: Optional[_TypeEngineArgument[_T]] = None,
833 ) -> None:
834 self.fn = fn
835 self.name = name
837 # if type is None, we get NULLTYPE, which is our _T. But I don't
838 # know how to get the overloads to express that correctly
839 self.type = type_api.to_instance(type_) # type: ignore
842class _FunctionGenerator:
843 """Generate SQL function expressions.
845 :data:`.func` is a special object instance which generates SQL
846 functions based on name-based attributes, e.g.:
848 .. sourcecode:: pycon+sql
850 >>> print(func.count(1))
851 {printsql}count(:param_1)
853 The returned object is an instance of :class:`.Function`, and is a
854 column-oriented SQL element like any other, and is used in that way:
856 .. sourcecode:: pycon+sql
858 >>> print(select(func.count(table.c.id)))
859 {printsql}SELECT count(sometable.id) FROM sometable
861 Any name can be given to :data:`.func`. If the function name is unknown to
862 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
863 which SQLAlchemy is aware of, the name may be interpreted as a *generic
864 function* which will be compiled appropriately to the target database:
866 .. sourcecode:: pycon+sql
868 >>> print(func.current_timestamp())
869 {printsql}CURRENT_TIMESTAMP
871 To call functions which are present in dot-separated packages,
872 specify them in the same manner:
874 .. sourcecode:: pycon+sql
876 >>> print(func.stats.yield_curve(5, 10))
877 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
879 SQLAlchemy can be made aware of the return type of functions to enable
880 type-specific lexical and result-based behavior. For example, to ensure
881 that a string-based function returns a Unicode value and is similarly
882 treated as a string in expressions, specify
883 :class:`~sqlalchemy.types.Unicode` as the type:
885 .. sourcecode:: pycon+sql
887 >>> print(
888 ... func.my_string("hi", type_=Unicode)
889 ... + " "
890 ... + func.my_string("there", type_=Unicode)
891 ... )
892 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
894 The object returned by a :data:`.func` call is usually an instance of
895 :class:`.Function`.
896 This object meets the "column" interface, including comparison and labeling
897 functions. The object can also be passed the :meth:`~.Connectable.execute`
898 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
899 where it will be
900 wrapped inside of a SELECT statement first::
902 print(connection.execute(func.current_timestamp()).scalar())
904 In a few exception cases, the :data:`.func` accessor
905 will redirect a name to a built-in expression such as :func:`.cast`
906 or :func:`.extract`, as these names have well-known meaning
907 but are not exactly the same as "functions" from a SQLAlchemy
908 perspective.
910 Functions which are interpreted as "generic" functions know how to
911 calculate their return type automatically. For a listing of known generic
912 functions, see :ref:`generic_functions`.
914 .. note::
916 The :data:`.func` construct has only limited support for calling
917 standalone "stored procedures", especially those with special
918 parameterization concerns.
920 See the section :ref:`stored_procedures` for details on how to use
921 the DBAPI-level ``callproc()`` method for fully traditional stored
922 procedures.
924 .. seealso::
926 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
928 :class:`.Function`
930 """ # noqa
932 def __init__(self, **opts: Any) -> None:
933 self.__names: List[str] = []
934 self.opts = opts
936 def __getattr__(self, name: str) -> _FunctionGenerator:
937 # passthru __ attributes; fixes pydoc
938 if name.startswith("__"):
939 try:
940 return self.__dict__[name] # type: ignore
941 except KeyError:
942 raise AttributeError(name)
944 elif name.endswith("_"):
945 name = name[0:-1]
946 f = _FunctionGenerator(**self.opts)
947 f.__names = list(self.__names) + [name]
948 return f
950 @overload
951 def __call__(
952 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
953 ) -> Function[_T]: ...
955 @overload
956 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
958 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
959 o = self.opts.copy()
960 o.update(kwargs)
962 tokens = len(self.__names)
964 if tokens == 2:
965 package, fname = self.__names
966 elif tokens == 1:
967 package, fname = "_default", self.__names[0]
968 else:
969 package = None
971 if package is not None:
972 func = _registry[package].get(fname.lower())
973 if func is not None:
974 return func(*c, **o)
976 return Function(
977 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
978 )
980 if TYPE_CHECKING:
981 # START GENERATED FUNCTION ACCESSORS
983 # code within this block is **programmatically,
984 # statically generated** by tools/generate_sql_functions.py
986 @property
987 def aggregate_strings(self) -> Type[aggregate_strings]: ...
989 @property
990 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
992 # set ColumnElement[_T] as a separate overload, to appease
993 # mypy which seems to not want to accept _T from
994 # _ColumnExpressionArgument. Seems somewhat related to the covariant
995 # _HasClauseElement as of mypy 1.15
997 @overload
998 def array_agg(
999 self,
1000 col: ColumnElement[_T],
1001 *args: _ColumnExpressionOrLiteralArgument[Any],
1002 **kwargs: Any,
1003 ) -> array_agg[_T]: ...
1005 @overload
1006 def array_agg(
1007 self,
1008 col: _ColumnExpressionArgument[_T],
1009 *args: _ColumnExpressionOrLiteralArgument[Any],
1010 **kwargs: Any,
1011 ) -> array_agg[_T]: ...
1013 @overload
1014 def array_agg(
1015 self,
1016 col: _T,
1017 *args: _ColumnExpressionOrLiteralArgument[Any],
1018 **kwargs: Any,
1019 ) -> array_agg[_T]: ...
1021 def array_agg(
1022 self,
1023 col: _ColumnExpressionOrLiteralArgument[_T],
1024 *args: _ColumnExpressionOrLiteralArgument[Any],
1025 **kwargs: Any,
1026 ) -> array_agg[_T]: ...
1028 @property
1029 def cast(self) -> Type[Cast[Any]]: ...
1031 @property
1032 def char_length(self) -> Type[char_length]: ...
1034 # set ColumnElement[_T] as a separate overload, to appease
1035 # mypy which seems to not want to accept _T from
1036 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1037 # _HasClauseElement as of mypy 1.15
1039 @overload
1040 def coalesce(
1041 self,
1042 col: ColumnElement[_T],
1043 *args: _ColumnExpressionOrLiteralArgument[Any],
1044 **kwargs: Any,
1045 ) -> coalesce[_T]: ...
1047 @overload
1048 def coalesce(
1049 self,
1050 col: _ColumnExpressionArgument[_T],
1051 *args: _ColumnExpressionOrLiteralArgument[Any],
1052 **kwargs: Any,
1053 ) -> coalesce[_T]: ...
1055 @overload
1056 def coalesce(
1057 self,
1058 col: _T,
1059 *args: _ColumnExpressionOrLiteralArgument[Any],
1060 **kwargs: Any,
1061 ) -> coalesce[_T]: ...
1063 def coalesce(
1064 self,
1065 col: _ColumnExpressionOrLiteralArgument[_T],
1066 *args: _ColumnExpressionOrLiteralArgument[Any],
1067 **kwargs: Any,
1068 ) -> coalesce[_T]: ...
1070 @property
1071 def concat(self) -> Type[concat]: ...
1073 @property
1074 def count(self) -> Type[count]: ...
1076 @property
1077 def cube(self) -> Type[cube[Any]]: ...
1079 @property
1080 def cume_dist(self) -> Type[cume_dist]: ...
1082 @property
1083 def current_date(self) -> Type[current_date]: ...
1085 @property
1086 def current_time(self) -> Type[current_time]: ...
1088 @property
1089 def current_timestamp(self) -> Type[current_timestamp]: ...
1091 @property
1092 def current_user(self) -> Type[current_user]: ...
1094 @property
1095 def dense_rank(self) -> Type[dense_rank]: ...
1097 @property
1098 def extract(self) -> Type[Extract]: ...
1100 @property
1101 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1103 @property
1104 def localtime(self) -> Type[localtime]: ...
1106 @property
1107 def localtimestamp(self) -> Type[localtimestamp]: ...
1109 # set ColumnElement[_T] as a separate overload, to appease
1110 # mypy which seems to not want to accept _T from
1111 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1112 # _HasClauseElement as of mypy 1.15
1114 @overload
1115 def max( # noqa: A001
1116 self,
1117 col: ColumnElement[_T],
1118 *args: _ColumnExpressionOrLiteralArgument[Any],
1119 **kwargs: Any,
1120 ) -> max[_T]: ...
1122 @overload
1123 def max( # noqa: A001
1124 self,
1125 col: _ColumnExpressionArgument[_T],
1126 *args: _ColumnExpressionOrLiteralArgument[Any],
1127 **kwargs: Any,
1128 ) -> max[_T]: ...
1130 @overload
1131 def max( # noqa: A001
1132 self,
1133 col: _T,
1134 *args: _ColumnExpressionOrLiteralArgument[Any],
1135 **kwargs: Any,
1136 ) -> max[_T]: ...
1138 def max( # noqa: A001
1139 self,
1140 col: _ColumnExpressionOrLiteralArgument[_T],
1141 *args: _ColumnExpressionOrLiteralArgument[Any],
1142 **kwargs: Any,
1143 ) -> max[_T]: ...
1145 # set ColumnElement[_T] as a separate overload, to appease
1146 # mypy which seems to not want to accept _T from
1147 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1148 # _HasClauseElement as of mypy 1.15
1150 @overload
1151 def min( # noqa: A001
1152 self,
1153 col: ColumnElement[_T],
1154 *args: _ColumnExpressionOrLiteralArgument[Any],
1155 **kwargs: Any,
1156 ) -> min[_T]: ...
1158 @overload
1159 def min( # noqa: A001
1160 self,
1161 col: _ColumnExpressionArgument[_T],
1162 *args: _ColumnExpressionOrLiteralArgument[Any],
1163 **kwargs: Any,
1164 ) -> min[_T]: ...
1166 @overload
1167 def min( # noqa: A001
1168 self,
1169 col: _T,
1170 *args: _ColumnExpressionOrLiteralArgument[Any],
1171 **kwargs: Any,
1172 ) -> min[_T]: ...
1174 def min( # noqa: A001
1175 self,
1176 col: _ColumnExpressionOrLiteralArgument[_T],
1177 *args: _ColumnExpressionOrLiteralArgument[Any],
1178 **kwargs: Any,
1179 ) -> min[_T]: ...
1181 @property
1182 def mode(self) -> Type[mode[Any]]: ...
1184 @property
1185 def next_value(self) -> Type[next_value]: ...
1187 @property
1188 def now(self) -> Type[now]: ...
1190 @property
1191 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1193 @property
1194 def percent_rank(self) -> Type[percent_rank]: ...
1196 @property
1197 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1199 @property
1200 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1202 # set ColumnElement[_T] as a separate overload, to appease
1203 # mypy which seems to not want to accept _T from
1204 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1205 # _HasClauseElement as of mypy 1.15
1207 @overload
1208 def pow( # noqa: A001
1209 self,
1210 col: ColumnElement[_T],
1211 *args: _ColumnExpressionOrLiteralArgument[Any],
1212 **kwargs: Any,
1213 ) -> pow[_T]: ...
1215 @overload
1216 def pow( # noqa: A001
1217 self,
1218 col: _ColumnExpressionArgument[_T],
1219 *args: _ColumnExpressionOrLiteralArgument[Any],
1220 **kwargs: Any,
1221 ) -> pow[_T]: ...
1223 @overload
1224 def pow( # noqa: A001
1225 self,
1226 col: _T,
1227 *args: _ColumnExpressionOrLiteralArgument[Any],
1228 **kwargs: Any,
1229 ) -> pow[_T]: ...
1231 def pow( # noqa: A001
1232 self,
1233 col: _ColumnExpressionOrLiteralArgument[_T],
1234 *args: _ColumnExpressionOrLiteralArgument[Any],
1235 **kwargs: Any,
1236 ) -> pow[_T]: ...
1238 @property
1239 def random(self) -> Type[random]: ...
1241 @property
1242 def rank(self) -> Type[rank]: ...
1244 @property
1245 def rollup(self) -> Type[rollup[Any]]: ...
1247 @property
1248 def session_user(self) -> Type[session_user]: ...
1250 # set ColumnElement[_T] as a separate overload, to appease
1251 # mypy which seems to not want to accept _T from
1252 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1253 # _HasClauseElement as of mypy 1.15
1255 @overload
1256 def sum( # noqa: A001
1257 self,
1258 col: ColumnElement[_T],
1259 *args: _ColumnExpressionOrLiteralArgument[Any],
1260 **kwargs: Any,
1261 ) -> sum[_T]: ...
1263 @overload
1264 def sum( # noqa: A001
1265 self,
1266 col: _ColumnExpressionArgument[_T],
1267 *args: _ColumnExpressionOrLiteralArgument[Any],
1268 **kwargs: Any,
1269 ) -> sum[_T]: ...
1271 @overload
1272 def sum( # noqa: A001
1273 self,
1274 col: _T,
1275 *args: _ColumnExpressionOrLiteralArgument[Any],
1276 **kwargs: Any,
1277 ) -> sum[_T]: ...
1279 def sum( # noqa: A001
1280 self,
1281 col: _ColumnExpressionOrLiteralArgument[_T],
1282 *args: _ColumnExpressionOrLiteralArgument[Any],
1283 **kwargs: Any,
1284 ) -> sum[_T]: ...
1286 @property
1287 def sysdate(self) -> Type[sysdate]: ...
1289 @property
1290 def user(self) -> Type[user]: ...
1292 # END GENERATED FUNCTION ACCESSORS
1295func = _FunctionGenerator()
1296func.__doc__ = _FunctionGenerator.__doc__
1298modifier = _FunctionGenerator(group=False)
1301class Function(FunctionElement[_T]):
1302 r"""Describe a named SQL function.
1304 The :class:`.Function` object is typically generated from the
1305 :data:`.func` generation object.
1308 :param \*clauses: list of column expressions that form the arguments
1309 of the SQL function call.
1311 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1312 used as the return value of the column expression generated by this
1313 function call.
1315 :param packagenames: a string which indicates package prefix names
1316 to be prepended to the function name when the SQL is generated.
1317 The :data:`.func` generator creates these when it is called using
1318 dotted format, e.g.::
1320 func.mypackage.some_function(col1, col2)
1322 .. seealso::
1324 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1326 :data:`.func` - namespace which produces registered or ad-hoc
1327 :class:`.Function` instances.
1329 :class:`.GenericFunction` - allows creation of registered function
1330 types.
1332 """
1334 __visit_name__ = "function"
1336 _traverse_internals = FunctionElement._traverse_internals + [
1337 ("packagenames", InternalTraversal.dp_plain_obj),
1338 ("name", InternalTraversal.dp_string),
1339 ("type", InternalTraversal.dp_type),
1340 ]
1342 name: str
1344 identifier: str
1346 type: TypeEngine[_T]
1347 """A :class:`_types.TypeEngine` object which refers to the SQL return
1348 type represented by this SQL function.
1350 This datatype may be configured when generating a
1351 :class:`_functions.Function` object by passing the
1352 :paramref:`_functions.Function.type_` parameter, e.g.::
1354 >>> select(func.lower("some VALUE", type_=String))
1356 The small number of built-in classes of :class:`_functions.Function` come
1357 with a built-in datatype that's appropriate to the class of function and
1358 its arguments. For functions that aren't known, the type defaults to the
1359 "null type".
1361 """
1363 @overload
1364 def __init__(
1365 self,
1366 name: str,
1367 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1368 type_: None = ...,
1369 packagenames: Optional[Tuple[str, ...]] = ...,
1370 ) -> None: ...
1372 @overload
1373 def __init__(
1374 self,
1375 name: str,
1376 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1377 type_: _TypeEngineArgument[_T] = ...,
1378 packagenames: Optional[Tuple[str, ...]] = ...,
1379 ) -> None: ...
1381 def __init__(
1382 self,
1383 name: str,
1384 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1385 type_: Optional[_TypeEngineArgument[_T]] = None,
1386 packagenames: Optional[Tuple[str, ...]] = None,
1387 ) -> None:
1388 """Construct a :class:`.Function`.
1390 The :data:`.func` construct is normally used to construct
1391 new :class:`.Function` instances.
1393 """
1394 self.packagenames = packagenames or ()
1395 self.name = name
1397 # if type is None, we get NULLTYPE, which is our _T. But I don't
1398 # know how to get the overloads to express that correctly
1399 self.type = type_api.to_instance(type_) # type: ignore
1401 FunctionElement.__init__(self, *clauses)
1403 def _bind_param(
1404 self,
1405 operator: OperatorType,
1406 obj: Any,
1407 type_: Optional[TypeEngine[_T]] = None,
1408 expanding: bool = False,
1409 **kw: Any,
1410 ) -> BindParameter[_T]:
1411 return BindParameter(
1412 self.name,
1413 obj,
1414 _compared_to_operator=operator,
1415 _compared_to_type=self.type,
1416 type_=type_,
1417 unique=True,
1418 expanding=expanding,
1419 **kw,
1420 )
1423class GenericFunction(Function[_T]):
1424 """Define a 'generic' function.
1426 A generic function is a pre-established :class:`.Function`
1427 class that is instantiated automatically when called
1428 by name from the :data:`.func` attribute. Note that
1429 calling any name from :data:`.func` has the effect that
1430 a new :class:`.Function` instance is created automatically,
1431 given that name. The primary use case for defining
1432 a :class:`.GenericFunction` class is so that a function
1433 of a particular name may be given a fixed return type.
1434 It can also include custom argument parsing schemes as well
1435 as additional methods.
1437 Subclasses of :class:`.GenericFunction` are automatically
1438 registered under the name of the class. For
1439 example, a user-defined function ``as_utc()`` would
1440 be available immediately::
1442 from sqlalchemy.sql.functions import GenericFunction
1443 from sqlalchemy.types import DateTime
1446 class as_utc(GenericFunction):
1447 type = DateTime()
1448 inherit_cache = True
1451 print(select(func.as_utc()))
1453 User-defined generic functions can be organized into
1454 packages by specifying the "package" attribute when defining
1455 :class:`.GenericFunction`. Third party libraries
1456 containing many functions may want to use this in order
1457 to avoid name conflicts with other systems. For example,
1458 if our ``as_utc()`` function were part of a package
1459 "time"::
1461 class as_utc(GenericFunction):
1462 type = DateTime()
1463 package = "time"
1464 inherit_cache = True
1466 The above function would be available from :data:`.func`
1467 using the package name ``time``::
1469 print(select(func.time.as_utc()))
1471 A final option is to allow the function to be accessed
1472 from one name in :data:`.func` but to render as a different name.
1473 The ``identifier`` attribute will override the name used to
1474 access the function as loaded from :data:`.func`, but will retain
1475 the usage of ``name`` as the rendered name::
1477 class GeoBuffer(GenericFunction):
1478 type = Geometry()
1479 package = "geo"
1480 name = "ST_Buffer"
1481 identifier = "buffer"
1482 inherit_cache = True
1484 The above function will render as follows:
1486 .. sourcecode:: pycon+sql
1488 >>> print(func.geo.buffer())
1489 {printsql}ST_Buffer()
1491 The name will be rendered as is, however without quoting unless the name
1492 contains special characters that require quoting. To force quoting
1493 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1494 construct::
1496 from sqlalchemy.sql import quoted_name
1499 class GeoBuffer(GenericFunction):
1500 type = Geometry()
1501 package = "geo"
1502 name = quoted_name("ST_Buffer", True)
1503 identifier = "buffer"
1504 inherit_cache = True
1506 The above function will render as:
1508 .. sourcecode:: pycon+sql
1510 >>> print(func.geo.buffer())
1511 {printsql}"ST_Buffer"()
1513 Type parameters for this class as a
1514 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1515 and should match the type seen in a :class:`_engine.Result`. For example::
1517 class as_utc(GenericFunction[datetime.datetime]):
1518 type = DateTime()
1519 inherit_cache = True
1521 The above indicates that the following expression returns a ``datetime``
1522 object::
1524 connection.scalar(select(func.as_utc()))
1526 """
1528 coerce_arguments = True
1529 inherit_cache = True
1531 _register: bool
1533 name = "GenericFunction"
1535 def __init_subclass__(cls) -> None:
1536 if annotation.Annotated not in cls.__mro__:
1537 cls._register_generic_function(cls.__name__, cls.__dict__)
1538 super().__init_subclass__()
1540 @classmethod
1541 def _register_generic_function(
1542 cls, clsname: str, clsdict: Mapping[str, Any]
1543 ) -> None:
1544 cls.name = name = clsdict.get("name", clsname)
1545 cls.identifier = identifier = clsdict.get("identifier", name)
1546 package = clsdict.get("package", "_default")
1547 # legacy
1548 if "__return_type__" in clsdict:
1549 cls.type = clsdict["__return_type__"]
1551 # Check _register attribute status
1552 cls._register = getattr(cls, "_register", True)
1554 # Register the function if required
1555 if cls._register:
1556 register_function(identifier, cls, package)
1557 else:
1558 # Set _register to True to register child classes by default
1559 cls._register = True
1561 def __init__(
1562 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1563 ) -> None:
1564 parsed_args = kwargs.pop("_parsed_args", None)
1565 if parsed_args is None:
1566 parsed_args = [
1567 coercions.expect(
1568 roles.ExpressionElementRole,
1569 c,
1570 name=self.name,
1571 apply_propagate_attrs=self,
1572 )
1573 for c in args
1574 ]
1575 self._has_args = self._has_args or bool(parsed_args)
1576 self.packagenames = ()
1578 self.clause_expr = Grouping(
1579 ClauseList(
1580 operator=operators.comma_op, group_contents=True, *parsed_args
1581 )
1582 )
1584 self.type = type_api.to_instance( # type: ignore
1585 kwargs.pop("type_", None) or getattr(self, "type", None)
1586 )
1589register_function("cast", Cast) # type: ignore
1590register_function("extract", Extract) # type: ignore
1593class next_value(GenericFunction[int]):
1594 """Represent the 'next value', given a :class:`.Sequence`
1595 as its single argument.
1597 Compiles into the appropriate function on each backend,
1598 or will raise NotImplementedError if used on a backend
1599 that does not provide support for sequences.
1601 """
1603 type = sqltypes.Integer()
1604 name = "next_value"
1606 _traverse_internals = [
1607 ("sequence", InternalTraversal.dp_named_ddl_element)
1608 ]
1610 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1611 assert isinstance(
1612 seq, schema.Sequence
1613 ), "next_value() accepts a Sequence object as input."
1614 self.sequence = seq
1615 self.type = sqltypes.to_instance( # type: ignore
1616 seq.data_type or getattr(self, "type", None)
1617 )
1619 def compare(self, other: Any, **kw: Any) -> bool:
1620 return (
1621 isinstance(other, next_value)
1622 and self.sequence.name == other.sequence.name
1623 )
1625 @property
1626 def _from_objects(self) -> Any:
1627 return []
1630class AnsiFunction(GenericFunction[_T]):
1631 """Define a function in "ansi" format, which doesn't render parenthesis."""
1633 inherit_cache = True
1635 def __init__(
1636 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1637 ) -> None:
1638 GenericFunction.__init__(self, *args, **kwargs)
1641class ReturnTypeFromArgs(GenericFunction[_T]):
1642 """Define a function whose return type is bound to the type of its
1643 arguments.
1644 """
1646 inherit_cache = True
1648 # set ColumnElement[_T] as a separate overload, to appease
1649 # mypy which seems to not want to accept _T from
1650 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1651 # _HasClauseElement as of mypy 1.15
1653 @overload
1654 def __init__(
1655 self,
1656 col: ColumnElement[_T],
1657 *args: _ColumnExpressionOrLiteralArgument[Any],
1658 **kwargs: Any,
1659 ) -> None: ...
1661 @overload
1662 def __init__(
1663 self,
1664 col: _ColumnExpressionArgument[_T],
1665 *args: _ColumnExpressionOrLiteralArgument[Any],
1666 **kwargs: Any,
1667 ) -> None: ...
1669 @overload
1670 def __init__(
1671 self,
1672 col: _T,
1673 *args: _ColumnExpressionOrLiteralArgument[Any],
1674 **kwargs: Any,
1675 ) -> None: ...
1677 def __init__(
1678 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1679 ) -> None:
1680 fn_args: Sequence[ColumnElement[Any]] = [
1681 coercions.expect(
1682 roles.ExpressionElementRole,
1683 c,
1684 name=self.name,
1685 apply_propagate_attrs=self,
1686 )
1687 for c in args
1688 ]
1689 kwargs.setdefault("type_", _type_from_args(fn_args))
1690 kwargs["_parsed_args"] = fn_args
1691 super().__init__(*fn_args, **kwargs)
1694class coalesce(ReturnTypeFromArgs[_T]):
1695 _has_args = True
1696 inherit_cache = True
1699class max(ReturnTypeFromArgs[_T]): # noqa: A001
1700 """The SQL MAX() aggregate function."""
1702 inherit_cache = True
1705class min(ReturnTypeFromArgs[_T]): # noqa: A001
1706 """The SQL MIN() aggregate function."""
1708 inherit_cache = True
1711class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1712 """The SQL SUM() aggregate function."""
1714 inherit_cache = True
1717class now(GenericFunction[datetime.datetime]):
1718 """The SQL now() datetime function.
1720 SQLAlchemy dialects will usually render this particular function
1721 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1723 """
1725 type = sqltypes.DateTime()
1726 inherit_cache = True
1729class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1730 """The SQL POW() function which performs the power operator.
1732 E.g.:
1734 .. sourcecode:: pycon+sql
1736 >>> print(select(func.pow(2, 8)))
1737 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1739 .. versionadded:: 2.1
1741 """
1743 inherit_cache = True
1746class concat(GenericFunction[str]):
1747 """The SQL CONCAT() function, which concatenates strings.
1749 E.g.:
1751 .. sourcecode:: pycon+sql
1753 >>> print(select(func.concat("a", "b")))
1754 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1756 String concatenation in SQLAlchemy is more commonly available using the
1757 Python ``+`` operator with string datatypes, which will render a
1758 backend-specific concatenation operator, such as :
1760 .. sourcecode:: pycon+sql
1762 >>> print(select(literal("a") + "b"))
1763 {printsql}SELECT :param_1 || :param_2 AS anon_1
1766 """
1768 type = sqltypes.String()
1769 inherit_cache = True
1772class char_length(GenericFunction[int]):
1773 """The CHAR_LENGTH() SQL function."""
1775 type = sqltypes.Integer()
1776 inherit_cache = True
1778 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1779 # slight hack to limit to just one positional argument
1780 # not sure why this one function has this special treatment
1781 super().__init__(arg, **kw)
1784class random(GenericFunction[float]):
1785 """The RANDOM() SQL function."""
1787 _has_args = True
1788 inherit_cache = True
1791class count(GenericFunction[int]):
1792 r"""The ANSI COUNT aggregate function. With no arguments,
1793 emits COUNT \*.
1795 E.g.::
1797 from sqlalchemy import func
1798 from sqlalchemy import select
1799 from sqlalchemy import table, column
1801 my_table = table("some_table", column("id"))
1803 stmt = select(func.count()).select_from(my_table)
1805 Executing ``stmt`` would emit:
1807 .. sourcecode:: sql
1809 SELECT count(*) AS count_1
1810 FROM some_table
1813 """
1815 type = sqltypes.Integer()
1816 inherit_cache = True
1818 def __init__(
1819 self,
1820 expression: Union[
1821 _ColumnExpressionArgument[Any], _StarOrOne, None
1822 ] = None,
1823 **kwargs: Any,
1824 ) -> None:
1825 if expression is None:
1826 expression = literal_column("*")
1827 super().__init__(expression, **kwargs)
1830class current_date(AnsiFunction[datetime.date]):
1831 """The CURRENT_DATE() SQL function."""
1833 type = sqltypes.Date()
1834 inherit_cache = True
1837class current_time(AnsiFunction[datetime.time]):
1838 """The CURRENT_TIME() SQL function."""
1840 type = sqltypes.Time()
1841 inherit_cache = True
1844class current_timestamp(AnsiFunction[datetime.datetime]):
1845 """The CURRENT_TIMESTAMP() SQL function."""
1847 type = sqltypes.DateTime()
1848 inherit_cache = True
1851class current_user(AnsiFunction[str]):
1852 """The CURRENT_USER() SQL function."""
1854 type = sqltypes.String()
1855 inherit_cache = True
1858class localtime(AnsiFunction[datetime.datetime]):
1859 """The localtime() SQL function."""
1861 type = sqltypes.DateTime()
1862 inherit_cache = True
1865class localtimestamp(AnsiFunction[datetime.datetime]):
1866 """The localtimestamp() SQL function."""
1868 type = sqltypes.DateTime()
1869 inherit_cache = True
1872class session_user(AnsiFunction[str]):
1873 """The SESSION_USER() SQL function."""
1875 type = sqltypes.String()
1876 inherit_cache = True
1879class sysdate(AnsiFunction[datetime.datetime]):
1880 """The SYSDATE() SQL function."""
1882 type = sqltypes.DateTime()
1883 inherit_cache = True
1886class user(AnsiFunction[str]):
1887 """The USER() SQL function."""
1889 type = sqltypes.String()
1890 inherit_cache = True
1893class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1894 """Support for the ARRAY_AGG function.
1896 The ``func.array_agg(expr)`` construct returns an expression of
1897 type :class:`_types.ARRAY`.
1899 e.g.::
1901 stmt = select(func.array_agg(table.c.values)[2:5])
1903 .. seealso::
1905 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1906 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1907 added.
1909 """
1911 inherit_cache = True
1913 def __init__(
1914 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1915 ) -> None:
1916 fn_args: Sequence[ColumnElement[Any]] = [
1917 coercions.expect(
1918 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1919 )
1920 for c in args
1921 ]
1923 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1924 if "type_" not in kwargs:
1925 type_from_args = _type_from_args(fn_args)
1926 if isinstance(type_from_args, sqltypes.ARRAY):
1927 kwargs["type_"] = type_from_args
1928 else:
1929 kwargs["type_"] = default_array_type(
1930 type_from_args, dimensions=1
1931 )
1932 kwargs["_parsed_args"] = fn_args
1933 super().__init__(*fn_args, **kwargs)
1936class OrderedSetAgg(GenericFunction[_T]):
1937 """Define a function where the return type is based on the sort
1938 expression type as defined by the expression passed to the
1939 :meth:`.FunctionElement.within_group` method."""
1941 array_for_multi_clause = False
1942 inherit_cache = True
1944 def within_group_type(
1945 self, within_group: WithinGroup[Any]
1946 ) -> TypeEngine[Any]:
1947 func_clauses = cast(ClauseList, self.clause_expr.element)
1948 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
1949 within_group.order_by
1950 )
1951 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1952 return sqltypes.ARRAY(order_by[0].type)
1953 else:
1954 return order_by[0].type
1957class mode(OrderedSetAgg[_T]):
1958 """Implement the ``mode`` ordered-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 the same as the sort expression.
1965 """
1967 inherit_cache = True
1970class percentile_cont(OrderedSetAgg[_T]):
1971 """Implement the ``percentile_cont`` ordered-set aggregate function.
1973 This function must be used with the :meth:`.FunctionElement.within_group`
1974 modifier to supply a sort expression to operate upon.
1976 The return type of this function is the same as the sort expression,
1977 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1978 expression's type.
1980 """
1982 array_for_multi_clause = True
1983 inherit_cache = True
1986class percentile_disc(OrderedSetAgg[_T]):
1987 """Implement the ``percentile_disc`` ordered-set aggregate function.
1989 This function must be used with the :meth:`.FunctionElement.within_group`
1990 modifier to supply a sort expression to operate upon.
1992 The return type of this function is the same as the sort expression,
1993 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1994 expression's type.
1996 """
1998 array_for_multi_clause = True
1999 inherit_cache = True
2002class rank(GenericFunction[int]):
2003 """Implement the ``rank`` hypothetical-set aggregate function.
2005 This function must be used with the :meth:`.FunctionElement.within_group`
2006 modifier to supply a sort expression to operate upon.
2008 The return type of this function is :class:`.Integer`.
2010 """
2012 type = sqltypes.Integer()
2013 inherit_cache = True
2016class dense_rank(GenericFunction[int]):
2017 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2019 This function must be used with the :meth:`.FunctionElement.within_group`
2020 modifier to supply a sort expression to operate upon.
2022 The return type of this function is :class:`.Integer`.
2024 """
2026 type = sqltypes.Integer()
2027 inherit_cache = True
2030class percent_rank(GenericFunction[decimal.Decimal]):
2031 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2033 This function must be used with the :meth:`.FunctionElement.within_group`
2034 modifier to supply a sort expression to operate upon.
2036 The return type of this function is :class:`.Numeric`.
2038 """
2040 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2041 inherit_cache = True
2044class cume_dist(GenericFunction[decimal.Decimal]):
2045 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2047 This function must be used with the :meth:`.FunctionElement.within_group`
2048 modifier to supply a sort expression to operate upon.
2050 The return type of this function is :class:`.Numeric`.
2052 """
2054 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2055 inherit_cache = True
2058class cube(GenericFunction[_T]):
2059 r"""Implement the ``CUBE`` grouping operation.
2061 This function is used as part of the GROUP BY of a statement,
2062 e.g. :meth:`_expression.Select.group_by`::
2064 stmt = select(
2065 func.sum(table.c.value), table.c.col_1, table.c.col_2
2066 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2068 """
2070 _has_args = True
2071 inherit_cache = True
2074class rollup(GenericFunction[_T]):
2075 r"""Implement the ``ROLLUP`` grouping operation.
2077 This function is used as part of the GROUP BY of a statement,
2078 e.g. :meth:`_expression.Select.group_by`::
2080 stmt = select(
2081 func.sum(table.c.value), table.c.col_1, table.c.col_2
2082 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2084 """
2086 _has_args = True
2087 inherit_cache = True
2090class grouping_sets(GenericFunction[_T]):
2091 r"""Implement the ``GROUPING SETS`` grouping operation.
2093 This function is used as part of the GROUP BY of a statement,
2094 e.g. :meth:`_expression.Select.group_by`::
2096 stmt = select(
2097 func.sum(table.c.value), table.c.col_1, table.c.col_2
2098 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2100 In order to group by multiple sets, use the :func:`.tuple_` construct::
2102 from sqlalchemy import tuple_
2104 stmt = select(
2105 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2106 ).group_by(
2107 func.grouping_sets(
2108 tuple_(table.c.col_1, table.c.col_2),
2109 tuple_(table.c.value, table.c.col_3),
2110 )
2111 )
2113 """ # noqa: E501
2115 _has_args = True
2116 inherit_cache = True
2119class aggregate_strings(GenericFunction[str]):
2120 """Implement a generic string aggregation function.
2122 This function will concatenate non-null values into a string and
2123 separate the values by a delimiter.
2125 This function is compiled on a per-backend basis, into functions
2126 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2128 e.g. Example usage with delimiter '.'::
2130 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2132 The return type of this function is :class:`.String`.
2134 .. versionadded:: 2.0.21
2136 """
2138 type = sqltypes.String()
2139 _has_args = True
2140 inherit_cache = True
2142 def __init__(
2143 self, clause: _ColumnExpressionArgument[Any], separator: str
2144 ) -> None:
2145 super().__init__(clause, separator)