Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/functions.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8"""SQL function API, factories, and built-in functions."""
10from __future__ import annotations
12import datetime
13import decimal
14from typing import Any
15from typing import cast
16from typing import Dict
17from typing import List
18from typing import Mapping
19from typing import Optional
20from typing import overload
21from typing import Sequence
22from typing import Tuple
23from typing import Type
24from typing import TYPE_CHECKING
25from typing import TypeAlias
26from typing import TypeVar
27from typing import Union
29from . import annotation
30from . import coercions
31from . import operators
32from . import roles
33from . import schema
34from . import sqltypes
35from . import type_api
36from . import util as sqlutil
37from ._typing import is_table_value_type
38from .base import _entity_namespace
39from .base import ColumnCollection
40from .base import ExecutableStatement
41from .base import Generative
42from .base import HasMemoized
43from .base import ReadOnlyColumnCollection
44from .base import WriteableColumnCollection
45from .elements import _type_from_args
46from .elements import AggregateOrderBy
47from .elements import BinaryExpression
48from .elements import BindParameter
49from .elements import Cast
50from .elements import ClauseList
51from .elements import ColumnElement
52from .elements import Extract
53from .elements import FunctionFilter
54from .elements import Grouping
55from .elements import literal_column
56from .elements import NamedColumn
57from .elements import Over
58from .elements import WithinGroup
59from .selectable import FromClause
60from .selectable import Select
61from .selectable import TableValuedAlias
62from .sqltypes import TableValueType
63from .type_api import TypeEngine
64from .visitors import InternalTraversal
65from .. import util
67if TYPE_CHECKING:
68 from ._typing import _ByArgument
69 from ._typing import _ColumnExpressionArgument
70 from ._typing import _ColumnExpressionOrLiteralArgument
71 from ._typing import _ColumnExpressionOrStrLabelArgument
72 from ._typing import _CovariantTypeEngineArgument
73 from ._typing import _StarOrOne
74 from ._typing import _TypeEngineArgument
75 from .base import _EntityNamespace
76 from .elements import _FrameIntTuple
77 from .elements import ClauseElement
78 from .elements import FrameClause
79 from .elements import KeyedColumnElement
80 from .elements import TableValuedColumn
81 from .operators import OperatorType
82 from ..engine.base import Connection
83 from ..engine.cursor import CursorResult
84 from ..engine.interfaces import _CoreMultiExecuteParams
85 from ..engine.interfaces import CoreExecuteOptionsParameter
86 from ..util.typing import Self
88_T = TypeVar("_T", bound=Any)
89_S = TypeVar("_S", bound=Any)
91_registry: util.defaultdict[str, Dict[str, Type[Function[Any]]]] = (
92 util.defaultdict(dict)
93)
96def register_function(
97 identifier: str, fn: Type[Function[Any]], package: str = "_default"
98) -> None:
99 """Associate a callable with a particular func. name.
101 This is normally called by GenericFunction, but is also
102 available by itself so that a non-Function construct
103 can be associated with the :data:`.func` accessor (i.e.
104 CAST, EXTRACT).
106 """
107 reg = _registry[package]
109 identifier = str(identifier).lower()
111 # Check if a function with the same identifier is registered.
112 if identifier in reg:
113 util.warn(
114 "The GenericFunction '{}' is already registered and "
115 "is going to be overridden.".format(identifier)
116 )
117 reg[identifier] = fn
120class FunctionElement(
121 ColumnElement[_T], ExecutableStatement, FromClause, Generative
122):
123 """Base for SQL function-oriented constructs.
125 This is a `generic type <https://peps.python.org/pep-0484/#generics>`_,
126 meaning that type checkers and IDEs can be instructed on the types to
127 expect in a :class:`_engine.Result` for this function. See
128 :class:`.GenericFunction` for an example of how this is done.
130 .. seealso::
132 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
134 :class:`.Function` - named SQL function.
136 :data:`.func` - namespace which produces registered or ad-hoc
137 :class:`.Function` instances.
139 :class:`.GenericFunction` - allows creation of registered function
140 types.
142 """
144 _traverse_internals = [
145 ("clause_expr", InternalTraversal.dp_clauseelement),
146 ("_with_ordinality", InternalTraversal.dp_boolean),
147 ("_table_value_type", InternalTraversal.dp_has_cache_key),
148 ] + ExecutableStatement._executable_traverse_internals
150 packagenames: Tuple[str, ...] = ()
152 monotonic: bool = False
154 _has_args = False
155 _with_ordinality = False
156 _table_value_type: Optional[TableValueType] = None
158 # some attributes that are defined between both ColumnElement and
159 # FromClause are set to Any here to avoid typing errors
160 primary_key: Any
161 _is_clone_of: Any
163 clause_expr: Grouping[Any]
165 def __init__(
166 self, *clauses: _ColumnExpressionOrLiteralArgument[Any]
167 ) -> None:
168 r"""Construct a :class:`.FunctionElement`.
170 :param \*clauses: list of column expressions that form the arguments
171 of the SQL function call.
173 :param \**kwargs: additional kwargs are typically consumed by
174 subclasses.
176 .. seealso::
178 :data:`.func`
180 :class:`.Function`
182 """
183 args: Sequence[_ColumnExpressionArgument[Any]] = [
184 coercions.expect(
185 roles.ExpressionElementRole,
186 c,
187 name=getattr(self, "name", None),
188 apply_propagate_attrs=self,
189 )
190 for c in clauses
191 ]
192 self._has_args = self._has_args or bool(args)
193 self.clause_expr = Grouping(
194 ClauseList(operator=operators.comma_op, group_contents=True, *args)
195 )
197 _non_anon_label = None
199 @property
200 def _proxy_key(self) -> Any:
201 return super()._proxy_key or getattr(self, "name", None)
203 def _execute_on_connection(
204 self,
205 connection: Connection,
206 distilled_params: _CoreMultiExecuteParams,
207 execution_options: CoreExecuteOptionsParameter,
208 ) -> CursorResult[Any]:
209 return connection._execute_function(
210 self, distilled_params, execution_options
211 )
213 def scalar_table_valued(
214 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
215 ) -> ScalarFunctionColumn[_T]:
216 """Return a column expression that's against this
217 :class:`_functions.FunctionElement` as a scalar
218 table-valued expression.
220 The returned expression is similar to that returned by a single column
221 accessed off of a :meth:`_functions.FunctionElement.table_valued`
222 construct, except no FROM clause is generated; the function is rendered
223 in the similar way as a scalar subquery.
225 E.g.:
227 .. sourcecode:: pycon+sql
229 >>> from sqlalchemy import func, select
230 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
231 >>> print(select(fn))
232 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
234 .. versionadded:: 1.4.0b2
236 .. seealso::
238 :meth:`_functions.FunctionElement.table_valued`
240 :meth:`_functions.FunctionElement.alias`
242 :meth:`_functions.FunctionElement.column_valued`
244 """ # noqa: E501
246 return ScalarFunctionColumn(self, name, type_)
248 def table_valued(
249 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
250 ) -> TableValuedAlias:
251 r"""Return a :class:`_sql.TableValuedAlias` representation of this
252 :class:`_functions.FunctionElement` with table-valued expressions added.
254 e.g. to use the SQLite form of ``generate_series()`` (including
255 hidden columns "start", "stop", "step"):
257 .. sourcecode:: pycon+sql
259 >>> fn = func.generate_series(1, 5).table_valued(
260 ... "value", "start", "stop", "step"
261 ... )
263 >>> print(select(fn))
264 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
265 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
267 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
268 {printsql}SELECT anon_1.value, anon_1.stop
269 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
270 WHERE anon_1.value > :value_1{stop}
272 Backends like PostgreSQL need the accessed columns to be explicitly
273 named in "AS" clause. To achieve this, use
274 :meth:`_sql.TableValuedAlias.render_derived`; be sure to consult the
275 :ref:`PostgreSQL-specific documentation for table valued functions
276 <postgresql_table_valued>` for additional examples:
278 .. sourcecode:: pycon+sql
280 >>> fn = func.generate_series(1, 5).table_valued("value").render_derived()
282 >>> print(select(fn))
283 {printsql}SELECT anon_1.value FROM
284 generate_series(:generate_series_1, :generate_series_2) AS anon_1(value){stop}
286 A WITH ORDINALITY expression may be generated by passing the keyword
287 argument :paramref:`.FunctionElement.table_valued.with_ordinality`,
288 illustrated below using PostgreSQL's syntax:
290 .. sourcecode:: pycon+sql
292 >>> fn = func.generate_series(4, 1, -1).table_valued(
293 ... "gen", with_ordinality="ordinality"
294 ... )
295 >>> print(select(fn.render_derived()))
296 {printsql}SELECT anon_1.gen, anon_1.ordinality
297 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3)
298 WITH ORDINALITY AS anon_1(gen, ordinality)
300 :param \*expr: A series of string column names that will be added to the
301 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
302 construct as columns. :func:`_sql.column` objects with or without
303 datatypes may also be used.
305 :param name: optional name to assign to the alias name that's generated.
306 If omitted, a unique anonymizing name is used.
308 :param with_ordinality: string name that when present results in the
309 ``WITH ORDINALITY`` clause being added to the alias, and the given
310 string name will be added as a column to the .c collection
311 of the resulting :class:`_sql.TableValuedAlias`.
313 :param joins_implicitly: when True, the table valued function may be
314 used in the FROM clause without any explicit JOIN to other tables
315 in the SQL query, and no "cartesian product" warning will be generated.
316 May be useful for SQL functions such as ``func.json_each()``.
318 .. versionadded:: 1.4.33
320 .. versionadded:: 1.4.0b2
323 .. seealso::
325 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
327 :ref:`Table-Valued Functions on PostgreSQL <postgresql_table_valued>` - in the :ref:`postgresql_toplevel` documentation
329 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
330 :meth:`_functions.FunctionElement.table_valued` which delivers the
331 complete table valued expression as a scalar column expression
333 :meth:`_functions.FunctionElement.column_valued`
335 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
336 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
338 """ # noqa: 501
340 new_func = self._generate()
342 with_ordinality = kw.pop("with_ordinality", None)
343 joins_implicitly = kw.pop("joins_implicitly", None)
344 name = kw.pop("name", None)
346 if with_ordinality:
347 expr += (with_ordinality,)
348 new_func._with_ordinality = True
350 new_func.type = new_func._table_value_type = TableValueType(*expr)
352 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
354 def column_valued(
355 self, name: Optional[str] = None, joins_implicitly: bool = False
356 ) -> TableValuedColumn[_T]:
357 """Return this :class:`_functions.FunctionElement` as a column expression that
358 selects from itself as a FROM clause.
360 E.g.:
362 .. sourcecode:: pycon+sql
364 >>> from sqlalchemy import select, func
365 >>> gs = func.generate_series(1, 5, -1).column_valued()
366 >>> print(select(gs))
367 {printsql}SELECT anon_1
368 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
370 This is shorthand for::
372 gs = func.generate_series(1, 5, -1).alias().column
374 :param name: optional name to assign to the alias name that's generated.
375 If omitted, a unique anonymizing name is used.
377 :param joins_implicitly: when True, the "table" portion of the column
378 valued function may be a member of the FROM clause without any
379 explicit JOIN to other tables in the SQL query, and no "cartesian
380 product" warning will be generated. May be useful for SQL functions
381 such as ``func.json_array_elements()``.
383 .. versionadded:: 1.4.46
385 .. seealso::
387 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
389 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
391 :meth:`_functions.FunctionElement.table_valued`
393 """ # noqa: 501
395 return self.alias(name=name, joins_implicitly=joins_implicitly).column
397 @util.ro_non_memoized_property
398 def columns(
399 self,
400 ) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:
401 r"""The set of columns exported by this :class:`.FunctionElement`.
403 This is a placeholder collection that allows the function to be
404 placed in the FROM clause of a statement:
406 .. sourcecode:: pycon+sql
408 >>> from sqlalchemy import column, select, func
409 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
410 >>> print(stmt)
411 {printsql}SELECT x, y FROM myfunction()
413 The above form is a legacy feature that is now superseded by the
414 fully capable :meth:`_functions.FunctionElement.table_valued`
415 method; see that method for details.
417 .. seealso::
419 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
420 SQL function expressions.
422 """ # noqa: E501
423 return self.c
425 @util.ro_memoized_property
426 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:
427 """synonym for :attr:`.FunctionElement.columns`."""
429 return WriteableColumnCollection(
430 columns=[(col.key, col) for col in self._all_selected_columns]
431 ).as_readonly()
433 @property
434 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
435 if is_table_value_type(self.type):
436 # TODO: this might not be fully accurate
437 cols = cast(
438 "Sequence[KeyedColumnElement[Any]]", self.type._elements
439 )
440 else:
441 cols = [self.label(None)]
443 return cols
445 @property
446 def exported_columns( # type: ignore[override]
447 self,
448 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
449 return self.columns
451 @HasMemoized.memoized_attribute
452 def clauses(self) -> ClauseList:
453 """Return the underlying :class:`.ClauseList` which contains
454 the arguments for this :class:`.FunctionElement`.
456 """
457 return cast(ClauseList, self.clause_expr.element)
459 def over(
460 self,
461 *,
462 partition_by: _ByArgument | None = None,
463 order_by: _ByArgument | None = None,
464 rows: _FrameIntTuple | FrameClause | None = None,
465 range_: _FrameIntTuple | FrameClause | None = None,
466 groups: _FrameIntTuple | FrameClause | None = None,
467 exclude: str | None = None,
468 ) -> Over[_T]:
469 """Produce an OVER clause against this function.
471 Used against aggregate or so-called "window" functions,
472 for database backends that support window functions.
474 The expression::
476 func.row_number().over(order_by="x")
478 is shorthand for::
480 from sqlalchemy import over
482 over(func.row_number(), order_by="x")
484 See :func:`_expression.over` for a full description.
486 .. seealso::
488 :func:`_expression.over`
490 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
492 """
493 return Over(
494 self,
495 partition_by=partition_by,
496 order_by=order_by,
497 rows=rows,
498 range_=range_,
499 groups=groups,
500 exclude=exclude,
501 )
503 def aggregate_order_by(
504 self, *order_by: _ColumnExpressionArgument[Any]
505 ) -> AggregateOrderBy[_T]:
506 r"""Produce a :class:`.AggregateOrderBy` object against a function.
508 Used for aggregating functions such as :class:`_functions.array_agg`,
509 ``group_concat``, ``json_agg`` on backends that support ordering via an
510 embedded ORDER BY parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
511 When used on backends like Oracle and SQL Server, SQL compilation uses
512 that of :class:`.WithinGroup`.
514 See :func:`_expression.aggregate_order_by` for a full description.
516 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
517 :func:`_postgresql.aggregate_order_by` function to a method on
518 :class:`.Function` that is backend agnostic.
520 .. seealso::
522 :class:`_functions.aggregate_strings` - backend-agnostic string
523 concatenation function which also supports ORDER BY
525 """
527 return AggregateOrderBy(self, *order_by)
529 def within_group(
530 self, *order_by: _ColumnExpressionArgument[Any]
531 ) -> WithinGroup[_T]:
532 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
534 Used against so-called "ordered set aggregate" and "hypothetical
535 set aggregate" functions, including :class:`.percentile_cont`,
536 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
537 used by PostgreSQL, Oracle Database, and Microsoft SQL Server.
539 For simple ORDER BY expressions within aggregate functions on
540 PostgreSQL, MySQL/MariaDB, SQLite, see :func:`_sql.aggregate_order_by`.
542 See :func:`_expression.within_group` for a full description.
544 .. seealso::
546 :ref:`tutorial_functions_within_group` -
547 in the :ref:`unified_tutorial`
550 """
551 return WithinGroup(self, *order_by)
553 @overload
554 def filter(self) -> Self: ...
556 @overload
557 def filter(
558 self,
559 __criterion0: _ColumnExpressionArgument[bool],
560 *criterion: _ColumnExpressionArgument[bool],
561 ) -> FunctionFilter[_T]: ...
563 def filter(
564 self, *criterion: _ColumnExpressionArgument[bool]
565 ) -> Union[Self, FunctionFilter[_T]]:
566 """Produce a FILTER clause against this function.
568 Used against aggregate and window functions,
569 for database backends that support the "FILTER" clause.
571 The expression::
573 func.count(1).filter(True)
575 is shorthand for::
577 from sqlalchemy import funcfilter
579 funcfilter(func.count(1), True)
581 .. seealso::
583 :ref:`tutorial_functions_within_group` -
584 in the :ref:`unified_tutorial`
586 :class:`.FunctionFilter`
588 :func:`.funcfilter`
591 """
592 if not criterion:
593 return self
594 return FunctionFilter(self, *criterion)
596 def as_comparison(
597 self, left_index: int, right_index: int
598 ) -> FunctionAsBinary:
599 """Interpret this expression as a boolean comparison between two
600 values.
602 This method is used for an ORM use case described at
603 :ref:`relationship_custom_operator_sql_function`.
605 A hypothetical SQL function "is_equal()" which compares to values
606 for equality would be written in the Core expression language as::
608 expr = func.is_equal("a", "b")
610 If "is_equal()" above is comparing "a" and "b" for equality, the
611 :meth:`.FunctionElement.as_comparison` method would be invoked as::
613 expr = func.is_equal("a", "b").as_comparison(1, 2)
615 Where above, the integer value "1" refers to the first argument of the
616 "is_equal()" function and the integer value "2" refers to the second.
618 This would create a :class:`.BinaryExpression` that is equivalent to::
620 BinaryExpression("a", "b", operator=op.eq)
622 However, at the SQL level it would still render as
623 "is_equal('a', 'b')".
625 The ORM, when it loads a related object or collection, needs to be able
626 to manipulate the "left" and "right" sides of the ON clause of a JOIN
627 expression. The purpose of this method is to provide a SQL function
628 construct that can also supply this information to the ORM, when used
629 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
630 return value is a containment object called :class:`.FunctionAsBinary`.
632 An ORM example is as follows::
634 class Venue(Base):
635 __tablename__ = "venue"
636 id = Column(Integer, primary_key=True)
637 name = Column(String)
639 descendants = relationship(
640 "Venue",
641 primaryjoin=func.instr(
642 remote(foreign(name)), name + "/"
643 ).as_comparison(1, 2)
644 == 1,
645 viewonly=True,
646 order_by=name,
647 )
649 Above, the "Venue" class can load descendant "Venue" objects by
650 determining if the name of the parent Venue is contained within the
651 start of the hypothetical descendant value's name, e.g. "parent1" would
652 match up to "parent1/child1", but not to "parent2/child1".
654 Possible use cases include the "materialized path" example given above,
655 as well as making use of special SQL functions such as geometric
656 functions to create join conditions.
658 :param left_index: the integer 1-based index of the function argument
659 that serves as the "left" side of the expression.
660 :param right_index: the integer 1-based index of the function argument
661 that serves as the "right" side of the expression.
663 .. seealso::
665 :ref:`relationship_custom_operator_sql_function` -
666 example use within the ORM
668 """
669 return FunctionAsBinary(self, left_index, right_index)
671 @property
672 def _from_objects(self) -> Any:
673 return self.clauses._from_objects
675 def within_group_type(
676 self, within_group: WithinGroup[_S]
677 ) -> Optional[TypeEngine[_S]]:
678 """For types that define their return type as based on the criteria
679 within a WITHIN GROUP (ORDER BY) expression, called by the
680 :class:`.WithinGroup` construct.
682 Returns None by default, in which case the function's normal ``.type``
683 is used.
685 """
687 return None
689 def alias(
690 self, name: Optional[str] = None, joins_implicitly: bool = False
691 ) -> TableValuedAlias:
692 r"""Produce a :class:`_expression.Alias` construct against this
693 :class:`.FunctionElement`.
695 .. tip::
697 The :meth:`_functions.FunctionElement.alias` method is part of the
698 mechanism by which "table valued" SQL functions are created.
699 However, most use cases are covered by higher level methods on
700 :class:`_functions.FunctionElement` including
701 :meth:`_functions.FunctionElement.table_valued`, and
702 :meth:`_functions.FunctionElement.column_valued`.
704 This construct wraps the function in a named alias which
705 is suitable for the FROM clause, in the style accepted for example
706 by PostgreSQL. A column expression is also provided using the
707 special ``.column`` attribute, which may
708 be used to refer to the output of the function as a scalar value
709 in the columns or where clause, for a backend such as PostgreSQL.
711 For a full table-valued expression, use the
712 :meth:`_functions.FunctionElement.table_valued` method first to
713 establish named columns.
715 e.g.:
717 .. sourcecode:: pycon+sql
719 >>> from sqlalchemy import func, select, column
720 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
721 >>> print(select(data_view.column))
722 {printsql}SELECT data_view
723 FROM unnest(:unnest_1) AS data_view
725 The :meth:`_functions.FunctionElement.column_valued` method provides
726 a shortcut for the above pattern:
728 .. sourcecode:: pycon+sql
730 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
731 >>> print(select(data_view))
732 {printsql}SELECT data_view
733 FROM unnest(:unnest_1) AS data_view
735 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
737 :param name: alias name, will be rendered as ``AS <name>`` in the
738 FROM clause
740 :param joins_implicitly: when True, the table valued function may be
741 used in the FROM clause without any explicit JOIN to other tables
742 in the SQL query, and no "cartesian product" warning will be
743 generated. May be useful for SQL functions such as
744 ``func.json_each()``.
746 .. versionadded:: 1.4.33
748 .. seealso::
750 :ref:`tutorial_functions_table_valued` -
751 in the :ref:`unified_tutorial`
753 :meth:`_functions.FunctionElement.table_valued`
755 :meth:`_functions.FunctionElement.scalar_table_valued`
757 :meth:`_functions.FunctionElement.column_valued`
760 """
762 return TableValuedAlias._construct(
763 self,
764 name=name,
765 table_value_type=self.type,
766 joins_implicitly=joins_implicitly,
767 )
769 def select(self) -> Select[_T]:
770 """Produce a :func:`_expression.select` construct
771 against this :class:`.FunctionElement`.
773 This is shorthand for::
775 s = select(function_element)
777 """
778 s: Select[_T] = Select(self)
779 if self._execution_options:
780 s = s.execution_options(**self._execution_options)
781 return s
783 def _bind_param(
784 self,
785 operator: OperatorType,
786 obj: Any,
787 type_: Optional[TypeEngine[_T]] = None,
788 expanding: bool = False,
789 **kw: Any,
790 ) -> BindParameter[_T]:
791 return BindParameter(
792 None,
793 obj,
794 _compared_to_operator=operator,
795 _compared_to_type=self.type,
796 unique=True,
797 type_=type_,
798 expanding=expanding,
799 **kw,
800 )
802 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
803 # for the moment, we are parenthesizing all array-returning
804 # expressions against getitem. This may need to be made
805 # more portable if in the future we support other DBs
806 # besides postgresql.
807 if against is operators.getitem and isinstance(
808 self.type, sqltypes.ARRAY
809 ):
810 return Grouping(self)
811 else:
812 return super().self_group(against=against)
814 @property
815 def entity_namespace(self) -> _EntityNamespace:
816 """overrides FromClause.entity_namespace as functions are generally
817 column expressions and not FromClauses.
819 """
820 # ideally functions would not be fromclauses but we failed to make
821 # this adjustment in 1.4
822 return _entity_namespace(self.clause_expr)
825class FunctionAsBinary(BinaryExpression[Any]):
826 _traverse_internals = [
827 ("sql_function", InternalTraversal.dp_clauseelement),
828 ("left_index", InternalTraversal.dp_plain_obj),
829 ("right_index", InternalTraversal.dp_plain_obj),
830 ("modifiers", InternalTraversal.dp_plain_dict),
831 ]
833 sql_function: FunctionElement[Any]
834 left_index: int
835 right_index: int
837 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
838 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
840 def __init__(
841 self, fn: FunctionElement[Any], left_index: int, right_index: int
842 ) -> None:
843 self.sql_function = fn
844 self.left_index = left_index
845 self.right_index = right_index
847 self.operator = operators.function_as_comparison_op
848 self.type = sqltypes.BOOLEANTYPE
849 self.negate = None
850 self._is_implicitly_boolean = True
851 self.modifiers = util.immutabledict({})
853 @property
854 def left_expr(self) -> ColumnElement[Any]:
855 return self.sql_function.clauses.clauses[self.left_index - 1]
857 @left_expr.setter
858 def left_expr(self, value: ColumnElement[Any]) -> None:
859 self.sql_function.clauses.clauses[self.left_index - 1] = value
861 @property
862 def right_expr(self) -> ColumnElement[Any]:
863 return self.sql_function.clauses.clauses[self.right_index - 1]
865 @right_expr.setter
866 def right_expr(self, value: ColumnElement[Any]) -> None:
867 self.sql_function.clauses.clauses[self.right_index - 1] = value
869 if not TYPE_CHECKING:
870 # mypy can't accommodate @property to replace an instance
871 # variable
873 left = left_expr
874 right = right_expr
877class ScalarFunctionColumn(NamedColumn[_T]):
878 __visit_name__ = "scalar_function_column"
880 _traverse_internals = [
881 ("name", InternalTraversal.dp_anon_name),
882 ("type", InternalTraversal.dp_type),
883 ("fn", InternalTraversal.dp_clauseelement),
884 ]
886 is_literal = False
887 table = None
889 def __init__(
890 self,
891 fn: FunctionElement[_T],
892 name: str,
893 type_: Optional[_TypeEngineArgument[_T]] = None,
894 ) -> None:
895 self.fn = fn
896 self.name = name
898 # if type is None, we get NULLTYPE, which is our _T. But I don't
899 # know how to get the overloads to express that correctly
900 self.type = type_api.to_instance(type_) # type: ignore[assignment]
903class _FunctionGenerator:
904 """Generate SQL function expressions.
906 :data:`.func` is a special object instance which generates SQL
907 functions based on name-based attributes, e.g.:
909 .. sourcecode:: pycon+sql
911 >>> print(func.count(1))
912 {printsql}count(:param_1)
914 The returned object is an instance of :class:`.Function`, and is a
915 column-oriented SQL element like any other, and is used in that way:
917 .. sourcecode:: pycon+sql
919 >>> print(select(func.count(table.c.id)))
920 {printsql}SELECT count(sometable.id) FROM sometable
922 Any name can be given to :data:`.func`. If the function name is unknown to
923 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
924 which SQLAlchemy is aware of, the name may be interpreted as a *generic
925 function* which will be compiled appropriately to the target database:
927 .. sourcecode:: pycon+sql
929 >>> print(func.current_timestamp())
930 {printsql}CURRENT_TIMESTAMP
932 To call functions which are present in dot-separated packages,
933 specify them in the same manner:
935 .. sourcecode:: pycon+sql
937 >>> print(func.stats.yield_curve(5, 10))
938 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
940 SQLAlchemy can be made aware of the return type of functions to enable
941 type-specific lexical and result-based behavior. For example, to ensure
942 that a string-based function returns a Unicode value and is similarly
943 treated as a string in expressions, specify
944 :class:`~sqlalchemy.types.Unicode` as the type:
946 .. sourcecode:: pycon+sql
948 >>> print(
949 ... func.my_string("hi", type_=Unicode)
950 ... + " "
951 ... + func.my_string("there", type_=Unicode)
952 ... )
953 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
955 The object returned by a :data:`.func` call is usually an instance of
956 :class:`.Function`.
957 This object meets the "column" interface, including comparison and labeling
958 functions. The object can also be passed the :meth:`~.Connectable.execute`
959 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
960 where it will be
961 wrapped inside of a SELECT statement first::
963 print(connection.execute(func.current_timestamp()).scalar())
965 In a few exception cases, the :data:`.func` accessor
966 will redirect a name to a built-in expression such as :func:`.cast`
967 or :func:`.extract`, as these names have well-known meaning
968 but are not exactly the same as "functions" from a SQLAlchemy
969 perspective.
971 Functions which are interpreted as "generic" functions know how to
972 calculate their return type automatically. For a listing of known generic
973 functions, see :ref:`generic_functions`.
975 .. note::
977 The :data:`.func` construct has only limited support for calling
978 standalone "stored procedures", especially those with special
979 parameterization concerns.
981 See the section :ref:`stored_procedures` for details on how to use
982 the DBAPI-level ``callproc()`` method for fully traditional stored
983 procedures.
985 .. seealso::
987 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
989 :class:`.Function`
991 """ # noqa
993 def __init__(self, **opts: Any) -> None:
994 self.__names: List[str] = []
995 self.opts = opts
997 def __getattr__(self, name: str) -> _FunctionGenerator:
998 # passthru __ attributes; fixes pydoc
999 if name.startswith("__"):
1000 try:
1001 return self.__dict__[name] # type: ignore[no-any-return]
1002 except KeyError:
1003 raise AttributeError(name)
1005 elif name.endswith("_"):
1006 name = name[0:-1]
1007 f = _FunctionGenerator(**self.opts)
1008 f.__names = list(self.__names) + [name]
1009 return f
1011 @overload
1012 def __call__(
1013 self, *c: Any, type_: _CovariantTypeEngineArgument[_T], **kwargs: Any
1014 ) -> Function[_T]: ...
1016 @overload
1017 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
1019 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
1020 o = self.opts.copy()
1021 o.update(kwargs)
1023 tokens = len(self.__names)
1025 if tokens == 2:
1026 package, fname = self.__names
1027 elif tokens == 1:
1028 package, fname = "_default", self.__names[0]
1029 else:
1030 package = None
1032 if package is not None:
1033 func = _registry[package].get(fname.lower())
1034 if func is not None:
1035 return func(*c, **o)
1037 return Function(
1038 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
1039 )
1041 if TYPE_CHECKING:
1042 # START GENERATED FUNCTION ACCESSORS
1044 # code within this block is **programmatically,
1045 # statically generated** by tools/generate_sql_functions.py
1047 @property
1048 def aggregate_strings(self) -> Type[_aggregate_strings_func]: ...
1050 @property
1051 def all(self) -> Type[_all__func[Any]]: # noqa: A001
1052 ...
1054 @property
1055 def ansifunction(self) -> Type[_AnsiFunction_func[Any]]: ...
1057 @property
1058 def any(self) -> Type[_any__func[Any]]: # noqa: A001
1059 ...
1061 # set ColumnElement[_T] as a separate overload, to appease
1062 # mypy which seems to not want to accept _T from
1063 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1064 # _HasClauseElement as of mypy 1.15
1066 @overload
1067 def array_agg(
1068 self,
1069 col: ColumnElement[_T],
1070 *args: _ColumnExpressionOrLiteralArgument[Any],
1071 **kwargs: Any,
1072 ) -> _array_agg_func[_T]: ...
1074 @overload
1075 def array_agg(
1076 self,
1077 col: _ColumnExpressionArgument[_T],
1078 *args: _ColumnExpressionOrLiteralArgument[Any],
1079 **kwargs: Any,
1080 ) -> _array_agg_func[_T]: ...
1082 @overload
1083 def array_agg(
1084 self,
1085 col: _T,
1086 *args: _ColumnExpressionOrLiteralArgument[Any],
1087 **kwargs: Any,
1088 ) -> _array_agg_func[_T]: ...
1090 def array_agg(
1091 self,
1092 col: _ColumnExpressionOrLiteralArgument[_T],
1093 *args: _ColumnExpressionOrLiteralArgument[Any],
1094 **kwargs: Any,
1095 ) -> _array_agg_func[_T]: ...
1097 @property
1098 def cast(self) -> Type[_Cast_func[Any]]: ...
1100 @property
1101 def char_length(self) -> Type[_char_length_func]: ...
1103 # set ColumnElement[_T] as a separate overload, to appease
1104 # mypy which seems to not want to accept _T from
1105 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1106 # _HasClauseElement as of mypy 1.15
1108 @overload
1109 def coalesce(
1110 self,
1111 col: ColumnElement[_T],
1112 *args: _ColumnExpressionOrLiteralArgument[Any],
1113 **kwargs: Any,
1114 ) -> _coalesce_func[_T]: ...
1116 @overload
1117 def coalesce(
1118 self,
1119 col: _ColumnExpressionArgument[Optional[_T]],
1120 *args: _ColumnExpressionOrLiteralArgument[Any],
1121 **kwargs: Any,
1122 ) -> _coalesce_func[_T]: ...
1124 @overload
1125 def coalesce(
1126 self,
1127 col: Optional[_T],
1128 *args: _ColumnExpressionOrLiteralArgument[Any],
1129 **kwargs: Any,
1130 ) -> _coalesce_func[_T]: ...
1132 def coalesce(
1133 self,
1134 col: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1135 *args: _ColumnExpressionOrLiteralArgument[Any],
1136 **kwargs: Any,
1137 ) -> _coalesce_func[_T]: ...
1139 @property
1140 def concat(self) -> Type[_concat_func]: ...
1142 @property
1143 def count(self) -> Type[_count_func]: ...
1145 @property
1146 def cube(self) -> Type[_cube_func[Any]]: ...
1148 @property
1149 def cume_dist(self) -> Type[_cume_dist_func]: ...
1151 @property
1152 def current_date(self) -> Type[_current_date_func]: ...
1154 @property
1155 def current_time(self) -> Type[_current_time_func]: ...
1157 @property
1158 def current_timestamp(self) -> Type[_current_timestamp_func]: ...
1160 @property
1161 def current_user(self) -> Type[_current_user_func]: ...
1163 @property
1164 def dense_rank(self) -> Type[_dense_rank_func]: ...
1166 @property
1167 def extract(self) -> Type[_Extract_func]: ...
1169 @property
1170 def grouping_sets(self) -> Type[_grouping_sets_func[Any]]: ...
1172 @property
1173 def localtime(self) -> Type[_localtime_func]: ...
1175 @property
1176 def localtimestamp(self) -> Type[_localtimestamp_func]: ...
1178 # set ColumnElement[_T] as a separate overload, to appease
1179 # mypy which seems to not want to accept _T from
1180 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1181 # _HasClauseElement as of mypy 1.15
1183 @overload
1184 def max( # noqa: A001
1185 self,
1186 col: ColumnElement[_T],
1187 *args: _ColumnExpressionOrLiteralArgument[Any],
1188 **kwargs: Any,
1189 ) -> _max_func[_T]: ...
1191 @overload
1192 def max( # noqa: A001
1193 self,
1194 col: _ColumnExpressionArgument[_T],
1195 *args: _ColumnExpressionOrLiteralArgument[Any],
1196 **kwargs: Any,
1197 ) -> _max_func[_T]: ...
1199 @overload
1200 def max( # noqa: A001
1201 self,
1202 col: _T,
1203 *args: _ColumnExpressionOrLiteralArgument[Any],
1204 **kwargs: Any,
1205 ) -> _max_func[_T]: ...
1207 def max( # noqa: A001
1208 self,
1209 col: _ColumnExpressionOrLiteralArgument[_T],
1210 *args: _ColumnExpressionOrLiteralArgument[Any],
1211 **kwargs: Any,
1212 ) -> _max_func[_T]: ...
1214 # set ColumnElement[_T] as a separate overload, to appease
1215 # mypy which seems to not want to accept _T from
1216 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1217 # _HasClauseElement as of mypy 1.15
1219 @overload
1220 def min( # noqa: A001
1221 self,
1222 col: ColumnElement[_T],
1223 *args: _ColumnExpressionOrLiteralArgument[Any],
1224 **kwargs: Any,
1225 ) -> _min_func[_T]: ...
1227 @overload
1228 def min( # noqa: A001
1229 self,
1230 col: _ColumnExpressionArgument[_T],
1231 *args: _ColumnExpressionOrLiteralArgument[Any],
1232 **kwargs: Any,
1233 ) -> _min_func[_T]: ...
1235 @overload
1236 def min( # noqa: A001
1237 self,
1238 col: _T,
1239 *args: _ColumnExpressionOrLiteralArgument[Any],
1240 **kwargs: Any,
1241 ) -> _min_func[_T]: ...
1243 def min( # noqa: A001
1244 self,
1245 col: _ColumnExpressionOrLiteralArgument[_T],
1246 *args: _ColumnExpressionOrLiteralArgument[Any],
1247 **kwargs: Any,
1248 ) -> _min_func[_T]: ...
1250 @property
1251 def mode(self) -> Type[_mode_func[Any]]: ...
1253 @property
1254 def next_value(self) -> Type[_next_value_func]: ...
1256 @property
1257 def now(self) -> Type[_now_func]: ...
1259 @property
1260 def orderedsetagg(self) -> Type[_OrderedSetAgg_func[Any]]: ...
1262 @property
1263 def percent_rank(self) -> Type[_percent_rank_func]: ...
1265 @property
1266 def percentile_cont(self) -> Type[_percentile_cont_func[Any]]: ...
1268 @property
1269 def percentile_disc(self) -> Type[_percentile_disc_func[Any]]: ...
1271 # set ColumnElement[_T] as a separate overload, to appease
1272 # mypy which seems to not want to accept _T from
1273 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1274 # _HasClauseElement as of mypy 1.15
1276 @overload
1277 def pow( # noqa: A001
1278 self,
1279 col: ColumnElement[_T],
1280 *args: _ColumnExpressionOrLiteralArgument[Any],
1281 **kwargs: Any,
1282 ) -> _pow_func[_T]: ...
1284 @overload
1285 def pow( # noqa: A001
1286 self,
1287 col: _ColumnExpressionArgument[_T],
1288 *args: _ColumnExpressionOrLiteralArgument[Any],
1289 **kwargs: Any,
1290 ) -> _pow_func[_T]: ...
1292 @overload
1293 def pow( # noqa: A001
1294 self,
1295 col: _T,
1296 *args: _ColumnExpressionOrLiteralArgument[Any],
1297 **kwargs: Any,
1298 ) -> _pow_func[_T]: ...
1300 def pow( # noqa: A001
1301 self,
1302 col: _ColumnExpressionOrLiteralArgument[_T],
1303 *args: _ColumnExpressionOrLiteralArgument[Any],
1304 **kwargs: Any,
1305 ) -> _pow_func[_T]: ...
1307 @property
1308 def random(self) -> Type[_random_func]: ...
1310 @property
1311 def rank(self) -> Type[_rank_func]: ...
1313 @property
1314 def rollup(self) -> Type[_rollup_func[Any]]: ...
1316 @property
1317 def session_user(self) -> Type[_session_user_func]: ...
1319 @property
1320 def some(self) -> Type[_some_func[Any]]: ...
1322 # set ColumnElement[_T] as a separate overload, to appease
1323 # mypy which seems to not want to accept _T from
1324 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1325 # _HasClauseElement as of mypy 1.15
1327 @overload
1328 def sum( # noqa: A001
1329 self,
1330 col: ColumnElement[_T],
1331 *args: _ColumnExpressionOrLiteralArgument[Any],
1332 **kwargs: Any,
1333 ) -> _sum_func[_T]: ...
1335 @overload
1336 def sum( # noqa: A001
1337 self,
1338 col: _ColumnExpressionArgument[_T],
1339 *args: _ColumnExpressionOrLiteralArgument[Any],
1340 **kwargs: Any,
1341 ) -> _sum_func[_T]: ...
1343 @overload
1344 def sum( # noqa: A001
1345 self,
1346 col: _T,
1347 *args: _ColumnExpressionOrLiteralArgument[Any],
1348 **kwargs: Any,
1349 ) -> _sum_func[_T]: ...
1351 def sum( # noqa: A001
1352 self,
1353 col: _ColumnExpressionOrLiteralArgument[_T],
1354 *args: _ColumnExpressionOrLiteralArgument[Any],
1355 **kwargs: Any,
1356 ) -> _sum_func[_T]: ...
1358 @property
1359 def sysdate(self) -> Type[_sysdate_func]: ...
1361 @property
1362 def user(self) -> Type[_user_func]: ...
1364 # END GENERATED FUNCTION ACCESSORS
1367func = _FunctionGenerator()
1368func.__doc__ = _FunctionGenerator.__doc__
1370modifier = _FunctionGenerator(group=False)
1373class Function(FunctionElement[_T]):
1374 r"""Describe a named SQL function.
1376 The :class:`.Function` object is typically generated from the
1377 :data:`.func` generation object.
1380 :param \*clauses: list of column expressions that form the arguments
1381 of the SQL function call.
1383 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1384 used as the return value of the column expression generated by this
1385 function call.
1387 :param packagenames: a string which indicates package prefix names
1388 to be prepended to the function name when the SQL is generated.
1389 The :data:`.func` generator creates these when it is called using
1390 dotted format, e.g.::
1392 func.mypackage.some_function(col1, col2)
1394 .. seealso::
1396 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1398 :data:`.func` - namespace which produces registered or ad-hoc
1399 :class:`.Function` instances.
1401 :class:`.GenericFunction` - allows creation of registered function
1402 types.
1404 """
1406 __visit_name__ = "function"
1408 _traverse_internals = FunctionElement._traverse_internals + [
1409 ("packagenames", InternalTraversal.dp_plain_obj),
1410 ("name", InternalTraversal.dp_string),
1411 ("type", InternalTraversal.dp_type),
1412 ]
1414 name: str
1416 identifier: str
1418 type: TypeEngine[_T]
1419 """A :class:`_types.TypeEngine` object which refers to the SQL return
1420 type represented by this SQL function.
1422 This datatype may be configured when generating a
1423 :class:`_functions.Function` object by passing the
1424 :paramref:`_functions.Function.type_` parameter, e.g.::
1426 >>> select(func.lower("some VALUE", type_=String))
1428 The small number of built-in classes of :class:`_functions.Function` come
1429 with a built-in datatype that's appropriate to the class of function and
1430 its arguments. For functions that aren't known, the type defaults to the
1431 "null type".
1433 """
1435 @overload
1436 def __init__(
1437 self,
1438 name: str,
1439 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1440 type_: None = ...,
1441 packagenames: Optional[Tuple[str, ...]] = ...,
1442 monotonic: bool = ...,
1443 ) -> None: ...
1445 @overload
1446 def __init__(
1447 self,
1448 name: str,
1449 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1450 type_: _CovariantTypeEngineArgument[_T] = ...,
1451 packagenames: Optional[Tuple[str, ...]] = ...,
1452 monotonic: bool = ...,
1453 ) -> None: ...
1455 def __init__(
1456 self,
1457 name: str,
1458 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1459 type_: Optional[_CovariantTypeEngineArgument[_T]] = None,
1460 packagenames: Optional[Tuple[str, ...]] = None,
1461 monotonic: bool = False,
1462 ) -> None:
1463 """Construct a :class:`.Function`.
1465 The :data:`.func` construct is normally used to construct
1466 new :class:`.Function` instances.
1468 """
1469 self.packagenames = packagenames or ()
1470 self.name = name
1471 self.monotonic = monotonic
1473 # if type is None, we get NULLTYPE, which is our _T. But I don't
1474 # know how to get the overloads to express that correctly
1475 self.type = type_api.to_instance(type_) # type: ignore[assignment]
1477 FunctionElement.__init__(self, *clauses)
1479 def _bind_param(
1480 self,
1481 operator: OperatorType,
1482 obj: Any,
1483 type_: Optional[TypeEngine[_T]] = None,
1484 expanding: bool = False,
1485 **kw: Any,
1486 ) -> BindParameter[_T]:
1487 return BindParameter(
1488 self.name,
1489 obj,
1490 _compared_to_operator=operator,
1491 _compared_to_type=self.type,
1492 type_=type_,
1493 unique=True,
1494 expanding=expanding,
1495 **kw,
1496 )
1499class GenericFunction(Function[_T]):
1500 """Define a 'generic' function.
1502 A generic function is a pre-established :class:`.Function`
1503 class that is instantiated automatically when called
1504 by name from the :data:`.func` attribute. Note that
1505 calling any name from :data:`.func` has the effect that
1506 a new :class:`.Function` instance is created automatically,
1507 given that name. The primary use case for defining
1508 a :class:`.GenericFunction` class is so that a function
1509 of a particular name may be given a fixed return type.
1510 It can also include custom argument parsing schemes as well
1511 as additional methods.
1513 Subclasses of :class:`.GenericFunction` are automatically
1514 registered under the name of the class. For
1515 example, a user-defined function ``as_utc()`` would
1516 be available immediately::
1518 from sqlalchemy.sql.functions import GenericFunction
1519 from sqlalchemy.types import DateTime
1522 class as_utc(GenericFunction):
1523 type = DateTime()
1524 inherit_cache = True
1527 print(select(func.as_utc()))
1529 User-defined generic functions can be organized into
1530 packages by specifying the "package" attribute when defining
1531 :class:`.GenericFunction`. Third party libraries
1532 containing many functions may want to use this in order
1533 to avoid name conflicts with other systems. For example,
1534 if our ``as_utc()`` function were part of a package
1535 "time"::
1537 class as_utc(GenericFunction):
1538 type = DateTime()
1539 package = "time"
1540 inherit_cache = True
1542 The above function would be available from :data:`.func`
1543 using the package name ``time``::
1545 print(select(func.time.as_utc()))
1547 A final option is to allow the function to be accessed
1548 from one name in :data:`.func` but to render as a different name.
1549 The ``identifier`` attribute will override the name used to
1550 access the function as loaded from :data:`.func`, but will retain
1551 the usage of ``name`` as the rendered name::
1553 class GeoBuffer(GenericFunction):
1554 type = Geometry()
1555 package = "geo"
1556 name = "ST_Buffer"
1557 identifier = "buffer"
1558 inherit_cache = True
1560 The above function will render as follows:
1562 .. sourcecode:: pycon+sql
1564 >>> print(func.geo.buffer())
1565 {printsql}ST_Buffer()
1567 The name will be rendered as is, however without quoting unless the name
1568 contains special characters that require quoting. To force quoting
1569 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1570 construct::
1572 from sqlalchemy.sql import quoted_name
1575 class GeoBuffer(GenericFunction):
1576 type = Geometry()
1577 package = "geo"
1578 name = quoted_name("ST_Buffer", True)
1579 identifier = "buffer"
1580 inherit_cache = True
1582 The above function will render as:
1584 .. sourcecode:: pycon+sql
1586 >>> print(func.geo.buffer())
1587 {printsql}"ST_Buffer"()
1589 Type parameters for this class as a
1590 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1591 and should match the type seen in a :class:`_engine.Result`. For example::
1593 class as_utc(GenericFunction[datetime.datetime]):
1594 type = DateTime()
1595 inherit_cache = True
1597 The above indicates that the following expression returns a ``datetime``
1598 object::
1600 connection.scalar(select(func.as_utc()))
1602 """
1604 coerce_arguments = True
1605 inherit_cache = True
1607 _register: bool
1609 name = "GenericFunction"
1611 def __init_subclass__(cls) -> None:
1612 if annotation.Annotated not in cls.__mro__:
1613 cls._register_generic_function(cls.__name__, cls.__dict__)
1614 super().__init_subclass__()
1616 @classmethod
1617 def _register_generic_function(
1618 cls, clsname: str, clsdict: Mapping[str, Any]
1619 ) -> None:
1620 cls.name = name = clsdict.get("name", clsname)
1621 cls.identifier = identifier = clsdict.get("identifier", name)
1622 package = clsdict.get("package", "_default")
1623 # legacy
1624 if "__return_type__" in clsdict:
1625 cls.type = clsdict["__return_type__"]
1627 # Check _register attribute status
1628 cls._register = getattr(cls, "_register", True)
1630 # Register the function if required
1631 if cls._register:
1632 register_function(identifier, cls, package)
1633 else:
1634 # Set _register to True to register child classes by default
1635 cls._register = True
1637 def __init__(
1638 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1639 ) -> None:
1640 parsed_args = kwargs.pop("_parsed_args", None)
1641 if parsed_args is None:
1642 parsed_args = [
1643 coercions.expect(
1644 roles.ExpressionElementRole,
1645 c,
1646 name=self.name,
1647 apply_propagate_attrs=self,
1648 )
1649 for c in args
1650 ]
1651 self._has_args = self._has_args or bool(parsed_args)
1652 self.packagenames = ()
1654 self.clause_expr = Grouping(
1655 ClauseList(
1656 operator=operators.comma_op, group_contents=True, *parsed_args
1657 )
1658 )
1660 self.type = type_api.to_instance( # type: ignore[assignment]
1661 kwargs.pop("type_", None) or getattr(self, "type", None)
1662 )
1665register_function("cast", Cast) # type: ignore[arg-type]
1666register_function("extract", Extract) # type: ignore[arg-type]
1669class next_value(GenericFunction[int]):
1670 """Represent the 'next value', given a :class:`.Sequence`
1671 as its single argument.
1673 Compiles into the appropriate function on each backend,
1674 or will raise NotImplementedError if used on a backend
1675 that does not provide support for sequences.
1677 """
1679 type = sqltypes.Integer()
1680 name = "next_value"
1682 _traverse_internals = [
1683 ("sequence", InternalTraversal.dp_named_ddl_element)
1684 ]
1686 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1687 assert isinstance(
1688 seq, schema.Sequence
1689 ), "next_value() accepts a Sequence object as input."
1690 self.sequence = seq
1691 self.type = sqltypes.to_instance( # type: ignore[assignment]
1692 seq.data_type or getattr(self, "type", None)
1693 )
1695 def compare(self, other: Any, **kw: Any) -> bool:
1696 return (
1697 isinstance(other, next_value)
1698 and self.sequence.name == other.sequence.name
1699 )
1701 @property
1702 def _from_objects(self) -> Any:
1703 return []
1706class AnsiFunction(GenericFunction[_T]):
1707 """Define a function in "ansi" format, which doesn't render parenthesis."""
1709 inherit_cache = True
1711 def __init__(
1712 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1713 ) -> None:
1714 GenericFunction.__init__(self, *args, **kwargs)
1717class CollectionAggregateFunction(GenericFunction[_T]):
1718 """Define a function that acts as a collection aggregate modifier.
1720 Collection aggregate functions such as ``ANY``, ``ALL``, and ``SOME``
1721 modify the semantics of comparison operators, so negation of comparisons
1722 involving these functions must use ``NOT`` rather than flipping the
1723 comparison operator.
1725 .. versionadded:: 2.1
1727 """
1729 _is_collection_aggregate = True
1730 _register = False
1731 inherit_cache = True
1734class ReturnTypeFromArgs(GenericFunction[_T]):
1735 """Define a function whose return type is bound to the type of its
1736 arguments.
1737 """
1739 inherit_cache = True
1741 # set ColumnElement[_T] as a separate overload, to appease
1742 # mypy which seems to not want to accept _T from
1743 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1744 # _HasClauseElement as of mypy 1.15
1746 @overload
1747 def __init__(
1748 self,
1749 col: ColumnElement[_T],
1750 *args: _ColumnExpressionOrLiteralArgument[Any],
1751 **kwargs: Any,
1752 ) -> None: ...
1754 @overload
1755 def __init__(
1756 self,
1757 col: _ColumnExpressionArgument[_T],
1758 *args: _ColumnExpressionOrLiteralArgument[Any],
1759 **kwargs: Any,
1760 ) -> None: ...
1762 @overload
1763 def __init__(
1764 self,
1765 col: _T,
1766 *args: _ColumnExpressionOrLiteralArgument[Any],
1767 **kwargs: Any,
1768 ) -> None: ...
1770 def __init__(
1771 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1772 ) -> None:
1773 fn_args: Sequence[ColumnElement[Any]] = [
1774 coercions.expect(
1775 roles.ExpressionElementRole,
1776 c,
1777 name=self.name,
1778 apply_propagate_attrs=self,
1779 )
1780 for c in args
1781 ]
1782 kwargs.setdefault("type_", _type_from_args(fn_args))
1783 kwargs["_parsed_args"] = fn_args
1784 super().__init__(*fn_args, **kwargs)
1787class ReturnTypeFromOptionalArgs(ReturnTypeFromArgs[_T]):
1788 inherit_cache = True
1790 @overload
1791 def __init__(
1792 self,
1793 col: ColumnElement[_T],
1794 *args: _ColumnExpressionOrLiteralArgument[Any],
1795 **kwargs: Any,
1796 ) -> None: ...
1798 @overload
1799 def __init__(
1800 self,
1801 col: _ColumnExpressionArgument[Optional[_T]],
1802 *args: _ColumnExpressionOrLiteralArgument[Any],
1803 **kwargs: Any,
1804 ) -> None: ...
1806 @overload
1807 def __init__(
1808 self,
1809 col: Optional[_T],
1810 *args: _ColumnExpressionOrLiteralArgument[Any],
1811 **kwargs: Any,
1812 ) -> None: ...
1814 def __init__(
1815 self,
1816 *args: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1817 **kwargs: Any,
1818 ) -> None:
1819 super().__init__(*args, **kwargs) # type: ignore[arg-type]
1822class coalesce(ReturnTypeFromOptionalArgs[_T]):
1823 _has_args = True
1824 inherit_cache = True
1827class max(ReturnTypeFromArgs[_T]): # noqa: A001
1828 """The SQL MAX() aggregate function."""
1830 inherit_cache = True
1833class min(ReturnTypeFromArgs[_T]): # noqa: A001
1834 """The SQL MIN() aggregate function."""
1836 inherit_cache = True
1839class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1840 """The SQL SUM() aggregate function."""
1842 inherit_cache = True
1845class now(GenericFunction[datetime.datetime]):
1846 """The SQL now() datetime function.
1848 SQLAlchemy dialects will usually render this particular function
1849 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1851 """
1853 type = sqltypes.DateTime()
1854 inherit_cache = True
1857class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1858 """The SQL POW() function which performs the power operator.
1860 E.g.:
1862 .. sourcecode:: pycon+sql
1864 >>> print(select(func.pow(2, 8)))
1865 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1867 .. versionadded:: 2.1
1869 """
1871 inherit_cache = True
1874class concat(GenericFunction[str]):
1875 """The SQL CONCAT() function, which concatenates strings.
1877 E.g.:
1879 .. sourcecode:: pycon+sql
1881 >>> print(select(func.concat("a", "b")))
1882 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1884 String concatenation in SQLAlchemy is more commonly available using the
1885 Python ``+`` operator with string datatypes, which will render a
1886 backend-specific concatenation operator, such as :
1888 .. sourcecode:: pycon+sql
1890 >>> print(select(literal("a") + "b"))
1891 {printsql}SELECT :param_1 || :param_2 AS anon_1
1894 """
1896 type = sqltypes.String()
1897 inherit_cache = True
1900class char_length(GenericFunction[int]):
1901 """The CHAR_LENGTH() SQL function."""
1903 type = sqltypes.Integer()
1904 inherit_cache = True
1906 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1907 # slight hack to limit to just one positional argument
1908 # not sure why this one function has this special treatment
1909 super().__init__(arg, **kw)
1912class random(GenericFunction[float]):
1913 """The RANDOM() SQL function."""
1915 _has_args = True
1916 inherit_cache = True
1919class count(GenericFunction[int]):
1920 r"""The ANSI COUNT aggregate function. With no arguments,
1921 emits COUNT \*.
1923 E.g.::
1925 from sqlalchemy import func
1926 from sqlalchemy import select
1927 from sqlalchemy import table, column
1929 my_table = table("some_table", column("id"))
1931 stmt = select(func.count()).select_from(my_table)
1933 Executing ``stmt`` would emit:
1935 .. sourcecode:: sql
1937 SELECT count(*) AS count_1
1938 FROM some_table
1941 """
1943 type = sqltypes.Integer()
1944 inherit_cache = True
1946 def __init__(
1947 self,
1948 expression: Union[
1949 _ColumnExpressionArgument[Any], _StarOrOne, None
1950 ] = None,
1951 **kwargs: Any,
1952 ) -> None:
1953 if expression is None:
1954 expression = literal_column("*")
1955 super().__init__(expression, **kwargs)
1958class current_date(AnsiFunction[datetime.date]):
1959 """The CURRENT_DATE() SQL function."""
1961 type = sqltypes.Date()
1962 inherit_cache = True
1965class current_time(AnsiFunction[datetime.time]):
1966 """The CURRENT_TIME() SQL function."""
1968 type = sqltypes.Time()
1969 inherit_cache = True
1972class current_timestamp(AnsiFunction[datetime.datetime]):
1973 """The CURRENT_TIMESTAMP() SQL function."""
1975 type = sqltypes.DateTime()
1976 inherit_cache = True
1979class current_user(AnsiFunction[str]):
1980 """The CURRENT_USER() SQL function."""
1982 type = sqltypes.String()
1983 inherit_cache = True
1986class localtime(AnsiFunction[datetime.datetime]):
1987 """The localtime() SQL function."""
1989 type = sqltypes.DateTime()
1990 inherit_cache = True
1993class localtimestamp(AnsiFunction[datetime.datetime]):
1994 """The localtimestamp() SQL function."""
1996 type = sqltypes.DateTime()
1997 inherit_cache = True
2000class session_user(AnsiFunction[str]):
2001 """The SESSION_USER() SQL function."""
2003 type = sqltypes.String()
2004 inherit_cache = True
2007class sysdate(AnsiFunction[datetime.datetime]):
2008 """The SYSDATE() SQL function."""
2010 type = sqltypes.DateTime()
2011 inherit_cache = True
2014class user(AnsiFunction[str]):
2015 """The USER() SQL function."""
2017 type = sqltypes.String()
2018 inherit_cache = True
2021class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
2022 """Support for the ARRAY_AGG function.
2024 The ``func.array_agg(expr)`` construct returns an expression of
2025 type :class:`_types.ARRAY`.
2027 e.g.::
2029 stmt = select(func.array_agg(table.c.values)[2:5])
2031 .. seealso::
2033 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
2034 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
2035 added.
2037 """
2039 inherit_cache = True
2041 def __init__(
2042 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
2043 ) -> None:
2044 fn_args: Sequence[ColumnElement[Any]] = [
2045 coercions.expect(
2046 roles.ExpressionElementRole, c, apply_propagate_attrs=self
2047 )
2048 for c in args
2049 ]
2051 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
2052 if "type_" not in kwargs:
2053 type_from_args = _type_from_args(fn_args)
2054 if isinstance(type_from_args, sqltypes.ARRAY):
2055 kwargs["type_"] = type_from_args
2056 else:
2057 kwargs["type_"] = default_array_type(
2058 type_from_args, dimensions=1
2059 )
2060 kwargs["_parsed_args"] = fn_args
2061 super().__init__(*fn_args, **kwargs)
2064class any_(CollectionAggregateFunction[_T]):
2065 """The SQL ANY() collection aggregate function.
2067 .. versionadded:: 2.1
2069 .. seealso::
2071 :func:`_expression.any_` - standalone ANY expression
2073 """
2075 name = "any"
2076 identifier = "any"
2077 inherit_cache = True
2080class all_(CollectionAggregateFunction[_T]):
2081 """The SQL ALL() collection aggregate function.
2083 .. versionadded:: 2.1
2085 .. seealso::
2087 :func:`_expression.all_` - standalone ALL expression
2089 """
2091 name = "all"
2092 identifier = "all"
2093 inherit_cache = True
2096class some(CollectionAggregateFunction[_T]):
2097 """The SQL SOME() collection aggregate function.
2099 SOME is a synonym for ANY in the SQL standard.
2101 .. versionadded:: 2.1
2103 """
2105 inherit_cache = True
2108class OrderedSetAgg(GenericFunction[_T]):
2109 """Define a function where the return type is based on the sort
2110 expression type as defined by the expression passed to the
2111 :meth:`.FunctionElement.within_group` method."""
2113 array_for_multi_clause = False
2114 inherit_cache = True
2116 def within_group_type(
2117 self, within_group: WithinGroup[Any]
2118 ) -> TypeEngine[Any]:
2119 func_clauses = cast(ClauseList, self.clause_expr.element)
2120 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
2121 within_group.order_by
2122 )
2123 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
2124 return sqltypes.ARRAY(order_by[0].type)
2125 else:
2126 return order_by[0].type
2129class mode(OrderedSetAgg[_T]):
2130 """Implement the ``mode`` ordered-set aggregate function.
2132 This function must be used with the :meth:`.FunctionElement.within_group`
2133 modifier to supply a sort expression to operate upon.
2135 The return type of this function is the same as the sort expression.
2137 """
2139 inherit_cache = True
2142class percentile_cont(OrderedSetAgg[_T]):
2143 """Implement the ``percentile_cont`` ordered-set aggregate function.
2145 This function must be used with the :meth:`.FunctionElement.within_group`
2146 modifier to supply a sort expression to operate upon.
2148 The return type of this function is the same as the sort expression,
2149 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2150 expression's type.
2152 """
2154 array_for_multi_clause = True
2155 inherit_cache = True
2158class percentile_disc(OrderedSetAgg[_T]):
2159 """Implement the ``percentile_disc`` ordered-set aggregate function.
2161 This function must be used with the :meth:`.FunctionElement.within_group`
2162 modifier to supply a sort expression to operate upon.
2164 The return type of this function is the same as the sort expression,
2165 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2166 expression's type.
2168 """
2170 array_for_multi_clause = True
2171 inherit_cache = True
2174class rank(GenericFunction[int]):
2175 """Implement the ``rank`` hypothetical-set aggregate function.
2177 This function must be used with the :meth:`.FunctionElement.within_group`
2178 modifier to supply a sort expression to operate upon.
2180 The return type of this function is :class:`.Integer`.
2182 """
2184 type = sqltypes.Integer()
2185 inherit_cache = True
2188class dense_rank(GenericFunction[int]):
2189 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2191 This function must be used with the :meth:`.FunctionElement.within_group`
2192 modifier to supply a sort expression to operate upon.
2194 The return type of this function is :class:`.Integer`.
2196 """
2198 type = sqltypes.Integer()
2199 inherit_cache = True
2202class percent_rank(GenericFunction[decimal.Decimal]):
2203 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2205 This function must be used with the :meth:`.FunctionElement.within_group`
2206 modifier to supply a sort expression to operate upon.
2208 The return type of this function is :class:`.Numeric`.
2210 """
2212 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2213 inherit_cache = True
2216class cume_dist(GenericFunction[decimal.Decimal]):
2217 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2219 This function must be used with the :meth:`.FunctionElement.within_group`
2220 modifier to supply a sort expression to operate upon.
2222 The return type of this function is :class:`.Numeric`.
2224 """
2226 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2227 inherit_cache = True
2230class cube(GenericFunction[_T]):
2231 r"""Implement the ``CUBE`` grouping operation.
2233 This function is used as part of the GROUP BY of a statement,
2234 e.g. :meth:`_expression.Select.group_by`::
2236 stmt = select(
2237 func.sum(table.c.value), table.c.col_1, table.c.col_2
2238 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2240 """
2242 _has_args = True
2243 inherit_cache = True
2246class rollup(GenericFunction[_T]):
2247 r"""Implement the ``ROLLUP`` grouping operation.
2249 This function is used as part of the GROUP BY of a statement,
2250 e.g. :meth:`_expression.Select.group_by`::
2252 stmt = select(
2253 func.sum(table.c.value), table.c.col_1, table.c.col_2
2254 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2256 """
2258 _has_args = True
2259 inherit_cache = True
2262class grouping_sets(GenericFunction[_T]):
2263 r"""Implement the ``GROUPING SETS`` grouping operation.
2265 This function is used as part of the GROUP BY of a statement,
2266 e.g. :meth:`_expression.Select.group_by`::
2268 stmt = select(
2269 func.sum(table.c.value), table.c.col_1, table.c.col_2
2270 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2272 In order to group by multiple sets, use the :func:`.tuple_` construct::
2274 from sqlalchemy import tuple_
2276 stmt = select(
2277 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2278 ).group_by(
2279 func.grouping_sets(
2280 tuple_(table.c.col_1, table.c.col_2),
2281 tuple_(table.c.value, table.c.col_3),
2282 )
2283 )
2285 """ # noqa: E501
2287 _has_args = True
2288 inherit_cache = True
2291class aggregate_strings(GenericFunction[str]):
2292 """Implement a generic string aggregation function.
2294 This function will concatenate non-null values into a string and
2295 separate the values by a delimiter.
2297 This function is compiled on a per-backend basis, into functions
2298 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2300 e.g. Example usage with delimiter '.'::
2302 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2304 .. versionadded:: 2.0.21
2306 To add ordering to the expression, use the
2307 :meth:`_functions.FunctionElement.aggregate_order_by` modifier method,
2308 which will emit ORDER BY within the appropriate part of the column
2309 expression (varies by backend)::
2311 stmt = select(
2312 func.aggregate_strings(table.c.str_col, ".").aggregate_order_by(
2313 table.c.str_col
2314 )
2315 )
2317 .. versionadded:: 2.1 added :meth:`_functions.FunctionElement.aggregate_order_by`
2318 for all aggregate functions.
2320 :param clause: the SQL expression to be concatenated
2322 :param separator: separator string
2325 """ # noqa: E501
2327 type = sqltypes.String()
2328 _has_args = True
2329 inherit_cache = True
2331 def __init__(
2332 self,
2333 clause: _ColumnExpressionArgument[Any],
2334 separator: str,
2335 ) -> None:
2336 super().__init__(clause, separator)
2339# These aliases are required to avoid shadowing the class with the function
2340# name. See https://github.com/sqlalchemy/sqlalchemy/issues/13167
2341# START GENERATED FUNCTION ALIASES
2342_aggregate_strings_func: TypeAlias = aggregate_strings
2343_all__func: TypeAlias = all_[_T]
2344_AnsiFunction_func: TypeAlias = AnsiFunction[_T]
2345_any__func: TypeAlias = any_[_T]
2346_array_agg_func: TypeAlias = array_agg[_T]
2347_Cast_func: TypeAlias = Cast[_T]
2348_char_length_func: TypeAlias = char_length
2349_coalesce_func: TypeAlias = coalesce[_T]
2350_concat_func: TypeAlias = concat
2351_count_func: TypeAlias = count
2352_cube_func: TypeAlias = cube[_T]
2353_cume_dist_func: TypeAlias = cume_dist
2354_current_date_func: TypeAlias = current_date
2355_current_time_func: TypeAlias = current_time
2356_current_timestamp_func: TypeAlias = current_timestamp
2357_current_user_func: TypeAlias = current_user
2358_dense_rank_func: TypeAlias = dense_rank
2359_Extract_func: TypeAlias = Extract
2360_grouping_sets_func: TypeAlias = grouping_sets[_T]
2361_localtime_func: TypeAlias = localtime
2362_localtimestamp_func: TypeAlias = localtimestamp
2363_max_func: TypeAlias = max[_T]
2364_min_func: TypeAlias = min[_T]
2365_mode_func: TypeAlias = mode[_T]
2366_next_value_func: TypeAlias = next_value
2367_now_func: TypeAlias = now
2368_OrderedSetAgg_func: TypeAlias = OrderedSetAgg[_T]
2369_percent_rank_func: TypeAlias = percent_rank
2370_percentile_cont_func: TypeAlias = percentile_cont[_T]
2371_percentile_disc_func: TypeAlias = percentile_disc[_T]
2372_pow_func: TypeAlias = pow[_T]
2373_random_func: TypeAlias = random
2374_rank_func: TypeAlias = rank
2375_rollup_func: TypeAlias = rollup[_T]
2376_session_user_func: TypeAlias = session_user
2377_some_func: TypeAlias = some[_T]
2378_sum_func: TypeAlias = sum[_T]
2379_sysdate_func: TypeAlias = sysdate
2380_user_func: TypeAlias = user
2381# END GENERATED FUNCTION ALIASES