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 ansifunction(self) -> Type[_AnsiFunction_func[Any]]: ...
1052 # set ColumnElement[_T] as a separate overload, to appease
1053 # mypy which seems to not want to accept _T from
1054 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1055 # _HasClauseElement as of mypy 1.15
1057 @overload
1058 def array_agg(
1059 self,
1060 col: ColumnElement[_T],
1061 *args: _ColumnExpressionOrLiteralArgument[Any],
1062 **kwargs: Any,
1063 ) -> _array_agg_func[_T]: ...
1065 @overload
1066 def array_agg(
1067 self,
1068 col: _ColumnExpressionArgument[_T],
1069 *args: _ColumnExpressionOrLiteralArgument[Any],
1070 **kwargs: Any,
1071 ) -> _array_agg_func[_T]: ...
1073 @overload
1074 def array_agg(
1075 self,
1076 col: _T,
1077 *args: _ColumnExpressionOrLiteralArgument[Any],
1078 **kwargs: Any,
1079 ) -> _array_agg_func[_T]: ...
1081 def array_agg(
1082 self,
1083 col: _ColumnExpressionOrLiteralArgument[_T],
1084 *args: _ColumnExpressionOrLiteralArgument[Any],
1085 **kwargs: Any,
1086 ) -> _array_agg_func[_T]: ...
1088 @property
1089 def cast(self) -> Type[_Cast_func[Any]]: ...
1091 @property
1092 def char_length(self) -> Type[_char_length_func]: ...
1094 # set ColumnElement[_T] as a separate overload, to appease
1095 # mypy which seems to not want to accept _T from
1096 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1097 # _HasClauseElement as of mypy 1.15
1099 @overload
1100 def coalesce(
1101 self,
1102 col: ColumnElement[_T],
1103 *args: _ColumnExpressionOrLiteralArgument[Any],
1104 **kwargs: Any,
1105 ) -> _coalesce_func[_T]: ...
1107 @overload
1108 def coalesce(
1109 self,
1110 col: _ColumnExpressionArgument[Optional[_T]],
1111 *args: _ColumnExpressionOrLiteralArgument[Any],
1112 **kwargs: Any,
1113 ) -> _coalesce_func[_T]: ...
1115 @overload
1116 def coalesce(
1117 self,
1118 col: Optional[_T],
1119 *args: _ColumnExpressionOrLiteralArgument[Any],
1120 **kwargs: Any,
1121 ) -> _coalesce_func[_T]: ...
1123 def coalesce(
1124 self,
1125 col: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1126 *args: _ColumnExpressionOrLiteralArgument[Any],
1127 **kwargs: Any,
1128 ) -> _coalesce_func[_T]: ...
1130 @property
1131 def concat(self) -> Type[_concat_func]: ...
1133 @property
1134 def count(self) -> Type[_count_func]: ...
1136 @property
1137 def cube(self) -> Type[_cube_func[Any]]: ...
1139 @property
1140 def cume_dist(self) -> Type[_cume_dist_func]: ...
1142 @property
1143 def current_date(self) -> Type[_current_date_func]: ...
1145 @property
1146 def current_time(self) -> Type[_current_time_func]: ...
1148 @property
1149 def current_timestamp(self) -> Type[_current_timestamp_func]: ...
1151 @property
1152 def current_user(self) -> Type[_current_user_func]: ...
1154 @property
1155 def dense_rank(self) -> Type[_dense_rank_func]: ...
1157 @property
1158 def extract(self) -> Type[_Extract_func]: ...
1160 @property
1161 def grouping_sets(self) -> Type[_grouping_sets_func[Any]]: ...
1163 @property
1164 def localtime(self) -> Type[_localtime_func]: ...
1166 @property
1167 def localtimestamp(self) -> Type[_localtimestamp_func]: ...
1169 # set ColumnElement[_T] as a separate overload, to appease
1170 # mypy which seems to not want to accept _T from
1171 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1172 # _HasClauseElement as of mypy 1.15
1174 @overload
1175 def max( # noqa: A001
1176 self,
1177 col: ColumnElement[_T],
1178 *args: _ColumnExpressionOrLiteralArgument[Any],
1179 **kwargs: Any,
1180 ) -> _max_func[_T]: ...
1182 @overload
1183 def max( # noqa: A001
1184 self,
1185 col: _ColumnExpressionArgument[_T],
1186 *args: _ColumnExpressionOrLiteralArgument[Any],
1187 **kwargs: Any,
1188 ) -> _max_func[_T]: ...
1190 @overload
1191 def max( # noqa: A001
1192 self,
1193 col: _T,
1194 *args: _ColumnExpressionOrLiteralArgument[Any],
1195 **kwargs: Any,
1196 ) -> _max_func[_T]: ...
1198 def max( # noqa: A001
1199 self,
1200 col: _ColumnExpressionOrLiteralArgument[_T],
1201 *args: _ColumnExpressionOrLiteralArgument[Any],
1202 **kwargs: Any,
1203 ) -> _max_func[_T]: ...
1205 # set ColumnElement[_T] as a separate overload, to appease
1206 # mypy which seems to not want to accept _T from
1207 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1208 # _HasClauseElement as of mypy 1.15
1210 @overload
1211 def min( # noqa: A001
1212 self,
1213 col: ColumnElement[_T],
1214 *args: _ColumnExpressionOrLiteralArgument[Any],
1215 **kwargs: Any,
1216 ) -> _min_func[_T]: ...
1218 @overload
1219 def min( # noqa: A001
1220 self,
1221 col: _ColumnExpressionArgument[_T],
1222 *args: _ColumnExpressionOrLiteralArgument[Any],
1223 **kwargs: Any,
1224 ) -> _min_func[_T]: ...
1226 @overload
1227 def min( # noqa: A001
1228 self,
1229 col: _T,
1230 *args: _ColumnExpressionOrLiteralArgument[Any],
1231 **kwargs: Any,
1232 ) -> _min_func[_T]: ...
1234 def min( # noqa: A001
1235 self,
1236 col: _ColumnExpressionOrLiteralArgument[_T],
1237 *args: _ColumnExpressionOrLiteralArgument[Any],
1238 **kwargs: Any,
1239 ) -> _min_func[_T]: ...
1241 @property
1242 def mode(self) -> Type[_mode_func[Any]]: ...
1244 @property
1245 def next_value(self) -> Type[_next_value_func]: ...
1247 @property
1248 def now(self) -> Type[_now_func]: ...
1250 @property
1251 def orderedsetagg(self) -> Type[_OrderedSetAgg_func[Any]]: ...
1253 @property
1254 def percent_rank(self) -> Type[_percent_rank_func]: ...
1256 @property
1257 def percentile_cont(self) -> Type[_percentile_cont_func[Any]]: ...
1259 @property
1260 def percentile_disc(self) -> Type[_percentile_disc_func[Any]]: ...
1262 # set ColumnElement[_T] as a separate overload, to appease
1263 # mypy which seems to not want to accept _T from
1264 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1265 # _HasClauseElement as of mypy 1.15
1267 @overload
1268 def pow( # noqa: A001
1269 self,
1270 col: ColumnElement[_T],
1271 *args: _ColumnExpressionOrLiteralArgument[Any],
1272 **kwargs: Any,
1273 ) -> _pow_func[_T]: ...
1275 @overload
1276 def pow( # noqa: A001
1277 self,
1278 col: _ColumnExpressionArgument[_T],
1279 *args: _ColumnExpressionOrLiteralArgument[Any],
1280 **kwargs: Any,
1281 ) -> _pow_func[_T]: ...
1283 @overload
1284 def pow( # noqa: A001
1285 self,
1286 col: _T,
1287 *args: _ColumnExpressionOrLiteralArgument[Any],
1288 **kwargs: Any,
1289 ) -> _pow_func[_T]: ...
1291 def pow( # noqa: A001
1292 self,
1293 col: _ColumnExpressionOrLiteralArgument[_T],
1294 *args: _ColumnExpressionOrLiteralArgument[Any],
1295 **kwargs: Any,
1296 ) -> _pow_func[_T]: ...
1298 @property
1299 def random(self) -> Type[_random_func]: ...
1301 @property
1302 def rank(self) -> Type[_rank_func]: ...
1304 @property
1305 def rollup(self) -> Type[_rollup_func[Any]]: ...
1307 @property
1308 def session_user(self) -> Type[_session_user_func]: ...
1310 # set ColumnElement[_T] as a separate overload, to appease
1311 # mypy which seems to not want to accept _T from
1312 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1313 # _HasClauseElement as of mypy 1.15
1315 @overload
1316 def sum( # noqa: A001
1317 self,
1318 col: ColumnElement[_T],
1319 *args: _ColumnExpressionOrLiteralArgument[Any],
1320 **kwargs: Any,
1321 ) -> _sum_func[_T]: ...
1323 @overload
1324 def sum( # noqa: A001
1325 self,
1326 col: _ColumnExpressionArgument[_T],
1327 *args: _ColumnExpressionOrLiteralArgument[Any],
1328 **kwargs: Any,
1329 ) -> _sum_func[_T]: ...
1331 @overload
1332 def sum( # noqa: A001
1333 self,
1334 col: _T,
1335 *args: _ColumnExpressionOrLiteralArgument[Any],
1336 **kwargs: Any,
1337 ) -> _sum_func[_T]: ...
1339 def sum( # noqa: A001
1340 self,
1341 col: _ColumnExpressionOrLiteralArgument[_T],
1342 *args: _ColumnExpressionOrLiteralArgument[Any],
1343 **kwargs: Any,
1344 ) -> _sum_func[_T]: ...
1346 @property
1347 def sysdate(self) -> Type[_sysdate_func]: ...
1349 @property
1350 def user(self) -> Type[_user_func]: ...
1352 # END GENERATED FUNCTION ACCESSORS
1355func = _FunctionGenerator()
1356func.__doc__ = _FunctionGenerator.__doc__
1358modifier = _FunctionGenerator(group=False)
1361class Function(FunctionElement[_T]):
1362 r"""Describe a named SQL function.
1364 The :class:`.Function` object is typically generated from the
1365 :data:`.func` generation object.
1368 :param \*clauses: list of column expressions that form the arguments
1369 of the SQL function call.
1371 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1372 used as the return value of the column expression generated by this
1373 function call.
1375 :param packagenames: a string which indicates package prefix names
1376 to be prepended to the function name when the SQL is generated.
1377 The :data:`.func` generator creates these when it is called using
1378 dotted format, e.g.::
1380 func.mypackage.some_function(col1, col2)
1382 .. seealso::
1384 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1386 :data:`.func` - namespace which produces registered or ad-hoc
1387 :class:`.Function` instances.
1389 :class:`.GenericFunction` - allows creation of registered function
1390 types.
1392 """
1394 __visit_name__ = "function"
1396 _traverse_internals = FunctionElement._traverse_internals + [
1397 ("packagenames", InternalTraversal.dp_plain_obj),
1398 ("name", InternalTraversal.dp_string),
1399 ("type", InternalTraversal.dp_type),
1400 ]
1402 name: str
1404 identifier: str
1406 type: TypeEngine[_T]
1407 """A :class:`_types.TypeEngine` object which refers to the SQL return
1408 type represented by this SQL function.
1410 This datatype may be configured when generating a
1411 :class:`_functions.Function` object by passing the
1412 :paramref:`_functions.Function.type_` parameter, e.g.::
1414 >>> select(func.lower("some VALUE", type_=String))
1416 The small number of built-in classes of :class:`_functions.Function` come
1417 with a built-in datatype that's appropriate to the class of function and
1418 its arguments. For functions that aren't known, the type defaults to the
1419 "null type".
1421 """
1423 @overload
1424 def __init__(
1425 self,
1426 name: str,
1427 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1428 type_: None = ...,
1429 packagenames: Optional[Tuple[str, ...]] = ...,
1430 monotonic: bool = ...,
1431 ) -> None: ...
1433 @overload
1434 def __init__(
1435 self,
1436 name: str,
1437 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1438 type_: _TypeEngineArgument[_T] = ...,
1439 packagenames: Optional[Tuple[str, ...]] = ...,
1440 monotonic: bool = ...,
1441 ) -> None: ...
1443 def __init__(
1444 self,
1445 name: str,
1446 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1447 type_: Optional[_TypeEngineArgument[_T]] = None,
1448 packagenames: Optional[Tuple[str, ...]] = None,
1449 monotonic: bool = False,
1450 ) -> None:
1451 """Construct a :class:`.Function`.
1453 The :data:`.func` construct is normally used to construct
1454 new :class:`.Function` instances.
1456 """
1457 self.packagenames = packagenames or ()
1458 self.name = name
1459 self.monotonic = monotonic
1461 # if type is None, we get NULLTYPE, which is our _T. But I don't
1462 # know how to get the overloads to express that correctly
1463 self.type = type_api.to_instance(type_) # type: ignore
1465 FunctionElement.__init__(self, *clauses)
1467 def _bind_param(
1468 self,
1469 operator: OperatorType,
1470 obj: Any,
1471 type_: Optional[TypeEngine[_T]] = None,
1472 expanding: bool = False,
1473 **kw: Any,
1474 ) -> BindParameter[_T]:
1475 return BindParameter(
1476 self.name,
1477 obj,
1478 _compared_to_operator=operator,
1479 _compared_to_type=self.type,
1480 type_=type_,
1481 unique=True,
1482 expanding=expanding,
1483 **kw,
1484 )
1487class GenericFunction(Function[_T]):
1488 """Define a 'generic' function.
1490 A generic function is a pre-established :class:`.Function`
1491 class that is instantiated automatically when called
1492 by name from the :data:`.func` attribute. Note that
1493 calling any name from :data:`.func` has the effect that
1494 a new :class:`.Function` instance is created automatically,
1495 given that name. The primary use case for defining
1496 a :class:`.GenericFunction` class is so that a function
1497 of a particular name may be given a fixed return type.
1498 It can also include custom argument parsing schemes as well
1499 as additional methods.
1501 Subclasses of :class:`.GenericFunction` are automatically
1502 registered under the name of the class. For
1503 example, a user-defined function ``as_utc()`` would
1504 be available immediately::
1506 from sqlalchemy.sql.functions import GenericFunction
1507 from sqlalchemy.types import DateTime
1510 class as_utc(GenericFunction):
1511 type = DateTime()
1512 inherit_cache = True
1515 print(select(func.as_utc()))
1517 User-defined generic functions can be organized into
1518 packages by specifying the "package" attribute when defining
1519 :class:`.GenericFunction`. Third party libraries
1520 containing many functions may want to use this in order
1521 to avoid name conflicts with other systems. For example,
1522 if our ``as_utc()`` function were part of a package
1523 "time"::
1525 class as_utc(GenericFunction):
1526 type = DateTime()
1527 package = "time"
1528 inherit_cache = True
1530 The above function would be available from :data:`.func`
1531 using the package name ``time``::
1533 print(select(func.time.as_utc()))
1535 A final option is to allow the function to be accessed
1536 from one name in :data:`.func` but to render as a different name.
1537 The ``identifier`` attribute will override the name used to
1538 access the function as loaded from :data:`.func`, but will retain
1539 the usage of ``name`` as the rendered name::
1541 class GeoBuffer(GenericFunction):
1542 type = Geometry()
1543 package = "geo"
1544 name = "ST_Buffer"
1545 identifier = "buffer"
1546 inherit_cache = True
1548 The above function will render as follows:
1550 .. sourcecode:: pycon+sql
1552 >>> print(func.geo.buffer())
1553 {printsql}ST_Buffer()
1555 The name will be rendered as is, however without quoting unless the name
1556 contains special characters that require quoting. To force quoting
1557 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1558 construct::
1560 from sqlalchemy.sql import quoted_name
1563 class GeoBuffer(GenericFunction):
1564 type = Geometry()
1565 package = "geo"
1566 name = quoted_name("ST_Buffer", True)
1567 identifier = "buffer"
1568 inherit_cache = True
1570 The above function will render as:
1572 .. sourcecode:: pycon+sql
1574 >>> print(func.geo.buffer())
1575 {printsql}"ST_Buffer"()
1577 Type parameters for this class as a
1578 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1579 and should match the type seen in a :class:`_engine.Result`. For example::
1581 class as_utc(GenericFunction[datetime.datetime]):
1582 type = DateTime()
1583 inherit_cache = True
1585 The above indicates that the following expression returns a ``datetime``
1586 object::
1588 connection.scalar(select(func.as_utc()))
1590 """
1592 coerce_arguments = True
1593 inherit_cache = True
1595 _register: bool
1597 name = "GenericFunction"
1599 def __init_subclass__(cls) -> None:
1600 if annotation.Annotated not in cls.__mro__:
1601 cls._register_generic_function(cls.__name__, cls.__dict__)
1602 super().__init_subclass__()
1604 @classmethod
1605 def _register_generic_function(
1606 cls, clsname: str, clsdict: Mapping[str, Any]
1607 ) -> None:
1608 cls.name = name = clsdict.get("name", clsname)
1609 cls.identifier = identifier = clsdict.get("identifier", name)
1610 package = clsdict.get("package", "_default")
1611 # legacy
1612 if "__return_type__" in clsdict:
1613 cls.type = clsdict["__return_type__"]
1615 # Check _register attribute status
1616 cls._register = getattr(cls, "_register", True)
1618 # Register the function if required
1619 if cls._register:
1620 register_function(identifier, cls, package)
1621 else:
1622 # Set _register to True to register child classes by default
1623 cls._register = True
1625 def __init__(
1626 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1627 ) -> None:
1628 parsed_args = kwargs.pop("_parsed_args", None)
1629 if parsed_args is None:
1630 parsed_args = [
1631 coercions.expect(
1632 roles.ExpressionElementRole,
1633 c,
1634 name=self.name,
1635 apply_propagate_attrs=self,
1636 )
1637 for c in args
1638 ]
1639 self._has_args = self._has_args or bool(parsed_args)
1640 self.packagenames = ()
1642 self.clause_expr = Grouping(
1643 ClauseList(
1644 operator=operators.comma_op, group_contents=True, *parsed_args
1645 )
1646 )
1648 self.type = type_api.to_instance( # type: ignore
1649 kwargs.pop("type_", None) or getattr(self, "type", None)
1650 )
1653register_function("cast", Cast) # type: ignore
1654register_function("extract", Extract) # type: ignore
1657class next_value(GenericFunction[int]):
1658 """Represent the 'next value', given a :class:`.Sequence`
1659 as its single argument.
1661 Compiles into the appropriate function on each backend,
1662 or will raise NotImplementedError if used on a backend
1663 that does not provide support for sequences.
1665 """
1667 type = sqltypes.Integer()
1668 name = "next_value"
1670 _traverse_internals = [
1671 ("sequence", InternalTraversal.dp_named_ddl_element)
1672 ]
1674 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1675 assert isinstance(
1676 seq, schema.Sequence
1677 ), "next_value() accepts a Sequence object as input."
1678 self.sequence = seq
1679 self.type = sqltypes.to_instance( # type: ignore
1680 seq.data_type or getattr(self, "type", None)
1681 )
1683 def compare(self, other: Any, **kw: Any) -> bool:
1684 return (
1685 isinstance(other, next_value)
1686 and self.sequence.name == other.sequence.name
1687 )
1689 @property
1690 def _from_objects(self) -> Any:
1691 return []
1694class AnsiFunction(GenericFunction[_T]):
1695 """Define a function in "ansi" format, which doesn't render parenthesis."""
1697 inherit_cache = True
1699 def __init__(
1700 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1701 ) -> None:
1702 GenericFunction.__init__(self, *args, **kwargs)
1705class ReturnTypeFromArgs(GenericFunction[_T]):
1706 """Define a function whose return type is bound to the type of its
1707 arguments.
1708 """
1710 inherit_cache = True
1712 # set ColumnElement[_T] as a separate overload, to appease
1713 # mypy which seems to not want to accept _T from
1714 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1715 # _HasClauseElement as of mypy 1.15
1717 @overload
1718 def __init__(
1719 self,
1720 col: ColumnElement[_T],
1721 *args: _ColumnExpressionOrLiteralArgument[Any],
1722 **kwargs: Any,
1723 ) -> None: ...
1725 @overload
1726 def __init__(
1727 self,
1728 col: _ColumnExpressionArgument[_T],
1729 *args: _ColumnExpressionOrLiteralArgument[Any],
1730 **kwargs: Any,
1731 ) -> None: ...
1733 @overload
1734 def __init__(
1735 self,
1736 col: _T,
1737 *args: _ColumnExpressionOrLiteralArgument[Any],
1738 **kwargs: Any,
1739 ) -> None: ...
1741 def __init__(
1742 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1743 ) -> None:
1744 fn_args: Sequence[ColumnElement[Any]] = [
1745 coercions.expect(
1746 roles.ExpressionElementRole,
1747 c,
1748 name=self.name,
1749 apply_propagate_attrs=self,
1750 )
1751 for c in args
1752 ]
1753 kwargs.setdefault("type_", _type_from_args(fn_args))
1754 kwargs["_parsed_args"] = fn_args
1755 super().__init__(*fn_args, **kwargs)
1758class ReturnTypeFromOptionalArgs(ReturnTypeFromArgs[_T]):
1759 inherit_cache = True
1761 @overload
1762 def __init__(
1763 self,
1764 col: ColumnElement[_T],
1765 *args: _ColumnExpressionOrLiteralArgument[Any],
1766 **kwargs: Any,
1767 ) -> None: ...
1769 @overload
1770 def __init__(
1771 self,
1772 col: _ColumnExpressionArgument[Optional[_T]],
1773 *args: _ColumnExpressionOrLiteralArgument[Any],
1774 **kwargs: Any,
1775 ) -> None: ...
1777 @overload
1778 def __init__(
1779 self,
1780 col: Optional[_T],
1781 *args: _ColumnExpressionOrLiteralArgument[Any],
1782 **kwargs: Any,
1783 ) -> None: ...
1785 def __init__(
1786 self,
1787 *args: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1788 **kwargs: Any,
1789 ) -> None:
1790 super().__init__(*args, **kwargs) # type: ignore
1793class coalesce(ReturnTypeFromOptionalArgs[_T]):
1794 _has_args = True
1795 inherit_cache = True
1798class max(ReturnTypeFromArgs[_T]): # noqa: A001
1799 """The SQL MAX() aggregate function."""
1801 inherit_cache = True
1804class min(ReturnTypeFromArgs[_T]): # noqa: A001
1805 """The SQL MIN() aggregate function."""
1807 inherit_cache = True
1810class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1811 """The SQL SUM() aggregate function."""
1813 inherit_cache = True
1816class now(GenericFunction[datetime.datetime]):
1817 """The SQL now() datetime function.
1819 SQLAlchemy dialects will usually render this particular function
1820 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1822 """
1824 type = sqltypes.DateTime()
1825 inherit_cache = True
1828class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1829 """The SQL POW() function which performs the power operator.
1831 E.g.:
1833 .. sourcecode:: pycon+sql
1835 >>> print(select(func.pow(2, 8)))
1836 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1838 .. versionadded:: 2.1
1840 """
1842 inherit_cache = True
1845class concat(GenericFunction[str]):
1846 """The SQL CONCAT() function, which concatenates strings.
1848 E.g.:
1850 .. sourcecode:: pycon+sql
1852 >>> print(select(func.concat("a", "b")))
1853 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1855 String concatenation in SQLAlchemy is more commonly available using the
1856 Python ``+`` operator with string datatypes, which will render a
1857 backend-specific concatenation operator, such as :
1859 .. sourcecode:: pycon+sql
1861 >>> print(select(literal("a") + "b"))
1862 {printsql}SELECT :param_1 || :param_2 AS anon_1
1865 """
1867 type = sqltypes.String()
1868 inherit_cache = True
1871class char_length(GenericFunction[int]):
1872 """The CHAR_LENGTH() SQL function."""
1874 type = sqltypes.Integer()
1875 inherit_cache = True
1877 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1878 # slight hack to limit to just one positional argument
1879 # not sure why this one function has this special treatment
1880 super().__init__(arg, **kw)
1883class random(GenericFunction[float]):
1884 """The RANDOM() SQL function."""
1886 _has_args = True
1887 inherit_cache = True
1890class count(GenericFunction[int]):
1891 r"""The ANSI COUNT aggregate function. With no arguments,
1892 emits COUNT \*.
1894 E.g.::
1896 from sqlalchemy import func
1897 from sqlalchemy import select
1898 from sqlalchemy import table, column
1900 my_table = table("some_table", column("id"))
1902 stmt = select(func.count()).select_from(my_table)
1904 Executing ``stmt`` would emit:
1906 .. sourcecode:: sql
1908 SELECT count(*) AS count_1
1909 FROM some_table
1912 """
1914 type = sqltypes.Integer()
1915 inherit_cache = True
1917 def __init__(
1918 self,
1919 expression: Union[
1920 _ColumnExpressionArgument[Any], _StarOrOne, None
1921 ] = None,
1922 **kwargs: Any,
1923 ) -> None:
1924 if expression is None:
1925 expression = literal_column("*")
1926 super().__init__(expression, **kwargs)
1929class current_date(AnsiFunction[datetime.date]):
1930 """The CURRENT_DATE() SQL function."""
1932 type = sqltypes.Date()
1933 inherit_cache = True
1936class current_time(AnsiFunction[datetime.time]):
1937 """The CURRENT_TIME() SQL function."""
1939 type = sqltypes.Time()
1940 inherit_cache = True
1943class current_timestamp(AnsiFunction[datetime.datetime]):
1944 """The CURRENT_TIMESTAMP() SQL function."""
1946 type = sqltypes.DateTime()
1947 inherit_cache = True
1950class current_user(AnsiFunction[str]):
1951 """The CURRENT_USER() SQL function."""
1953 type = sqltypes.String()
1954 inherit_cache = True
1957class localtime(AnsiFunction[datetime.datetime]):
1958 """The localtime() SQL function."""
1960 type = sqltypes.DateTime()
1961 inherit_cache = True
1964class localtimestamp(AnsiFunction[datetime.datetime]):
1965 """The localtimestamp() SQL function."""
1967 type = sqltypes.DateTime()
1968 inherit_cache = True
1971class session_user(AnsiFunction[str]):
1972 """The SESSION_USER() SQL function."""
1974 type = sqltypes.String()
1975 inherit_cache = True
1978class sysdate(AnsiFunction[datetime.datetime]):
1979 """The SYSDATE() SQL function."""
1981 type = sqltypes.DateTime()
1982 inherit_cache = True
1985class user(AnsiFunction[str]):
1986 """The USER() SQL function."""
1988 type = sqltypes.String()
1989 inherit_cache = True
1992class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1993 """Support for the ARRAY_AGG function.
1995 The ``func.array_agg(expr)`` construct returns an expression of
1996 type :class:`_types.ARRAY`.
1998 e.g.::
2000 stmt = select(func.array_agg(table.c.values)[2:5])
2002 .. seealso::
2004 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
2005 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
2006 added.
2008 """
2010 inherit_cache = True
2012 def __init__(
2013 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
2014 ) -> None:
2015 fn_args: Sequence[ColumnElement[Any]] = [
2016 coercions.expect(
2017 roles.ExpressionElementRole, c, apply_propagate_attrs=self
2018 )
2019 for c in args
2020 ]
2022 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
2023 if "type_" not in kwargs:
2024 type_from_args = _type_from_args(fn_args)
2025 if isinstance(type_from_args, sqltypes.ARRAY):
2026 kwargs["type_"] = type_from_args
2027 else:
2028 kwargs["type_"] = default_array_type(
2029 type_from_args, dimensions=1
2030 )
2031 kwargs["_parsed_args"] = fn_args
2032 super().__init__(*fn_args, **kwargs)
2035class OrderedSetAgg(GenericFunction[_T]):
2036 """Define a function where the return type is based on the sort
2037 expression type as defined by the expression passed to the
2038 :meth:`.FunctionElement.within_group` method."""
2040 array_for_multi_clause = False
2041 inherit_cache = True
2043 def within_group_type(
2044 self, within_group: WithinGroup[Any]
2045 ) -> TypeEngine[Any]:
2046 func_clauses = cast(ClauseList, self.clause_expr.element)
2047 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
2048 within_group.order_by
2049 )
2050 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
2051 return sqltypes.ARRAY(order_by[0].type)
2052 else:
2053 return order_by[0].type
2056class mode(OrderedSetAgg[_T]):
2057 """Implement the ``mode`` ordered-set aggregate function.
2059 This function must be used with the :meth:`.FunctionElement.within_group`
2060 modifier to supply a sort expression to operate upon.
2062 The return type of this function is the same as the sort expression.
2064 """
2066 inherit_cache = True
2069class percentile_cont(OrderedSetAgg[_T]):
2070 """Implement the ``percentile_cont`` ordered-set aggregate function.
2072 This function must be used with the :meth:`.FunctionElement.within_group`
2073 modifier to supply a sort expression to operate upon.
2075 The return type of this function is the same as the sort expression,
2076 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2077 expression's type.
2079 """
2081 array_for_multi_clause = True
2082 inherit_cache = True
2085class percentile_disc(OrderedSetAgg[_T]):
2086 """Implement the ``percentile_disc`` ordered-set aggregate function.
2088 This function must be used with the :meth:`.FunctionElement.within_group`
2089 modifier to supply a sort expression to operate upon.
2091 The return type of this function is the same as the sort expression,
2092 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2093 expression's type.
2095 """
2097 array_for_multi_clause = True
2098 inherit_cache = True
2101class rank(GenericFunction[int]):
2102 """Implement the ``rank`` hypothetical-set aggregate function.
2104 This function must be used with the :meth:`.FunctionElement.within_group`
2105 modifier to supply a sort expression to operate upon.
2107 The return type of this function is :class:`.Integer`.
2109 """
2111 type = sqltypes.Integer()
2112 inherit_cache = True
2115class dense_rank(GenericFunction[int]):
2116 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2118 This function must be used with the :meth:`.FunctionElement.within_group`
2119 modifier to supply a sort expression to operate upon.
2121 The return type of this function is :class:`.Integer`.
2123 """
2125 type = sqltypes.Integer()
2126 inherit_cache = True
2129class percent_rank(GenericFunction[decimal.Decimal]):
2130 """Implement the ``percent_rank`` hypothetical-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 :class:`.Numeric`.
2137 """
2139 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2140 inherit_cache = True
2143class cume_dist(GenericFunction[decimal.Decimal]):
2144 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2146 This function must be used with the :meth:`.FunctionElement.within_group`
2147 modifier to supply a sort expression to operate upon.
2149 The return type of this function is :class:`.Numeric`.
2151 """
2153 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2154 inherit_cache = True
2157class cube(GenericFunction[_T]):
2158 r"""Implement the ``CUBE`` grouping operation.
2160 This function is used as part of the GROUP BY of a statement,
2161 e.g. :meth:`_expression.Select.group_by`::
2163 stmt = select(
2164 func.sum(table.c.value), table.c.col_1, table.c.col_2
2165 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2167 """
2169 _has_args = True
2170 inherit_cache = True
2173class rollup(GenericFunction[_T]):
2174 r"""Implement the ``ROLLUP`` grouping operation.
2176 This function is used as part of the GROUP BY of a statement,
2177 e.g. :meth:`_expression.Select.group_by`::
2179 stmt = select(
2180 func.sum(table.c.value), table.c.col_1, table.c.col_2
2181 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2183 """
2185 _has_args = True
2186 inherit_cache = True
2189class grouping_sets(GenericFunction[_T]):
2190 r"""Implement the ``GROUPING SETS`` grouping operation.
2192 This function is used as part of the GROUP BY of a statement,
2193 e.g. :meth:`_expression.Select.group_by`::
2195 stmt = select(
2196 func.sum(table.c.value), table.c.col_1, table.c.col_2
2197 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2199 In order to group by multiple sets, use the :func:`.tuple_` construct::
2201 from sqlalchemy import tuple_
2203 stmt = select(
2204 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2205 ).group_by(
2206 func.grouping_sets(
2207 tuple_(table.c.col_1, table.c.col_2),
2208 tuple_(table.c.value, table.c.col_3),
2209 )
2210 )
2212 """ # noqa: E501
2214 _has_args = True
2215 inherit_cache = True
2218class aggregate_strings(GenericFunction[str]):
2219 """Implement a generic string aggregation function.
2221 This function will concatenate non-null values into a string and
2222 separate the values by a delimiter.
2224 This function is compiled on a per-backend basis, into functions
2225 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2227 e.g. Example usage with delimiter '.'::
2229 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2231 .. versionadded:: 2.0.21
2233 To add ordering to the expression, use the
2234 :meth:`_functions.FunctionElement.aggregate_order_by` modifier method,
2235 which will emit ORDER BY within the appropriate part of the column
2236 expression (varies by backend)::
2238 stmt = select(
2239 func.aggregate_strings(table.c.str_col, ".").aggregate_order_by(
2240 table.c.str_col
2241 )
2242 )
2244 .. versionadded:: 2.1 added :meth:`_functions.FunctionElement.aggregate_order_by`
2245 for all aggregate functions.
2247 :param clause: the SQL expression to be concatenated
2249 :param separator: separator string
2252 """ # noqa: E501
2254 type = sqltypes.String()
2255 _has_args = True
2256 inherit_cache = True
2258 def __init__(
2259 self,
2260 clause: _ColumnExpressionArgument[Any],
2261 separator: str,
2262 ) -> None:
2263 super().__init__(clause, separator)
2266# These aliases are required to avoid shadowing the class with the function
2267# name. See https://github.com/sqlalchemy/sqlalchemy/issues/13167
2268# START GENERATED FUNCTION ALIASES
2269_aggregate_strings_func: TypeAlias = aggregate_strings
2270_AnsiFunction_func: TypeAlias = AnsiFunction[_T]
2271_array_agg_func: TypeAlias = array_agg[_T]
2272_Cast_func: TypeAlias = Cast[_T]
2273_char_length_func: TypeAlias = char_length
2274_coalesce_func: TypeAlias = coalesce[_T]
2275_concat_func: TypeAlias = concat
2276_count_func: TypeAlias = count
2277_cube_func: TypeAlias = cube[_T]
2278_cume_dist_func: TypeAlias = cume_dist
2279_current_date_func: TypeAlias = current_date
2280_current_time_func: TypeAlias = current_time
2281_current_timestamp_func: TypeAlias = current_timestamp
2282_current_user_func: TypeAlias = current_user
2283_dense_rank_func: TypeAlias = dense_rank
2284_Extract_func: TypeAlias = Extract
2285_grouping_sets_func: TypeAlias = grouping_sets[_T]
2286_localtime_func: TypeAlias = localtime
2287_localtimestamp_func: TypeAlias = localtimestamp
2288_max_func: TypeAlias = max[_T]
2289_min_func: TypeAlias = min[_T]
2290_mode_func: TypeAlias = mode[_T]
2291_next_value_func: TypeAlias = next_value
2292_now_func: TypeAlias = now
2293_OrderedSetAgg_func: TypeAlias = OrderedSetAgg[_T]
2294_percent_rank_func: TypeAlias = percent_rank
2295_percentile_cont_func: TypeAlias = percentile_cont[_T]
2296_percentile_disc_func: TypeAlias = percentile_disc[_T]
2297_pow_func: TypeAlias = pow[_T]
2298_random_func: TypeAlias = random
2299_rank_func: TypeAlias = rank
2300_rollup_func: TypeAlias = rollup[_T]
2301_session_user_func: TypeAlias = session_user
2302_sum_func: TypeAlias = sum[_T]
2303_sysdate_func: TypeAlias = sysdate
2304_user_func: TypeAlias = user
2305# END GENERATED FUNCTION ALIASES