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 _StarOrOne
73 from ._typing import _TypeEngineArgument
74 from .base import _EntityNamespace
75 from .elements import _FrameIntTuple
76 from .elements import ClauseElement
77 from .elements import FrameClause
78 from .elements import KeyedColumnElement
79 from .elements import TableValuedColumn
80 from .operators import OperatorType
81 from ..engine.base import Connection
82 from ..engine.cursor import CursorResult
83 from ..engine.interfaces import _CoreMultiExecuteParams
84 from ..engine.interfaces import CoreExecuteOptionsParameter
85 from ..util.typing import Self
87_T = TypeVar("_T", bound=Any)
88_S = TypeVar("_S", bound=Any)
90_registry: util.defaultdict[str, Dict[str, Type[Function[Any]]]] = (
91 util.defaultdict(dict)
92)
95def register_function(
96 identifier: str, fn: Type[Function[Any]], package: str = "_default"
97) -> None:
98 """Associate a callable with a particular func. name.
100 This is normally called by GenericFunction, but is also
101 available by itself so that a non-Function construct
102 can be associated with the :data:`.func` accessor (i.e.
103 CAST, EXTRACT).
105 """
106 reg = _registry[package]
108 identifier = str(identifier).lower()
110 # Check if a function with the same identifier is registered.
111 if identifier in reg:
112 util.warn(
113 "The GenericFunction '{}' is already registered and "
114 "is going to be overridden.".format(identifier)
115 )
116 reg[identifier] = fn
119class FunctionElement(
120 ColumnElement[_T], ExecutableStatement, FromClause, Generative
121):
122 """Base for SQL function-oriented constructs.
124 This is a `generic type <https://peps.python.org/pep-0484/#generics>`_,
125 meaning that type checkers and IDEs can be instructed on the types to
126 expect in a :class:`_engine.Result` for this function. See
127 :class:`.GenericFunction` for an example of how this is done.
129 .. seealso::
131 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
133 :class:`.Function` - named SQL function.
135 :data:`.func` - namespace which produces registered or ad-hoc
136 :class:`.Function` instances.
138 :class:`.GenericFunction` - allows creation of registered function
139 types.
141 """
143 _traverse_internals = [
144 ("clause_expr", InternalTraversal.dp_clauseelement),
145 ("_with_ordinality", InternalTraversal.dp_boolean),
146 ("_table_value_type", InternalTraversal.dp_has_cache_key),
147 ] + ExecutableStatement._executable_traverse_internals
149 packagenames: Tuple[str, ...] = ()
151 monotonic: bool = False
153 _has_args = False
154 _with_ordinality = False
155 _table_value_type: Optional[TableValueType] = None
157 # some attributes that are defined between both ColumnElement and
158 # FromClause are set to Any here to avoid typing errors
159 primary_key: Any
160 _is_clone_of: Any
162 clause_expr: Grouping[Any]
164 def __init__(
165 self, *clauses: _ColumnExpressionOrLiteralArgument[Any]
166 ) -> None:
167 r"""Construct a :class:`.FunctionElement`.
169 :param \*clauses: list of column expressions that form the arguments
170 of the SQL function call.
172 :param \**kwargs: additional kwargs are typically consumed by
173 subclasses.
175 .. seealso::
177 :data:`.func`
179 :class:`.Function`
181 """
182 args: Sequence[_ColumnExpressionArgument[Any]] = [
183 coercions.expect(
184 roles.ExpressionElementRole,
185 c,
186 name=getattr(self, "name", None),
187 apply_propagate_attrs=self,
188 )
189 for c in clauses
190 ]
191 self._has_args = self._has_args or bool(args)
192 self.clause_expr = Grouping(
193 ClauseList(operator=operators.comma_op, group_contents=True, *args)
194 )
196 _non_anon_label = None
198 @property
199 def _proxy_key(self) -> Any:
200 return super()._proxy_key or getattr(self, "name", None)
202 def _execute_on_connection(
203 self,
204 connection: Connection,
205 distilled_params: _CoreMultiExecuteParams,
206 execution_options: CoreExecuteOptionsParameter,
207 ) -> CursorResult[Any]:
208 return connection._execute_function(
209 self, distilled_params, execution_options
210 )
212 def scalar_table_valued(
213 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
214 ) -> ScalarFunctionColumn[_T]:
215 """Return a column expression that's against this
216 :class:`_functions.FunctionElement` as a scalar
217 table-valued expression.
219 The returned expression is similar to that returned by a single column
220 accessed off of a :meth:`_functions.FunctionElement.table_valued`
221 construct, except no FROM clause is generated; the function is rendered
222 in the similar way as a scalar subquery.
224 E.g.:
226 .. sourcecode:: pycon+sql
228 >>> from sqlalchemy import func, select
229 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
230 >>> print(select(fn))
231 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
233 .. versionadded:: 1.4.0b2
235 .. seealso::
237 :meth:`_functions.FunctionElement.table_valued`
239 :meth:`_functions.FunctionElement.alias`
241 :meth:`_functions.FunctionElement.column_valued`
243 """ # noqa: E501
245 return ScalarFunctionColumn(self, name, type_)
247 def table_valued(
248 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
249 ) -> TableValuedAlias:
250 r"""Return a :class:`_sql.TableValuedAlias` representation of this
251 :class:`_functions.FunctionElement` with table-valued expressions added.
253 e.g. to use the SQLite form of ``generate_series()`` (including
254 hidden columns "start", "stop", "step"):
256 .. sourcecode:: pycon+sql
258 >>> fn = func.generate_series(1, 5).table_valued(
259 ... "value", "start", "stop", "step"
260 ... )
262 >>> print(select(fn))
263 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
264 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
266 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
267 {printsql}SELECT anon_1.value, anon_1.stop
268 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
269 WHERE anon_1.value > :value_1{stop}
271 Backends like PostgreSQL need the accessed columns to be explicitly
272 named in "AS" clause. To achieve this, use
273 :meth:`_sql.TableValuedAlias.render_derived`; be sure to consult the
274 :ref:`PostgreSQL-specific documentation for table valued functions
275 <postgresql_table_valued>` for additional examples:
277 .. sourcecode:: pycon+sql
279 >>> fn = func.generate_series(1, 5).table_valued("value").render_derived()
281 >>> print(select(fn))
282 {printsql}SELECT anon_1.value FROM
283 generate_series(:generate_series_1, :generate_series_2) AS anon_1(value){stop}
285 A WITH ORDINALITY expression may be generated by passing the keyword
286 argument :paramref:`.FunctionElement.table_valued.with_ordinality`,
287 illustrated below using PostgreSQL's syntax:
289 .. sourcecode:: pycon+sql
291 >>> fn = func.generate_series(4, 1, -1).table_valued(
292 ... "gen", with_ordinality="ordinality"
293 ... )
294 >>> print(select(fn.render_derived()))
295 {printsql}SELECT anon_1.gen, anon_1.ordinality
296 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3)
297 WITH ORDINALITY AS anon_1(gen, ordinality)
299 :param \*expr: A series of string column names that will be added to the
300 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
301 construct as columns. :func:`_sql.column` objects with or without
302 datatypes may also be used.
304 :param name: optional name to assign to the alias name that's generated.
305 If omitted, a unique anonymizing name is used.
307 :param with_ordinality: string name that when present results in the
308 ``WITH ORDINALITY`` clause being added to the alias, and the given
309 string name will be added as a column to the .c collection
310 of the resulting :class:`_sql.TableValuedAlias`.
312 :param joins_implicitly: when True, the table valued function may be
313 used in the FROM clause without any explicit JOIN to other tables
314 in the SQL query, and no "cartesian product" warning will be generated.
315 May be useful for SQL functions such as ``func.json_each()``.
317 .. versionadded:: 1.4.33
319 .. versionadded:: 1.4.0b2
322 .. seealso::
324 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
326 :ref:`Table-Valued Functions on PostgreSQL <postgresql_table_valued>` - in the :ref:`postgresql_toplevel` documentation
328 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
329 :meth:`_functions.FunctionElement.table_valued` which delivers the
330 complete table valued expression as a scalar column expression
332 :meth:`_functions.FunctionElement.column_valued`
334 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
335 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
337 """ # noqa: 501
339 new_func = self._generate()
341 with_ordinality = kw.pop("with_ordinality", None)
342 joins_implicitly = kw.pop("joins_implicitly", None)
343 name = kw.pop("name", None)
345 if with_ordinality:
346 expr += (with_ordinality,)
347 new_func._with_ordinality = True
349 new_func.type = new_func._table_value_type = TableValueType(*expr)
351 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
353 def column_valued(
354 self, name: Optional[str] = None, joins_implicitly: bool = False
355 ) -> TableValuedColumn[_T]:
356 """Return this :class:`_functions.FunctionElement` as a column expression that
357 selects from itself as a FROM clause.
359 E.g.:
361 .. sourcecode:: pycon+sql
363 >>> from sqlalchemy import select, func
364 >>> gs = func.generate_series(1, 5, -1).column_valued()
365 >>> print(select(gs))
366 {printsql}SELECT anon_1
367 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
369 This is shorthand for::
371 gs = func.generate_series(1, 5, -1).alias().column
373 :param name: optional name to assign to the alias name that's generated.
374 If omitted, a unique anonymizing name is used.
376 :param joins_implicitly: when True, the "table" portion of the column
377 valued function may be a member of the FROM clause without any
378 explicit JOIN to other tables in the SQL query, and no "cartesian
379 product" warning will be generated. May be useful for SQL functions
380 such as ``func.json_array_elements()``.
382 .. versionadded:: 1.4.46
384 .. seealso::
386 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
388 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
390 :meth:`_functions.FunctionElement.table_valued`
392 """ # noqa: 501
394 return self.alias(name=name, joins_implicitly=joins_implicitly).column
396 @util.ro_non_memoized_property
397 def columns(
398 self,
399 ) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:
400 r"""The set of columns exported by this :class:`.FunctionElement`.
402 This is a placeholder collection that allows the function to be
403 placed in the FROM clause of a statement:
405 .. sourcecode:: pycon+sql
407 >>> from sqlalchemy import column, select, func
408 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
409 >>> print(stmt)
410 {printsql}SELECT x, y FROM myfunction()
412 The above form is a legacy feature that is now superseded by the
413 fully capable :meth:`_functions.FunctionElement.table_valued`
414 method; see that method for details.
416 .. seealso::
418 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
419 SQL function expressions.
421 """ # noqa: E501
422 return self.c
424 @util.ro_memoized_property
425 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:
426 """synonym for :attr:`.FunctionElement.columns`."""
428 return WriteableColumnCollection(
429 columns=[(col.key, col) for col in self._all_selected_columns]
430 ).as_readonly()
432 @property
433 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
434 if is_table_value_type(self.type):
435 # TODO: this might not be fully accurate
436 cols = cast(
437 "Sequence[KeyedColumnElement[Any]]", self.type._elements
438 )
439 else:
440 cols = [self.label(None)]
442 return cols
444 @property
445 def exported_columns( # type: ignore[override]
446 self,
447 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
448 return self.columns
450 @HasMemoized.memoized_attribute
451 def clauses(self) -> ClauseList:
452 """Return the underlying :class:`.ClauseList` which contains
453 the arguments for this :class:`.FunctionElement`.
455 """
456 return cast(ClauseList, self.clause_expr.element)
458 def over(
459 self,
460 *,
461 partition_by: _ByArgument | None = None,
462 order_by: _ByArgument | None = None,
463 rows: _FrameIntTuple | FrameClause | None = None,
464 range_: _FrameIntTuple | FrameClause | None = None,
465 groups: _FrameIntTuple | FrameClause | None = None,
466 exclude: str | None = None,
467 ) -> Over[_T]:
468 """Produce an OVER clause against this function.
470 Used against aggregate or so-called "window" functions,
471 for database backends that support window functions.
473 The expression::
475 func.row_number().over(order_by="x")
477 is shorthand for::
479 from sqlalchemy import over
481 over(func.row_number(), order_by="x")
483 See :func:`_expression.over` for a full description.
485 .. seealso::
487 :func:`_expression.over`
489 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
491 """
492 return Over(
493 self,
494 partition_by=partition_by,
495 order_by=order_by,
496 rows=rows,
497 range_=range_,
498 groups=groups,
499 exclude=exclude,
500 )
502 def aggregate_order_by(
503 self, *order_by: _ColumnExpressionArgument[Any]
504 ) -> AggregateOrderBy[_T]:
505 r"""Produce a :class:`.AggregateOrderBy` object against a function.
507 Used for aggregating functions such as :class:`_functions.array_agg`,
508 ``group_concat``, ``json_agg`` on backends that support ordering via an
509 embedded ORDER BY parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
510 When used on backends like Oracle and SQL Server, SQL compilation uses
511 that of :class:`.WithinGroup`.
513 See :func:`_expression.aggregate_order_by` for a full description.
515 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
516 :func:`_postgresql.aggregate_order_by` function to a method on
517 :class:`.Function` that is backend agnostic.
519 .. seealso::
521 :class:`_functions.aggregate_strings` - backend-agnostic string
522 concatenation function which also supports ORDER BY
524 """
526 return AggregateOrderBy(self, *order_by)
528 def within_group(
529 self, *order_by: _ColumnExpressionArgument[Any]
530 ) -> WithinGroup[_T]:
531 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
533 Used against so-called "ordered set aggregate" and "hypothetical
534 set aggregate" functions, including :class:`.percentile_cont`,
535 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
536 used by PostgreSQL, Oracle Database, and Microsoft SQL Server.
538 For simple ORDER BY expressions within aggregate functions on
539 PostgreSQL, MySQL/MariaDB, SQLite, see :func:`_sql.aggregate_order_by`.
541 See :func:`_expression.within_group` for a full description.
543 .. seealso::
545 :ref:`tutorial_functions_within_group` -
546 in the :ref:`unified_tutorial`
549 """
550 return WithinGroup(self, *order_by)
552 @overload
553 def filter(self) -> Self: ...
555 @overload
556 def filter(
557 self,
558 __criterion0: _ColumnExpressionArgument[bool],
559 *criterion: _ColumnExpressionArgument[bool],
560 ) -> FunctionFilter[_T]: ...
562 def filter(
563 self, *criterion: _ColumnExpressionArgument[bool]
564 ) -> Union[Self, FunctionFilter[_T]]:
565 """Produce a FILTER clause against this function.
567 Used against aggregate and window functions,
568 for database backends that support the "FILTER" clause.
570 The expression::
572 func.count(1).filter(True)
574 is shorthand for::
576 from sqlalchemy import funcfilter
578 funcfilter(func.count(1), True)
580 .. seealso::
582 :ref:`tutorial_functions_within_group` -
583 in the :ref:`unified_tutorial`
585 :class:`.FunctionFilter`
587 :func:`.funcfilter`
590 """
591 if not criterion:
592 return self
593 return FunctionFilter(self, *criterion)
595 def as_comparison(
596 self, left_index: int, right_index: int
597 ) -> FunctionAsBinary:
598 """Interpret this expression as a boolean comparison between two
599 values.
601 This method is used for an ORM use case described at
602 :ref:`relationship_custom_operator_sql_function`.
604 A hypothetical SQL function "is_equal()" which compares to values
605 for equality would be written in the Core expression language as::
607 expr = func.is_equal("a", "b")
609 If "is_equal()" above is comparing "a" and "b" for equality, the
610 :meth:`.FunctionElement.as_comparison` method would be invoked as::
612 expr = func.is_equal("a", "b").as_comparison(1, 2)
614 Where above, the integer value "1" refers to the first argument of the
615 "is_equal()" function and the integer value "2" refers to the second.
617 This would create a :class:`.BinaryExpression` that is equivalent to::
619 BinaryExpression("a", "b", operator=op.eq)
621 However, at the SQL level it would still render as
622 "is_equal('a', 'b')".
624 The ORM, when it loads a related object or collection, needs to be able
625 to manipulate the "left" and "right" sides of the ON clause of a JOIN
626 expression. The purpose of this method is to provide a SQL function
627 construct that can also supply this information to the ORM, when used
628 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
629 return value is a containment object called :class:`.FunctionAsBinary`.
631 An ORM example is as follows::
633 class Venue(Base):
634 __tablename__ = "venue"
635 id = Column(Integer, primary_key=True)
636 name = Column(String)
638 descendants = relationship(
639 "Venue",
640 primaryjoin=func.instr(
641 remote(foreign(name)), name + "/"
642 ).as_comparison(1, 2)
643 == 1,
644 viewonly=True,
645 order_by=name,
646 )
648 Above, the "Venue" class can load descendant "Venue" objects by
649 determining if the name of the parent Venue is contained within the
650 start of the hypothetical descendant value's name, e.g. "parent1" would
651 match up to "parent1/child1", but not to "parent2/child1".
653 Possible use cases include the "materialized path" example given above,
654 as well as making use of special SQL functions such as geometric
655 functions to create join conditions.
657 :param left_index: the integer 1-based index of the function argument
658 that serves as the "left" side of the expression.
659 :param right_index: the integer 1-based index of the function argument
660 that serves as the "right" side of the expression.
662 .. seealso::
664 :ref:`relationship_custom_operator_sql_function` -
665 example use within the ORM
667 """
668 return FunctionAsBinary(self, left_index, right_index)
670 @property
671 def _from_objects(self) -> Any:
672 return self.clauses._from_objects
674 def within_group_type(
675 self, within_group: WithinGroup[_S]
676 ) -> Optional[TypeEngine[_S]]:
677 """For types that define their return type as based on the criteria
678 within a WITHIN GROUP (ORDER BY) expression, called by the
679 :class:`.WithinGroup` construct.
681 Returns None by default, in which case the function's normal ``.type``
682 is used.
684 """
686 return None
688 def alias(
689 self, name: Optional[str] = None, joins_implicitly: bool = False
690 ) -> TableValuedAlias:
691 r"""Produce a :class:`_expression.Alias` construct against this
692 :class:`.FunctionElement`.
694 .. tip::
696 The :meth:`_functions.FunctionElement.alias` method is part of the
697 mechanism by which "table valued" SQL functions are created.
698 However, most use cases are covered by higher level methods on
699 :class:`_functions.FunctionElement` including
700 :meth:`_functions.FunctionElement.table_valued`, and
701 :meth:`_functions.FunctionElement.column_valued`.
703 This construct wraps the function in a named alias which
704 is suitable for the FROM clause, in the style accepted for example
705 by PostgreSQL. A column expression is also provided using the
706 special ``.column`` attribute, which may
707 be used to refer to the output of the function as a scalar value
708 in the columns or where clause, for a backend such as PostgreSQL.
710 For a full table-valued expression, use the
711 :meth:`_functions.FunctionElement.table_valued` method first to
712 establish named columns.
714 e.g.:
716 .. sourcecode:: pycon+sql
718 >>> from sqlalchemy import func, select, column
719 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
720 >>> print(select(data_view.column))
721 {printsql}SELECT data_view
722 FROM unnest(:unnest_1) AS data_view
724 The :meth:`_functions.FunctionElement.column_valued` method provides
725 a shortcut for the above pattern:
727 .. sourcecode:: pycon+sql
729 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
730 >>> print(select(data_view))
731 {printsql}SELECT data_view
732 FROM unnest(:unnest_1) AS data_view
734 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
736 :param name: alias name, will be rendered as ``AS <name>`` in the
737 FROM clause
739 :param joins_implicitly: when True, the table valued function may be
740 used in the FROM clause without any explicit JOIN to other tables
741 in the SQL query, and no "cartesian product" warning will be
742 generated. May be useful for SQL functions such as
743 ``func.json_each()``.
745 .. versionadded:: 1.4.33
747 .. seealso::
749 :ref:`tutorial_functions_table_valued` -
750 in the :ref:`unified_tutorial`
752 :meth:`_functions.FunctionElement.table_valued`
754 :meth:`_functions.FunctionElement.scalar_table_valued`
756 :meth:`_functions.FunctionElement.column_valued`
759 """
761 return TableValuedAlias._construct(
762 self,
763 name=name,
764 table_value_type=self.type,
765 joins_implicitly=joins_implicitly,
766 )
768 def select(self) -> Select[_T]:
769 """Produce a :func:`_expression.select` construct
770 against this :class:`.FunctionElement`.
772 This is shorthand for::
774 s = select(function_element)
776 """
777 s: Select[_T] = Select(self)
778 if self._execution_options:
779 s = s.execution_options(**self._execution_options)
780 return s
782 def _bind_param(
783 self,
784 operator: OperatorType,
785 obj: Any,
786 type_: Optional[TypeEngine[_T]] = None,
787 expanding: bool = False,
788 **kw: Any,
789 ) -> BindParameter[_T]:
790 return BindParameter(
791 None,
792 obj,
793 _compared_to_operator=operator,
794 _compared_to_type=self.type,
795 unique=True,
796 type_=type_,
797 expanding=expanding,
798 **kw,
799 )
801 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
802 # for the moment, we are parenthesizing all array-returning
803 # expressions against getitem. This may need to be made
804 # more portable if in the future we support other DBs
805 # besides postgresql.
806 if against is operators.getitem and isinstance(
807 self.type, sqltypes.ARRAY
808 ):
809 return Grouping(self)
810 else:
811 return super().self_group(against=against)
813 @property
814 def entity_namespace(self) -> _EntityNamespace:
815 """overrides FromClause.entity_namespace as functions are generally
816 column expressions and not FromClauses.
818 """
819 # ideally functions would not be fromclauses but we failed to make
820 # this adjustment in 1.4
821 return _entity_namespace(self.clause_expr)
824class FunctionAsBinary(BinaryExpression[Any]):
825 _traverse_internals = [
826 ("sql_function", InternalTraversal.dp_clauseelement),
827 ("left_index", InternalTraversal.dp_plain_obj),
828 ("right_index", InternalTraversal.dp_plain_obj),
829 ("modifiers", InternalTraversal.dp_plain_dict),
830 ]
832 sql_function: FunctionElement[Any]
833 left_index: int
834 right_index: int
836 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
837 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
839 def __init__(
840 self, fn: FunctionElement[Any], left_index: int, right_index: int
841 ) -> None:
842 self.sql_function = fn
843 self.left_index = left_index
844 self.right_index = right_index
846 self.operator = operators.function_as_comparison_op
847 self.type = sqltypes.BOOLEANTYPE
848 self.negate = None
849 self._is_implicitly_boolean = True
850 self.modifiers = util.immutabledict({})
852 @property
853 def left_expr(self) -> ColumnElement[Any]:
854 return self.sql_function.clauses.clauses[self.left_index - 1]
856 @left_expr.setter
857 def left_expr(self, value: ColumnElement[Any]) -> None:
858 self.sql_function.clauses.clauses[self.left_index - 1] = value
860 @property
861 def right_expr(self) -> ColumnElement[Any]:
862 return self.sql_function.clauses.clauses[self.right_index - 1]
864 @right_expr.setter
865 def right_expr(self, value: ColumnElement[Any]) -> None:
866 self.sql_function.clauses.clauses[self.right_index - 1] = value
868 if not TYPE_CHECKING:
869 # mypy can't accommodate @property to replace an instance
870 # variable
872 left = left_expr
873 right = right_expr
876class ScalarFunctionColumn(NamedColumn[_T]):
877 __visit_name__ = "scalar_function_column"
879 _traverse_internals = [
880 ("name", InternalTraversal.dp_anon_name),
881 ("type", InternalTraversal.dp_type),
882 ("fn", InternalTraversal.dp_clauseelement),
883 ]
885 is_literal = False
886 table = None
888 def __init__(
889 self,
890 fn: FunctionElement[_T],
891 name: str,
892 type_: Optional[_TypeEngineArgument[_T]] = None,
893 ) -> None:
894 self.fn = fn
895 self.name = name
897 # if type is None, we get NULLTYPE, which is our _T. But I don't
898 # know how to get the overloads to express that correctly
899 self.type = type_api.to_instance(type_) # type: ignore
902class _FunctionGenerator:
903 """Generate SQL function expressions.
905 :data:`.func` is a special object instance which generates SQL
906 functions based on name-based attributes, e.g.:
908 .. sourcecode:: pycon+sql
910 >>> print(func.count(1))
911 {printsql}count(:param_1)
913 The returned object is an instance of :class:`.Function`, and is a
914 column-oriented SQL element like any other, and is used in that way:
916 .. sourcecode:: pycon+sql
918 >>> print(select(func.count(table.c.id)))
919 {printsql}SELECT count(sometable.id) FROM sometable
921 Any name can be given to :data:`.func`. If the function name is unknown to
922 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
923 which SQLAlchemy is aware of, the name may be interpreted as a *generic
924 function* which will be compiled appropriately to the target database:
926 .. sourcecode:: pycon+sql
928 >>> print(func.current_timestamp())
929 {printsql}CURRENT_TIMESTAMP
931 To call functions which are present in dot-separated packages,
932 specify them in the same manner:
934 .. sourcecode:: pycon+sql
936 >>> print(func.stats.yield_curve(5, 10))
937 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
939 SQLAlchemy can be made aware of the return type of functions to enable
940 type-specific lexical and result-based behavior. For example, to ensure
941 that a string-based function returns a Unicode value and is similarly
942 treated as a string in expressions, specify
943 :class:`~sqlalchemy.types.Unicode` as the type:
945 .. sourcecode:: pycon+sql
947 >>> print(
948 ... func.my_string("hi", type_=Unicode)
949 ... + " "
950 ... + func.my_string("there", type_=Unicode)
951 ... )
952 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
954 The object returned by a :data:`.func` call is usually an instance of
955 :class:`.Function`.
956 This object meets the "column" interface, including comparison and labeling
957 functions. The object can also be passed the :meth:`~.Connectable.execute`
958 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
959 where it will be
960 wrapped inside of a SELECT statement first::
962 print(connection.execute(func.current_timestamp()).scalar())
964 In a few exception cases, the :data:`.func` accessor
965 will redirect a name to a built-in expression such as :func:`.cast`
966 or :func:`.extract`, as these names have well-known meaning
967 but are not exactly the same as "functions" from a SQLAlchemy
968 perspective.
970 Functions which are interpreted as "generic" functions know how to
971 calculate their return type automatically. For a listing of known generic
972 functions, see :ref:`generic_functions`.
974 .. note::
976 The :data:`.func` construct has only limited support for calling
977 standalone "stored procedures", especially those with special
978 parameterization concerns.
980 See the section :ref:`stored_procedures` for details on how to use
981 the DBAPI-level ``callproc()`` method for fully traditional stored
982 procedures.
984 .. seealso::
986 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
988 :class:`.Function`
990 """ # noqa
992 def __init__(self, **opts: Any) -> None:
993 self.__names: List[str] = []
994 self.opts = opts
996 def __getattr__(self, name: str) -> _FunctionGenerator:
997 # passthru __ attributes; fixes pydoc
998 if name.startswith("__"):
999 try:
1000 return self.__dict__[name] # type: ignore
1001 except KeyError:
1002 raise AttributeError(name)
1004 elif name.endswith("_"):
1005 name = name[0:-1]
1006 f = _FunctionGenerator(**self.opts)
1007 f.__names = list(self.__names) + [name]
1008 return f
1010 @overload
1011 def __call__(
1012 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
1013 ) -> Function[_T]: ...
1015 @overload
1016 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
1018 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
1019 o = self.opts.copy()
1020 o.update(kwargs)
1022 tokens = len(self.__names)
1024 if tokens == 2:
1025 package, fname = self.__names
1026 elif tokens == 1:
1027 package, fname = "_default", self.__names[0]
1028 else:
1029 package = None
1031 if package is not None:
1032 func = _registry[package].get(fname.lower())
1033 if func is not None:
1034 return func(*c, **o)
1036 return Function(
1037 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
1038 )
1040 if TYPE_CHECKING:
1041 # START GENERATED FUNCTION ACCESSORS
1043 # code within this block is **programmatically,
1044 # statically generated** by tools/generate_sql_functions.py
1046 @property
1047 def aggregate_strings(self) -> Type[_aggregate_strings_func]: ...
1049 @property
1050 def all(self) -> Type[_all__func[Any]]: # noqa: A001
1051 ...
1053 @property
1054 def ansifunction(self) -> Type[_AnsiFunction_func[Any]]: ...
1056 @property
1057 def any(self) -> Type[_any__func[Any]]: # noqa: A001
1058 ...
1060 # set ColumnElement[_T] as a separate overload, to appease
1061 # mypy which seems to not want to accept _T from
1062 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1063 # _HasClauseElement as of mypy 1.15
1065 @overload
1066 def array_agg(
1067 self,
1068 col: ColumnElement[_T],
1069 *args: _ColumnExpressionOrLiteralArgument[Any],
1070 **kwargs: Any,
1071 ) -> _array_agg_func[_T]: ...
1073 @overload
1074 def array_agg(
1075 self,
1076 col: _ColumnExpressionArgument[_T],
1077 *args: _ColumnExpressionOrLiteralArgument[Any],
1078 **kwargs: Any,
1079 ) -> _array_agg_func[_T]: ...
1081 @overload
1082 def array_agg(
1083 self,
1084 col: _T,
1085 *args: _ColumnExpressionOrLiteralArgument[Any],
1086 **kwargs: Any,
1087 ) -> _array_agg_func[_T]: ...
1089 def array_agg(
1090 self,
1091 col: _ColumnExpressionOrLiteralArgument[_T],
1092 *args: _ColumnExpressionOrLiteralArgument[Any],
1093 **kwargs: Any,
1094 ) -> _array_agg_func[_T]: ...
1096 @property
1097 def cast(self) -> Type[_Cast_func[Any]]: ...
1099 @property
1100 def char_length(self) -> Type[_char_length_func]: ...
1102 # set ColumnElement[_T] as a separate overload, to appease
1103 # mypy which seems to not want to accept _T from
1104 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1105 # _HasClauseElement as of mypy 1.15
1107 @overload
1108 def coalesce(
1109 self,
1110 col: ColumnElement[_T],
1111 *args: _ColumnExpressionOrLiteralArgument[Any],
1112 **kwargs: Any,
1113 ) -> _coalesce_func[_T]: ...
1115 @overload
1116 def coalesce(
1117 self,
1118 col: _ColumnExpressionArgument[Optional[_T]],
1119 *args: _ColumnExpressionOrLiteralArgument[Any],
1120 **kwargs: Any,
1121 ) -> _coalesce_func[_T]: ...
1123 @overload
1124 def coalesce(
1125 self,
1126 col: Optional[_T],
1127 *args: _ColumnExpressionOrLiteralArgument[Any],
1128 **kwargs: Any,
1129 ) -> _coalesce_func[_T]: ...
1131 def coalesce(
1132 self,
1133 col: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1134 *args: _ColumnExpressionOrLiteralArgument[Any],
1135 **kwargs: Any,
1136 ) -> _coalesce_func[_T]: ...
1138 @property
1139 def concat(self) -> Type[_concat_func]: ...
1141 @property
1142 def count(self) -> Type[_count_func]: ...
1144 @property
1145 def cube(self) -> Type[_cube_func[Any]]: ...
1147 @property
1148 def cume_dist(self) -> Type[_cume_dist_func]: ...
1150 @property
1151 def current_date(self) -> Type[_current_date_func]: ...
1153 @property
1154 def current_time(self) -> Type[_current_time_func]: ...
1156 @property
1157 def current_timestamp(self) -> Type[_current_timestamp_func]: ...
1159 @property
1160 def current_user(self) -> Type[_current_user_func]: ...
1162 @property
1163 def dense_rank(self) -> Type[_dense_rank_func]: ...
1165 @property
1166 def extract(self) -> Type[_Extract_func]: ...
1168 @property
1169 def grouping_sets(self) -> Type[_grouping_sets_func[Any]]: ...
1171 @property
1172 def localtime(self) -> Type[_localtime_func]: ...
1174 @property
1175 def localtimestamp(self) -> Type[_localtimestamp_func]: ...
1177 # set ColumnElement[_T] as a separate overload, to appease
1178 # mypy which seems to not want to accept _T from
1179 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1180 # _HasClauseElement as of mypy 1.15
1182 @overload
1183 def max( # noqa: A001
1184 self,
1185 col: ColumnElement[_T],
1186 *args: _ColumnExpressionOrLiteralArgument[Any],
1187 **kwargs: Any,
1188 ) -> _max_func[_T]: ...
1190 @overload
1191 def max( # noqa: A001
1192 self,
1193 col: _ColumnExpressionArgument[_T],
1194 *args: _ColumnExpressionOrLiteralArgument[Any],
1195 **kwargs: Any,
1196 ) -> _max_func[_T]: ...
1198 @overload
1199 def max( # noqa: A001
1200 self,
1201 col: _T,
1202 *args: _ColumnExpressionOrLiteralArgument[Any],
1203 **kwargs: Any,
1204 ) -> _max_func[_T]: ...
1206 def max( # noqa: A001
1207 self,
1208 col: _ColumnExpressionOrLiteralArgument[_T],
1209 *args: _ColumnExpressionOrLiteralArgument[Any],
1210 **kwargs: Any,
1211 ) -> _max_func[_T]: ...
1213 # set ColumnElement[_T] as a separate overload, to appease
1214 # mypy which seems to not want to accept _T from
1215 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1216 # _HasClauseElement as of mypy 1.15
1218 @overload
1219 def min( # noqa: A001
1220 self,
1221 col: ColumnElement[_T],
1222 *args: _ColumnExpressionOrLiteralArgument[Any],
1223 **kwargs: Any,
1224 ) -> _min_func[_T]: ...
1226 @overload
1227 def min( # noqa: A001
1228 self,
1229 col: _ColumnExpressionArgument[_T],
1230 *args: _ColumnExpressionOrLiteralArgument[Any],
1231 **kwargs: Any,
1232 ) -> _min_func[_T]: ...
1234 @overload
1235 def min( # noqa: A001
1236 self,
1237 col: _T,
1238 *args: _ColumnExpressionOrLiteralArgument[Any],
1239 **kwargs: Any,
1240 ) -> _min_func[_T]: ...
1242 def min( # noqa: A001
1243 self,
1244 col: _ColumnExpressionOrLiteralArgument[_T],
1245 *args: _ColumnExpressionOrLiteralArgument[Any],
1246 **kwargs: Any,
1247 ) -> _min_func[_T]: ...
1249 @property
1250 def mode(self) -> Type[_mode_func[Any]]: ...
1252 @property
1253 def next_value(self) -> Type[_next_value_func]: ...
1255 @property
1256 def now(self) -> Type[_now_func]: ...
1258 @property
1259 def orderedsetagg(self) -> Type[_OrderedSetAgg_func[Any]]: ...
1261 @property
1262 def percent_rank(self) -> Type[_percent_rank_func]: ...
1264 @property
1265 def percentile_cont(self) -> Type[_percentile_cont_func[Any]]: ...
1267 @property
1268 def percentile_disc(self) -> Type[_percentile_disc_func[Any]]: ...
1270 # set ColumnElement[_T] as a separate overload, to appease
1271 # mypy which seems to not want to accept _T from
1272 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1273 # _HasClauseElement as of mypy 1.15
1275 @overload
1276 def pow( # noqa: A001
1277 self,
1278 col: ColumnElement[_T],
1279 *args: _ColumnExpressionOrLiteralArgument[Any],
1280 **kwargs: Any,
1281 ) -> _pow_func[_T]: ...
1283 @overload
1284 def pow( # noqa: A001
1285 self,
1286 col: _ColumnExpressionArgument[_T],
1287 *args: _ColumnExpressionOrLiteralArgument[Any],
1288 **kwargs: Any,
1289 ) -> _pow_func[_T]: ...
1291 @overload
1292 def pow( # noqa: A001
1293 self,
1294 col: _T,
1295 *args: _ColumnExpressionOrLiteralArgument[Any],
1296 **kwargs: Any,
1297 ) -> _pow_func[_T]: ...
1299 def pow( # noqa: A001
1300 self,
1301 col: _ColumnExpressionOrLiteralArgument[_T],
1302 *args: _ColumnExpressionOrLiteralArgument[Any],
1303 **kwargs: Any,
1304 ) -> _pow_func[_T]: ...
1306 @property
1307 def random(self) -> Type[_random_func]: ...
1309 @property
1310 def rank(self) -> Type[_rank_func]: ...
1312 @property
1313 def rollup(self) -> Type[_rollup_func[Any]]: ...
1315 @property
1316 def session_user(self) -> Type[_session_user_func]: ...
1318 @property
1319 def some(self) -> Type[_some_func[Any]]: ...
1321 # set ColumnElement[_T] as a separate overload, to appease
1322 # mypy which seems to not want to accept _T from
1323 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1324 # _HasClauseElement as of mypy 1.15
1326 @overload
1327 def sum( # noqa: A001
1328 self,
1329 col: ColumnElement[_T],
1330 *args: _ColumnExpressionOrLiteralArgument[Any],
1331 **kwargs: Any,
1332 ) -> _sum_func[_T]: ...
1334 @overload
1335 def sum( # noqa: A001
1336 self,
1337 col: _ColumnExpressionArgument[_T],
1338 *args: _ColumnExpressionOrLiteralArgument[Any],
1339 **kwargs: Any,
1340 ) -> _sum_func[_T]: ...
1342 @overload
1343 def sum( # noqa: A001
1344 self,
1345 col: _T,
1346 *args: _ColumnExpressionOrLiteralArgument[Any],
1347 **kwargs: Any,
1348 ) -> _sum_func[_T]: ...
1350 def sum( # noqa: A001
1351 self,
1352 col: _ColumnExpressionOrLiteralArgument[_T],
1353 *args: _ColumnExpressionOrLiteralArgument[Any],
1354 **kwargs: Any,
1355 ) -> _sum_func[_T]: ...
1357 @property
1358 def sysdate(self) -> Type[_sysdate_func]: ...
1360 @property
1361 def user(self) -> Type[_user_func]: ...
1363 # END GENERATED FUNCTION ACCESSORS
1366func = _FunctionGenerator()
1367func.__doc__ = _FunctionGenerator.__doc__
1369modifier = _FunctionGenerator(group=False)
1372class Function(FunctionElement[_T]):
1373 r"""Describe a named SQL function.
1375 The :class:`.Function` object is typically generated from the
1376 :data:`.func` generation object.
1379 :param \*clauses: list of column expressions that form the arguments
1380 of the SQL function call.
1382 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1383 used as the return value of the column expression generated by this
1384 function call.
1386 :param packagenames: a string which indicates package prefix names
1387 to be prepended to the function name when the SQL is generated.
1388 The :data:`.func` generator creates these when it is called using
1389 dotted format, e.g.::
1391 func.mypackage.some_function(col1, col2)
1393 .. seealso::
1395 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1397 :data:`.func` - namespace which produces registered or ad-hoc
1398 :class:`.Function` instances.
1400 :class:`.GenericFunction` - allows creation of registered function
1401 types.
1403 """
1405 __visit_name__ = "function"
1407 _traverse_internals = FunctionElement._traverse_internals + [
1408 ("packagenames", InternalTraversal.dp_plain_obj),
1409 ("name", InternalTraversal.dp_string),
1410 ("type", InternalTraversal.dp_type),
1411 ]
1413 name: str
1415 identifier: str
1417 type: TypeEngine[_T]
1418 """A :class:`_types.TypeEngine` object which refers to the SQL return
1419 type represented by this SQL function.
1421 This datatype may be configured when generating a
1422 :class:`_functions.Function` object by passing the
1423 :paramref:`_functions.Function.type_` parameter, e.g.::
1425 >>> select(func.lower("some VALUE", type_=String))
1427 The small number of built-in classes of :class:`_functions.Function` come
1428 with a built-in datatype that's appropriate to the class of function and
1429 its arguments. For functions that aren't known, the type defaults to the
1430 "null type".
1432 """
1434 @overload
1435 def __init__(
1436 self,
1437 name: str,
1438 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1439 type_: None = ...,
1440 packagenames: Optional[Tuple[str, ...]] = ...,
1441 monotonic: bool = ...,
1442 ) -> None: ...
1444 @overload
1445 def __init__(
1446 self,
1447 name: str,
1448 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1449 type_: _TypeEngineArgument[_T] = ...,
1450 packagenames: Optional[Tuple[str, ...]] = ...,
1451 monotonic: bool = ...,
1452 ) -> None: ...
1454 def __init__(
1455 self,
1456 name: str,
1457 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1458 type_: Optional[_TypeEngineArgument[_T]] = None,
1459 packagenames: Optional[Tuple[str, ...]] = None,
1460 monotonic: bool = False,
1461 ) -> None:
1462 """Construct a :class:`.Function`.
1464 The :data:`.func` construct is normally used to construct
1465 new :class:`.Function` instances.
1467 """
1468 self.packagenames = packagenames or ()
1469 self.name = name
1470 self.monotonic = monotonic
1472 # if type is None, we get NULLTYPE, which is our _T. But I don't
1473 # know how to get the overloads to express that correctly
1474 self.type = type_api.to_instance(type_) # type: ignore
1476 FunctionElement.__init__(self, *clauses)
1478 def _bind_param(
1479 self,
1480 operator: OperatorType,
1481 obj: Any,
1482 type_: Optional[TypeEngine[_T]] = None,
1483 expanding: bool = False,
1484 **kw: Any,
1485 ) -> BindParameter[_T]:
1486 return BindParameter(
1487 self.name,
1488 obj,
1489 _compared_to_operator=operator,
1490 _compared_to_type=self.type,
1491 type_=type_,
1492 unique=True,
1493 expanding=expanding,
1494 **kw,
1495 )
1498class GenericFunction(Function[_T]):
1499 """Define a 'generic' function.
1501 A generic function is a pre-established :class:`.Function`
1502 class that is instantiated automatically when called
1503 by name from the :data:`.func` attribute. Note that
1504 calling any name from :data:`.func` has the effect that
1505 a new :class:`.Function` instance is created automatically,
1506 given that name. The primary use case for defining
1507 a :class:`.GenericFunction` class is so that a function
1508 of a particular name may be given a fixed return type.
1509 It can also include custom argument parsing schemes as well
1510 as additional methods.
1512 Subclasses of :class:`.GenericFunction` are automatically
1513 registered under the name of the class. For
1514 example, a user-defined function ``as_utc()`` would
1515 be available immediately::
1517 from sqlalchemy.sql.functions import GenericFunction
1518 from sqlalchemy.types import DateTime
1521 class as_utc(GenericFunction):
1522 type = DateTime()
1523 inherit_cache = True
1526 print(select(func.as_utc()))
1528 User-defined generic functions can be organized into
1529 packages by specifying the "package" attribute when defining
1530 :class:`.GenericFunction`. Third party libraries
1531 containing many functions may want to use this in order
1532 to avoid name conflicts with other systems. For example,
1533 if our ``as_utc()`` function were part of a package
1534 "time"::
1536 class as_utc(GenericFunction):
1537 type = DateTime()
1538 package = "time"
1539 inherit_cache = True
1541 The above function would be available from :data:`.func`
1542 using the package name ``time``::
1544 print(select(func.time.as_utc()))
1546 A final option is to allow the function to be accessed
1547 from one name in :data:`.func` but to render as a different name.
1548 The ``identifier`` attribute will override the name used to
1549 access the function as loaded from :data:`.func`, but will retain
1550 the usage of ``name`` as the rendered name::
1552 class GeoBuffer(GenericFunction):
1553 type = Geometry()
1554 package = "geo"
1555 name = "ST_Buffer"
1556 identifier = "buffer"
1557 inherit_cache = True
1559 The above function will render as follows:
1561 .. sourcecode:: pycon+sql
1563 >>> print(func.geo.buffer())
1564 {printsql}ST_Buffer()
1566 The name will be rendered as is, however without quoting unless the name
1567 contains special characters that require quoting. To force quoting
1568 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1569 construct::
1571 from sqlalchemy.sql import quoted_name
1574 class GeoBuffer(GenericFunction):
1575 type = Geometry()
1576 package = "geo"
1577 name = quoted_name("ST_Buffer", True)
1578 identifier = "buffer"
1579 inherit_cache = True
1581 The above function will render as:
1583 .. sourcecode:: pycon+sql
1585 >>> print(func.geo.buffer())
1586 {printsql}"ST_Buffer"()
1588 Type parameters for this class as a
1589 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1590 and should match the type seen in a :class:`_engine.Result`. For example::
1592 class as_utc(GenericFunction[datetime.datetime]):
1593 type = DateTime()
1594 inherit_cache = True
1596 The above indicates that the following expression returns a ``datetime``
1597 object::
1599 connection.scalar(select(func.as_utc()))
1601 """
1603 coerce_arguments = True
1604 inherit_cache = True
1606 _register: bool
1608 name = "GenericFunction"
1610 def __init_subclass__(cls) -> None:
1611 if annotation.Annotated not in cls.__mro__:
1612 cls._register_generic_function(cls.__name__, cls.__dict__)
1613 super().__init_subclass__()
1615 @classmethod
1616 def _register_generic_function(
1617 cls, clsname: str, clsdict: Mapping[str, Any]
1618 ) -> None:
1619 cls.name = name = clsdict.get("name", clsname)
1620 cls.identifier = identifier = clsdict.get("identifier", name)
1621 package = clsdict.get("package", "_default")
1622 # legacy
1623 if "__return_type__" in clsdict:
1624 cls.type = clsdict["__return_type__"]
1626 # Check _register attribute status
1627 cls._register = getattr(cls, "_register", True)
1629 # Register the function if required
1630 if cls._register:
1631 register_function(identifier, cls, package)
1632 else:
1633 # Set _register to True to register child classes by default
1634 cls._register = True
1636 def __init__(
1637 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1638 ) -> None:
1639 parsed_args = kwargs.pop("_parsed_args", None)
1640 if parsed_args is None:
1641 parsed_args = [
1642 coercions.expect(
1643 roles.ExpressionElementRole,
1644 c,
1645 name=self.name,
1646 apply_propagate_attrs=self,
1647 )
1648 for c in args
1649 ]
1650 self._has_args = self._has_args or bool(parsed_args)
1651 self.packagenames = ()
1653 self.clause_expr = Grouping(
1654 ClauseList(
1655 operator=operators.comma_op, group_contents=True, *parsed_args
1656 )
1657 )
1659 self.type = type_api.to_instance( # type: ignore
1660 kwargs.pop("type_", None) or getattr(self, "type", None)
1661 )
1664register_function("cast", Cast) # type: ignore
1665register_function("extract", Extract) # type: ignore
1668class next_value(GenericFunction[int]):
1669 """Represent the 'next value', given a :class:`.Sequence`
1670 as its single argument.
1672 Compiles into the appropriate function on each backend,
1673 or will raise NotImplementedError if used on a backend
1674 that does not provide support for sequences.
1676 """
1678 type = sqltypes.Integer()
1679 name = "next_value"
1681 _traverse_internals = [
1682 ("sequence", InternalTraversal.dp_named_ddl_element)
1683 ]
1685 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1686 assert isinstance(
1687 seq, schema.Sequence
1688 ), "next_value() accepts a Sequence object as input."
1689 self.sequence = seq
1690 self.type = sqltypes.to_instance( # type: ignore
1691 seq.data_type or getattr(self, "type", None)
1692 )
1694 def compare(self, other: Any, **kw: Any) -> bool:
1695 return (
1696 isinstance(other, next_value)
1697 and self.sequence.name == other.sequence.name
1698 )
1700 @property
1701 def _from_objects(self) -> Any:
1702 return []
1705class AnsiFunction(GenericFunction[_T]):
1706 """Define a function in "ansi" format, which doesn't render parenthesis."""
1708 inherit_cache = True
1710 def __init__(
1711 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1712 ) -> None:
1713 GenericFunction.__init__(self, *args, **kwargs)
1716class CollectionAggregateFunction(GenericFunction[_T]):
1717 """Define a function that acts as a collection aggregate modifier.
1719 Collection aggregate functions such as ``ANY``, ``ALL``, and ``SOME``
1720 modify the semantics of comparison operators, so negation of comparisons
1721 involving these functions must use ``NOT`` rather than flipping the
1722 comparison operator.
1724 .. versionadded:: 2.1
1726 """
1728 _is_collection_aggregate = True
1729 _register = False
1730 inherit_cache = True
1733class ReturnTypeFromArgs(GenericFunction[_T]):
1734 """Define a function whose return type is bound to the type of its
1735 arguments.
1736 """
1738 inherit_cache = True
1740 # set ColumnElement[_T] as a separate overload, to appease
1741 # mypy which seems to not want to accept _T from
1742 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1743 # _HasClauseElement as of mypy 1.15
1745 @overload
1746 def __init__(
1747 self,
1748 col: ColumnElement[_T],
1749 *args: _ColumnExpressionOrLiteralArgument[Any],
1750 **kwargs: Any,
1751 ) -> None: ...
1753 @overload
1754 def __init__(
1755 self,
1756 col: _ColumnExpressionArgument[_T],
1757 *args: _ColumnExpressionOrLiteralArgument[Any],
1758 **kwargs: Any,
1759 ) -> None: ...
1761 @overload
1762 def __init__(
1763 self,
1764 col: _T,
1765 *args: _ColumnExpressionOrLiteralArgument[Any],
1766 **kwargs: Any,
1767 ) -> None: ...
1769 def __init__(
1770 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1771 ) -> None:
1772 fn_args: Sequence[ColumnElement[Any]] = [
1773 coercions.expect(
1774 roles.ExpressionElementRole,
1775 c,
1776 name=self.name,
1777 apply_propagate_attrs=self,
1778 )
1779 for c in args
1780 ]
1781 kwargs.setdefault("type_", _type_from_args(fn_args))
1782 kwargs["_parsed_args"] = fn_args
1783 super().__init__(*fn_args, **kwargs)
1786class ReturnTypeFromOptionalArgs(ReturnTypeFromArgs[_T]):
1787 inherit_cache = True
1789 @overload
1790 def __init__(
1791 self,
1792 col: ColumnElement[_T],
1793 *args: _ColumnExpressionOrLiteralArgument[Any],
1794 **kwargs: Any,
1795 ) -> None: ...
1797 @overload
1798 def __init__(
1799 self,
1800 col: _ColumnExpressionArgument[Optional[_T]],
1801 *args: _ColumnExpressionOrLiteralArgument[Any],
1802 **kwargs: Any,
1803 ) -> None: ...
1805 @overload
1806 def __init__(
1807 self,
1808 col: Optional[_T],
1809 *args: _ColumnExpressionOrLiteralArgument[Any],
1810 **kwargs: Any,
1811 ) -> None: ...
1813 def __init__(
1814 self,
1815 *args: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1816 **kwargs: Any,
1817 ) -> None:
1818 super().__init__(*args, **kwargs) # type: ignore
1821class coalesce(ReturnTypeFromOptionalArgs[_T]):
1822 _has_args = True
1823 inherit_cache = True
1826class max(ReturnTypeFromArgs[_T]): # noqa: A001
1827 """The SQL MAX() aggregate function."""
1829 inherit_cache = True
1832class min(ReturnTypeFromArgs[_T]): # noqa: A001
1833 """The SQL MIN() aggregate function."""
1835 inherit_cache = True
1838class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1839 """The SQL SUM() aggregate function."""
1841 inherit_cache = True
1844class now(GenericFunction[datetime.datetime]):
1845 """The SQL now() datetime function.
1847 SQLAlchemy dialects will usually render this particular function
1848 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1850 """
1852 type = sqltypes.DateTime()
1853 inherit_cache = True
1856class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1857 """The SQL POW() function which performs the power operator.
1859 E.g.:
1861 .. sourcecode:: pycon+sql
1863 >>> print(select(func.pow(2, 8)))
1864 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1866 .. versionadded:: 2.1
1868 """
1870 inherit_cache = True
1873class concat(GenericFunction[str]):
1874 """The SQL CONCAT() function, which concatenates strings.
1876 E.g.:
1878 .. sourcecode:: pycon+sql
1880 >>> print(select(func.concat("a", "b")))
1881 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1883 String concatenation in SQLAlchemy is more commonly available using the
1884 Python ``+`` operator with string datatypes, which will render a
1885 backend-specific concatenation operator, such as :
1887 .. sourcecode:: pycon+sql
1889 >>> print(select(literal("a") + "b"))
1890 {printsql}SELECT :param_1 || :param_2 AS anon_1
1893 """
1895 type = sqltypes.String()
1896 inherit_cache = True
1899class char_length(GenericFunction[int]):
1900 """The CHAR_LENGTH() SQL function."""
1902 type = sqltypes.Integer()
1903 inherit_cache = True
1905 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1906 # slight hack to limit to just one positional argument
1907 # not sure why this one function has this special treatment
1908 super().__init__(arg, **kw)
1911class random(GenericFunction[float]):
1912 """The RANDOM() SQL function."""
1914 _has_args = True
1915 inherit_cache = True
1918class count(GenericFunction[int]):
1919 r"""The ANSI COUNT aggregate function. With no arguments,
1920 emits COUNT \*.
1922 E.g.::
1924 from sqlalchemy import func
1925 from sqlalchemy import select
1926 from sqlalchemy import table, column
1928 my_table = table("some_table", column("id"))
1930 stmt = select(func.count()).select_from(my_table)
1932 Executing ``stmt`` would emit:
1934 .. sourcecode:: sql
1936 SELECT count(*) AS count_1
1937 FROM some_table
1940 """
1942 type = sqltypes.Integer()
1943 inherit_cache = True
1945 def __init__(
1946 self,
1947 expression: Union[
1948 _ColumnExpressionArgument[Any], _StarOrOne, None
1949 ] = None,
1950 **kwargs: Any,
1951 ) -> None:
1952 if expression is None:
1953 expression = literal_column("*")
1954 super().__init__(expression, **kwargs)
1957class current_date(AnsiFunction[datetime.date]):
1958 """The CURRENT_DATE() SQL function."""
1960 type = sqltypes.Date()
1961 inherit_cache = True
1964class current_time(AnsiFunction[datetime.time]):
1965 """The CURRENT_TIME() SQL function."""
1967 type = sqltypes.Time()
1968 inherit_cache = True
1971class current_timestamp(AnsiFunction[datetime.datetime]):
1972 """The CURRENT_TIMESTAMP() SQL function."""
1974 type = sqltypes.DateTime()
1975 inherit_cache = True
1978class current_user(AnsiFunction[str]):
1979 """The CURRENT_USER() SQL function."""
1981 type = sqltypes.String()
1982 inherit_cache = True
1985class localtime(AnsiFunction[datetime.datetime]):
1986 """The localtime() SQL function."""
1988 type = sqltypes.DateTime()
1989 inherit_cache = True
1992class localtimestamp(AnsiFunction[datetime.datetime]):
1993 """The localtimestamp() SQL function."""
1995 type = sqltypes.DateTime()
1996 inherit_cache = True
1999class session_user(AnsiFunction[str]):
2000 """The SESSION_USER() SQL function."""
2002 type = sqltypes.String()
2003 inherit_cache = True
2006class sysdate(AnsiFunction[datetime.datetime]):
2007 """The SYSDATE() SQL function."""
2009 type = sqltypes.DateTime()
2010 inherit_cache = True
2013class user(AnsiFunction[str]):
2014 """The USER() SQL function."""
2016 type = sqltypes.String()
2017 inherit_cache = True
2020class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
2021 """Support for the ARRAY_AGG function.
2023 The ``func.array_agg(expr)`` construct returns an expression of
2024 type :class:`_types.ARRAY`.
2026 e.g.::
2028 stmt = select(func.array_agg(table.c.values)[2:5])
2030 .. seealso::
2032 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
2033 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
2034 added.
2036 """
2038 inherit_cache = True
2040 def __init__(
2041 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
2042 ) -> None:
2043 fn_args: Sequence[ColumnElement[Any]] = [
2044 coercions.expect(
2045 roles.ExpressionElementRole, c, apply_propagate_attrs=self
2046 )
2047 for c in args
2048 ]
2050 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
2051 if "type_" not in kwargs:
2052 type_from_args = _type_from_args(fn_args)
2053 if isinstance(type_from_args, sqltypes.ARRAY):
2054 kwargs["type_"] = type_from_args
2055 else:
2056 kwargs["type_"] = default_array_type(
2057 type_from_args, dimensions=1
2058 )
2059 kwargs["_parsed_args"] = fn_args
2060 super().__init__(*fn_args, **kwargs)
2063class any_(CollectionAggregateFunction[_T]):
2064 """The SQL ANY() collection aggregate function.
2066 .. versionadded:: 2.1
2068 .. seealso::
2070 :func:`_expression.any_` - standalone ANY expression
2072 """
2074 name = "any"
2075 identifier = "any"
2076 inherit_cache = True
2079class all_(CollectionAggregateFunction[_T]):
2080 """The SQL ALL() collection aggregate function.
2082 .. versionadded:: 2.1
2084 .. seealso::
2086 :func:`_expression.all_` - standalone ALL expression
2088 """
2090 name = "all"
2091 identifier = "all"
2092 inherit_cache = True
2095class some(CollectionAggregateFunction[_T]):
2096 """The SQL SOME() collection aggregate function.
2098 SOME is a synonym for ANY in the SQL standard.
2100 .. versionadded:: 2.1
2102 """
2104 inherit_cache = True
2107class OrderedSetAgg(GenericFunction[_T]):
2108 """Define a function where the return type is based on the sort
2109 expression type as defined by the expression passed to the
2110 :meth:`.FunctionElement.within_group` method."""
2112 array_for_multi_clause = False
2113 inherit_cache = True
2115 def within_group_type(
2116 self, within_group: WithinGroup[Any]
2117 ) -> TypeEngine[Any]:
2118 func_clauses = cast(ClauseList, self.clause_expr.element)
2119 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
2120 within_group.order_by
2121 )
2122 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
2123 return sqltypes.ARRAY(order_by[0].type)
2124 else:
2125 return order_by[0].type
2128class mode(OrderedSetAgg[_T]):
2129 """Implement the ``mode`` ordered-set aggregate function.
2131 This function must be used with the :meth:`.FunctionElement.within_group`
2132 modifier to supply a sort expression to operate upon.
2134 The return type of this function is the same as the sort expression.
2136 """
2138 inherit_cache = True
2141class percentile_cont(OrderedSetAgg[_T]):
2142 """Implement the ``percentile_cont`` ordered-set aggregate function.
2144 This function must be used with the :meth:`.FunctionElement.within_group`
2145 modifier to supply a sort expression to operate upon.
2147 The return type of this function is the same as the sort expression,
2148 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2149 expression's type.
2151 """
2153 array_for_multi_clause = True
2154 inherit_cache = True
2157class percentile_disc(OrderedSetAgg[_T]):
2158 """Implement the ``percentile_disc`` ordered-set aggregate function.
2160 This function must be used with the :meth:`.FunctionElement.within_group`
2161 modifier to supply a sort expression to operate upon.
2163 The return type of this function is the same as the sort expression,
2164 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2165 expression's type.
2167 """
2169 array_for_multi_clause = True
2170 inherit_cache = True
2173class rank(GenericFunction[int]):
2174 """Implement the ``rank`` hypothetical-set aggregate function.
2176 This function must be used with the :meth:`.FunctionElement.within_group`
2177 modifier to supply a sort expression to operate upon.
2179 The return type of this function is :class:`.Integer`.
2181 """
2183 type = sqltypes.Integer()
2184 inherit_cache = True
2187class dense_rank(GenericFunction[int]):
2188 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2190 This function must be used with the :meth:`.FunctionElement.within_group`
2191 modifier to supply a sort expression to operate upon.
2193 The return type of this function is :class:`.Integer`.
2195 """
2197 type = sqltypes.Integer()
2198 inherit_cache = True
2201class percent_rank(GenericFunction[decimal.Decimal]):
2202 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2204 This function must be used with the :meth:`.FunctionElement.within_group`
2205 modifier to supply a sort expression to operate upon.
2207 The return type of this function is :class:`.Numeric`.
2209 """
2211 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2212 inherit_cache = True
2215class cume_dist(GenericFunction[decimal.Decimal]):
2216 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2218 This function must be used with the :meth:`.FunctionElement.within_group`
2219 modifier to supply a sort expression to operate upon.
2221 The return type of this function is :class:`.Numeric`.
2223 """
2225 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2226 inherit_cache = True
2229class cube(GenericFunction[_T]):
2230 r"""Implement the ``CUBE`` grouping operation.
2232 This function is used as part of the GROUP BY of a statement,
2233 e.g. :meth:`_expression.Select.group_by`::
2235 stmt = select(
2236 func.sum(table.c.value), table.c.col_1, table.c.col_2
2237 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2239 """
2241 _has_args = True
2242 inherit_cache = True
2245class rollup(GenericFunction[_T]):
2246 r"""Implement the ``ROLLUP`` grouping operation.
2248 This function is used as part of the GROUP BY of a statement,
2249 e.g. :meth:`_expression.Select.group_by`::
2251 stmt = select(
2252 func.sum(table.c.value), table.c.col_1, table.c.col_2
2253 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2255 """
2257 _has_args = True
2258 inherit_cache = True
2261class grouping_sets(GenericFunction[_T]):
2262 r"""Implement the ``GROUPING SETS`` grouping operation.
2264 This function is used as part of the GROUP BY of a statement,
2265 e.g. :meth:`_expression.Select.group_by`::
2267 stmt = select(
2268 func.sum(table.c.value), table.c.col_1, table.c.col_2
2269 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2271 In order to group by multiple sets, use the :func:`.tuple_` construct::
2273 from sqlalchemy import tuple_
2275 stmt = select(
2276 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2277 ).group_by(
2278 func.grouping_sets(
2279 tuple_(table.c.col_1, table.c.col_2),
2280 tuple_(table.c.value, table.c.col_3),
2281 )
2282 )
2284 """ # noqa: E501
2286 _has_args = True
2287 inherit_cache = True
2290class aggregate_strings(GenericFunction[str]):
2291 """Implement a generic string aggregation function.
2293 This function will concatenate non-null values into a string and
2294 separate the values by a delimiter.
2296 This function is compiled on a per-backend basis, into functions
2297 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2299 e.g. Example usage with delimiter '.'::
2301 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2303 .. versionadded:: 2.0.21
2305 To add ordering to the expression, use the
2306 :meth:`_functions.FunctionElement.aggregate_order_by` modifier method,
2307 which will emit ORDER BY within the appropriate part of the column
2308 expression (varies by backend)::
2310 stmt = select(
2311 func.aggregate_strings(table.c.str_col, ".").aggregate_order_by(
2312 table.c.str_col
2313 )
2314 )
2316 .. versionadded:: 2.1 added :meth:`_functions.FunctionElement.aggregate_order_by`
2317 for all aggregate functions.
2319 :param clause: the SQL expression to be concatenated
2321 :param separator: separator string
2324 """ # noqa: E501
2326 type = sqltypes.String()
2327 _has_args = True
2328 inherit_cache = True
2330 def __init__(
2331 self,
2332 clause: _ColumnExpressionArgument[Any],
2333 separator: str,
2334 ) -> None:
2335 super().__init__(clause, separator)
2338# These aliases are required to avoid shadowing the class with the function
2339# name. See https://github.com/sqlalchemy/sqlalchemy/issues/13167
2340# START GENERATED FUNCTION ALIASES
2341_aggregate_strings_func: TypeAlias = aggregate_strings
2342_all__func: TypeAlias = all_[_T]
2343_AnsiFunction_func: TypeAlias = AnsiFunction[_T]
2344_any__func: TypeAlias = any_[_T]
2345_array_agg_func: TypeAlias = array_agg[_T]
2346_Cast_func: TypeAlias = Cast[_T]
2347_char_length_func: TypeAlias = char_length
2348_coalesce_func: TypeAlias = coalesce[_T]
2349_concat_func: TypeAlias = concat
2350_count_func: TypeAlias = count
2351_cube_func: TypeAlias = cube[_T]
2352_cume_dist_func: TypeAlias = cume_dist
2353_current_date_func: TypeAlias = current_date
2354_current_time_func: TypeAlias = current_time
2355_current_timestamp_func: TypeAlias = current_timestamp
2356_current_user_func: TypeAlias = current_user
2357_dense_rank_func: TypeAlias = dense_rank
2358_Extract_func: TypeAlias = Extract
2359_grouping_sets_func: TypeAlias = grouping_sets[_T]
2360_localtime_func: TypeAlias = localtime
2361_localtimestamp_func: TypeAlias = localtimestamp
2362_max_func: TypeAlias = max[_T]
2363_min_func: TypeAlias = min[_T]
2364_mode_func: TypeAlias = mode[_T]
2365_next_value_func: TypeAlias = next_value
2366_now_func: TypeAlias = now
2367_OrderedSetAgg_func: TypeAlias = OrderedSetAgg[_T]
2368_percent_rank_func: TypeAlias = percent_rank
2369_percentile_cont_func: TypeAlias = percentile_cont[_T]
2370_percentile_disc_func: TypeAlias = percentile_disc[_T]
2371_pow_func: TypeAlias = pow[_T]
2372_random_func: TypeAlias = random
2373_rank_func: TypeAlias = rank
2374_rollup_func: TypeAlias = rollup[_T]
2375_session_user_func: TypeAlias = session_user
2376_some_func: TypeAlias = some[_T]
2377_sum_func: TypeAlias = sum[_T]
2378_sysdate_func: TypeAlias = sysdate
2379_user_func: TypeAlias = user
2380# END GENERATED FUNCTION ALIASES