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 AggregateOrderBy
44from .elements import BinaryExpression
45from .elements import BindParameter
46from .elements import Cast
47from .elements import ClauseList
48from .elements import ColumnElement
49from .elements import Extract
50from .elements import FunctionFilter
51from .elements import Grouping
52from .elements import literal_column
53from .elements import NamedColumn
54from .elements import Over
55from .elements import WithinGroup
56from .selectable import FromClause
57from .selectable import Select
58from .selectable import TableValuedAlias
59from .sqltypes import TableValueType
60from .type_api import TypeEngine
61from .visitors import InternalTraversal
62from .. import util
65if TYPE_CHECKING:
66 from ._typing import _ByArgument
67 from ._typing import _ColumnExpressionArgument
68 from ._typing import _ColumnExpressionOrLiteralArgument
69 from ._typing import _ColumnExpressionOrStrLabelArgument
70 from ._typing import _StarOrOne
71 from ._typing import _TypeEngineArgument
72 from .base import _EntityNamespace
73 from .elements import ClauseElement
74 from .elements import KeyedColumnElement
75 from .elements import TableValuedColumn
76 from .operators import OperatorType
77 from ..engine.base import Connection
78 from ..engine.cursor import CursorResult
79 from ..engine.interfaces import _CoreMultiExecuteParams
80 from ..engine.interfaces import CoreExecuteOptionsParameter
81 from ..util.typing import Self
83_T = TypeVar("_T", bound=Any)
84_S = TypeVar("_S", bound=Any)
86_registry: util.defaultdict[str, Dict[str, Type[Function[Any]]]] = (
87 util.defaultdict(dict)
88)
91def register_function(
92 identifier: str, fn: Type[Function[Any]], package: str = "_default"
93) -> None:
94 """Associate a callable with a particular func. name.
96 This is normally called by GenericFunction, but is also
97 available by itself so that a non-Function construct
98 can be associated with the :data:`.func` accessor (i.e.
99 CAST, EXTRACT).
101 """
102 reg = _registry[package]
104 identifier = str(identifier).lower()
106 # Check if a function with the same identifier is registered.
107 if identifier in reg:
108 util.warn(
109 "The GenericFunction '{}' is already registered and "
110 "is going to be overridden.".format(identifier)
111 )
112 reg[identifier] = fn
115class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
116 """Base for SQL function-oriented constructs.
118 This is a `generic type <https://peps.python.org/pep-0484/#generics>`_,
119 meaning that type checkers and IDEs can be instructed on the types to
120 expect in a :class:`_engine.Result` for this function. See
121 :class:`.GenericFunction` for an example of how this is done.
123 .. seealso::
125 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
127 :class:`.Function` - named SQL function.
129 :data:`.func` - namespace which produces registered or ad-hoc
130 :class:`.Function` instances.
132 :class:`.GenericFunction` - allows creation of registered function
133 types.
135 """
137 _traverse_internals = [
138 ("clause_expr", InternalTraversal.dp_clauseelement),
139 ("_with_ordinality", InternalTraversal.dp_boolean),
140 ("_table_value_type", InternalTraversal.dp_has_cache_key),
141 ] + Executable._executable_traverse_internals
143 packagenames: Tuple[str, ...] = ()
145 _has_args = False
146 _with_ordinality = False
147 _table_value_type: Optional[TableValueType] = None
149 # some attributes that are defined between both ColumnElement and
150 # FromClause are set to Any here to avoid typing errors
151 primary_key: Any
152 _is_clone_of: Any
154 clause_expr: Grouping[Any]
156 def __init__(
157 self, *clauses: _ColumnExpressionOrLiteralArgument[Any]
158 ) -> None:
159 r"""Construct a :class:`.FunctionElement`.
161 :param \*clauses: list of column expressions that form the arguments
162 of the SQL function call.
164 :param \**kwargs: additional kwargs are typically consumed by
165 subclasses.
167 .. seealso::
169 :data:`.func`
171 :class:`.Function`
173 """
174 args: Sequence[_ColumnExpressionArgument[Any]] = [
175 coercions.expect(
176 roles.ExpressionElementRole,
177 c,
178 name=getattr(self, "name", None),
179 apply_propagate_attrs=self,
180 )
181 for c in clauses
182 ]
183 self._has_args = self._has_args or bool(args)
184 self.clause_expr = Grouping(
185 ClauseList(operator=operators.comma_op, group_contents=True, *args)
186 )
188 _non_anon_label = None
190 @property
191 def _proxy_key(self) -> Any:
192 return super()._proxy_key or getattr(self, "name", None)
194 def _execute_on_connection(
195 self,
196 connection: Connection,
197 distilled_params: _CoreMultiExecuteParams,
198 execution_options: CoreExecuteOptionsParameter,
199 ) -> CursorResult[Any]:
200 return connection._execute_function(
201 self, distilled_params, execution_options
202 )
204 def scalar_table_valued(
205 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
206 ) -> ScalarFunctionColumn[_T]:
207 """Return a column expression that's against this
208 :class:`_functions.FunctionElement` as a scalar
209 table-valued expression.
211 The returned expression is similar to that returned by a single column
212 accessed off of a :meth:`_functions.FunctionElement.table_valued`
213 construct, except no FROM clause is generated; the function is rendered
214 in the similar way as a scalar subquery.
216 E.g.:
218 .. sourcecode:: pycon+sql
220 >>> from sqlalchemy import func, select
221 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
222 >>> print(select(fn))
223 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
225 .. versionadded:: 1.4.0b2
227 .. seealso::
229 :meth:`_functions.FunctionElement.table_valued`
231 :meth:`_functions.FunctionElement.alias`
233 :meth:`_functions.FunctionElement.column_valued`
235 """ # noqa: E501
237 return ScalarFunctionColumn(self, name, type_)
239 def table_valued(
240 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
241 ) -> TableValuedAlias:
242 r"""Return a :class:`_sql.TableValuedAlias` representation of this
243 :class:`_functions.FunctionElement` with table-valued expressions added.
245 e.g.:
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 A WITH ORDINALITY expression may be generated by passing the keyword
263 argument "with_ordinality":
265 .. sourcecode:: pycon+sql
267 >>> fn = func.generate_series(4, 1, -1).table_valued(
268 ... "gen", with_ordinality="ordinality"
269 ... )
270 >>> print(select(fn))
271 {printsql}SELECT anon_1.gen, anon_1.ordinality
272 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1
274 :param \*expr: A series of string column names that will be added to the
275 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
276 construct as columns. :func:`_sql.column` objects with or without
277 datatypes may also be used.
279 :param name: optional name to assign to the alias name that's generated.
280 If omitted, a unique anonymizing name is used.
282 :param with_ordinality: string name that when present results in the
283 ``WITH ORDINALITY`` clause being added to the alias, and the given
284 string name will be added as a column to the .c collection
285 of the resulting :class:`_sql.TableValuedAlias`.
287 :param joins_implicitly: when True, the table valued function may be
288 used in the FROM clause without any explicit JOIN to other tables
289 in the SQL query, and no "cartesian product" warning will be generated.
290 May be useful for SQL functions such as ``func.json_each()``.
292 .. versionadded:: 1.4.33
294 .. versionadded:: 1.4.0b2
297 .. seealso::
299 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
301 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
303 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
304 :meth:`_functions.FunctionElement.table_valued` which delivers the
305 complete table valued expression as a scalar column expression
307 :meth:`_functions.FunctionElement.column_valued`
309 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
310 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
312 """ # noqa: 501
314 new_func = self._generate()
316 with_ordinality = kw.pop("with_ordinality", None)
317 joins_implicitly = kw.pop("joins_implicitly", None)
318 name = kw.pop("name", None)
320 if with_ordinality:
321 expr += (with_ordinality,)
322 new_func._with_ordinality = True
324 new_func.type = new_func._table_value_type = TableValueType(*expr)
326 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
328 def column_valued(
329 self, name: Optional[str] = None, joins_implicitly: bool = False
330 ) -> TableValuedColumn[_T]:
331 """Return this :class:`_functions.FunctionElement` as a column expression that
332 selects from itself as a FROM clause.
334 E.g.:
336 .. sourcecode:: pycon+sql
338 >>> from sqlalchemy import select, func
339 >>> gs = func.generate_series(1, 5, -1).column_valued()
340 >>> print(select(gs))
341 {printsql}SELECT anon_1
342 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
344 This is shorthand for::
346 gs = func.generate_series(1, 5, -1).alias().column
348 :param name: optional name to assign to the alias name that's generated.
349 If omitted, a unique anonymizing name is used.
351 :param joins_implicitly: when True, the "table" portion of the column
352 valued function may be a member of the FROM clause without any
353 explicit JOIN to other tables in the SQL query, and no "cartesian
354 product" warning will be generated. May be useful for SQL functions
355 such as ``func.json_array_elements()``.
357 .. versionadded:: 1.4.46
359 .. seealso::
361 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
363 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
365 :meth:`_functions.FunctionElement.table_valued`
367 """ # noqa: 501
369 return self.alias(name=name, joins_implicitly=joins_implicitly).column
371 @util.ro_non_memoized_property
372 def columns(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
373 r"""The set of columns exported by this :class:`.FunctionElement`.
375 This is a placeholder collection that allows the function to be
376 placed in the FROM clause of a statement:
378 .. sourcecode:: pycon+sql
380 >>> from sqlalchemy import column, select, func
381 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
382 >>> print(stmt)
383 {printsql}SELECT x, y FROM myfunction()
385 The above form is a legacy feature that is now superseded by the
386 fully capable :meth:`_functions.FunctionElement.table_valued`
387 method; see that method for details.
389 .. seealso::
391 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
392 SQL function expressions.
394 """ # noqa: E501
395 return self.c
397 @util.ro_memoized_property
398 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
399 """synonym for :attr:`.FunctionElement.columns`."""
401 return ColumnCollection(
402 columns=[(col.key, col) for col in self._all_selected_columns]
403 )
405 @property
406 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
407 if is_table_value_type(self.type):
408 # TODO: this might not be fully accurate
409 cols = cast(
410 "Sequence[KeyedColumnElement[Any]]", self.type._elements
411 )
412 else:
413 cols = [self.label(None)]
415 return cols
417 @property
418 def exported_columns( # type: ignore[override]
419 self,
420 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
421 return self.columns
423 @HasMemoized.memoized_attribute
424 def clauses(self) -> ClauseList:
425 """Return the underlying :class:`.ClauseList` which contains
426 the arguments for this :class:`.FunctionElement`.
428 """
429 return cast(ClauseList, self.clause_expr.element)
431 def over(
432 self,
433 *,
434 partition_by: Optional[_ByArgument] = None,
435 order_by: Optional[_ByArgument] = None,
436 rows: Optional[Tuple[Optional[int], Optional[int]]] = None,
437 range_: Optional[Tuple[Optional[int], Optional[int]]] = None,
438 groups: Optional[Tuple[Optional[int], Optional[int]]] = None,
439 ) -> Over[_T]:
440 """Produce an OVER clause against this function.
442 Used against aggregate or so-called "window" functions,
443 for database backends that support window functions.
445 The expression::
447 func.row_number().over(order_by="x")
449 is shorthand for::
451 from sqlalchemy import over
453 over(func.row_number(), order_by="x")
455 See :func:`_expression.over` for a full description.
457 .. seealso::
459 :func:`_expression.over`
461 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
463 """
464 return Over(
465 self,
466 partition_by=partition_by,
467 order_by=order_by,
468 rows=rows,
469 range_=range_,
470 groups=groups,
471 )
473 def aggregate_order_by(
474 self, *order_by: _ColumnExpressionArgument[Any]
475 ) -> AggregateOrderBy[_T]:
476 r"""Produce a :class:`.AggregateOrderBy` object against a function.
478 Used for aggregating functions such as :class:`_functions.array_agg`,
479 ``group_concat``, ``json_agg`` on backends that support ordering via an
480 embedded ORDER BY parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
481 When used on backends like Oracle and SQL Server, SQL compilation uses
482 that of :class:`.WithinGroup`.
484 See :func:`_expression.aggregate_order_by` for a full description.
486 .. versionadded:: 2.0.44 Generalized the PostgreSQL-specific
487 :func:`_postgresql.aggregate_order_by` function to a method on
488 :class:`.Function` that is backend agnostic.
490 .. seealso::
492 :class:`_functions.aggregate_strings` - backend-agnostic string
493 concatenation function which also supports ORDER BY
495 """
497 return AggregateOrderBy(self, *order_by)
499 def within_group(
500 self, *order_by: _ColumnExpressionArgument[Any]
501 ) -> WithinGroup[_T]:
502 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
504 Used against so-called "ordered set aggregate" and "hypothetical
505 set aggregate" functions, including :class:`.percentile_cont`,
506 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
507 used by PostgreSQL, Oracle Database, and Microsoft SQL Server.
509 For simple ORDER BY expressions within aggregate functions on
510 PostgreSQL, MySQL/MariaDB, SQLite, see :func:`_sql.aggregate_order_by`.
512 See :func:`_expression.within_group` for a full description.
514 .. seealso::
516 :ref:`tutorial_functions_within_group` -
517 in the :ref:`unified_tutorial`
520 """
521 return WithinGroup(self, *order_by)
523 @overload
524 def filter(self) -> Self: ...
526 @overload
527 def filter(
528 self,
529 __criterion0: _ColumnExpressionArgument[bool],
530 *criterion: _ColumnExpressionArgument[bool],
531 ) -> FunctionFilter[_T]: ...
533 def filter(
534 self, *criterion: _ColumnExpressionArgument[bool]
535 ) -> Union[Self, FunctionFilter[_T]]:
536 """Produce a FILTER clause against this function.
538 Used against aggregate and window functions,
539 for database backends that support the "FILTER" clause.
541 The expression::
543 func.count(1).filter(True)
545 is shorthand for::
547 from sqlalchemy import funcfilter
549 funcfilter(func.count(1), True)
551 .. seealso::
553 :ref:`tutorial_functions_within_group` -
554 in the :ref:`unified_tutorial`
556 :class:`.FunctionFilter`
558 :func:`.funcfilter`
561 """
562 if not criterion:
563 return self
564 return FunctionFilter(self, *criterion)
566 def as_comparison(
567 self, left_index: int, right_index: int
568 ) -> FunctionAsBinary:
569 """Interpret this expression as a boolean comparison between two
570 values.
572 This method is used for an ORM use case described at
573 :ref:`relationship_custom_operator_sql_function`.
575 A hypothetical SQL function "is_equal()" which compares to values
576 for equality would be written in the Core expression language as::
578 expr = func.is_equal("a", "b")
580 If "is_equal()" above is comparing "a" and "b" for equality, the
581 :meth:`.FunctionElement.as_comparison` method would be invoked as::
583 expr = func.is_equal("a", "b").as_comparison(1, 2)
585 Where above, the integer value "1" refers to the first argument of the
586 "is_equal()" function and the integer value "2" refers to the second.
588 This would create a :class:`.BinaryExpression` that is equivalent to::
590 BinaryExpression("a", "b", operator=op.eq)
592 However, at the SQL level it would still render as
593 "is_equal('a', 'b')".
595 The ORM, when it loads a related object or collection, needs to be able
596 to manipulate the "left" and "right" sides of the ON clause of a JOIN
597 expression. The purpose of this method is to provide a SQL function
598 construct that can also supply this information to the ORM, when used
599 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
600 return value is a containment object called :class:`.FunctionAsBinary`.
602 An ORM example is as follows::
604 class Venue(Base):
605 __tablename__ = "venue"
606 id = Column(Integer, primary_key=True)
607 name = Column(String)
609 descendants = relationship(
610 "Venue",
611 primaryjoin=func.instr(
612 remote(foreign(name)), name + "/"
613 ).as_comparison(1, 2)
614 == 1,
615 viewonly=True,
616 order_by=name,
617 )
619 Above, the "Venue" class can load descendant "Venue" objects by
620 determining if the name of the parent Venue is contained within the
621 start of the hypothetical descendant value's name, e.g. "parent1" would
622 match up to "parent1/child1", but not to "parent2/child1".
624 Possible use cases include the "materialized path" example given above,
625 as well as making use of special SQL functions such as geometric
626 functions to create join conditions.
628 :param left_index: the integer 1-based index of the function argument
629 that serves as the "left" side of the expression.
630 :param right_index: the integer 1-based index of the function argument
631 that serves as the "right" side of the expression.
633 .. seealso::
635 :ref:`relationship_custom_operator_sql_function` -
636 example use within the ORM
638 """
639 return FunctionAsBinary(self, left_index, right_index)
641 @property
642 def _from_objects(self) -> Any:
643 return self.clauses._from_objects
645 def within_group_type(
646 self, within_group: WithinGroup[_S]
647 ) -> Optional[TypeEngine[_S]]:
648 """For types that define their return type as based on the criteria
649 within a WITHIN GROUP (ORDER BY) expression, called by the
650 :class:`.WithinGroup` construct.
652 Returns None by default, in which case the function's normal ``.type``
653 is used.
655 """
657 return None
659 def alias(
660 self, name: Optional[str] = None, joins_implicitly: bool = False
661 ) -> TableValuedAlias:
662 r"""Produce a :class:`_expression.Alias` construct against this
663 :class:`.FunctionElement`.
665 .. tip::
667 The :meth:`_functions.FunctionElement.alias` method is part of the
668 mechanism by which "table valued" SQL functions are created.
669 However, most use cases are covered by higher level methods on
670 :class:`_functions.FunctionElement` including
671 :meth:`_functions.FunctionElement.table_valued`, and
672 :meth:`_functions.FunctionElement.column_valued`.
674 This construct wraps the function in a named alias which
675 is suitable for the FROM clause, in the style accepted for example
676 by PostgreSQL. A column expression is also provided using the
677 special ``.column`` attribute, which may
678 be used to refer to the output of the function as a scalar value
679 in the columns or where clause, for a backend such as PostgreSQL.
681 For a full table-valued expression, use the
682 :meth:`_functions.FunctionElement.table_valued` method first to
683 establish named columns.
685 e.g.:
687 .. sourcecode:: pycon+sql
689 >>> from sqlalchemy import func, select, column
690 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
691 >>> print(select(data_view.column))
692 {printsql}SELECT data_view
693 FROM unnest(:unnest_1) AS data_view
695 The :meth:`_functions.FunctionElement.column_valued` method provides
696 a shortcut for the above pattern:
698 .. sourcecode:: pycon+sql
700 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
701 >>> print(select(data_view))
702 {printsql}SELECT data_view
703 FROM unnest(:unnest_1) AS data_view
705 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
707 :param name: alias name, will be rendered as ``AS <name>`` in the
708 FROM clause
710 :param joins_implicitly: when True, the table valued function may be
711 used in the FROM clause without any explicit JOIN to other tables
712 in the SQL query, and no "cartesian product" warning will be
713 generated. May be useful for SQL functions such as
714 ``func.json_each()``.
716 .. versionadded:: 1.4.33
718 .. seealso::
720 :ref:`tutorial_functions_table_valued` -
721 in the :ref:`unified_tutorial`
723 :meth:`_functions.FunctionElement.table_valued`
725 :meth:`_functions.FunctionElement.scalar_table_valued`
727 :meth:`_functions.FunctionElement.column_valued`
730 """
732 return TableValuedAlias._construct(
733 self,
734 name=name,
735 table_value_type=self.type,
736 joins_implicitly=joins_implicitly,
737 )
739 def select(self) -> Select[_T]:
740 """Produce a :func:`_expression.select` construct
741 against this :class:`.FunctionElement`.
743 This is shorthand for::
745 s = select(function_element)
747 """
748 s: Select[_T] = Select(self)
749 if self._execution_options:
750 s = s.execution_options(**self._execution_options)
751 return s
753 def _bind_param(
754 self,
755 operator: OperatorType,
756 obj: Any,
757 type_: Optional[TypeEngine[_T]] = None,
758 expanding: bool = False,
759 **kw: Any,
760 ) -> BindParameter[_T]:
761 return BindParameter(
762 None,
763 obj,
764 _compared_to_operator=operator,
765 _compared_to_type=self.type,
766 unique=True,
767 type_=type_,
768 expanding=expanding,
769 **kw,
770 )
772 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
773 # for the moment, we are parenthesizing all array-returning
774 # expressions against getitem. This may need to be made
775 # more portable if in the future we support other DBs
776 # besides postgresql.
777 if against in (operators.getitem, operators.json_getitem_op):
778 return Grouping(self)
779 else:
780 return super().self_group(against=against)
782 @property
783 def entity_namespace(self) -> _EntityNamespace:
784 """overrides FromClause.entity_namespace as functions are generally
785 column expressions and not FromClauses.
787 """
788 # ideally functions would not be fromclauses but we failed to make
789 # this adjustment in 1.4
790 return _entity_namespace(self.clause_expr)
793class FunctionAsBinary(BinaryExpression[Any]):
794 _traverse_internals = [
795 ("sql_function", InternalTraversal.dp_clauseelement),
796 ("left_index", InternalTraversal.dp_plain_obj),
797 ("right_index", InternalTraversal.dp_plain_obj),
798 ("modifiers", InternalTraversal.dp_plain_dict),
799 ]
801 sql_function: FunctionElement[Any]
802 left_index: int
803 right_index: int
805 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
806 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
808 def __init__(
809 self, fn: FunctionElement[Any], left_index: int, right_index: int
810 ) -> None:
811 self.sql_function = fn
812 self.left_index = left_index
813 self.right_index = right_index
815 self.operator = operators.function_as_comparison_op
816 self.type = sqltypes.BOOLEANTYPE
817 self.negate = None
818 self._is_implicitly_boolean = True
819 self.modifiers = util.immutabledict({})
821 @property
822 def left_expr(self) -> ColumnElement[Any]:
823 return self.sql_function.clauses.clauses[self.left_index - 1]
825 @left_expr.setter
826 def left_expr(self, value: ColumnElement[Any]) -> None:
827 self.sql_function.clauses.clauses[self.left_index - 1] = value
829 @property
830 def right_expr(self) -> ColumnElement[Any]:
831 return self.sql_function.clauses.clauses[self.right_index - 1]
833 @right_expr.setter
834 def right_expr(self, value: ColumnElement[Any]) -> None:
835 self.sql_function.clauses.clauses[self.right_index - 1] = value
837 if not TYPE_CHECKING:
838 # mypy can't accommodate @property to replace an instance
839 # variable
841 left = left_expr
842 right = right_expr
845class ScalarFunctionColumn(NamedColumn[_T]):
846 __visit_name__ = "scalar_function_column"
848 _traverse_internals = [
849 ("name", InternalTraversal.dp_anon_name),
850 ("type", InternalTraversal.dp_type),
851 ("fn", InternalTraversal.dp_clauseelement),
852 ]
854 is_literal = False
855 table = None
857 def __init__(
858 self,
859 fn: FunctionElement[_T],
860 name: str,
861 type_: Optional[_TypeEngineArgument[_T]] = None,
862 ) -> None:
863 self.fn = fn
864 self.name = name
866 # if type is None, we get NULLTYPE, which is our _T. But I don't
867 # know how to get the overloads to express that correctly
868 self.type = type_api.to_instance(type_) # type: ignore
871class _FunctionGenerator:
872 """Generate SQL function expressions.
874 :data:`.func` is a special object instance which generates SQL
875 functions based on name-based attributes, e.g.:
877 .. sourcecode:: pycon+sql
879 >>> print(func.count(1))
880 {printsql}count(:param_1)
882 The returned object is an instance of :class:`.Function`, and is a
883 column-oriented SQL element like any other, and is used in that way:
885 .. sourcecode:: pycon+sql
887 >>> print(select(func.count(table.c.id)))
888 {printsql}SELECT count(sometable.id) FROM sometable
890 Any name can be given to :data:`.func`. If the function name is unknown to
891 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
892 which SQLAlchemy is aware of, the name may be interpreted as a *generic
893 function* which will be compiled appropriately to the target database:
895 .. sourcecode:: pycon+sql
897 >>> print(func.current_timestamp())
898 {printsql}CURRENT_TIMESTAMP
900 To call functions which are present in dot-separated packages,
901 specify them in the same manner:
903 .. sourcecode:: pycon+sql
905 >>> print(func.stats.yield_curve(5, 10))
906 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
908 SQLAlchemy can be made aware of the return type of functions to enable
909 type-specific lexical and result-based behavior. For example, to ensure
910 that a string-based function returns a Unicode value and is similarly
911 treated as a string in expressions, specify
912 :class:`~sqlalchemy.types.Unicode` as the type:
914 .. sourcecode:: pycon+sql
916 >>> print(
917 ... func.my_string("hi", type_=Unicode)
918 ... + " "
919 ... + func.my_string("there", type_=Unicode)
920 ... )
921 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
923 The object returned by a :data:`.func` call is usually an instance of
924 :class:`.Function`.
925 This object meets the "column" interface, including comparison and labeling
926 functions. The object can also be passed the :meth:`~.Connectable.execute`
927 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
928 where it will be
929 wrapped inside of a SELECT statement first::
931 print(connection.execute(func.current_timestamp()).scalar())
933 In a few exception cases, the :data:`.func` accessor
934 will redirect a name to a built-in expression such as :func:`.cast`
935 or :func:`.extract`, as these names have well-known meaning
936 but are not exactly the same as "functions" from a SQLAlchemy
937 perspective.
939 Functions which are interpreted as "generic" functions know how to
940 calculate their return type automatically. For a listing of known generic
941 functions, see :ref:`generic_functions`.
943 .. note::
945 The :data:`.func` construct has only limited support for calling
946 standalone "stored procedures", especially those with special
947 parameterization concerns.
949 See the section :ref:`stored_procedures` for details on how to use
950 the DBAPI-level ``callproc()`` method for fully traditional stored
951 procedures.
953 .. seealso::
955 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
957 :class:`.Function`
959 """ # noqa
961 def __init__(self, **opts: Any) -> None:
962 self.__names: List[str] = []
963 self.opts = opts
965 def __getattr__(self, name: str) -> _FunctionGenerator:
966 # passthru __ attributes; fixes pydoc
967 if name.startswith("__"):
968 try:
969 return self.__dict__[name] # type: ignore
970 except KeyError:
971 raise AttributeError(name)
973 elif name.endswith("_"):
974 name = name[0:-1]
975 f = _FunctionGenerator(**self.opts)
976 f.__names = list(self.__names) + [name]
977 return f
979 @overload
980 def __call__(
981 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
982 ) -> Function[_T]: ...
984 @overload
985 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
987 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
988 o = self.opts.copy()
989 o.update(kwargs)
991 tokens = len(self.__names)
993 if tokens == 2:
994 package, fname = self.__names
995 elif tokens == 1:
996 package, fname = "_default", self.__names[0]
997 else:
998 package = None
1000 if package is not None:
1001 func = _registry[package].get(fname.lower())
1002 if func is not None:
1003 return func(*c, **o)
1005 return Function(
1006 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
1007 )
1009 if TYPE_CHECKING:
1010 # START GENERATED FUNCTION ACCESSORS
1012 # code within this block is **programmatically,
1013 # statically generated** by tools/generate_sql_functions.py
1015 @property
1016 def aggregate_strings(self) -> Type[aggregate_strings]: ...
1018 @property
1019 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
1021 # set ColumnElement[_T] as a separate overload, to appease
1022 # mypy which seems to not want to accept _T from
1023 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1024 # _HasClauseElement as of mypy 1.15
1026 @overload
1027 def array_agg(
1028 self,
1029 col: ColumnElement[_T],
1030 *args: _ColumnExpressionOrLiteralArgument[Any],
1031 **kwargs: Any,
1032 ) -> array_agg[_T]: ...
1034 @overload
1035 def array_agg(
1036 self,
1037 col: _ColumnExpressionArgument[_T],
1038 *args: _ColumnExpressionOrLiteralArgument[Any],
1039 **kwargs: Any,
1040 ) -> array_agg[_T]: ...
1042 @overload
1043 def array_agg(
1044 self,
1045 col: _T,
1046 *args: _ColumnExpressionOrLiteralArgument[Any],
1047 **kwargs: Any,
1048 ) -> array_agg[_T]: ...
1050 def array_agg(
1051 self,
1052 col: _ColumnExpressionOrLiteralArgument[_T],
1053 *args: _ColumnExpressionOrLiteralArgument[Any],
1054 **kwargs: Any,
1055 ) -> array_agg[_T]: ...
1057 @property
1058 def cast(self) -> Type[Cast[Any]]: ...
1060 @property
1061 def char_length(self) -> Type[char_length]: ...
1063 # set ColumnElement[_T] as a separate overload, to appease
1064 # mypy which seems to not want to accept _T from
1065 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1066 # _HasClauseElement as of mypy 1.15
1068 @overload
1069 def coalesce(
1070 self,
1071 col: ColumnElement[_T],
1072 *args: _ColumnExpressionOrLiteralArgument[Any],
1073 **kwargs: Any,
1074 ) -> coalesce[_T]: ...
1076 @overload
1077 def coalesce(
1078 self,
1079 col: _ColumnExpressionArgument[_T],
1080 *args: _ColumnExpressionOrLiteralArgument[Any],
1081 **kwargs: Any,
1082 ) -> coalesce[_T]: ...
1084 @overload
1085 def coalesce(
1086 self,
1087 col: _T,
1088 *args: _ColumnExpressionOrLiteralArgument[Any],
1089 **kwargs: Any,
1090 ) -> coalesce[_T]: ...
1092 def coalesce(
1093 self,
1094 col: _ColumnExpressionOrLiteralArgument[_T],
1095 *args: _ColumnExpressionOrLiteralArgument[Any],
1096 **kwargs: Any,
1097 ) -> coalesce[_T]: ...
1099 @property
1100 def concat(self) -> Type[concat]: ...
1102 @property
1103 def count(self) -> Type[count]: ...
1105 @property
1106 def cube(self) -> Type[cube[Any]]: ...
1108 @property
1109 def cume_dist(self) -> Type[cume_dist]: ...
1111 @property
1112 def current_date(self) -> Type[current_date]: ...
1114 @property
1115 def current_time(self) -> Type[current_time]: ...
1117 @property
1118 def current_timestamp(self) -> Type[current_timestamp]: ...
1120 @property
1121 def current_user(self) -> Type[current_user]: ...
1123 @property
1124 def dense_rank(self) -> Type[dense_rank]: ...
1126 @property
1127 def extract(self) -> Type[Extract]: ...
1129 @property
1130 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1132 @property
1133 def localtime(self) -> Type[localtime]: ...
1135 @property
1136 def localtimestamp(self) -> Type[localtimestamp]: ...
1138 # set ColumnElement[_T] as a separate overload, to appease
1139 # mypy which seems to not want to accept _T from
1140 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1141 # _HasClauseElement as of mypy 1.15
1143 @overload
1144 def max( # noqa: A001
1145 self,
1146 col: ColumnElement[_T],
1147 *args: _ColumnExpressionOrLiteralArgument[Any],
1148 **kwargs: Any,
1149 ) -> max[_T]: ...
1151 @overload
1152 def max( # noqa: A001
1153 self,
1154 col: _ColumnExpressionArgument[_T],
1155 *args: _ColumnExpressionOrLiteralArgument[Any],
1156 **kwargs: Any,
1157 ) -> max[_T]: ...
1159 @overload
1160 def max( # noqa: A001
1161 self,
1162 col: _T,
1163 *args: _ColumnExpressionOrLiteralArgument[Any],
1164 **kwargs: Any,
1165 ) -> max[_T]: ...
1167 def max( # noqa: A001
1168 self,
1169 col: _ColumnExpressionOrLiteralArgument[_T],
1170 *args: _ColumnExpressionOrLiteralArgument[Any],
1171 **kwargs: Any,
1172 ) -> max[_T]: ...
1174 # set ColumnElement[_T] as a separate overload, to appease
1175 # mypy which seems to not want to accept _T from
1176 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1177 # _HasClauseElement as of mypy 1.15
1179 @overload
1180 def min( # noqa: A001
1181 self,
1182 col: ColumnElement[_T],
1183 *args: _ColumnExpressionOrLiteralArgument[Any],
1184 **kwargs: Any,
1185 ) -> min[_T]: ...
1187 @overload
1188 def min( # noqa: A001
1189 self,
1190 col: _ColumnExpressionArgument[_T],
1191 *args: _ColumnExpressionOrLiteralArgument[Any],
1192 **kwargs: Any,
1193 ) -> min[_T]: ...
1195 @overload
1196 def min( # noqa: A001
1197 self,
1198 col: _T,
1199 *args: _ColumnExpressionOrLiteralArgument[Any],
1200 **kwargs: Any,
1201 ) -> min[_T]: ...
1203 def min( # noqa: A001
1204 self,
1205 col: _ColumnExpressionOrLiteralArgument[_T],
1206 *args: _ColumnExpressionOrLiteralArgument[Any],
1207 **kwargs: Any,
1208 ) -> min[_T]: ...
1210 @property
1211 def mode(self) -> Type[mode[Any]]: ...
1213 @property
1214 def next_value(self) -> Type[next_value]: ...
1216 @property
1217 def now(self) -> Type[now]: ...
1219 @property
1220 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1222 @property
1223 def percent_rank(self) -> Type[percent_rank]: ...
1225 @property
1226 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1228 @property
1229 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1231 # set ColumnElement[_T] as a separate overload, to appease
1232 # mypy which seems to not want to accept _T from
1233 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1234 # _HasClauseElement as of mypy 1.15
1236 @overload
1237 def pow( # noqa: A001
1238 self,
1239 col: ColumnElement[_T],
1240 *args: _ColumnExpressionOrLiteralArgument[Any],
1241 **kwargs: Any,
1242 ) -> pow[_T]: ...
1244 @overload
1245 def pow( # noqa: A001
1246 self,
1247 col: _ColumnExpressionArgument[_T],
1248 *args: _ColumnExpressionOrLiteralArgument[Any],
1249 **kwargs: Any,
1250 ) -> pow[_T]: ...
1252 @overload
1253 def pow( # noqa: A001
1254 self,
1255 col: _T,
1256 *args: _ColumnExpressionOrLiteralArgument[Any],
1257 **kwargs: Any,
1258 ) -> pow[_T]: ...
1260 def pow( # noqa: A001
1261 self,
1262 col: _ColumnExpressionOrLiteralArgument[_T],
1263 *args: _ColumnExpressionOrLiteralArgument[Any],
1264 **kwargs: Any,
1265 ) -> pow[_T]: ...
1267 @property
1268 def random(self) -> Type[random]: ...
1270 @property
1271 def rank(self) -> Type[rank]: ...
1273 @property
1274 def rollup(self) -> Type[rollup[Any]]: ...
1276 @property
1277 def session_user(self) -> Type[session_user]: ...
1279 # set ColumnElement[_T] as a separate overload, to appease
1280 # mypy which seems to not want to accept _T from
1281 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1282 # _HasClauseElement as of mypy 1.15
1284 @overload
1285 def sum( # noqa: A001
1286 self,
1287 col: ColumnElement[_T],
1288 *args: _ColumnExpressionOrLiteralArgument[Any],
1289 **kwargs: Any,
1290 ) -> sum[_T]: ...
1292 @overload
1293 def sum( # noqa: A001
1294 self,
1295 col: _ColumnExpressionArgument[_T],
1296 *args: _ColumnExpressionOrLiteralArgument[Any],
1297 **kwargs: Any,
1298 ) -> sum[_T]: ...
1300 @overload
1301 def sum( # noqa: A001
1302 self,
1303 col: _T,
1304 *args: _ColumnExpressionOrLiteralArgument[Any],
1305 **kwargs: Any,
1306 ) -> sum[_T]: ...
1308 def sum( # noqa: A001
1309 self,
1310 col: _ColumnExpressionOrLiteralArgument[_T],
1311 *args: _ColumnExpressionOrLiteralArgument[Any],
1312 **kwargs: Any,
1313 ) -> sum[_T]: ...
1315 @property
1316 def sysdate(self) -> Type[sysdate]: ...
1318 @property
1319 def user(self) -> Type[user]: ...
1321 # END GENERATED FUNCTION ACCESSORS
1324func = _FunctionGenerator()
1325func.__doc__ = _FunctionGenerator.__doc__
1327modifier = _FunctionGenerator(group=False)
1330class Function(FunctionElement[_T]):
1331 r"""Describe a named SQL function.
1333 The :class:`.Function` object is typically generated from the
1334 :data:`.func` generation object.
1337 :param \*clauses: list of column expressions that form the arguments
1338 of the SQL function call.
1340 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1341 used as the return value of the column expression generated by this
1342 function call.
1344 :param packagenames: a string which indicates package prefix names
1345 to be prepended to the function name when the SQL is generated.
1346 The :data:`.func` generator creates these when it is called using
1347 dotted format, e.g.::
1349 func.mypackage.some_function(col1, col2)
1351 .. seealso::
1353 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1355 :data:`.func` - namespace which produces registered or ad-hoc
1356 :class:`.Function` instances.
1358 :class:`.GenericFunction` - allows creation of registered function
1359 types.
1361 """
1363 __visit_name__ = "function"
1365 _traverse_internals = FunctionElement._traverse_internals + [
1366 ("packagenames", InternalTraversal.dp_plain_obj),
1367 ("name", InternalTraversal.dp_string),
1368 ("type", InternalTraversal.dp_type),
1369 ]
1371 name: str
1373 identifier: str
1375 type: TypeEngine[_T]
1376 """A :class:`_types.TypeEngine` object which refers to the SQL return
1377 type represented by this SQL function.
1379 This datatype may be configured when generating a
1380 :class:`_functions.Function` object by passing the
1381 :paramref:`_functions.Function.type_` parameter, e.g.::
1383 >>> select(func.lower("some VALUE", type_=String))
1385 The small number of built-in classes of :class:`_functions.Function` come
1386 with a built-in datatype that's appropriate to the class of function and
1387 its arguments. For functions that aren't known, the type defaults to the
1388 "null type".
1390 """
1392 @overload
1393 def __init__(
1394 self,
1395 name: str,
1396 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1397 type_: None = ...,
1398 packagenames: Optional[Tuple[str, ...]] = ...,
1399 ) -> None: ...
1401 @overload
1402 def __init__(
1403 self,
1404 name: str,
1405 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1406 type_: _TypeEngineArgument[_T] = ...,
1407 packagenames: Optional[Tuple[str, ...]] = ...,
1408 ) -> None: ...
1410 def __init__(
1411 self,
1412 name: str,
1413 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1414 type_: Optional[_TypeEngineArgument[_T]] = None,
1415 packagenames: Optional[Tuple[str, ...]] = None,
1416 ) -> None:
1417 """Construct a :class:`.Function`.
1419 The :data:`.func` construct is normally used to construct
1420 new :class:`.Function` instances.
1422 """
1423 self.packagenames = packagenames or ()
1424 self.name = name
1426 # if type is None, we get NULLTYPE, which is our _T. But I don't
1427 # know how to get the overloads to express that correctly
1428 self.type = type_api.to_instance(type_) # type: ignore
1430 FunctionElement.__init__(self, *clauses)
1432 def _bind_param(
1433 self,
1434 operator: OperatorType,
1435 obj: Any,
1436 type_: Optional[TypeEngine[_T]] = None,
1437 expanding: bool = False,
1438 **kw: Any,
1439 ) -> BindParameter[_T]:
1440 return BindParameter(
1441 self.name,
1442 obj,
1443 _compared_to_operator=operator,
1444 _compared_to_type=self.type,
1445 type_=type_,
1446 unique=True,
1447 expanding=expanding,
1448 **kw,
1449 )
1452class GenericFunction(Function[_T]):
1453 """Define a 'generic' function.
1455 A generic function is a pre-established :class:`.Function`
1456 class that is instantiated automatically when called
1457 by name from the :data:`.func` attribute. Note that
1458 calling any name from :data:`.func` has the effect that
1459 a new :class:`.Function` instance is created automatically,
1460 given that name. The primary use case for defining
1461 a :class:`.GenericFunction` class is so that a function
1462 of a particular name may be given a fixed return type.
1463 It can also include custom argument parsing schemes as well
1464 as additional methods.
1466 Subclasses of :class:`.GenericFunction` are automatically
1467 registered under the name of the class. For
1468 example, a user-defined function ``as_utc()`` would
1469 be available immediately::
1471 from sqlalchemy.sql.functions import GenericFunction
1472 from sqlalchemy.types import DateTime
1475 class as_utc(GenericFunction):
1476 type = DateTime()
1477 inherit_cache = True
1480 print(select(func.as_utc()))
1482 User-defined generic functions can be organized into
1483 packages by specifying the "package" attribute when defining
1484 :class:`.GenericFunction`. Third party libraries
1485 containing many functions may want to use this in order
1486 to avoid name conflicts with other systems. For example,
1487 if our ``as_utc()`` function were part of a package
1488 "time"::
1490 class as_utc(GenericFunction):
1491 type = DateTime()
1492 package = "time"
1493 inherit_cache = True
1495 The above function would be available from :data:`.func`
1496 using the package name ``time``::
1498 print(select(func.time.as_utc()))
1500 A final option is to allow the function to be accessed
1501 from one name in :data:`.func` but to render as a different name.
1502 The ``identifier`` attribute will override the name used to
1503 access the function as loaded from :data:`.func`, but will retain
1504 the usage of ``name`` as the rendered name::
1506 class GeoBuffer(GenericFunction):
1507 type = Geometry()
1508 package = "geo"
1509 name = "ST_Buffer"
1510 identifier = "buffer"
1511 inherit_cache = True
1513 The above function will render as follows:
1515 .. sourcecode:: pycon+sql
1517 >>> print(func.geo.buffer())
1518 {printsql}ST_Buffer()
1520 The name will be rendered as is, however without quoting unless the name
1521 contains special characters that require quoting. To force quoting
1522 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1523 construct::
1525 from sqlalchemy.sql import quoted_name
1528 class GeoBuffer(GenericFunction):
1529 type = Geometry()
1530 package = "geo"
1531 name = quoted_name("ST_Buffer", True)
1532 identifier = "buffer"
1533 inherit_cache = True
1535 The above function will render as:
1537 .. sourcecode:: pycon+sql
1539 >>> print(func.geo.buffer())
1540 {printsql}"ST_Buffer"()
1542 Type parameters for this class as a
1543 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1544 and should match the type seen in a :class:`_engine.Result`. For example::
1546 class as_utc(GenericFunction[datetime.datetime]):
1547 type = DateTime()
1548 inherit_cache = True
1550 The above indicates that the following expression returns a ``datetime``
1551 object::
1553 connection.scalar(select(func.as_utc()))
1555 """
1557 coerce_arguments = True
1558 inherit_cache = True
1560 _register: bool
1562 name = "GenericFunction"
1564 def __init_subclass__(cls) -> None:
1565 if annotation.Annotated not in cls.__mro__:
1566 cls._register_generic_function(cls.__name__, cls.__dict__)
1567 super().__init_subclass__()
1569 @classmethod
1570 def _register_generic_function(
1571 cls, clsname: str, clsdict: Mapping[str, Any]
1572 ) -> None:
1573 cls.name = name = clsdict.get("name", clsname)
1574 cls.identifier = identifier = clsdict.get("identifier", name)
1575 package = clsdict.get("package", "_default")
1576 # legacy
1577 if "__return_type__" in clsdict:
1578 cls.type = clsdict["__return_type__"]
1580 # Check _register attribute status
1581 cls._register = getattr(cls, "_register", True)
1583 # Register the function if required
1584 if cls._register:
1585 register_function(identifier, cls, package)
1586 else:
1587 # Set _register to True to register child classes by default
1588 cls._register = True
1590 def __init__(
1591 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1592 ) -> None:
1593 parsed_args = kwargs.pop("_parsed_args", None)
1594 if parsed_args is None:
1595 parsed_args = [
1596 coercions.expect(
1597 roles.ExpressionElementRole,
1598 c,
1599 name=self.name,
1600 apply_propagate_attrs=self,
1601 )
1602 for c in args
1603 ]
1604 self._has_args = self._has_args or bool(parsed_args)
1605 self.packagenames = ()
1607 self.clause_expr = Grouping(
1608 ClauseList(
1609 operator=operators.comma_op, group_contents=True, *parsed_args
1610 )
1611 )
1613 self.type = type_api.to_instance( # type: ignore
1614 kwargs.pop("type_", None) or getattr(self, "type", None)
1615 )
1618register_function("cast", Cast) # type: ignore
1619register_function("extract", Extract) # type: ignore
1622class next_value(GenericFunction[int]):
1623 """Represent the 'next value', given a :class:`.Sequence`
1624 as its single argument.
1626 Compiles into the appropriate function on each backend,
1627 or will raise NotImplementedError if used on a backend
1628 that does not provide support for sequences.
1630 """
1632 type = sqltypes.Integer()
1633 name = "next_value"
1635 _traverse_internals = [
1636 ("sequence", InternalTraversal.dp_named_ddl_element)
1637 ]
1639 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1640 assert isinstance(
1641 seq, schema.Sequence
1642 ), "next_value() accepts a Sequence object as input."
1643 self.sequence = seq
1644 self.type = sqltypes.to_instance( # type: ignore
1645 seq.data_type or getattr(self, "type", None)
1646 )
1648 def compare(self, other: Any, **kw: Any) -> bool:
1649 return (
1650 isinstance(other, next_value)
1651 and self.sequence.name == other.sequence.name
1652 )
1654 @property
1655 def _from_objects(self) -> Any:
1656 return []
1659class AnsiFunction(GenericFunction[_T]):
1660 """Define a function in "ansi" format, which doesn't render parenthesis."""
1662 inherit_cache = True
1664 def __init__(
1665 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1666 ) -> None:
1667 GenericFunction.__init__(self, *args, **kwargs)
1670class ReturnTypeFromArgs(GenericFunction[_T]):
1671 """Define a function whose return type is bound to the type of its
1672 arguments.
1673 """
1675 inherit_cache = True
1677 # set ColumnElement[_T] as a separate overload, to appease
1678 # mypy which seems to not want to accept _T from
1679 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1680 # _HasClauseElement as of mypy 1.15
1682 @overload
1683 def __init__(
1684 self,
1685 col: ColumnElement[_T],
1686 *args: _ColumnExpressionOrLiteralArgument[Any],
1687 **kwargs: Any,
1688 ) -> None: ...
1690 @overload
1691 def __init__(
1692 self,
1693 col: _ColumnExpressionArgument[_T],
1694 *args: _ColumnExpressionOrLiteralArgument[Any],
1695 **kwargs: Any,
1696 ) -> None: ...
1698 @overload
1699 def __init__(
1700 self,
1701 col: _T,
1702 *args: _ColumnExpressionOrLiteralArgument[Any],
1703 **kwargs: Any,
1704 ) -> None: ...
1706 def __init__(
1707 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1708 ) -> None:
1709 fn_args: Sequence[ColumnElement[Any]] = [
1710 coercions.expect(
1711 roles.ExpressionElementRole,
1712 c,
1713 name=self.name,
1714 apply_propagate_attrs=self,
1715 )
1716 for c in args
1717 ]
1718 kwargs.setdefault("type_", _type_from_args(fn_args))
1719 kwargs["_parsed_args"] = fn_args
1720 super().__init__(*fn_args, **kwargs)
1723class coalesce(ReturnTypeFromArgs[_T]):
1724 _has_args = True
1725 inherit_cache = True
1728class max(ReturnTypeFromArgs[_T]): # noqa: A001
1729 """The SQL MAX() aggregate function."""
1731 inherit_cache = True
1734class min(ReturnTypeFromArgs[_T]): # noqa: A001
1735 """The SQL MIN() aggregate function."""
1737 inherit_cache = True
1740class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1741 """The SQL SUM() aggregate function."""
1743 inherit_cache = True
1746class now(GenericFunction[datetime.datetime]):
1747 """The SQL now() datetime function.
1749 SQLAlchemy dialects will usually render this particular function
1750 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1752 """
1754 type = sqltypes.DateTime()
1755 inherit_cache = True
1758class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1759 """The SQL POW() function which performs the power operator.
1761 E.g.:
1763 .. sourcecode:: pycon+sql
1765 >>> print(select(func.pow(2, 8)))
1766 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1768 .. versionadded:: 2.1
1770 """
1772 inherit_cache = True
1775class concat(GenericFunction[str]):
1776 """The SQL CONCAT() function, which concatenates strings.
1778 E.g.:
1780 .. sourcecode:: pycon+sql
1782 >>> print(select(func.concat("a", "b")))
1783 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1785 String concatenation in SQLAlchemy is more commonly available using the
1786 Python ``+`` operator with string datatypes, which will render a
1787 backend-specific concatenation operator, such as :
1789 .. sourcecode:: pycon+sql
1791 >>> print(select(literal("a") + "b"))
1792 {printsql}SELECT :param_1 || :param_2 AS anon_1
1795 """
1797 type = sqltypes.String()
1798 inherit_cache = True
1801class char_length(GenericFunction[int]):
1802 """The CHAR_LENGTH() SQL function."""
1804 type = sqltypes.Integer()
1805 inherit_cache = True
1807 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1808 # slight hack to limit to just one positional argument
1809 # not sure why this one function has this special treatment
1810 super().__init__(arg, **kw)
1813class random(GenericFunction[float]):
1814 """The RANDOM() SQL function."""
1816 _has_args = True
1817 inherit_cache = True
1820class count(GenericFunction[int]):
1821 r"""The ANSI COUNT aggregate function. With no arguments,
1822 emits COUNT \*.
1824 E.g.::
1826 from sqlalchemy import func
1827 from sqlalchemy import select
1828 from sqlalchemy import table, column
1830 my_table = table("some_table", column("id"))
1832 stmt = select(func.count()).select_from(my_table)
1834 Executing ``stmt`` would emit:
1836 .. sourcecode:: sql
1838 SELECT count(*) AS count_1
1839 FROM some_table
1842 """
1844 type = sqltypes.Integer()
1845 inherit_cache = True
1847 def __init__(
1848 self,
1849 expression: Union[
1850 _ColumnExpressionArgument[Any], _StarOrOne, None
1851 ] = None,
1852 **kwargs: Any,
1853 ) -> None:
1854 if expression is None:
1855 expression = literal_column("*")
1856 super().__init__(expression, **kwargs)
1859class current_date(AnsiFunction[datetime.date]):
1860 """The CURRENT_DATE() SQL function."""
1862 type = sqltypes.Date()
1863 inherit_cache = True
1866class current_time(AnsiFunction[datetime.time]):
1867 """The CURRENT_TIME() SQL function."""
1869 type = sqltypes.Time()
1870 inherit_cache = True
1873class current_timestamp(AnsiFunction[datetime.datetime]):
1874 """The CURRENT_TIMESTAMP() SQL function."""
1876 type = sqltypes.DateTime()
1877 inherit_cache = True
1880class current_user(AnsiFunction[str]):
1881 """The CURRENT_USER() SQL function."""
1883 type = sqltypes.String()
1884 inherit_cache = True
1887class localtime(AnsiFunction[datetime.datetime]):
1888 """The localtime() SQL function."""
1890 type = sqltypes.DateTime()
1891 inherit_cache = True
1894class localtimestamp(AnsiFunction[datetime.datetime]):
1895 """The localtimestamp() SQL function."""
1897 type = sqltypes.DateTime()
1898 inherit_cache = True
1901class session_user(AnsiFunction[str]):
1902 """The SESSION_USER() SQL function."""
1904 type = sqltypes.String()
1905 inherit_cache = True
1908class sysdate(AnsiFunction[datetime.datetime]):
1909 """The SYSDATE() SQL function."""
1911 type = sqltypes.DateTime()
1912 inherit_cache = True
1915class user(AnsiFunction[str]):
1916 """The USER() SQL function."""
1918 type = sqltypes.String()
1919 inherit_cache = True
1922class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1923 """Support for the ARRAY_AGG function.
1925 The ``func.array_agg(expr)`` construct returns an expression of
1926 type :class:`_types.ARRAY`.
1928 e.g.::
1930 stmt = select(func.array_agg(table.c.values)[2:5])
1932 .. seealso::
1934 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1935 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1936 added.
1938 """
1940 inherit_cache = True
1942 def __init__(
1943 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1944 ) -> None:
1945 fn_args: Sequence[ColumnElement[Any]] = [
1946 coercions.expect(
1947 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1948 )
1949 for c in args
1950 ]
1952 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1953 if "type_" not in kwargs:
1954 type_from_args = _type_from_args(fn_args)
1955 if isinstance(type_from_args, sqltypes.ARRAY):
1956 kwargs["type_"] = type_from_args
1957 else:
1958 kwargs["type_"] = default_array_type(
1959 type_from_args, dimensions=1
1960 )
1961 kwargs["_parsed_args"] = fn_args
1962 super().__init__(*fn_args, **kwargs)
1965class OrderedSetAgg(GenericFunction[_T]):
1966 """Define a function where the return type is based on the sort
1967 expression type as defined by the expression passed to the
1968 :meth:`.FunctionElement.within_group` method."""
1970 array_for_multi_clause = False
1971 inherit_cache = True
1973 def within_group_type(
1974 self, within_group: WithinGroup[Any]
1975 ) -> TypeEngine[Any]:
1976 func_clauses = cast(ClauseList, self.clause_expr.element)
1977 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
1978 within_group.order_by
1979 )
1980 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1981 return sqltypes.ARRAY(order_by[0].type)
1982 else:
1983 return order_by[0].type
1986class mode(OrderedSetAgg[_T]):
1987 """Implement the ``mode`` ordered-set aggregate function.
1989 This function must be used with the :meth:`.FunctionElement.within_group`
1990 modifier to supply a sort expression to operate upon.
1992 The return type of this function is the same as the sort expression.
1994 """
1996 inherit_cache = True
1999class percentile_cont(OrderedSetAgg[_T]):
2000 """Implement the ``percentile_cont`` ordered-set aggregate function.
2002 This function must be used with the :meth:`.FunctionElement.within_group`
2003 modifier to supply a sort expression to operate upon.
2005 The return type of this function is the same as the sort expression,
2006 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2007 expression's type.
2009 """
2011 array_for_multi_clause = True
2012 inherit_cache = True
2015class percentile_disc(OrderedSetAgg[_T]):
2016 """Implement the ``percentile_disc`` ordered-set aggregate function.
2018 This function must be used with the :meth:`.FunctionElement.within_group`
2019 modifier to supply a sort expression to operate upon.
2021 The return type of this function is the same as the sort expression,
2022 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2023 expression's type.
2025 """
2027 array_for_multi_clause = True
2028 inherit_cache = True
2031class rank(GenericFunction[int]):
2032 """Implement the ``rank`` hypothetical-set aggregate function.
2034 This function must be used with the :meth:`.FunctionElement.within_group`
2035 modifier to supply a sort expression to operate upon.
2037 The return type of this function is :class:`.Integer`.
2039 """
2041 type = sqltypes.Integer()
2042 inherit_cache = True
2045class dense_rank(GenericFunction[int]):
2046 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2048 This function must be used with the :meth:`.FunctionElement.within_group`
2049 modifier to supply a sort expression to operate upon.
2051 The return type of this function is :class:`.Integer`.
2053 """
2055 type = sqltypes.Integer()
2056 inherit_cache = True
2059class percent_rank(GenericFunction[decimal.Decimal]):
2060 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2062 This function must be used with the :meth:`.FunctionElement.within_group`
2063 modifier to supply a sort expression to operate upon.
2065 The return type of this function is :class:`.Numeric`.
2067 """
2069 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2070 inherit_cache = True
2073class cume_dist(GenericFunction[decimal.Decimal]):
2074 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2076 This function must be used with the :meth:`.FunctionElement.within_group`
2077 modifier to supply a sort expression to operate upon.
2079 The return type of this function is :class:`.Numeric`.
2081 """
2083 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2084 inherit_cache = True
2087class cube(GenericFunction[_T]):
2088 r"""Implement the ``CUBE`` grouping operation.
2090 This function is used as part of the GROUP BY of a statement,
2091 e.g. :meth:`_expression.Select.group_by`::
2093 stmt = select(
2094 func.sum(table.c.value), table.c.col_1, table.c.col_2
2095 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2097 """
2099 _has_args = True
2100 inherit_cache = True
2103class rollup(GenericFunction[_T]):
2104 r"""Implement the ``ROLLUP`` grouping operation.
2106 This function is used as part of the GROUP BY of a statement,
2107 e.g. :meth:`_expression.Select.group_by`::
2109 stmt = select(
2110 func.sum(table.c.value), table.c.col_1, table.c.col_2
2111 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2113 """
2115 _has_args = True
2116 inherit_cache = True
2119class grouping_sets(GenericFunction[_T]):
2120 r"""Implement the ``GROUPING SETS`` grouping operation.
2122 This function is used as part of the GROUP BY of a statement,
2123 e.g. :meth:`_expression.Select.group_by`::
2125 stmt = select(
2126 func.sum(table.c.value), table.c.col_1, table.c.col_2
2127 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2129 In order to group by multiple sets, use the :func:`.tuple_` construct::
2131 from sqlalchemy import tuple_
2133 stmt = select(
2134 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2135 ).group_by(
2136 func.grouping_sets(
2137 tuple_(table.c.col_1, table.c.col_2),
2138 tuple_(table.c.value, table.c.col_3),
2139 )
2140 )
2142 """ # noqa: E501
2144 _has_args = True
2145 inherit_cache = True
2148class aggregate_strings(GenericFunction[str]):
2149 """Implement a generic string aggregation function.
2151 This function will concatenate non-null values into a string and
2152 separate the values by a delimiter.
2154 This function is compiled on a per-backend basis, into functions
2155 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2157 e.g. Example usage with delimiter '.'::
2159 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2161 .. versionadded:: 2.0.21
2163 To add ordering to the expression, use the
2164 :meth:`_functions.FunctionElement.aggregate_order_by` modifier method,
2165 which will emit ORDER BY within the appropriate part of the column
2166 expression (varies by backend)::
2168 stmt = select(
2169 func.aggregate_strings(table.c.str_col, ".").aggregate_order_by(
2170 table.c.str_col
2171 )
2172 )
2174 .. versionadded:: 2.1 added :meth:`_functions.FunctionElement.aggregate_order_by`
2175 for all aggregate functions.
2177 :param clause: the SQL expression to be concatenated
2179 :param separator: separator string
2182 """ # noqa: E501
2184 type = sqltypes.String()
2185 _has_args = True
2186 inherit_cache = True
2188 def __init__(
2189 self,
2190 clause: _ColumnExpressionArgument[Any],
2191 separator: str,
2192 ) -> None:
2193 super().__init__(clause, separator)