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 in (operators.getitem, operators.json_getitem_op):
747 return Grouping(self)
748 else:
749 return super().self_group(against=against)
751 @property
752 def entity_namespace(self) -> _EntityNamespace:
753 """overrides FromClause.entity_namespace as functions are generally
754 column expressions and not FromClauses.
756 """
757 # ideally functions would not be fromclauses but we failed to make
758 # this adjustment in 1.4
759 return _entity_namespace(self.clause_expr)
762class FunctionAsBinary(BinaryExpression[Any]):
763 _traverse_internals = [
764 ("sql_function", InternalTraversal.dp_clauseelement),
765 ("left_index", InternalTraversal.dp_plain_obj),
766 ("right_index", InternalTraversal.dp_plain_obj),
767 ("modifiers", InternalTraversal.dp_plain_dict),
768 ]
770 sql_function: FunctionElement[Any]
771 left_index: int
772 right_index: int
774 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
775 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
777 def __init__(
778 self, fn: FunctionElement[Any], left_index: int, right_index: int
779 ) -> None:
780 self.sql_function = fn
781 self.left_index = left_index
782 self.right_index = right_index
784 self.operator = operators.function_as_comparison_op
785 self.type = sqltypes.BOOLEANTYPE
786 self.negate = None
787 self._is_implicitly_boolean = True
788 self.modifiers = util.immutabledict({})
790 @property
791 def left_expr(self) -> ColumnElement[Any]:
792 return self.sql_function.clauses.clauses[self.left_index - 1]
794 @left_expr.setter
795 def left_expr(self, value: ColumnElement[Any]) -> None:
796 self.sql_function.clauses.clauses[self.left_index - 1] = value
798 @property
799 def right_expr(self) -> ColumnElement[Any]:
800 return self.sql_function.clauses.clauses[self.right_index - 1]
802 @right_expr.setter
803 def right_expr(self, value: ColumnElement[Any]) -> None:
804 self.sql_function.clauses.clauses[self.right_index - 1] = value
806 if not TYPE_CHECKING:
807 # mypy can't accommodate @property to replace an instance
808 # variable
810 left = left_expr
811 right = right_expr
814class ScalarFunctionColumn(NamedColumn[_T]):
815 __visit_name__ = "scalar_function_column"
817 _traverse_internals = [
818 ("name", InternalTraversal.dp_anon_name),
819 ("type", InternalTraversal.dp_type),
820 ("fn", InternalTraversal.dp_clauseelement),
821 ]
823 is_literal = False
824 table = None
826 def __init__(
827 self,
828 fn: FunctionElement[_T],
829 name: str,
830 type_: Optional[_TypeEngineArgument[_T]] = None,
831 ) -> None:
832 self.fn = fn
833 self.name = name
835 # if type is None, we get NULLTYPE, which is our _T. But I don't
836 # know how to get the overloads to express that correctly
837 self.type = type_api.to_instance(type_) # type: ignore
840class _FunctionGenerator:
841 """Generate SQL function expressions.
843 :data:`.func` is a special object instance which generates SQL
844 functions based on name-based attributes, e.g.:
846 .. sourcecode:: pycon+sql
848 >>> print(func.count(1))
849 {printsql}count(:param_1)
851 The returned object is an instance of :class:`.Function`, and is a
852 column-oriented SQL element like any other, and is used in that way:
854 .. sourcecode:: pycon+sql
856 >>> print(select(func.count(table.c.id)))
857 {printsql}SELECT count(sometable.id) FROM sometable
859 Any name can be given to :data:`.func`. If the function name is unknown to
860 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
861 which SQLAlchemy is aware of, the name may be interpreted as a *generic
862 function* which will be compiled appropriately to the target database:
864 .. sourcecode:: pycon+sql
866 >>> print(func.current_timestamp())
867 {printsql}CURRENT_TIMESTAMP
869 To call functions which are present in dot-separated packages,
870 specify them in the same manner:
872 .. sourcecode:: pycon+sql
874 >>> print(func.stats.yield_curve(5, 10))
875 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
877 SQLAlchemy can be made aware of the return type of functions to enable
878 type-specific lexical and result-based behavior. For example, to ensure
879 that a string-based function returns a Unicode value and is similarly
880 treated as a string in expressions, specify
881 :class:`~sqlalchemy.types.Unicode` as the type:
883 .. sourcecode:: pycon+sql
885 >>> print(
886 ... func.my_string("hi", type_=Unicode)
887 ... + " "
888 ... + func.my_string("there", type_=Unicode)
889 ... )
890 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
892 The object returned by a :data:`.func` call is usually an instance of
893 :class:`.Function`.
894 This object meets the "column" interface, including comparison and labeling
895 functions. The object can also be passed the :meth:`~.Connectable.execute`
896 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
897 where it will be
898 wrapped inside of a SELECT statement first::
900 print(connection.execute(func.current_timestamp()).scalar())
902 In a few exception cases, the :data:`.func` accessor
903 will redirect a name to a built-in expression such as :func:`.cast`
904 or :func:`.extract`, as these names have well-known meaning
905 but are not exactly the same as "functions" from a SQLAlchemy
906 perspective.
908 Functions which are interpreted as "generic" functions know how to
909 calculate their return type automatically. For a listing of known generic
910 functions, see :ref:`generic_functions`.
912 .. note::
914 The :data:`.func` construct has only limited support for calling
915 standalone "stored procedures", especially those with special
916 parameterization concerns.
918 See the section :ref:`stored_procedures` for details on how to use
919 the DBAPI-level ``callproc()`` method for fully traditional stored
920 procedures.
922 .. seealso::
924 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
926 :class:`.Function`
928 """ # noqa
930 def __init__(self, **opts: Any) -> None:
931 self.__names: List[str] = []
932 self.opts = opts
934 def __getattr__(self, name: str) -> _FunctionGenerator:
935 # passthru __ attributes; fixes pydoc
936 if name.startswith("__"):
937 try:
938 return self.__dict__[name] # type: ignore
939 except KeyError:
940 raise AttributeError(name)
942 elif name.endswith("_"):
943 name = name[0:-1]
944 f = _FunctionGenerator(**self.opts)
945 f.__names = list(self.__names) + [name]
946 return f
948 @overload
949 def __call__(
950 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
951 ) -> Function[_T]: ...
953 @overload
954 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
956 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
957 o = self.opts.copy()
958 o.update(kwargs)
960 tokens = len(self.__names)
962 if tokens == 2:
963 package, fname = self.__names
964 elif tokens == 1:
965 package, fname = "_default", self.__names[0]
966 else:
967 package = None
969 if package is not None:
970 func = _registry[package].get(fname.lower())
971 if func is not None:
972 return func(*c, **o)
974 return Function(
975 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
976 )
978 if TYPE_CHECKING:
979 # START GENERATED FUNCTION ACCESSORS
981 # code within this block is **programmatically,
982 # statically generated** by tools/generate_sql_functions.py
984 @property
985 def aggregate_strings(self) -> Type[aggregate_strings]: ...
987 @property
988 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
990 # set ColumnElement[_T] as a separate overload, to appease
991 # mypy which seems to not want to accept _T from
992 # _ColumnExpressionArgument. Seems somewhat related to the covariant
993 # _HasClauseElement as of mypy 1.15
995 @overload
996 def array_agg(
997 self,
998 col: ColumnElement[_T],
999 *args: _ColumnExpressionOrLiteralArgument[Any],
1000 **kwargs: Any,
1001 ) -> array_agg[_T]: ...
1003 @overload
1004 def array_agg(
1005 self,
1006 col: _ColumnExpressionArgument[_T],
1007 *args: _ColumnExpressionOrLiteralArgument[Any],
1008 **kwargs: Any,
1009 ) -> array_agg[_T]: ...
1011 @overload
1012 def array_agg(
1013 self,
1014 col: _T,
1015 *args: _ColumnExpressionOrLiteralArgument[Any],
1016 **kwargs: Any,
1017 ) -> array_agg[_T]: ...
1019 def array_agg(
1020 self,
1021 col: _ColumnExpressionOrLiteralArgument[_T],
1022 *args: _ColumnExpressionOrLiteralArgument[Any],
1023 **kwargs: Any,
1024 ) -> array_agg[_T]: ...
1026 @property
1027 def cast(self) -> Type[Cast[Any]]: ...
1029 @property
1030 def char_length(self) -> Type[char_length]: ...
1032 # set ColumnElement[_T] as a separate overload, to appease
1033 # mypy which seems to not want to accept _T from
1034 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1035 # _HasClauseElement as of mypy 1.15
1037 @overload
1038 def coalesce(
1039 self,
1040 col: ColumnElement[_T],
1041 *args: _ColumnExpressionOrLiteralArgument[Any],
1042 **kwargs: Any,
1043 ) -> coalesce[_T]: ...
1045 @overload
1046 def coalesce(
1047 self,
1048 col: _ColumnExpressionArgument[_T],
1049 *args: _ColumnExpressionOrLiteralArgument[Any],
1050 **kwargs: Any,
1051 ) -> coalesce[_T]: ...
1053 @overload
1054 def coalesce(
1055 self,
1056 col: _T,
1057 *args: _ColumnExpressionOrLiteralArgument[Any],
1058 **kwargs: Any,
1059 ) -> coalesce[_T]: ...
1061 def coalesce(
1062 self,
1063 col: _ColumnExpressionOrLiteralArgument[_T],
1064 *args: _ColumnExpressionOrLiteralArgument[Any],
1065 **kwargs: Any,
1066 ) -> coalesce[_T]: ...
1068 @property
1069 def concat(self) -> Type[concat]: ...
1071 @property
1072 def count(self) -> Type[count]: ...
1074 @property
1075 def cube(self) -> Type[cube[Any]]: ...
1077 @property
1078 def cume_dist(self) -> Type[cume_dist]: ...
1080 @property
1081 def current_date(self) -> Type[current_date]: ...
1083 @property
1084 def current_time(self) -> Type[current_time]: ...
1086 @property
1087 def current_timestamp(self) -> Type[current_timestamp]: ...
1089 @property
1090 def current_user(self) -> Type[current_user]: ...
1092 @property
1093 def dense_rank(self) -> Type[dense_rank]: ...
1095 @property
1096 def extract(self) -> Type[Extract]: ...
1098 @property
1099 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1101 @property
1102 def localtime(self) -> Type[localtime]: ...
1104 @property
1105 def localtimestamp(self) -> Type[localtimestamp]: ...
1107 # set ColumnElement[_T] as a separate overload, to appease
1108 # mypy which seems to not want to accept _T from
1109 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1110 # _HasClauseElement as of mypy 1.15
1112 @overload
1113 def max( # noqa: A001
1114 self,
1115 col: ColumnElement[_T],
1116 *args: _ColumnExpressionOrLiteralArgument[Any],
1117 **kwargs: Any,
1118 ) -> max[_T]: ...
1120 @overload
1121 def max( # noqa: A001
1122 self,
1123 col: _ColumnExpressionArgument[_T],
1124 *args: _ColumnExpressionOrLiteralArgument[Any],
1125 **kwargs: Any,
1126 ) -> max[_T]: ...
1128 @overload
1129 def max( # noqa: A001
1130 self,
1131 col: _T,
1132 *args: _ColumnExpressionOrLiteralArgument[Any],
1133 **kwargs: Any,
1134 ) -> max[_T]: ...
1136 def max( # noqa: A001
1137 self,
1138 col: _ColumnExpressionOrLiteralArgument[_T],
1139 *args: _ColumnExpressionOrLiteralArgument[Any],
1140 **kwargs: Any,
1141 ) -> max[_T]: ...
1143 # set ColumnElement[_T] as a separate overload, to appease
1144 # mypy which seems to not want to accept _T from
1145 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1146 # _HasClauseElement as of mypy 1.15
1148 @overload
1149 def min( # noqa: A001
1150 self,
1151 col: ColumnElement[_T],
1152 *args: _ColumnExpressionOrLiteralArgument[Any],
1153 **kwargs: Any,
1154 ) -> min[_T]: ...
1156 @overload
1157 def min( # noqa: A001
1158 self,
1159 col: _ColumnExpressionArgument[_T],
1160 *args: _ColumnExpressionOrLiteralArgument[Any],
1161 **kwargs: Any,
1162 ) -> min[_T]: ...
1164 @overload
1165 def min( # noqa: A001
1166 self,
1167 col: _T,
1168 *args: _ColumnExpressionOrLiteralArgument[Any],
1169 **kwargs: Any,
1170 ) -> min[_T]: ...
1172 def min( # noqa: A001
1173 self,
1174 col: _ColumnExpressionOrLiteralArgument[_T],
1175 *args: _ColumnExpressionOrLiteralArgument[Any],
1176 **kwargs: Any,
1177 ) -> min[_T]: ...
1179 @property
1180 def mode(self) -> Type[mode[Any]]: ...
1182 @property
1183 def next_value(self) -> Type[next_value]: ...
1185 @property
1186 def now(self) -> Type[now]: ...
1188 @property
1189 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1191 @property
1192 def percent_rank(self) -> Type[percent_rank]: ...
1194 @property
1195 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1197 @property
1198 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1200 # set ColumnElement[_T] as a separate overload, to appease
1201 # mypy which seems to not want to accept _T from
1202 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1203 # _HasClauseElement as of mypy 1.15
1205 @overload
1206 def pow( # noqa: A001
1207 self,
1208 col: ColumnElement[_T],
1209 *args: _ColumnExpressionOrLiteralArgument[Any],
1210 **kwargs: Any,
1211 ) -> pow[_T]: ...
1213 @overload
1214 def pow( # noqa: A001
1215 self,
1216 col: _ColumnExpressionArgument[_T],
1217 *args: _ColumnExpressionOrLiteralArgument[Any],
1218 **kwargs: Any,
1219 ) -> pow[_T]: ...
1221 @overload
1222 def pow( # noqa: A001
1223 self,
1224 col: _T,
1225 *args: _ColumnExpressionOrLiteralArgument[Any],
1226 **kwargs: Any,
1227 ) -> pow[_T]: ...
1229 def pow( # noqa: A001
1230 self,
1231 col: _ColumnExpressionOrLiteralArgument[_T],
1232 *args: _ColumnExpressionOrLiteralArgument[Any],
1233 **kwargs: Any,
1234 ) -> pow[_T]: ...
1236 @property
1237 def random(self) -> Type[random]: ...
1239 @property
1240 def rank(self) -> Type[rank]: ...
1242 @property
1243 def rollup(self) -> Type[rollup[Any]]: ...
1245 @property
1246 def session_user(self) -> Type[session_user]: ...
1248 # set ColumnElement[_T] as a separate overload, to appease
1249 # mypy which seems to not want to accept _T from
1250 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1251 # _HasClauseElement as of mypy 1.15
1253 @overload
1254 def sum( # noqa: A001
1255 self,
1256 col: ColumnElement[_T],
1257 *args: _ColumnExpressionOrLiteralArgument[Any],
1258 **kwargs: Any,
1259 ) -> sum[_T]: ...
1261 @overload
1262 def sum( # noqa: A001
1263 self,
1264 col: _ColumnExpressionArgument[_T],
1265 *args: _ColumnExpressionOrLiteralArgument[Any],
1266 **kwargs: Any,
1267 ) -> sum[_T]: ...
1269 @overload
1270 def sum( # noqa: A001
1271 self,
1272 col: _T,
1273 *args: _ColumnExpressionOrLiteralArgument[Any],
1274 **kwargs: Any,
1275 ) -> sum[_T]: ...
1277 def sum( # noqa: A001
1278 self,
1279 col: _ColumnExpressionOrLiteralArgument[_T],
1280 *args: _ColumnExpressionOrLiteralArgument[Any],
1281 **kwargs: Any,
1282 ) -> sum[_T]: ...
1284 @property
1285 def sysdate(self) -> Type[sysdate]: ...
1287 @property
1288 def user(self) -> Type[user]: ...
1290 # END GENERATED FUNCTION ACCESSORS
1293func = _FunctionGenerator()
1294func.__doc__ = _FunctionGenerator.__doc__
1296modifier = _FunctionGenerator(group=False)
1299class Function(FunctionElement[_T]):
1300 r"""Describe a named SQL function.
1302 The :class:`.Function` object is typically generated from the
1303 :data:`.func` generation object.
1306 :param \*clauses: list of column expressions that form the arguments
1307 of the SQL function call.
1309 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1310 used as the return value of the column expression generated by this
1311 function call.
1313 :param packagenames: a string which indicates package prefix names
1314 to be prepended to the function name when the SQL is generated.
1315 The :data:`.func` generator creates these when it is called using
1316 dotted format, e.g.::
1318 func.mypackage.some_function(col1, col2)
1320 .. seealso::
1322 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1324 :data:`.func` - namespace which produces registered or ad-hoc
1325 :class:`.Function` instances.
1327 :class:`.GenericFunction` - allows creation of registered function
1328 types.
1330 """
1332 __visit_name__ = "function"
1334 _traverse_internals = FunctionElement._traverse_internals + [
1335 ("packagenames", InternalTraversal.dp_plain_obj),
1336 ("name", InternalTraversal.dp_string),
1337 ("type", InternalTraversal.dp_type),
1338 ]
1340 name: str
1342 identifier: str
1344 type: TypeEngine[_T]
1345 """A :class:`_types.TypeEngine` object which refers to the SQL return
1346 type represented by this SQL function.
1348 This datatype may be configured when generating a
1349 :class:`_functions.Function` object by passing the
1350 :paramref:`_functions.Function.type_` parameter, e.g.::
1352 >>> select(func.lower("some VALUE", type_=String))
1354 The small number of built-in classes of :class:`_functions.Function` come
1355 with a built-in datatype that's appropriate to the class of function and
1356 its arguments. For functions that aren't known, the type defaults to the
1357 "null type".
1359 """
1361 @overload
1362 def __init__(
1363 self,
1364 name: str,
1365 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1366 type_: None = ...,
1367 packagenames: Optional[Tuple[str, ...]] = ...,
1368 ) -> None: ...
1370 @overload
1371 def __init__(
1372 self,
1373 name: str,
1374 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1375 type_: _TypeEngineArgument[_T] = ...,
1376 packagenames: Optional[Tuple[str, ...]] = ...,
1377 ) -> None: ...
1379 def __init__(
1380 self,
1381 name: str,
1382 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1383 type_: Optional[_TypeEngineArgument[_T]] = None,
1384 packagenames: Optional[Tuple[str, ...]] = None,
1385 ) -> None:
1386 """Construct a :class:`.Function`.
1388 The :data:`.func` construct is normally used to construct
1389 new :class:`.Function` instances.
1391 """
1392 self.packagenames = packagenames or ()
1393 self.name = name
1395 # if type is None, we get NULLTYPE, which is our _T. But I don't
1396 # know how to get the overloads to express that correctly
1397 self.type = type_api.to_instance(type_) # type: ignore
1399 FunctionElement.__init__(self, *clauses)
1401 def _bind_param(
1402 self,
1403 operator: OperatorType,
1404 obj: Any,
1405 type_: Optional[TypeEngine[_T]] = None,
1406 expanding: bool = False,
1407 **kw: Any,
1408 ) -> BindParameter[_T]:
1409 return BindParameter(
1410 self.name,
1411 obj,
1412 _compared_to_operator=operator,
1413 _compared_to_type=self.type,
1414 type_=type_,
1415 unique=True,
1416 expanding=expanding,
1417 **kw,
1418 )
1421class GenericFunction(Function[_T]):
1422 """Define a 'generic' function.
1424 A generic function is a pre-established :class:`.Function`
1425 class that is instantiated automatically when called
1426 by name from the :data:`.func` attribute. Note that
1427 calling any name from :data:`.func` has the effect that
1428 a new :class:`.Function` instance is created automatically,
1429 given that name. The primary use case for defining
1430 a :class:`.GenericFunction` class is so that a function
1431 of a particular name may be given a fixed return type.
1432 It can also include custom argument parsing schemes as well
1433 as additional methods.
1435 Subclasses of :class:`.GenericFunction` are automatically
1436 registered under the name of the class. For
1437 example, a user-defined function ``as_utc()`` would
1438 be available immediately::
1440 from sqlalchemy.sql.functions import GenericFunction
1441 from sqlalchemy.types import DateTime
1444 class as_utc(GenericFunction):
1445 type = DateTime()
1446 inherit_cache = True
1449 print(select(func.as_utc()))
1451 User-defined generic functions can be organized into
1452 packages by specifying the "package" attribute when defining
1453 :class:`.GenericFunction`. Third party libraries
1454 containing many functions may want to use this in order
1455 to avoid name conflicts with other systems. For example,
1456 if our ``as_utc()`` function were part of a package
1457 "time"::
1459 class as_utc(GenericFunction):
1460 type = DateTime()
1461 package = "time"
1462 inherit_cache = True
1464 The above function would be available from :data:`.func`
1465 using the package name ``time``::
1467 print(select(func.time.as_utc()))
1469 A final option is to allow the function to be accessed
1470 from one name in :data:`.func` but to render as a different name.
1471 The ``identifier`` attribute will override the name used to
1472 access the function as loaded from :data:`.func`, but will retain
1473 the usage of ``name`` as the rendered name::
1475 class GeoBuffer(GenericFunction):
1476 type = Geometry()
1477 package = "geo"
1478 name = "ST_Buffer"
1479 identifier = "buffer"
1480 inherit_cache = True
1482 The above function will render as follows:
1484 .. sourcecode:: pycon+sql
1486 >>> print(func.geo.buffer())
1487 {printsql}ST_Buffer()
1489 The name will be rendered as is, however without quoting unless the name
1490 contains special characters that require quoting. To force quoting
1491 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1492 construct::
1494 from sqlalchemy.sql import quoted_name
1497 class GeoBuffer(GenericFunction):
1498 type = Geometry()
1499 package = "geo"
1500 name = quoted_name("ST_Buffer", True)
1501 identifier = "buffer"
1502 inherit_cache = True
1504 The above function will render as:
1506 .. sourcecode:: pycon+sql
1508 >>> print(func.geo.buffer())
1509 {printsql}"ST_Buffer"()
1511 Type parameters for this class as a
1512 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1513 and should match the type seen in a :class:`_engine.Result`. For example::
1515 class as_utc(GenericFunction[datetime.datetime]):
1516 type = DateTime()
1517 inherit_cache = True
1519 The above indicates that the following expression returns a ``datetime``
1520 object::
1522 connection.scalar(select(func.as_utc()))
1524 """
1526 coerce_arguments = True
1527 inherit_cache = True
1529 _register: bool
1531 name = "GenericFunction"
1533 def __init_subclass__(cls) -> None:
1534 if annotation.Annotated not in cls.__mro__:
1535 cls._register_generic_function(cls.__name__, cls.__dict__)
1536 super().__init_subclass__()
1538 @classmethod
1539 def _register_generic_function(
1540 cls, clsname: str, clsdict: Mapping[str, Any]
1541 ) -> None:
1542 cls.name = name = clsdict.get("name", clsname)
1543 cls.identifier = identifier = clsdict.get("identifier", name)
1544 package = clsdict.get("package", "_default")
1545 # legacy
1546 if "__return_type__" in clsdict:
1547 cls.type = clsdict["__return_type__"]
1549 # Check _register attribute status
1550 cls._register = getattr(cls, "_register", True)
1552 # Register the function if required
1553 if cls._register:
1554 register_function(identifier, cls, package)
1555 else:
1556 # Set _register to True to register child classes by default
1557 cls._register = True
1559 def __init__(
1560 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1561 ) -> None:
1562 parsed_args = kwargs.pop("_parsed_args", None)
1563 if parsed_args is None:
1564 parsed_args = [
1565 coercions.expect(
1566 roles.ExpressionElementRole,
1567 c,
1568 name=self.name,
1569 apply_propagate_attrs=self,
1570 )
1571 for c in args
1572 ]
1573 self._has_args = self._has_args or bool(parsed_args)
1574 self.packagenames = ()
1576 self.clause_expr = Grouping(
1577 ClauseList(
1578 operator=operators.comma_op, group_contents=True, *parsed_args
1579 )
1580 )
1582 self.type = type_api.to_instance( # type: ignore
1583 kwargs.pop("type_", None) or getattr(self, "type", None)
1584 )
1587register_function("cast", Cast) # type: ignore
1588register_function("extract", Extract) # type: ignore
1591class next_value(GenericFunction[int]):
1592 """Represent the 'next value', given a :class:`.Sequence`
1593 as its single argument.
1595 Compiles into the appropriate function on each backend,
1596 or will raise NotImplementedError if used on a backend
1597 that does not provide support for sequences.
1599 """
1601 type = sqltypes.Integer()
1602 name = "next_value"
1604 _traverse_internals = [
1605 ("sequence", InternalTraversal.dp_named_ddl_element)
1606 ]
1608 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1609 assert isinstance(
1610 seq, schema.Sequence
1611 ), "next_value() accepts a Sequence object as input."
1612 self.sequence = seq
1613 self.type = sqltypes.to_instance( # type: ignore
1614 seq.data_type or getattr(self, "type", None)
1615 )
1617 def compare(self, other: Any, **kw: Any) -> bool:
1618 return (
1619 isinstance(other, next_value)
1620 and self.sequence.name == other.sequence.name
1621 )
1623 @property
1624 def _from_objects(self) -> Any:
1625 return []
1628class AnsiFunction(GenericFunction[_T]):
1629 """Define a function in "ansi" format, which doesn't render parenthesis."""
1631 inherit_cache = True
1633 def __init__(
1634 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1635 ) -> None:
1636 GenericFunction.__init__(self, *args, **kwargs)
1639class ReturnTypeFromArgs(GenericFunction[_T]):
1640 """Define a function whose return type is bound to the type of its
1641 arguments.
1642 """
1644 inherit_cache = True
1646 # set ColumnElement[_T] as a separate overload, to appease
1647 # mypy which seems to not want to accept _T from
1648 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1649 # _HasClauseElement as of mypy 1.15
1651 @overload
1652 def __init__(
1653 self,
1654 col: ColumnElement[_T],
1655 *args: _ColumnExpressionOrLiteralArgument[Any],
1656 **kwargs: Any,
1657 ) -> None: ...
1659 @overload
1660 def __init__(
1661 self,
1662 col: _ColumnExpressionArgument[_T],
1663 *args: _ColumnExpressionOrLiteralArgument[Any],
1664 **kwargs: Any,
1665 ) -> None: ...
1667 @overload
1668 def __init__(
1669 self,
1670 col: _T,
1671 *args: _ColumnExpressionOrLiteralArgument[Any],
1672 **kwargs: Any,
1673 ) -> None: ...
1675 def __init__(
1676 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1677 ) -> None:
1678 fn_args: Sequence[ColumnElement[Any]] = [
1679 coercions.expect(
1680 roles.ExpressionElementRole,
1681 c,
1682 name=self.name,
1683 apply_propagate_attrs=self,
1684 )
1685 for c in args
1686 ]
1687 kwargs.setdefault("type_", _type_from_args(fn_args))
1688 kwargs["_parsed_args"] = fn_args
1689 super().__init__(*fn_args, **kwargs)
1692class coalesce(ReturnTypeFromArgs[_T]):
1693 _has_args = True
1694 inherit_cache = True
1697class max(ReturnTypeFromArgs[_T]): # noqa: A001
1698 """The SQL MAX() aggregate function."""
1700 inherit_cache = True
1703class min(ReturnTypeFromArgs[_T]): # noqa: A001
1704 """The SQL MIN() aggregate function."""
1706 inherit_cache = True
1709class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1710 """The SQL SUM() aggregate function."""
1712 inherit_cache = True
1715class now(GenericFunction[datetime.datetime]):
1716 """The SQL now() datetime function.
1718 SQLAlchemy dialects will usually render this particular function
1719 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1721 """
1723 type = sqltypes.DateTime()
1724 inherit_cache = True
1727class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1728 """The SQL POW() function which performs the power operator.
1730 E.g.:
1732 .. sourcecode:: pycon+sql
1734 >>> print(select(func.pow(2, 8)))
1735 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1737 .. versionadded:: 2.1
1739 """
1741 inherit_cache = True
1744class concat(GenericFunction[str]):
1745 """The SQL CONCAT() function, which concatenates strings.
1747 E.g.:
1749 .. sourcecode:: pycon+sql
1751 >>> print(select(func.concat("a", "b")))
1752 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1754 String concatenation in SQLAlchemy is more commonly available using the
1755 Python ``+`` operator with string datatypes, which will render a
1756 backend-specific concatenation operator, such as :
1758 .. sourcecode:: pycon+sql
1760 >>> print(select(literal("a") + "b"))
1761 {printsql}SELECT :param_1 || :param_2 AS anon_1
1764 """
1766 type = sqltypes.String()
1767 inherit_cache = True
1770class char_length(GenericFunction[int]):
1771 """The CHAR_LENGTH() SQL function."""
1773 type = sqltypes.Integer()
1774 inherit_cache = True
1776 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1777 # slight hack to limit to just one positional argument
1778 # not sure why this one function has this special treatment
1779 super().__init__(arg, **kw)
1782class random(GenericFunction[float]):
1783 """The RANDOM() SQL function."""
1785 _has_args = True
1786 inherit_cache = True
1789class count(GenericFunction[int]):
1790 r"""The ANSI COUNT aggregate function. With no arguments,
1791 emits COUNT \*.
1793 E.g.::
1795 from sqlalchemy import func
1796 from sqlalchemy import select
1797 from sqlalchemy import table, column
1799 my_table = table("some_table", column("id"))
1801 stmt = select(func.count()).select_from(my_table)
1803 Executing ``stmt`` would emit:
1805 .. sourcecode:: sql
1807 SELECT count(*) AS count_1
1808 FROM some_table
1811 """
1813 type = sqltypes.Integer()
1814 inherit_cache = True
1816 def __init__(
1817 self,
1818 expression: Union[
1819 _ColumnExpressionArgument[Any], _StarOrOne, None
1820 ] = None,
1821 **kwargs: Any,
1822 ) -> None:
1823 if expression is None:
1824 expression = literal_column("*")
1825 super().__init__(expression, **kwargs)
1828class current_date(AnsiFunction[datetime.date]):
1829 """The CURRENT_DATE() SQL function."""
1831 type = sqltypes.Date()
1832 inherit_cache = True
1835class current_time(AnsiFunction[datetime.time]):
1836 """The CURRENT_TIME() SQL function."""
1838 type = sqltypes.Time()
1839 inherit_cache = True
1842class current_timestamp(AnsiFunction[datetime.datetime]):
1843 """The CURRENT_TIMESTAMP() SQL function."""
1845 type = sqltypes.DateTime()
1846 inherit_cache = True
1849class current_user(AnsiFunction[str]):
1850 """The CURRENT_USER() SQL function."""
1852 type = sqltypes.String()
1853 inherit_cache = True
1856class localtime(AnsiFunction[datetime.datetime]):
1857 """The localtime() SQL function."""
1859 type = sqltypes.DateTime()
1860 inherit_cache = True
1863class localtimestamp(AnsiFunction[datetime.datetime]):
1864 """The localtimestamp() SQL function."""
1866 type = sqltypes.DateTime()
1867 inherit_cache = True
1870class session_user(AnsiFunction[str]):
1871 """The SESSION_USER() SQL function."""
1873 type = sqltypes.String()
1874 inherit_cache = True
1877class sysdate(AnsiFunction[datetime.datetime]):
1878 """The SYSDATE() SQL function."""
1880 type = sqltypes.DateTime()
1881 inherit_cache = True
1884class user(AnsiFunction[str]):
1885 """The USER() SQL function."""
1887 type = sqltypes.String()
1888 inherit_cache = True
1891class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1892 """Support for the ARRAY_AGG function.
1894 The ``func.array_agg(expr)`` construct returns an expression of
1895 type :class:`_types.ARRAY`.
1897 e.g.::
1899 stmt = select(func.array_agg(table.c.values)[2:5])
1901 .. seealso::
1903 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1904 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1905 added.
1907 """
1909 inherit_cache = True
1911 def __init__(
1912 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1913 ) -> None:
1914 fn_args: Sequence[ColumnElement[Any]] = [
1915 coercions.expect(
1916 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1917 )
1918 for c in args
1919 ]
1921 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1922 if "type_" not in kwargs:
1923 type_from_args = _type_from_args(fn_args)
1924 if isinstance(type_from_args, sqltypes.ARRAY):
1925 kwargs["type_"] = type_from_args
1926 else:
1927 kwargs["type_"] = default_array_type(
1928 type_from_args, dimensions=1
1929 )
1930 kwargs["_parsed_args"] = fn_args
1931 super().__init__(*fn_args, **kwargs)
1934class OrderedSetAgg(GenericFunction[_T]):
1935 """Define a function where the return type is based on the sort
1936 expression type as defined by the expression passed to the
1937 :meth:`.FunctionElement.within_group` method."""
1939 array_for_multi_clause = False
1940 inherit_cache = True
1942 def within_group_type(
1943 self, within_group: WithinGroup[Any]
1944 ) -> TypeEngine[Any]:
1945 func_clauses = cast(ClauseList, self.clause_expr.element)
1946 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
1947 within_group.order_by
1948 )
1949 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1950 return sqltypes.ARRAY(order_by[0].type)
1951 else:
1952 return order_by[0].type
1955class mode(OrderedSetAgg[_T]):
1956 """Implement the ``mode`` ordered-set aggregate function.
1958 This function must be used with the :meth:`.FunctionElement.within_group`
1959 modifier to supply a sort expression to operate upon.
1961 The return type of this function is the same as the sort expression.
1963 """
1965 inherit_cache = True
1968class percentile_cont(OrderedSetAgg[_T]):
1969 """Implement the ``percentile_cont`` ordered-set aggregate function.
1971 This function must be used with the :meth:`.FunctionElement.within_group`
1972 modifier to supply a sort expression to operate upon.
1974 The return type of this function is the same as the sort expression,
1975 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1976 expression's type.
1978 """
1980 array_for_multi_clause = True
1981 inherit_cache = True
1984class percentile_disc(OrderedSetAgg[_T]):
1985 """Implement the ``percentile_disc`` ordered-set aggregate function.
1987 This function must be used with the :meth:`.FunctionElement.within_group`
1988 modifier to supply a sort expression to operate upon.
1990 The return type of this function is the same as the sort expression,
1991 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1992 expression's type.
1994 """
1996 array_for_multi_clause = True
1997 inherit_cache = True
2000class rank(GenericFunction[int]):
2001 """Implement the ``rank`` hypothetical-set aggregate function.
2003 This function must be used with the :meth:`.FunctionElement.within_group`
2004 modifier to supply a sort expression to operate upon.
2006 The return type of this function is :class:`.Integer`.
2008 """
2010 type = sqltypes.Integer()
2011 inherit_cache = True
2014class dense_rank(GenericFunction[int]):
2015 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2017 This function must be used with the :meth:`.FunctionElement.within_group`
2018 modifier to supply a sort expression to operate upon.
2020 The return type of this function is :class:`.Integer`.
2022 """
2024 type = sqltypes.Integer()
2025 inherit_cache = True
2028class percent_rank(GenericFunction[decimal.Decimal]):
2029 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2031 This function must be used with the :meth:`.FunctionElement.within_group`
2032 modifier to supply a sort expression to operate upon.
2034 The return type of this function is :class:`.Numeric`.
2036 """
2038 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2039 inherit_cache = True
2042class cume_dist(GenericFunction[decimal.Decimal]):
2043 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2045 This function must be used with the :meth:`.FunctionElement.within_group`
2046 modifier to supply a sort expression to operate upon.
2048 The return type of this function is :class:`.Numeric`.
2050 """
2052 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2053 inherit_cache = True
2056class cube(GenericFunction[_T]):
2057 r"""Implement the ``CUBE`` grouping operation.
2059 This function is used as part of the GROUP BY of a statement,
2060 e.g. :meth:`_expression.Select.group_by`::
2062 stmt = select(
2063 func.sum(table.c.value), table.c.col_1, table.c.col_2
2064 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2066 """
2068 _has_args = True
2069 inherit_cache = True
2072class rollup(GenericFunction[_T]):
2073 r"""Implement the ``ROLLUP`` grouping operation.
2075 This function is used as part of the GROUP BY of a statement,
2076 e.g. :meth:`_expression.Select.group_by`::
2078 stmt = select(
2079 func.sum(table.c.value), table.c.col_1, table.c.col_2
2080 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2082 """
2084 _has_args = True
2085 inherit_cache = True
2088class grouping_sets(GenericFunction[_T]):
2089 r"""Implement the ``GROUPING SETS`` grouping operation.
2091 This function is used as part of the GROUP BY of a statement,
2092 e.g. :meth:`_expression.Select.group_by`::
2094 stmt = select(
2095 func.sum(table.c.value), table.c.col_1, table.c.col_2
2096 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2098 In order to group by multiple sets, use the :func:`.tuple_` construct::
2100 from sqlalchemy import tuple_
2102 stmt = select(
2103 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2104 ).group_by(
2105 func.grouping_sets(
2106 tuple_(table.c.col_1, table.c.col_2),
2107 tuple_(table.c.value, table.c.col_3),
2108 )
2109 )
2111 """ # noqa: E501
2113 _has_args = True
2114 inherit_cache = True
2117class aggregate_strings(GenericFunction[str]):
2118 """Implement a generic string aggregation function.
2120 This function will concatenate non-null values into a string and
2121 separate the values by a delimiter.
2123 This function is compiled on a per-backend basis, into functions
2124 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2126 e.g. Example usage with delimiter '.'::
2128 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2130 The return type of this function is :class:`.String`.
2132 .. versionadded:: 2.0.21
2134 """
2136 type = sqltypes.String()
2137 _has_args = True
2138 inherit_cache = True
2140 def __init__(
2141 self, clause: _ColumnExpressionArgument[Any], separator: str
2142 ) -> None:
2143 super().__init__(clause, separator)