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-2026 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. to use the SQLite form of ``generate_series()`` (including
245 hidden columns "start", "stop", "step"):
247 .. sourcecode:: pycon+sql
249 >>> fn = func.generate_series(1, 5).table_valued(
250 ... "value", "start", "stop", "step"
251 ... )
253 >>> print(select(fn))
254 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
255 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
257 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
258 {printsql}SELECT anon_1.value, anon_1.stop
259 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
260 WHERE anon_1.value > :value_1{stop}
262 Backends like PostgreSQL need the accessed columns to be explicitly
263 named in "AS" clause. To achieve this, use
264 :meth:`_sql.TableValuedAlias.render_derived`; be sure to consult the
265 :ref:`PostgreSQL-specific documentation for table valued functions
266 <postgresql_table_valued>` for additional examples:
268 .. sourcecode:: pycon+sql
270 >>> fn = func.generate_series(1, 5).table_valued("value").render_derived()
272 >>> print(select(fn))
273 {printsql}SELECT anon_1.value FROM
274 generate_series(:generate_series_1, :generate_series_2) AS anon_1(value){stop}
276 A WITH ORDINALITY expression may be generated by passing the keyword
277 argument :paramref:`.FunctionElement.table_valued.with_ordinality`,
278 illustrated below using PostgreSQL's syntax:
280 .. sourcecode:: pycon+sql
282 >>> fn = func.generate_series(4, 1, -1).table_valued(
283 ... "gen", with_ordinality="ordinality"
284 ... )
285 >>> print(select(fn.render_derived()))
286 {printsql}SELECT anon_1.gen, anon_1.ordinality
287 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3)
288 WITH ORDINALITY AS anon_1(gen, ordinality)
290 :param \*expr: A series of string column names that will be added to the
291 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
292 construct as columns. :func:`_sql.column` objects with or without
293 datatypes may also be used.
295 :param name: optional name to assign to the alias name that's generated.
296 If omitted, a unique anonymizing name is used.
298 :param with_ordinality: string name that when present results in the
299 ``WITH ORDINALITY`` clause being added to the alias, and the given
300 string name will be added as a column to the .c collection
301 of the resulting :class:`_sql.TableValuedAlias`.
303 :param joins_implicitly: when True, the table valued function may be
304 used in the FROM clause without any explicit JOIN to other tables
305 in the SQL query, and no "cartesian product" warning will be generated.
306 May be useful for SQL functions such as ``func.json_each()``.
308 .. versionadded:: 1.4.33
310 .. versionadded:: 1.4.0b2
313 .. seealso::
315 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
317 :ref:`Table-Valued Functions on PostgreSQL <postgresql_table_valued>` - in the :ref:`postgresql_toplevel` documentation
319 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
320 :meth:`_functions.FunctionElement.table_valued` which delivers the
321 complete table valued expression as a scalar column expression
323 :meth:`_functions.FunctionElement.column_valued`
325 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
326 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
328 """ # noqa: 501
330 new_func = self._generate()
332 with_ordinality = kw.pop("with_ordinality", None)
333 joins_implicitly = kw.pop("joins_implicitly", None)
334 name = kw.pop("name", None)
336 if with_ordinality:
337 expr += (with_ordinality,)
338 new_func._with_ordinality = True
340 new_func.type = new_func._table_value_type = TableValueType(*expr)
342 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
344 def column_valued(
345 self, name: Optional[str] = None, joins_implicitly: bool = False
346 ) -> TableValuedColumn[_T]:
347 """Return this :class:`_functions.FunctionElement` as a column expression that
348 selects from itself as a FROM clause.
350 E.g.:
352 .. sourcecode:: pycon+sql
354 >>> from sqlalchemy import select, func
355 >>> gs = func.generate_series(1, 5, -1).column_valued()
356 >>> print(select(gs))
357 {printsql}SELECT anon_1
358 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
360 This is shorthand for::
362 gs = func.generate_series(1, 5, -1).alias().column
364 :param name: optional name to assign to the alias name that's generated.
365 If omitted, a unique anonymizing name is used.
367 :param joins_implicitly: when True, the "table" portion of the column
368 valued function may be a member of the FROM clause without any
369 explicit JOIN to other tables in the SQL query, and no "cartesian
370 product" warning will be generated. May be useful for SQL functions
371 such as ``func.json_array_elements()``.
373 .. versionadded:: 1.4.46
375 .. seealso::
377 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
379 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
381 :meth:`_functions.FunctionElement.table_valued`
383 """ # noqa: 501
385 return self.alias(name=name, joins_implicitly=joins_implicitly).column
387 @util.ro_non_memoized_property
388 def columns(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
389 r"""The set of columns exported by this :class:`.FunctionElement`.
391 This is a placeholder collection that allows the function to be
392 placed in the FROM clause of a statement:
394 .. sourcecode:: pycon+sql
396 >>> from sqlalchemy import column, select, func
397 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
398 >>> print(stmt)
399 {printsql}SELECT x, y FROM myfunction()
401 The above form is a legacy feature that is now superseded by the
402 fully capable :meth:`_functions.FunctionElement.table_valued`
403 method; see that method for details.
405 .. seealso::
407 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
408 SQL function expressions.
410 """ # noqa: E501
411 return self.c
413 @util.ro_memoized_property
414 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
415 """synonym for :attr:`.FunctionElement.columns`."""
417 return ColumnCollection(
418 columns=[(col.key, col) for col in self._all_selected_columns]
419 )
421 @property
422 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
423 if is_table_value_type(self.type):
424 # TODO: this might not be fully accurate
425 cols = cast(
426 "Sequence[KeyedColumnElement[Any]]", self.type._elements
427 )
428 else:
429 cols = [self.label(None)]
431 return cols
433 @property
434 def exported_columns( # type: ignore[override]
435 self,
436 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
437 return self.columns
439 @HasMemoized.memoized_attribute
440 def clauses(self) -> ClauseList:
441 """Return the underlying :class:`.ClauseList` which contains
442 the arguments for this :class:`.FunctionElement`.
444 """
445 return cast(ClauseList, self.clause_expr.element)
447 def over(
448 self,
449 *,
450 partition_by: Optional[_ByArgument] = None,
451 order_by: Optional[_ByArgument] = None,
452 rows: Optional[Tuple[Optional[int], Optional[int]]] = None,
453 range_: Optional[Tuple[Optional[int], Optional[int]]] = None,
454 groups: Optional[Tuple[Optional[int], Optional[int]]] = None,
455 ) -> Over[_T]:
456 """Produce an OVER clause against this function.
458 Used against aggregate or so-called "window" functions,
459 for database backends that support window functions.
461 The expression::
463 func.row_number().over(order_by="x")
465 is shorthand for::
467 from sqlalchemy import over
469 over(func.row_number(), order_by="x")
471 See :func:`_expression.over` for a full description.
473 .. seealso::
475 :func:`_expression.over`
477 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
479 """
480 return Over(
481 self,
482 partition_by=partition_by,
483 order_by=order_by,
484 rows=rows,
485 range_=range_,
486 groups=groups,
487 )
489 def within_group(
490 self, *order_by: _ColumnExpressionArgument[Any]
491 ) -> WithinGroup[_T]:
492 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
494 Used against so-called "ordered set aggregate" and "hypothetical
495 set aggregate" functions, including :class:`.percentile_cont`,
496 :class:`.rank`, :class:`.dense_rank`, etc.
498 See :func:`_expression.within_group` for a full description.
500 .. seealso::
502 :ref:`tutorial_functions_within_group` -
503 in the :ref:`unified_tutorial`
506 """
507 return WithinGroup(self, *order_by)
509 @overload
510 def filter(self) -> Self: ...
512 @overload
513 def filter(
514 self,
515 __criterion0: _ColumnExpressionArgument[bool],
516 *criterion: _ColumnExpressionArgument[bool],
517 ) -> FunctionFilter[_T]: ...
519 def filter(
520 self, *criterion: _ColumnExpressionArgument[bool]
521 ) -> Union[Self, FunctionFilter[_T]]:
522 """Produce a FILTER clause against this function.
524 Used against aggregate and window functions,
525 for database backends that support the "FILTER" clause.
527 The expression::
529 func.count(1).filter(True)
531 is shorthand for::
533 from sqlalchemy import funcfilter
535 funcfilter(func.count(1), True)
537 .. seealso::
539 :ref:`tutorial_functions_within_group` -
540 in the :ref:`unified_tutorial`
542 :class:`.FunctionFilter`
544 :func:`.funcfilter`
547 """
548 if not criterion:
549 return self
550 return FunctionFilter(self, *criterion)
552 def as_comparison(
553 self, left_index: int, right_index: int
554 ) -> FunctionAsBinary:
555 """Interpret this expression as a boolean comparison between two
556 values.
558 This method is used for an ORM use case described at
559 :ref:`relationship_custom_operator_sql_function`.
561 A hypothetical SQL function "is_equal()" which compares to values
562 for equality would be written in the Core expression language as::
564 expr = func.is_equal("a", "b")
566 If "is_equal()" above is comparing "a" and "b" for equality, the
567 :meth:`.FunctionElement.as_comparison` method would be invoked as::
569 expr = func.is_equal("a", "b").as_comparison(1, 2)
571 Where above, the integer value "1" refers to the first argument of the
572 "is_equal()" function and the integer value "2" refers to the second.
574 This would create a :class:`.BinaryExpression` that is equivalent to::
576 BinaryExpression("a", "b", operator=op.eq)
578 However, at the SQL level it would still render as
579 "is_equal('a', 'b')".
581 The ORM, when it loads a related object or collection, needs to be able
582 to manipulate the "left" and "right" sides of the ON clause of a JOIN
583 expression. The purpose of this method is to provide a SQL function
584 construct that can also supply this information to the ORM, when used
585 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
586 return value is a containment object called :class:`.FunctionAsBinary`.
588 An ORM example is as follows::
590 class Venue(Base):
591 __tablename__ = "venue"
592 id = Column(Integer, primary_key=True)
593 name = Column(String)
595 descendants = relationship(
596 "Venue",
597 primaryjoin=func.instr(
598 remote(foreign(name)), name + "/"
599 ).as_comparison(1, 2)
600 == 1,
601 viewonly=True,
602 order_by=name,
603 )
605 Above, the "Venue" class can load descendant "Venue" objects by
606 determining if the name of the parent Venue is contained within the
607 start of the hypothetical descendant value's name, e.g. "parent1" would
608 match up to "parent1/child1", but not to "parent2/child1".
610 Possible use cases include the "materialized path" example given above,
611 as well as making use of special SQL functions such as geometric
612 functions to create join conditions.
614 :param left_index: the integer 1-based index of the function argument
615 that serves as the "left" side of the expression.
616 :param right_index: the integer 1-based index of the function argument
617 that serves as the "right" side of the expression.
619 .. versionadded:: 1.3
621 .. seealso::
623 :ref:`relationship_custom_operator_sql_function` -
624 example use within the ORM
626 """
627 return FunctionAsBinary(self, left_index, right_index)
629 @property
630 def _from_objects(self) -> Any:
631 return self.clauses._from_objects
633 def within_group_type(
634 self, within_group: WithinGroup[_S]
635 ) -> Optional[TypeEngine[_S]]:
636 """For types that define their return type as based on the criteria
637 within a WITHIN GROUP (ORDER BY) expression, called by the
638 :class:`.WithinGroup` construct.
640 Returns None by default, in which case the function's normal ``.type``
641 is used.
643 """
645 return None
647 def alias(
648 self, name: Optional[str] = None, joins_implicitly: bool = False
649 ) -> TableValuedAlias:
650 r"""Produce a :class:`_expression.Alias` construct against this
651 :class:`.FunctionElement`.
653 .. tip::
655 The :meth:`_functions.FunctionElement.alias` method is part of the
656 mechanism by which "table valued" SQL functions are created.
657 However, most use cases are covered by higher level methods on
658 :class:`_functions.FunctionElement` including
659 :meth:`_functions.FunctionElement.table_valued`, and
660 :meth:`_functions.FunctionElement.column_valued`.
662 This construct wraps the function in a named alias which
663 is suitable for the FROM clause, in the style accepted for example
664 by PostgreSQL. A column expression is also provided using the
665 special ``.column`` attribute, which may
666 be used to refer to the output of the function as a scalar value
667 in the columns or where clause, for a backend such as PostgreSQL.
669 For a full table-valued expression, use the
670 :meth:`_functions.FunctionElement.table_valued` method first to
671 establish named columns.
673 e.g.:
675 .. sourcecode:: pycon+sql
677 >>> from sqlalchemy import func, select, column
678 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
679 >>> print(select(data_view.column))
680 {printsql}SELECT data_view
681 FROM unnest(:unnest_1) AS data_view
683 The :meth:`_functions.FunctionElement.column_valued` method provides
684 a shortcut for the above pattern:
686 .. sourcecode:: pycon+sql
688 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
689 >>> print(select(data_view))
690 {printsql}SELECT data_view
691 FROM unnest(:unnest_1) AS data_view
693 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
695 :param name: alias name, will be rendered as ``AS <name>`` in the
696 FROM clause
698 :param joins_implicitly: when True, the table valued function may be
699 used in the FROM clause without any explicit JOIN to other tables
700 in the SQL query, and no "cartesian product" warning will be
701 generated. May be useful for SQL functions such as
702 ``func.json_each()``.
704 .. versionadded:: 1.4.33
706 .. seealso::
708 :ref:`tutorial_functions_table_valued` -
709 in the :ref:`unified_tutorial`
711 :meth:`_functions.FunctionElement.table_valued`
713 :meth:`_functions.FunctionElement.scalar_table_valued`
715 :meth:`_functions.FunctionElement.column_valued`
718 """
720 return TableValuedAlias._construct(
721 self,
722 name=name,
723 table_value_type=self.type,
724 joins_implicitly=joins_implicitly,
725 )
727 def select(self) -> Select[Tuple[_T]]:
728 """Produce a :func:`_expression.select` construct
729 against this :class:`.FunctionElement`.
731 This is shorthand for::
733 s = select(function_element)
735 """
736 s: Select[Any] = Select(self)
737 if self._execution_options:
738 s = s.execution_options(**self._execution_options)
739 return s
741 def _bind_param(
742 self,
743 operator: OperatorType,
744 obj: Any,
745 type_: Optional[TypeEngine[_T]] = None,
746 expanding: bool = False,
747 **kw: Any,
748 ) -> BindParameter[_T]:
749 return BindParameter(
750 None,
751 obj,
752 _compared_to_operator=operator,
753 _compared_to_type=self.type,
754 unique=True,
755 type_=type_,
756 expanding=expanding,
757 **kw,
758 )
760 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
761 # for the moment, we are parenthesizing all array-returning
762 # expressions against getitem. This may need to be made
763 # more portable if in the future we support other DBs
764 # besides postgresql.
765 if against is operators.getitem and isinstance(
766 self.type, sqltypes.ARRAY
767 ):
768 return Grouping(self)
769 else:
770 return super().self_group(against=against)
772 @property
773 def entity_namespace(self) -> _EntityNamespace:
774 """overrides FromClause.entity_namespace as functions are generally
775 column expressions and not FromClauses.
777 """
778 # ideally functions would not be fromclauses but we failed to make
779 # this adjustment in 1.4
780 return _entity_namespace(self.clause_expr)
783class FunctionAsBinary(BinaryExpression[Any]):
784 _traverse_internals = [
785 ("sql_function", InternalTraversal.dp_clauseelement),
786 ("left_index", InternalTraversal.dp_plain_obj),
787 ("right_index", InternalTraversal.dp_plain_obj),
788 ("modifiers", InternalTraversal.dp_plain_dict),
789 ]
791 sql_function: FunctionElement[Any]
792 left_index: int
793 right_index: int
795 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
796 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
798 def __init__(
799 self, fn: FunctionElement[Any], left_index: int, right_index: int
800 ) -> None:
801 self.sql_function = fn
802 self.left_index = left_index
803 self.right_index = right_index
805 self.operator = operators.function_as_comparison_op
806 self.type = sqltypes.BOOLEANTYPE
807 self.negate = None
808 self._is_implicitly_boolean = True
809 self.modifiers = util.immutabledict({})
811 @property
812 def left_expr(self) -> ColumnElement[Any]:
813 return self.sql_function.clauses.clauses[self.left_index - 1]
815 @left_expr.setter
816 def left_expr(self, value: ColumnElement[Any]) -> None:
817 self.sql_function.clauses.clauses[self.left_index - 1] = value
819 @property
820 def right_expr(self) -> ColumnElement[Any]:
821 return self.sql_function.clauses.clauses[self.right_index - 1]
823 @right_expr.setter
824 def right_expr(self, value: ColumnElement[Any]) -> None:
825 self.sql_function.clauses.clauses[self.right_index - 1] = value
827 if not TYPE_CHECKING:
828 # mypy can't accommodate @property to replace an instance
829 # variable
831 left = left_expr
832 right = right_expr
835class ScalarFunctionColumn(NamedColumn[_T]):
836 __visit_name__ = "scalar_function_column"
838 _traverse_internals = [
839 ("name", InternalTraversal.dp_anon_name),
840 ("type", InternalTraversal.dp_type),
841 ("fn", InternalTraversal.dp_clauseelement),
842 ]
844 is_literal = False
845 table = None
847 def __init__(
848 self,
849 fn: FunctionElement[_T],
850 name: str,
851 type_: Optional[_TypeEngineArgument[_T]] = None,
852 ) -> None:
853 self.fn = fn
854 self.name = name
856 # if type is None, we get NULLTYPE, which is our _T. But I don't
857 # know how to get the overloads to express that correctly
858 self.type = type_api.to_instance(type_) # type: ignore
861class _FunctionGenerator:
862 """Generate SQL function expressions.
864 :data:`.func` is a special object instance which generates SQL
865 functions based on name-based attributes, e.g.:
867 .. sourcecode:: pycon+sql
869 >>> print(func.count(1))
870 {printsql}count(:param_1)
872 The returned object is an instance of :class:`.Function`, and is a
873 column-oriented SQL element like any other, and is used in that way:
875 .. sourcecode:: pycon+sql
877 >>> print(select(func.count(table.c.id)))
878 {printsql}SELECT count(sometable.id) FROM sometable
880 Any name can be given to :data:`.func`. If the function name is unknown to
881 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
882 which SQLAlchemy is aware of, the name may be interpreted as a *generic
883 function* which will be compiled appropriately to the target database:
885 .. sourcecode:: pycon+sql
887 >>> print(func.current_timestamp())
888 {printsql}CURRENT_TIMESTAMP
890 To call functions which are present in dot-separated packages,
891 specify them in the same manner:
893 .. sourcecode:: pycon+sql
895 >>> print(func.stats.yield_curve(5, 10))
896 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
898 SQLAlchemy can be made aware of the return type of functions to enable
899 type-specific lexical and result-based behavior. For example, to ensure
900 that a string-based function returns a Unicode value and is similarly
901 treated as a string in expressions, specify
902 :class:`~sqlalchemy.types.Unicode` as the type:
904 .. sourcecode:: pycon+sql
906 >>> print(
907 ... func.my_string("hi", type_=Unicode)
908 ... + " "
909 ... + func.my_string("there", type_=Unicode)
910 ... )
911 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
913 The object returned by a :data:`.func` call is usually an instance of
914 :class:`.Function`.
915 This object meets the "column" interface, including comparison and labeling
916 functions. The object can also be passed the :meth:`~.Connectable.execute`
917 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
918 where it will be
919 wrapped inside of a SELECT statement first::
921 print(connection.execute(func.current_timestamp()).scalar())
923 In a few exception cases, the :data:`.func` accessor
924 will redirect a name to a built-in expression such as :func:`.cast`
925 or :func:`.extract`, as these names have well-known meaning
926 but are not exactly the same as "functions" from a SQLAlchemy
927 perspective.
929 Functions which are interpreted as "generic" functions know how to
930 calculate their return type automatically. For a listing of known generic
931 functions, see :ref:`generic_functions`.
933 .. note::
935 The :data:`.func` construct has only limited support for calling
936 standalone "stored procedures", especially those with special
937 parameterization concerns.
939 See the section :ref:`stored_procedures` for details on how to use
940 the DBAPI-level ``callproc()`` method for fully traditional stored
941 procedures.
943 .. seealso::
945 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
947 :class:`.Function`
949 """ # noqa
951 def __init__(self, **opts: Any) -> None:
952 self.__names: List[str] = []
953 self.opts = opts
955 def __getattr__(self, name: str) -> _FunctionGenerator:
956 # passthru __ attributes; fixes pydoc
957 if name.startswith("__"):
958 try:
959 return self.__dict__[name] # type: ignore
960 except KeyError:
961 raise AttributeError(name)
963 elif name.endswith("_"):
964 name = name[0:-1]
965 f = _FunctionGenerator(**self.opts)
966 f.__names = list(self.__names) + [name]
967 return f
969 @overload
970 def __call__(
971 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
972 ) -> Function[_T]: ...
974 @overload
975 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
977 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
978 o = self.opts.copy()
979 o.update(kwargs)
981 tokens = len(self.__names)
983 if tokens == 2:
984 package, fname = self.__names
985 elif tokens == 1:
986 package, fname = "_default", self.__names[0]
987 else:
988 package = None
990 if package is not None:
991 func = _registry[package].get(fname.lower())
992 if func is not None:
993 return func(*c, **o)
995 return Function(
996 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
997 )
999 if TYPE_CHECKING:
1000 # START GENERATED FUNCTION ACCESSORS
1002 # code within this block is **programmatically,
1003 # statically generated** by tools/generate_sql_functions.py
1005 @property
1006 def aggregate_strings(self) -> Type[aggregate_strings]: ...
1008 @property
1009 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
1011 # set ColumnElement[_T] as a separate overload, to appease
1012 # mypy which seems to not want to accept _T from
1013 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1014 # _HasClauseElement as of mypy 1.15
1016 @overload
1017 def array_agg(
1018 self,
1019 col: ColumnElement[_T],
1020 *args: _ColumnExpressionOrLiteralArgument[Any],
1021 **kwargs: Any,
1022 ) -> array_agg[_T]: ...
1024 @overload
1025 def array_agg(
1026 self,
1027 col: _ColumnExpressionArgument[_T],
1028 *args: _ColumnExpressionOrLiteralArgument[Any],
1029 **kwargs: Any,
1030 ) -> array_agg[_T]: ...
1032 @overload
1033 def array_agg(
1034 self,
1035 col: _T,
1036 *args: _ColumnExpressionOrLiteralArgument[Any],
1037 **kwargs: Any,
1038 ) -> array_agg[_T]: ...
1040 def array_agg(
1041 self,
1042 col: _ColumnExpressionOrLiteralArgument[_T],
1043 *args: _ColumnExpressionOrLiteralArgument[Any],
1044 **kwargs: Any,
1045 ) -> array_agg[_T]: ...
1047 @property
1048 def cast(self) -> Type[Cast[Any]]: ...
1050 @property
1051 def char_length(self) -> Type[char_length]: ...
1053 # set ColumnElement[_T] as a separate overload, to appease
1054 # mypy which seems to not want to accept _T from
1055 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1056 # _HasClauseElement as of mypy 1.15
1058 @overload
1059 def coalesce(
1060 self,
1061 col: ColumnElement[_T],
1062 *args: _ColumnExpressionOrLiteralArgument[Any],
1063 **kwargs: Any,
1064 ) -> coalesce[_T]: ...
1066 @overload
1067 def coalesce(
1068 self,
1069 col: _ColumnExpressionArgument[Optional[_T]],
1070 *args: _ColumnExpressionOrLiteralArgument[Any],
1071 **kwargs: Any,
1072 ) -> coalesce[_T]: ...
1074 @overload
1075 def coalesce(
1076 self,
1077 col: Optional[_T],
1078 *args: _ColumnExpressionOrLiteralArgument[Any],
1079 **kwargs: Any,
1080 ) -> coalesce[_T]: ...
1082 def coalesce(
1083 self,
1084 col: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1085 *args: _ColumnExpressionOrLiteralArgument[Any],
1086 **kwargs: Any,
1087 ) -> coalesce[_T]: ...
1089 @property
1090 def concat(self) -> Type[concat]: ...
1092 @property
1093 def count(self) -> Type[count]: ...
1095 @property
1096 def cube(self) -> Type[cube[Any]]: ...
1098 @property
1099 def cume_dist(self) -> Type[cume_dist]: ...
1101 @property
1102 def current_date(self) -> Type[current_date]: ...
1104 @property
1105 def current_time(self) -> Type[current_time]: ...
1107 @property
1108 def current_timestamp(self) -> Type[current_timestamp]: ...
1110 @property
1111 def current_user(self) -> Type[current_user]: ...
1113 @property
1114 def dense_rank(self) -> Type[dense_rank]: ...
1116 @property
1117 def extract(self) -> Type[Extract]: ...
1119 @property
1120 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1122 @property
1123 def localtime(self) -> Type[localtime]: ...
1125 @property
1126 def localtimestamp(self) -> Type[localtimestamp]: ...
1128 # set ColumnElement[_T] as a separate overload, to appease
1129 # mypy which seems to not want to accept _T from
1130 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1131 # _HasClauseElement as of mypy 1.15
1133 @overload
1134 def max( # noqa: A001
1135 self,
1136 col: ColumnElement[_T],
1137 *args: _ColumnExpressionOrLiteralArgument[Any],
1138 **kwargs: Any,
1139 ) -> max[_T]: ...
1141 @overload
1142 def max( # noqa: A001
1143 self,
1144 col: _ColumnExpressionArgument[_T],
1145 *args: _ColumnExpressionOrLiteralArgument[Any],
1146 **kwargs: Any,
1147 ) -> max[_T]: ...
1149 @overload
1150 def max( # noqa: A001
1151 self,
1152 col: _T,
1153 *args: _ColumnExpressionOrLiteralArgument[Any],
1154 **kwargs: Any,
1155 ) -> max[_T]: ...
1157 def max( # noqa: A001
1158 self,
1159 col: _ColumnExpressionOrLiteralArgument[_T],
1160 *args: _ColumnExpressionOrLiteralArgument[Any],
1161 **kwargs: Any,
1162 ) -> max[_T]: ...
1164 # set ColumnElement[_T] as a separate overload, to appease
1165 # mypy which seems to not want to accept _T from
1166 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1167 # _HasClauseElement as of mypy 1.15
1169 @overload
1170 def min( # noqa: A001
1171 self,
1172 col: ColumnElement[_T],
1173 *args: _ColumnExpressionOrLiteralArgument[Any],
1174 **kwargs: Any,
1175 ) -> min[_T]: ...
1177 @overload
1178 def min( # noqa: A001
1179 self,
1180 col: _ColumnExpressionArgument[_T],
1181 *args: _ColumnExpressionOrLiteralArgument[Any],
1182 **kwargs: Any,
1183 ) -> min[_T]: ...
1185 @overload
1186 def min( # noqa: A001
1187 self,
1188 col: _T,
1189 *args: _ColumnExpressionOrLiteralArgument[Any],
1190 **kwargs: Any,
1191 ) -> min[_T]: ...
1193 def min( # noqa: A001
1194 self,
1195 col: _ColumnExpressionOrLiteralArgument[_T],
1196 *args: _ColumnExpressionOrLiteralArgument[Any],
1197 **kwargs: Any,
1198 ) -> min[_T]: ...
1200 @property
1201 def mode(self) -> Type[mode[Any]]: ...
1203 @property
1204 def next_value(self) -> Type[next_value]: ...
1206 @property
1207 def now(self) -> Type[now]: ...
1209 @property
1210 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1212 @property
1213 def percent_rank(self) -> Type[percent_rank]: ...
1215 @property
1216 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1218 @property
1219 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1221 @property
1222 def random(self) -> Type[random]: ...
1224 @property
1225 def rank(self) -> Type[rank]: ...
1227 @property
1228 def rollup(self) -> Type[rollup[Any]]: ...
1230 @property
1231 def session_user(self) -> Type[session_user]: ...
1233 # set ColumnElement[_T] as a separate overload, to appease
1234 # mypy which seems to not want to accept _T from
1235 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1236 # _HasClauseElement as of mypy 1.15
1238 @overload
1239 def sum( # noqa: A001
1240 self,
1241 col: ColumnElement[_T],
1242 *args: _ColumnExpressionOrLiteralArgument[Any],
1243 **kwargs: Any,
1244 ) -> sum[_T]: ...
1246 @overload
1247 def sum( # noqa: A001
1248 self,
1249 col: _ColumnExpressionArgument[_T],
1250 *args: _ColumnExpressionOrLiteralArgument[Any],
1251 **kwargs: Any,
1252 ) -> sum[_T]: ...
1254 @overload
1255 def sum( # noqa: A001
1256 self,
1257 col: _T,
1258 *args: _ColumnExpressionOrLiteralArgument[Any],
1259 **kwargs: Any,
1260 ) -> sum[_T]: ...
1262 def sum( # noqa: A001
1263 self,
1264 col: _ColumnExpressionOrLiteralArgument[_T],
1265 *args: _ColumnExpressionOrLiteralArgument[Any],
1266 **kwargs: Any,
1267 ) -> sum[_T]: ...
1269 @property
1270 def sysdate(self) -> Type[sysdate]: ...
1272 @property
1273 def user(self) -> Type[user]: ...
1275 # END GENERATED FUNCTION ACCESSORS
1278func = _FunctionGenerator()
1279func.__doc__ = _FunctionGenerator.__doc__
1281modifier = _FunctionGenerator(group=False)
1284class Function(FunctionElement[_T]):
1285 r"""Describe a named SQL function.
1287 The :class:`.Function` object is typically generated from the
1288 :data:`.func` generation object.
1291 :param \*clauses: list of column expressions that form the arguments
1292 of the SQL function call.
1294 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1295 used as the return value of the column expression generated by this
1296 function call.
1298 :param packagenames: a string which indicates package prefix names
1299 to be prepended to the function name when the SQL is generated.
1300 The :data:`.func` generator creates these when it is called using
1301 dotted format, e.g.::
1303 func.mypackage.some_function(col1, col2)
1305 .. seealso::
1307 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1309 :data:`.func` - namespace which produces registered or ad-hoc
1310 :class:`.Function` instances.
1312 :class:`.GenericFunction` - allows creation of registered function
1313 types.
1315 """
1317 __visit_name__ = "function"
1319 _traverse_internals = FunctionElement._traverse_internals + [
1320 ("packagenames", InternalTraversal.dp_plain_obj),
1321 ("name", InternalTraversal.dp_string),
1322 ("type", InternalTraversal.dp_type),
1323 ]
1325 name: str
1327 identifier: str
1329 type: TypeEngine[_T]
1330 """A :class:`_types.TypeEngine` object which refers to the SQL return
1331 type represented by this SQL function.
1333 This datatype may be configured when generating a
1334 :class:`_functions.Function` object by passing the
1335 :paramref:`_functions.Function.type_` parameter, e.g.::
1337 >>> select(func.lower("some VALUE", type_=String))
1339 The small number of built-in classes of :class:`_functions.Function` come
1340 with a built-in datatype that's appropriate to the class of function and
1341 its arguments. For functions that aren't known, the type defaults to the
1342 "null type".
1344 """
1346 @overload
1347 def __init__(
1348 self,
1349 name: str,
1350 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1351 type_: None = ...,
1352 packagenames: Optional[Tuple[str, ...]] = ...,
1353 ) -> None: ...
1355 @overload
1356 def __init__(
1357 self,
1358 name: str,
1359 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1360 type_: _TypeEngineArgument[_T] = ...,
1361 packagenames: Optional[Tuple[str, ...]] = ...,
1362 ) -> None: ...
1364 def __init__(
1365 self,
1366 name: str,
1367 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1368 type_: Optional[_TypeEngineArgument[_T]] = None,
1369 packagenames: Optional[Tuple[str, ...]] = None,
1370 ) -> None:
1371 """Construct a :class:`.Function`.
1373 The :data:`.func` construct is normally used to construct
1374 new :class:`.Function` instances.
1376 """
1377 self.packagenames = packagenames or ()
1378 self.name = name
1380 # if type is None, we get NULLTYPE, which is our _T. But I don't
1381 # know how to get the overloads to express that correctly
1382 self.type = type_api.to_instance(type_) # type: ignore
1384 FunctionElement.__init__(self, *clauses)
1386 def _bind_param(
1387 self,
1388 operator: OperatorType,
1389 obj: Any,
1390 type_: Optional[TypeEngine[_T]] = None,
1391 expanding: bool = False,
1392 **kw: Any,
1393 ) -> BindParameter[_T]:
1394 return BindParameter(
1395 self.name,
1396 obj,
1397 _compared_to_operator=operator,
1398 _compared_to_type=self.type,
1399 type_=type_,
1400 unique=True,
1401 expanding=expanding,
1402 **kw,
1403 )
1406class GenericFunction(Function[_T]):
1407 """Define a 'generic' function.
1409 A generic function is a pre-established :class:`.Function`
1410 class that is instantiated automatically when called
1411 by name from the :data:`.func` attribute. Note that
1412 calling any name from :data:`.func` has the effect that
1413 a new :class:`.Function` instance is created automatically,
1414 given that name. The primary use case for defining
1415 a :class:`.GenericFunction` class is so that a function
1416 of a particular name may be given a fixed return type.
1417 It can also include custom argument parsing schemes as well
1418 as additional methods.
1420 Subclasses of :class:`.GenericFunction` are automatically
1421 registered under the name of the class. For
1422 example, a user-defined function ``as_utc()`` would
1423 be available immediately::
1425 from sqlalchemy.sql.functions import GenericFunction
1426 from sqlalchemy.types import DateTime
1429 class as_utc(GenericFunction):
1430 type = DateTime()
1431 inherit_cache = True
1434 print(select(func.as_utc()))
1436 User-defined generic functions can be organized into
1437 packages by specifying the "package" attribute when defining
1438 :class:`.GenericFunction`. Third party libraries
1439 containing many functions may want to use this in order
1440 to avoid name conflicts with other systems. For example,
1441 if our ``as_utc()`` function were part of a package
1442 "time"::
1444 class as_utc(GenericFunction):
1445 type = DateTime()
1446 package = "time"
1447 inherit_cache = True
1449 The above function would be available from :data:`.func`
1450 using the package name ``time``::
1452 print(select(func.time.as_utc()))
1454 A final option is to allow the function to be accessed
1455 from one name in :data:`.func` but to render as a different name.
1456 The ``identifier`` attribute will override the name used to
1457 access the function as loaded from :data:`.func`, but will retain
1458 the usage of ``name`` as the rendered name::
1460 class GeoBuffer(GenericFunction):
1461 type = Geometry()
1462 package = "geo"
1463 name = "ST_Buffer"
1464 identifier = "buffer"
1465 inherit_cache = True
1467 The above function will render as follows:
1469 .. sourcecode:: pycon+sql
1471 >>> print(func.geo.buffer())
1472 {printsql}ST_Buffer()
1474 The name will be rendered as is, however without quoting unless the name
1475 contains special characters that require quoting. To force quoting
1476 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1477 construct::
1479 from sqlalchemy.sql import quoted_name
1482 class GeoBuffer(GenericFunction):
1483 type = Geometry()
1484 package = "geo"
1485 name = quoted_name("ST_Buffer", True)
1486 identifier = "buffer"
1487 inherit_cache = True
1489 The above function will render as:
1491 .. sourcecode:: pycon+sql
1493 >>> print(func.geo.buffer())
1494 {printsql}"ST_Buffer"()
1496 Type parameters for this class as a
1497 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1498 and should match the type seen in a :class:`_engine.Result`. For example::
1500 class as_utc(GenericFunction[datetime.datetime]):
1501 type = DateTime()
1502 inherit_cache = True
1504 The above indicates that the following expression returns a ``datetime``
1505 object::
1507 connection.scalar(select(func.as_utc()))
1509 .. versionadded:: 1.3.13 The :class:`.quoted_name` construct is now
1510 recognized for quoting when used with the "name" attribute of the
1511 object, so that quoting can be forced on or off for the function
1512 name.
1515 """
1517 coerce_arguments = True
1518 inherit_cache = True
1520 _register: bool
1522 name = "GenericFunction"
1524 def __init_subclass__(cls) -> None:
1525 if annotation.Annotated not in cls.__mro__:
1526 cls._register_generic_function(cls.__name__, cls.__dict__)
1527 super().__init_subclass__()
1529 @classmethod
1530 def _register_generic_function(
1531 cls, clsname: str, clsdict: Mapping[str, Any]
1532 ) -> None:
1533 cls.name = name = clsdict.get("name", clsname)
1534 cls.identifier = identifier = clsdict.get("identifier", name)
1535 package = clsdict.get("package", "_default")
1536 # legacy
1537 if "__return_type__" in clsdict:
1538 cls.type = clsdict["__return_type__"]
1540 # Check _register attribute status
1541 cls._register = getattr(cls, "_register", True)
1543 # Register the function if required
1544 if cls._register:
1545 register_function(identifier, cls, package)
1546 else:
1547 # Set _register to True to register child classes by default
1548 cls._register = True
1550 def __init__(
1551 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1552 ) -> None:
1553 parsed_args = kwargs.pop("_parsed_args", None)
1554 if parsed_args is None:
1555 parsed_args = [
1556 coercions.expect(
1557 roles.ExpressionElementRole,
1558 c,
1559 name=self.name,
1560 apply_propagate_attrs=self,
1561 )
1562 for c in args
1563 ]
1564 self._has_args = self._has_args or bool(parsed_args)
1565 self.packagenames = ()
1567 self.clause_expr = Grouping(
1568 ClauseList(
1569 operator=operators.comma_op, group_contents=True, *parsed_args
1570 )
1571 )
1573 self.type = type_api.to_instance( # type: ignore
1574 kwargs.pop("type_", None) or getattr(self, "type", None)
1575 )
1578register_function("cast", Cast) # type: ignore
1579register_function("extract", Extract) # type: ignore
1582class next_value(GenericFunction[int]):
1583 """Represent the 'next value', given a :class:`.Sequence`
1584 as its single argument.
1586 Compiles into the appropriate function on each backend,
1587 or will raise NotImplementedError if used on a backend
1588 that does not provide support for sequences.
1590 """
1592 type = sqltypes.Integer()
1593 name = "next_value"
1595 _traverse_internals = [
1596 ("sequence", InternalTraversal.dp_named_ddl_element)
1597 ]
1599 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1600 assert isinstance(
1601 seq, schema.Sequence
1602 ), "next_value() accepts a Sequence object as input."
1603 self.sequence = seq
1604 self.type = sqltypes.to_instance( # type: ignore
1605 seq.data_type or getattr(self, "type", None)
1606 )
1608 def compare(self, other: Any, **kw: Any) -> bool:
1609 return (
1610 isinstance(other, next_value)
1611 and self.sequence.name == other.sequence.name
1612 )
1614 @property
1615 def _from_objects(self) -> Any:
1616 return []
1619class AnsiFunction(GenericFunction[_T]):
1620 """Define a function in "ansi" format, which doesn't render parenthesis."""
1622 inherit_cache = True
1624 def __init__(
1625 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1626 ) -> None:
1627 GenericFunction.__init__(self, *args, **kwargs)
1630class ReturnTypeFromArgs(GenericFunction[_T]):
1631 """Define a function whose return type is bound to the type of its
1632 arguments.
1633 """
1635 inherit_cache = True
1637 # set ColumnElement[_T] as a separate overload, to appease
1638 # mypy which seems to not want to accept _T from
1639 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1640 # _HasClauseElement as of mypy 1.15
1642 @overload
1643 def __init__(
1644 self,
1645 col: ColumnElement[_T],
1646 *args: _ColumnExpressionOrLiteralArgument[Any],
1647 **kwargs: Any,
1648 ) -> None: ...
1650 @overload
1651 def __init__(
1652 self,
1653 col: _ColumnExpressionArgument[_T],
1654 *args: _ColumnExpressionOrLiteralArgument[Any],
1655 **kwargs: Any,
1656 ) -> None: ...
1658 @overload
1659 def __init__(
1660 self,
1661 col: _T,
1662 *args: _ColumnExpressionOrLiteralArgument[Any],
1663 **kwargs: Any,
1664 ) -> None: ...
1666 def __init__(
1667 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1668 ) -> None:
1669 fn_args: Sequence[ColumnElement[Any]] = [
1670 coercions.expect(
1671 roles.ExpressionElementRole,
1672 c,
1673 name=self.name,
1674 apply_propagate_attrs=self,
1675 )
1676 for c in args
1677 ]
1678 kwargs.setdefault("type_", _type_from_args(fn_args))
1679 kwargs["_parsed_args"] = fn_args
1680 super().__init__(*fn_args, **kwargs)
1683class ReturnTypeFromOptionalArgs(ReturnTypeFromArgs[_T]):
1684 inherit_cache = True
1686 @overload
1687 def __init__(
1688 self,
1689 col: ColumnElement[_T],
1690 *args: _ColumnExpressionOrLiteralArgument[Any],
1691 **kwargs: Any,
1692 ) -> None: ...
1694 @overload
1695 def __init__(
1696 self,
1697 col: _ColumnExpressionArgument[Optional[_T]],
1698 *args: _ColumnExpressionOrLiteralArgument[Any],
1699 **kwargs: Any,
1700 ) -> None: ...
1702 @overload
1703 def __init__(
1704 self,
1705 col: Optional[_T],
1706 *args: _ColumnExpressionOrLiteralArgument[Any],
1707 **kwargs: Any,
1708 ) -> None: ...
1710 def __init__(
1711 self,
1712 *args: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1713 **kwargs: Any,
1714 ) -> None:
1715 super().__init__(*args, **kwargs) # type: ignore
1718class coalesce(ReturnTypeFromOptionalArgs[_T]):
1719 _has_args = True
1720 inherit_cache = True
1723class max(ReturnTypeFromArgs[_T]): # noqa: A001
1724 """The SQL MAX() aggregate function."""
1726 inherit_cache = True
1729class min(ReturnTypeFromArgs[_T]): # noqa: A001
1730 """The SQL MIN() aggregate function."""
1732 inherit_cache = True
1735class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1736 """The SQL SUM() aggregate function."""
1738 inherit_cache = True
1741class now(GenericFunction[datetime.datetime]):
1742 """The SQL now() datetime function.
1744 SQLAlchemy dialects will usually render this particular function
1745 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1747 """
1749 type = sqltypes.DateTime()
1750 inherit_cache = True
1753class concat(GenericFunction[str]):
1754 """The SQL CONCAT() function, which concatenates strings.
1756 E.g.:
1758 .. sourcecode:: pycon+sql
1760 >>> print(select(func.concat("a", "b")))
1761 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1763 String concatenation in SQLAlchemy is more commonly available using the
1764 Python ``+`` operator with string datatypes, which will render a
1765 backend-specific concatenation operator, such as :
1767 .. sourcecode:: pycon+sql
1769 >>> print(select(literal("a") + "b"))
1770 {printsql}SELECT :param_1 || :param_2 AS anon_1
1773 """
1775 type = sqltypes.String()
1776 inherit_cache = True
1779class char_length(GenericFunction[int]):
1780 """The CHAR_LENGTH() SQL function."""
1782 type = sqltypes.Integer()
1783 inherit_cache = True
1785 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1786 # slight hack to limit to just one positional argument
1787 # not sure why this one function has this special treatment
1788 super().__init__(arg, **kw)
1791class random(GenericFunction[float]):
1792 """The RANDOM() SQL function."""
1794 _has_args = True
1795 inherit_cache = True
1798class count(GenericFunction[int]):
1799 r"""The ANSI COUNT aggregate function. With no arguments,
1800 emits COUNT \*.
1802 E.g.::
1804 from sqlalchemy import func
1805 from sqlalchemy import select
1806 from sqlalchemy import table, column
1808 my_table = table("some_table", column("id"))
1810 stmt = select(func.count()).select_from(my_table)
1812 Executing ``stmt`` would emit:
1814 .. sourcecode:: sql
1816 SELECT count(*) AS count_1
1817 FROM some_table
1820 """
1822 type = sqltypes.Integer()
1823 inherit_cache = True
1825 def __init__(
1826 self,
1827 expression: Union[
1828 _ColumnExpressionArgument[Any], _StarOrOne, None
1829 ] = None,
1830 **kwargs: Any,
1831 ) -> None:
1832 if expression is None:
1833 expression = literal_column("*")
1834 super().__init__(expression, **kwargs)
1837class current_date(AnsiFunction[datetime.date]):
1838 """The CURRENT_DATE() SQL function."""
1840 type = sqltypes.Date()
1841 inherit_cache = True
1844class current_time(AnsiFunction[datetime.time]):
1845 """The CURRENT_TIME() SQL function."""
1847 type = sqltypes.Time()
1848 inherit_cache = True
1851class current_timestamp(AnsiFunction[datetime.datetime]):
1852 """The CURRENT_TIMESTAMP() SQL function."""
1854 type = sqltypes.DateTime()
1855 inherit_cache = True
1858class current_user(AnsiFunction[str]):
1859 """The CURRENT_USER() SQL function."""
1861 type = sqltypes.String()
1862 inherit_cache = True
1865class localtime(AnsiFunction[datetime.datetime]):
1866 """The localtime() SQL function."""
1868 type = sqltypes.DateTime()
1869 inherit_cache = True
1872class localtimestamp(AnsiFunction[datetime.datetime]):
1873 """The localtimestamp() SQL function."""
1875 type = sqltypes.DateTime()
1876 inherit_cache = True
1879class session_user(AnsiFunction[str]):
1880 """The SESSION_USER() SQL function."""
1882 type = sqltypes.String()
1883 inherit_cache = True
1886class sysdate(AnsiFunction[datetime.datetime]):
1887 """The SYSDATE() SQL function."""
1889 type = sqltypes.DateTime()
1890 inherit_cache = True
1893class user(AnsiFunction[str]):
1894 """The USER() SQL function."""
1896 type = sqltypes.String()
1897 inherit_cache = True
1900class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1901 """Support for the ARRAY_AGG function.
1903 The ``func.array_agg(expr)`` construct returns an expression of
1904 type :class:`_types.ARRAY`.
1906 e.g.::
1908 stmt = select(func.array_agg(table.c.values)[2:5])
1910 .. seealso::
1912 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1913 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1914 added.
1916 """
1918 inherit_cache = True
1920 def __init__(
1921 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1922 ) -> None:
1923 fn_args: Sequence[ColumnElement[Any]] = [
1924 coercions.expect(
1925 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1926 )
1927 for c in args
1928 ]
1930 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1931 if "type_" not in kwargs:
1932 type_from_args = _type_from_args(fn_args)
1933 if isinstance(type_from_args, sqltypes.ARRAY):
1934 kwargs["type_"] = type_from_args
1935 else:
1936 kwargs["type_"] = default_array_type(
1937 type_from_args, dimensions=1
1938 )
1939 kwargs["_parsed_args"] = fn_args
1940 super().__init__(*fn_args, **kwargs)
1943class OrderedSetAgg(GenericFunction[_T]):
1944 """Define a function where the return type is based on the sort
1945 expression type as defined by the expression passed to the
1946 :meth:`.FunctionElement.within_group` method."""
1948 array_for_multi_clause = False
1949 inherit_cache = True
1951 def within_group_type(
1952 self, within_group: WithinGroup[Any]
1953 ) -> TypeEngine[Any]:
1954 func_clauses = cast(ClauseList, self.clause_expr.element)
1955 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
1956 within_group.order_by
1957 )
1958 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1959 return sqltypes.ARRAY(order_by[0].type)
1960 else:
1961 return order_by[0].type
1964class mode(OrderedSetAgg[_T]):
1965 """Implement the ``mode`` ordered-set aggregate function.
1967 This function must be used with the :meth:`.FunctionElement.within_group`
1968 modifier to supply a sort expression to operate upon.
1970 The return type of this function is the same as the sort expression.
1972 """
1974 inherit_cache = True
1977class percentile_cont(OrderedSetAgg[_T]):
1978 """Implement the ``percentile_cont`` ordered-set aggregate function.
1980 This function must be used with the :meth:`.FunctionElement.within_group`
1981 modifier to supply a sort expression to operate upon.
1983 The return type of this function is the same as the sort expression,
1984 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1985 expression's type.
1987 """
1989 array_for_multi_clause = True
1990 inherit_cache = True
1993class percentile_disc(OrderedSetAgg[_T]):
1994 """Implement the ``percentile_disc`` ordered-set aggregate function.
1996 This function must be used with the :meth:`.FunctionElement.within_group`
1997 modifier to supply a sort expression to operate upon.
1999 The return type of this function is the same as the sort expression,
2000 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2001 expression's type.
2003 """
2005 array_for_multi_clause = True
2006 inherit_cache = True
2009class rank(GenericFunction[int]):
2010 """Implement the ``rank`` hypothetical-set aggregate function.
2012 This function must be used with the :meth:`.FunctionElement.within_group`
2013 modifier to supply a sort expression to operate upon.
2015 The return type of this function is :class:`.Integer`.
2017 """
2019 type = sqltypes.Integer()
2020 inherit_cache = True
2023class dense_rank(GenericFunction[int]):
2024 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2026 This function must be used with the :meth:`.FunctionElement.within_group`
2027 modifier to supply a sort expression to operate upon.
2029 The return type of this function is :class:`.Integer`.
2031 """
2033 type = sqltypes.Integer()
2034 inherit_cache = True
2037class percent_rank(GenericFunction[decimal.Decimal]):
2038 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2040 This function must be used with the :meth:`.FunctionElement.within_group`
2041 modifier to supply a sort expression to operate upon.
2043 The return type of this function is :class:`.Numeric`.
2045 """
2047 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2048 inherit_cache = True
2051class cume_dist(GenericFunction[decimal.Decimal]):
2052 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2054 This function must be used with the :meth:`.FunctionElement.within_group`
2055 modifier to supply a sort expression to operate upon.
2057 The return type of this function is :class:`.Numeric`.
2059 """
2061 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2062 inherit_cache = True
2065class cube(GenericFunction[_T]):
2066 r"""Implement the ``CUBE`` grouping operation.
2068 This function is used as part of the GROUP BY of a statement,
2069 e.g. :meth:`_expression.Select.group_by`::
2071 stmt = select(
2072 func.sum(table.c.value), table.c.col_1, table.c.col_2
2073 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2075 .. versionadded:: 1.2
2077 """
2079 _has_args = True
2080 inherit_cache = True
2083class rollup(GenericFunction[_T]):
2084 r"""Implement the ``ROLLUP`` grouping operation.
2086 This function is used as part of the GROUP BY of a statement,
2087 e.g. :meth:`_expression.Select.group_by`::
2089 stmt = select(
2090 func.sum(table.c.value), table.c.col_1, table.c.col_2
2091 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2093 .. versionadded:: 1.2
2095 """
2097 _has_args = True
2098 inherit_cache = True
2101class grouping_sets(GenericFunction[_T]):
2102 r"""Implement the ``GROUPING SETS`` grouping operation.
2104 This function is used as part of the GROUP BY of a statement,
2105 e.g. :meth:`_expression.Select.group_by`::
2107 stmt = select(
2108 func.sum(table.c.value), table.c.col_1, table.c.col_2
2109 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2111 In order to group by multiple sets, use the :func:`.tuple_` construct::
2113 from sqlalchemy import tuple_
2115 stmt = select(
2116 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2117 ).group_by(
2118 func.grouping_sets(
2119 tuple_(table.c.col_1, table.c.col_2),
2120 tuple_(table.c.value, table.c.col_3),
2121 )
2122 )
2124 .. versionadded:: 1.2
2126 """ # noqa: E501
2128 _has_args = True
2129 inherit_cache = True
2132class aggregate_strings(GenericFunction[str]):
2133 """Implement a generic string aggregation function.
2135 This function will concatenate non-null values into a string and
2136 separate the values by a delimiter.
2138 This function is compiled on a per-backend basis, into functions
2139 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2141 e.g. Example usage with delimiter '.'::
2143 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2145 The return type of this function is :class:`.String`.
2147 .. versionadded: 2.0.21
2149 """
2151 type = sqltypes.String()
2152 _has_args = True
2153 inherit_cache = True
2155 def __init__(
2156 self, clause: _ColumnExpressionArgument[Any], separator: str
2157 ) -> None:
2158 super().__init__(clause, separator)