Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/functions.py
2# Copyright (C) 2005-2025 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8"""SQL function API, factories, and built-in functions."""
10from __future__ import annotations
12import datetime
13import decimal
14from typing import Any
15from typing import cast
16from typing import Dict
17from typing import List
18from typing import Mapping
19from typing import Optional
20from typing import overload
21from typing import Sequence
22from typing import Tuple
23from typing import Type
24from typing import TYPE_CHECKING
25from typing import TypeVar
26from typing import Union
28from . import annotation
29from . import coercions
30from . import operators
31from . import roles
32from . import schema
33from . import sqltypes
34from . import type_api
35from . import util as sqlutil
36from ._typing import is_table_value_type
37from .base import _entity_namespace
38from .base import ColumnCollection
39from .base import Executable
40from .base import Generative
41from .base import HasMemoized
42from .elements import _type_from_args
43from .elements import AggregateOrderBy
44from .elements import BinaryExpression
45from .elements import BindParameter
46from .elements import Cast
47from .elements import ClauseList
48from .elements import ColumnElement
49from .elements import Extract
50from .elements import FunctionFilter
51from .elements import Grouping
52from .elements import literal_column
53from .elements import NamedColumn
54from .elements import Over
55from .elements import WithinGroup
56from .selectable import FromClause
57from .selectable import Select
58from .selectable import TableValuedAlias
59from .sqltypes import TableValueType
60from .type_api import TypeEngine
61from .visitors import InternalTraversal
62from .. import util
65if TYPE_CHECKING:
66 from ._typing import _ByArgument
67 from ._typing import _ColumnExpressionArgument
68 from ._typing import _ColumnExpressionOrLiteralArgument
69 from ._typing import _ColumnExpressionOrStrLabelArgument
70 from ._typing import _StarOrOne
71 from ._typing import _TypeEngineArgument
72 from .base import _EntityNamespace
73 from .elements import _FrameIntTuple
74 from .elements import ClauseElement
75 from .elements import FrameClause
76 from .elements import KeyedColumnElement
77 from .elements import TableValuedColumn
78 from .operators import OperatorType
79 from ..engine.base import Connection
80 from ..engine.cursor import CursorResult
81 from ..engine.interfaces import _CoreMultiExecuteParams
82 from ..engine.interfaces import CoreExecuteOptionsParameter
83 from ..util.typing import Self
85_T = TypeVar("_T", bound=Any)
86_S = TypeVar("_S", bound=Any)
88_registry: util.defaultdict[str, Dict[str, Type[Function[Any]]]] = (
89 util.defaultdict(dict)
90)
93def register_function(
94 identifier: str, fn: Type[Function[Any]], package: str = "_default"
95) -> None:
96 """Associate a callable with a particular func. name.
98 This is normally called by GenericFunction, but is also
99 available by itself so that a non-Function construct
100 can be associated with the :data:`.func` accessor (i.e.
101 CAST, EXTRACT).
103 """
104 reg = _registry[package]
106 identifier = str(identifier).lower()
108 # Check if a function with the same identifier is registered.
109 if identifier in reg:
110 util.warn(
111 "The GenericFunction '{}' is already registered and "
112 "is going to be overridden.".format(identifier)
113 )
114 reg[identifier] = fn
117class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
118 """Base for SQL function-oriented constructs.
120 This is a `generic type <https://peps.python.org/pep-0484/#generics>`_,
121 meaning that type checkers and IDEs can be instructed on the types to
122 expect in a :class:`_engine.Result` for this function. See
123 :class:`.GenericFunction` for an example of how this is done.
125 .. seealso::
127 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
129 :class:`.Function` - named SQL function.
131 :data:`.func` - namespace which produces registered or ad-hoc
132 :class:`.Function` instances.
134 :class:`.GenericFunction` - allows creation of registered function
135 types.
137 """
139 _traverse_internals = [
140 ("clause_expr", InternalTraversal.dp_clauseelement),
141 ("_with_ordinality", InternalTraversal.dp_boolean),
142 ("_table_value_type", InternalTraversal.dp_has_cache_key),
143 ] + Executable._executable_traverse_internals
145 packagenames: Tuple[str, ...] = ()
147 _has_args = False
148 _with_ordinality = False
149 _table_value_type: Optional[TableValueType] = None
151 # some attributes that are defined between both ColumnElement and
152 # FromClause are set to Any here to avoid typing errors
153 primary_key: Any
154 _is_clone_of: Any
156 clause_expr: Grouping[Any]
158 def __init__(
159 self, *clauses: _ColumnExpressionOrLiteralArgument[Any]
160 ) -> None:
161 r"""Construct a :class:`.FunctionElement`.
163 :param \*clauses: list of column expressions that form the arguments
164 of the SQL function call.
166 :param \**kwargs: additional kwargs are typically consumed by
167 subclasses.
169 .. seealso::
171 :data:`.func`
173 :class:`.Function`
175 """
176 args: Sequence[_ColumnExpressionArgument[Any]] = [
177 coercions.expect(
178 roles.ExpressionElementRole,
179 c,
180 name=getattr(self, "name", None),
181 apply_propagate_attrs=self,
182 )
183 for c in clauses
184 ]
185 self._has_args = self._has_args or bool(args)
186 self.clause_expr = Grouping(
187 ClauseList(operator=operators.comma_op, group_contents=True, *args)
188 )
190 _non_anon_label = None
192 @property
193 def _proxy_key(self) -> Any:
194 return super()._proxy_key or getattr(self, "name", None)
196 def _execute_on_connection(
197 self,
198 connection: Connection,
199 distilled_params: _CoreMultiExecuteParams,
200 execution_options: CoreExecuteOptionsParameter,
201 ) -> CursorResult[Any]:
202 return connection._execute_function(
203 self, distilled_params, execution_options
204 )
206 def scalar_table_valued(
207 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
208 ) -> ScalarFunctionColumn[_T]:
209 """Return a column expression that's against this
210 :class:`_functions.FunctionElement` as a scalar
211 table-valued expression.
213 The returned expression is similar to that returned by a single column
214 accessed off of a :meth:`_functions.FunctionElement.table_valued`
215 construct, except no FROM clause is generated; the function is rendered
216 in the similar way as a scalar subquery.
218 E.g.:
220 .. sourcecode:: pycon+sql
222 >>> from sqlalchemy import func, select
223 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
224 >>> print(select(fn))
225 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
227 .. versionadded:: 1.4.0b2
229 .. seealso::
231 :meth:`_functions.FunctionElement.table_valued`
233 :meth:`_functions.FunctionElement.alias`
235 :meth:`_functions.FunctionElement.column_valued`
237 """ # noqa: E501
239 return ScalarFunctionColumn(self, name, type_)
241 def table_valued(
242 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
243 ) -> TableValuedAlias:
244 r"""Return a :class:`_sql.TableValuedAlias` representation of this
245 :class:`_functions.FunctionElement` with table-valued expressions added.
247 e.g.:
249 .. sourcecode:: pycon+sql
251 >>> fn = func.generate_series(1, 5).table_valued(
252 ... "value", "start", "stop", "step"
253 ... )
255 >>> print(select(fn))
256 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
257 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
259 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
260 {printsql}SELECT anon_1.value, anon_1.stop
261 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
262 WHERE anon_1.value > :value_1{stop}
264 A WITH ORDINALITY expression may be generated by passing the keyword
265 argument "with_ordinality":
267 .. sourcecode:: pycon+sql
269 >>> fn = func.generate_series(4, 1, -1).table_valued(
270 ... "gen", with_ordinality="ordinality"
271 ... )
272 >>> print(select(fn))
273 {printsql}SELECT anon_1.gen, anon_1.ordinality
274 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1
276 :param \*expr: A series of string column names that will be added to the
277 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
278 construct as columns. :func:`_sql.column` objects with or without
279 datatypes may also be used.
281 :param name: optional name to assign to the alias name that's generated.
282 If omitted, a unique anonymizing name is used.
284 :param with_ordinality: string name that when present results in the
285 ``WITH ORDINALITY`` clause being added to the alias, and the given
286 string name will be added as a column to the .c collection
287 of the resulting :class:`_sql.TableValuedAlias`.
289 :param joins_implicitly: when True, the table valued function may be
290 used in the FROM clause without any explicit JOIN to other tables
291 in the SQL query, and no "cartesian product" warning will be generated.
292 May be useful for SQL functions such as ``func.json_each()``.
294 .. versionadded:: 1.4.33
296 .. versionadded:: 1.4.0b2
299 .. seealso::
301 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
303 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
305 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
306 :meth:`_functions.FunctionElement.table_valued` which delivers the
307 complete table valued expression as a scalar column expression
309 :meth:`_functions.FunctionElement.column_valued`
311 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
312 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
314 """ # noqa: 501
316 new_func = self._generate()
318 with_ordinality = kw.pop("with_ordinality", None)
319 joins_implicitly = kw.pop("joins_implicitly", None)
320 name = kw.pop("name", None)
322 if with_ordinality:
323 expr += (with_ordinality,)
324 new_func._with_ordinality = True
326 new_func.type = new_func._table_value_type = TableValueType(*expr)
328 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
330 def column_valued(
331 self, name: Optional[str] = None, joins_implicitly: bool = False
332 ) -> TableValuedColumn[_T]:
333 """Return this :class:`_functions.FunctionElement` as a column expression that
334 selects from itself as a FROM clause.
336 E.g.:
338 .. sourcecode:: pycon+sql
340 >>> from sqlalchemy import select, func
341 >>> gs = func.generate_series(1, 5, -1).column_valued()
342 >>> print(select(gs))
343 {printsql}SELECT anon_1
344 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
346 This is shorthand for::
348 gs = func.generate_series(1, 5, -1).alias().column
350 :param name: optional name to assign to the alias name that's generated.
351 If omitted, a unique anonymizing name is used.
353 :param joins_implicitly: when True, the "table" portion of the column
354 valued function may be a member of the FROM clause without any
355 explicit JOIN to other tables in the SQL query, and no "cartesian
356 product" warning will be generated. May be useful for SQL functions
357 such as ``func.json_array_elements()``.
359 .. versionadded:: 1.4.46
361 .. seealso::
363 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
365 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
367 :meth:`_functions.FunctionElement.table_valued`
369 """ # noqa: 501
371 return self.alias(name=name, joins_implicitly=joins_implicitly).column
373 @util.ro_non_memoized_property
374 def columns(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
375 r"""The set of columns exported by this :class:`.FunctionElement`.
377 This is a placeholder collection that allows the function to be
378 placed in the FROM clause of a statement:
380 .. sourcecode:: pycon+sql
382 >>> from sqlalchemy import column, select, func
383 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
384 >>> print(stmt)
385 {printsql}SELECT x, y FROM myfunction()
387 The above form is a legacy feature that is now superseded by the
388 fully capable :meth:`_functions.FunctionElement.table_valued`
389 method; see that method for details.
391 .. seealso::
393 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
394 SQL function expressions.
396 """ # noqa: E501
397 return self.c
399 @util.ro_memoized_property
400 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
401 """synonym for :attr:`.FunctionElement.columns`."""
403 return ColumnCollection(
404 columns=[(col.key, col) for col in self._all_selected_columns]
405 )
407 @property
408 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
409 if is_table_value_type(self.type):
410 # TODO: this might not be fully accurate
411 cols = cast(
412 "Sequence[KeyedColumnElement[Any]]", self.type._elements
413 )
414 else:
415 cols = [self.label(None)]
417 return cols
419 @property
420 def exported_columns( # type: ignore[override]
421 self,
422 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
423 return self.columns
425 @HasMemoized.memoized_attribute
426 def clauses(self) -> ClauseList:
427 """Return the underlying :class:`.ClauseList` which contains
428 the arguments for this :class:`.FunctionElement`.
430 """
431 return cast(ClauseList, self.clause_expr.element)
433 def over(
434 self,
435 *,
436 partition_by: _ByArgument | None = None,
437 order_by: _ByArgument | None = None,
438 rows: _FrameIntTuple | FrameClause | None = None,
439 range_: _FrameIntTuple | FrameClause | None = None,
440 groups: _FrameIntTuple | FrameClause | None = None,
441 ) -> Over[_T]:
442 """Produce an OVER clause against this function.
444 Used against aggregate or so-called "window" functions,
445 for database backends that support window functions.
447 The expression::
449 func.row_number().over(order_by="x")
451 is shorthand for::
453 from sqlalchemy import over
455 over(func.row_number(), order_by="x")
457 See :func:`_expression.over` for a full description.
459 .. seealso::
461 :func:`_expression.over`
463 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
465 """
466 return Over(
467 self,
468 partition_by=partition_by,
469 order_by=order_by,
470 rows=rows,
471 range_=range_,
472 groups=groups,
473 )
475 def aggregate_order_by(
476 self, *order_by: _ColumnExpressionArgument[Any]
477 ) -> AggregateOrderBy[_T]:
478 r"""Produce a :class:`.AggregateOrderBy` object against a function.
480 Used for aggregating functions such as :class:`_functions.array_agg`,
481 ``group_concat``, ``json_agg`` on backends that support ordering via an
482 embedded ORDER BY parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
483 When used on backends like Oracle and SQL Server, SQL compilation uses
484 that of :class:`.WithinGroup`.
486 See :func:`_expression.aggregate_order_by` for a full description.
488 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
489 :func:`_postgresql.aggregate_order_by` function to a method on
490 :class:`.Function` that is backend agnostic.
492 .. seealso::
494 :class:`_functions.aggregate_strings` - backend-agnostic string
495 concatenation function which also supports ORDER BY
497 """
499 return AggregateOrderBy(self, *order_by)
501 def within_group(
502 self, *order_by: _ColumnExpressionArgument[Any]
503 ) -> WithinGroup[_T]:
504 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
506 Used against so-called "ordered set aggregate" and "hypothetical
507 set aggregate" functions, including :class:`.percentile_cont`,
508 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
509 used by PostgreSQL, Oracle Database, and Microsoft SQL Server.
511 For simple ORDER BY expressions within aggregate functions on
512 PostgreSQL, MySQL/MariaDB, SQLite, see :func:`_sql.aggregate_order_by`.
514 See :func:`_expression.within_group` for a full description.
516 .. seealso::
518 :ref:`tutorial_functions_within_group` -
519 in the :ref:`unified_tutorial`
522 """
523 return WithinGroup(self, *order_by)
525 @overload
526 def filter(self) -> Self: ...
528 @overload
529 def filter(
530 self,
531 __criterion0: _ColumnExpressionArgument[bool],
532 *criterion: _ColumnExpressionArgument[bool],
533 ) -> FunctionFilter[_T]: ...
535 def filter(
536 self, *criterion: _ColumnExpressionArgument[bool]
537 ) -> Union[Self, FunctionFilter[_T]]:
538 """Produce a FILTER clause against this function.
540 Used against aggregate and window functions,
541 for database backends that support the "FILTER" clause.
543 The expression::
545 func.count(1).filter(True)
547 is shorthand for::
549 from sqlalchemy import funcfilter
551 funcfilter(func.count(1), True)
553 .. seealso::
555 :ref:`tutorial_functions_within_group` -
556 in the :ref:`unified_tutorial`
558 :class:`.FunctionFilter`
560 :func:`.funcfilter`
563 """
564 if not criterion:
565 return self
566 return FunctionFilter(self, *criterion)
568 def as_comparison(
569 self, left_index: int, right_index: int
570 ) -> FunctionAsBinary:
571 """Interpret this expression as a boolean comparison between two
572 values.
574 This method is used for an ORM use case described at
575 :ref:`relationship_custom_operator_sql_function`.
577 A hypothetical SQL function "is_equal()" which compares to values
578 for equality would be written in the Core expression language as::
580 expr = func.is_equal("a", "b")
582 If "is_equal()" above is comparing "a" and "b" for equality, the
583 :meth:`.FunctionElement.as_comparison` method would be invoked as::
585 expr = func.is_equal("a", "b").as_comparison(1, 2)
587 Where above, the integer value "1" refers to the first argument of the
588 "is_equal()" function and the integer value "2" refers to the second.
590 This would create a :class:`.BinaryExpression` that is equivalent to::
592 BinaryExpression("a", "b", operator=op.eq)
594 However, at the SQL level it would still render as
595 "is_equal('a', 'b')".
597 The ORM, when it loads a related object or collection, needs to be able
598 to manipulate the "left" and "right" sides of the ON clause of a JOIN
599 expression. The purpose of this method is to provide a SQL function
600 construct that can also supply this information to the ORM, when used
601 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
602 return value is a containment object called :class:`.FunctionAsBinary`.
604 An ORM example is as follows::
606 class Venue(Base):
607 __tablename__ = "venue"
608 id = Column(Integer, primary_key=True)
609 name = Column(String)
611 descendants = relationship(
612 "Venue",
613 primaryjoin=func.instr(
614 remote(foreign(name)), name + "/"
615 ).as_comparison(1, 2)
616 == 1,
617 viewonly=True,
618 order_by=name,
619 )
621 Above, the "Venue" class can load descendant "Venue" objects by
622 determining if the name of the parent Venue is contained within the
623 start of the hypothetical descendant value's name, e.g. "parent1" would
624 match up to "parent1/child1", but not to "parent2/child1".
626 Possible use cases include the "materialized path" example given above,
627 as well as making use of special SQL functions such as geometric
628 functions to create join conditions.
630 :param left_index: the integer 1-based index of the function argument
631 that serves as the "left" side of the expression.
632 :param right_index: the integer 1-based index of the function argument
633 that serves as the "right" side of the expression.
635 .. seealso::
637 :ref:`relationship_custom_operator_sql_function` -
638 example use within the ORM
640 """
641 return FunctionAsBinary(self, left_index, right_index)
643 @property
644 def _from_objects(self) -> Any:
645 return self.clauses._from_objects
647 def within_group_type(
648 self, within_group: WithinGroup[_S]
649 ) -> Optional[TypeEngine[_S]]:
650 """For types that define their return type as based on the criteria
651 within a WITHIN GROUP (ORDER BY) expression, called by the
652 :class:`.WithinGroup` construct.
654 Returns None by default, in which case the function's normal ``.type``
655 is used.
657 """
659 return None
661 def alias(
662 self, name: Optional[str] = None, joins_implicitly: bool = False
663 ) -> TableValuedAlias:
664 r"""Produce a :class:`_expression.Alias` construct against this
665 :class:`.FunctionElement`.
667 .. tip::
669 The :meth:`_functions.FunctionElement.alias` method is part of the
670 mechanism by which "table valued" SQL functions are created.
671 However, most use cases are covered by higher level methods on
672 :class:`_functions.FunctionElement` including
673 :meth:`_functions.FunctionElement.table_valued`, and
674 :meth:`_functions.FunctionElement.column_valued`.
676 This construct wraps the function in a named alias which
677 is suitable for the FROM clause, in the style accepted for example
678 by PostgreSQL. A column expression is also provided using the
679 special ``.column`` attribute, which may
680 be used to refer to the output of the function as a scalar value
681 in the columns or where clause, for a backend such as PostgreSQL.
683 For a full table-valued expression, use the
684 :meth:`_functions.FunctionElement.table_valued` method first to
685 establish named columns.
687 e.g.:
689 .. sourcecode:: pycon+sql
691 >>> from sqlalchemy import func, select, column
692 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
693 >>> print(select(data_view.column))
694 {printsql}SELECT data_view
695 FROM unnest(:unnest_1) AS data_view
697 The :meth:`_functions.FunctionElement.column_valued` method provides
698 a shortcut for the above pattern:
700 .. sourcecode:: pycon+sql
702 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
703 >>> print(select(data_view))
704 {printsql}SELECT data_view
705 FROM unnest(:unnest_1) AS data_view
707 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
709 :param name: alias name, will be rendered as ``AS <name>`` in the
710 FROM clause
712 :param joins_implicitly: when True, the table valued function may be
713 used in the FROM clause without any explicit JOIN to other tables
714 in the SQL query, and no "cartesian product" warning will be
715 generated. May be useful for SQL functions such as
716 ``func.json_each()``.
718 .. versionadded:: 1.4.33
720 .. seealso::
722 :ref:`tutorial_functions_table_valued` -
723 in the :ref:`unified_tutorial`
725 :meth:`_functions.FunctionElement.table_valued`
727 :meth:`_functions.FunctionElement.scalar_table_valued`
729 :meth:`_functions.FunctionElement.column_valued`
732 """
734 return TableValuedAlias._construct(
735 self,
736 name=name,
737 table_value_type=self.type,
738 joins_implicitly=joins_implicitly,
739 )
741 def select(self) -> Select[_T]:
742 """Produce a :func:`_expression.select` construct
743 against this :class:`.FunctionElement`.
745 This is shorthand for::
747 s = select(function_element)
749 """
750 s: Select[_T] = Select(self)
751 if self._execution_options:
752 s = s.execution_options(**self._execution_options)
753 return s
755 def _bind_param(
756 self,
757 operator: OperatorType,
758 obj: Any,
759 type_: Optional[TypeEngine[_T]] = None,
760 expanding: bool = False,
761 **kw: Any,
762 ) -> BindParameter[_T]:
763 return BindParameter(
764 None,
765 obj,
766 _compared_to_operator=operator,
767 _compared_to_type=self.type,
768 unique=True,
769 type_=type_,
770 expanding=expanding,
771 **kw,
772 )
774 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
775 # for the moment, we are parenthesizing all array-returning
776 # expressions against getitem. This may need to be made
777 # more portable if in the future we support other DBs
778 # besides postgresql.
779 if against in (operators.getitem, operators.json_getitem_op):
780 return Grouping(self)
781 else:
782 return super().self_group(against=against)
784 @property
785 def entity_namespace(self) -> _EntityNamespace:
786 """overrides FromClause.entity_namespace as functions are generally
787 column expressions and not FromClauses.
789 """
790 # ideally functions would not be fromclauses but we failed to make
791 # this adjustment in 1.4
792 return _entity_namespace(self.clause_expr)
795class FunctionAsBinary(BinaryExpression[Any]):
796 _traverse_internals = [
797 ("sql_function", InternalTraversal.dp_clauseelement),
798 ("left_index", InternalTraversal.dp_plain_obj),
799 ("right_index", InternalTraversal.dp_plain_obj),
800 ("modifiers", InternalTraversal.dp_plain_dict),
801 ]
803 sql_function: FunctionElement[Any]
804 left_index: int
805 right_index: int
807 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
808 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
810 def __init__(
811 self, fn: FunctionElement[Any], left_index: int, right_index: int
812 ) -> None:
813 self.sql_function = fn
814 self.left_index = left_index
815 self.right_index = right_index
817 self.operator = operators.function_as_comparison_op
818 self.type = sqltypes.BOOLEANTYPE
819 self.negate = None
820 self._is_implicitly_boolean = True
821 self.modifiers = util.immutabledict({})
823 @property
824 def left_expr(self) -> ColumnElement[Any]:
825 return self.sql_function.clauses.clauses[self.left_index - 1]
827 @left_expr.setter
828 def left_expr(self, value: ColumnElement[Any]) -> None:
829 self.sql_function.clauses.clauses[self.left_index - 1] = value
831 @property
832 def right_expr(self) -> ColumnElement[Any]:
833 return self.sql_function.clauses.clauses[self.right_index - 1]
835 @right_expr.setter
836 def right_expr(self, value: ColumnElement[Any]) -> None:
837 self.sql_function.clauses.clauses[self.right_index - 1] = value
839 if not TYPE_CHECKING:
840 # mypy can't accommodate @property to replace an instance
841 # variable
843 left = left_expr
844 right = right_expr
847class ScalarFunctionColumn(NamedColumn[_T]):
848 __visit_name__ = "scalar_function_column"
850 _traverse_internals = [
851 ("name", InternalTraversal.dp_anon_name),
852 ("type", InternalTraversal.dp_type),
853 ("fn", InternalTraversal.dp_clauseelement),
854 ]
856 is_literal = False
857 table = None
859 def __init__(
860 self,
861 fn: FunctionElement[_T],
862 name: str,
863 type_: Optional[_TypeEngineArgument[_T]] = None,
864 ) -> None:
865 self.fn = fn
866 self.name = name
868 # if type is None, we get NULLTYPE, which is our _T. But I don't
869 # know how to get the overloads to express that correctly
870 self.type = type_api.to_instance(type_) # type: ignore
873class _FunctionGenerator:
874 """Generate SQL function expressions.
876 :data:`.func` is a special object instance which generates SQL
877 functions based on name-based attributes, e.g.:
879 .. sourcecode:: pycon+sql
881 >>> print(func.count(1))
882 {printsql}count(:param_1)
884 The returned object is an instance of :class:`.Function`, and is a
885 column-oriented SQL element like any other, and is used in that way:
887 .. sourcecode:: pycon+sql
889 >>> print(select(func.count(table.c.id)))
890 {printsql}SELECT count(sometable.id) FROM sometable
892 Any name can be given to :data:`.func`. If the function name is unknown to
893 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
894 which SQLAlchemy is aware of, the name may be interpreted as a *generic
895 function* which will be compiled appropriately to the target database:
897 .. sourcecode:: pycon+sql
899 >>> print(func.current_timestamp())
900 {printsql}CURRENT_TIMESTAMP
902 To call functions which are present in dot-separated packages,
903 specify them in the same manner:
905 .. sourcecode:: pycon+sql
907 >>> print(func.stats.yield_curve(5, 10))
908 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
910 SQLAlchemy can be made aware of the return type of functions to enable
911 type-specific lexical and result-based behavior. For example, to ensure
912 that a string-based function returns a Unicode value and is similarly
913 treated as a string in expressions, specify
914 :class:`~sqlalchemy.types.Unicode` as the type:
916 .. sourcecode:: pycon+sql
918 >>> print(
919 ... func.my_string("hi", type_=Unicode)
920 ... + " "
921 ... + func.my_string("there", type_=Unicode)
922 ... )
923 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
925 The object returned by a :data:`.func` call is usually an instance of
926 :class:`.Function`.
927 This object meets the "column" interface, including comparison and labeling
928 functions. The object can also be passed the :meth:`~.Connectable.execute`
929 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
930 where it will be
931 wrapped inside of a SELECT statement first::
933 print(connection.execute(func.current_timestamp()).scalar())
935 In a few exception cases, the :data:`.func` accessor
936 will redirect a name to a built-in expression such as :func:`.cast`
937 or :func:`.extract`, as these names have well-known meaning
938 but are not exactly the same as "functions" from a SQLAlchemy
939 perspective.
941 Functions which are interpreted as "generic" functions know how to
942 calculate their return type automatically. For a listing of known generic
943 functions, see :ref:`generic_functions`.
945 .. note::
947 The :data:`.func` construct has only limited support for calling
948 standalone "stored procedures", especially those with special
949 parameterization concerns.
951 See the section :ref:`stored_procedures` for details on how to use
952 the DBAPI-level ``callproc()`` method for fully traditional stored
953 procedures.
955 .. seealso::
957 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
959 :class:`.Function`
961 """ # noqa
963 def __init__(self, **opts: Any) -> None:
964 self.__names: List[str] = []
965 self.opts = opts
967 def __getattr__(self, name: str) -> _FunctionGenerator:
968 # passthru __ attributes; fixes pydoc
969 if name.startswith("__"):
970 try:
971 return self.__dict__[name] # type: ignore
972 except KeyError:
973 raise AttributeError(name)
975 elif name.endswith("_"):
976 name = name[0:-1]
977 f = _FunctionGenerator(**self.opts)
978 f.__names = list(self.__names) + [name]
979 return f
981 @overload
982 def __call__(
983 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
984 ) -> Function[_T]: ...
986 @overload
987 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
989 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
990 o = self.opts.copy()
991 o.update(kwargs)
993 tokens = len(self.__names)
995 if tokens == 2:
996 package, fname = self.__names
997 elif tokens == 1:
998 package, fname = "_default", self.__names[0]
999 else:
1000 package = None
1002 if package is not None:
1003 func = _registry[package].get(fname.lower())
1004 if func is not None:
1005 return func(*c, **o)
1007 return Function(
1008 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
1009 )
1011 if TYPE_CHECKING:
1012 # START GENERATED FUNCTION ACCESSORS
1014 # code within this block is **programmatically,
1015 # statically generated** by tools/generate_sql_functions.py
1017 @property
1018 def aggregate_strings(self) -> Type[aggregate_strings]: ...
1020 @property
1021 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
1023 # set ColumnElement[_T] as a separate overload, to appease
1024 # mypy which seems to not want to accept _T from
1025 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1026 # _HasClauseElement as of mypy 1.15
1028 @overload
1029 def array_agg(
1030 self,
1031 col: ColumnElement[_T],
1032 *args: _ColumnExpressionOrLiteralArgument[Any],
1033 **kwargs: Any,
1034 ) -> array_agg[_T]: ...
1036 @overload
1037 def array_agg(
1038 self,
1039 col: _ColumnExpressionArgument[_T],
1040 *args: _ColumnExpressionOrLiteralArgument[Any],
1041 **kwargs: Any,
1042 ) -> array_agg[_T]: ...
1044 @overload
1045 def array_agg(
1046 self,
1047 col: _T,
1048 *args: _ColumnExpressionOrLiteralArgument[Any],
1049 **kwargs: Any,
1050 ) -> array_agg[_T]: ...
1052 def array_agg(
1053 self,
1054 col: _ColumnExpressionOrLiteralArgument[_T],
1055 *args: _ColumnExpressionOrLiteralArgument[Any],
1056 **kwargs: Any,
1057 ) -> array_agg[_T]: ...
1059 @property
1060 def cast(self) -> Type[Cast[Any]]: ...
1062 @property
1063 def char_length(self) -> Type[char_length]: ...
1065 # set ColumnElement[_T] as a separate overload, to appease
1066 # mypy which seems to not want to accept _T from
1067 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1068 # _HasClauseElement as of mypy 1.15
1070 @overload
1071 def coalesce(
1072 self,
1073 col: ColumnElement[_T],
1074 *args: _ColumnExpressionOrLiteralArgument[Any],
1075 **kwargs: Any,
1076 ) -> coalesce[_T]: ...
1078 @overload
1079 def coalesce(
1080 self,
1081 col: _ColumnExpressionArgument[_T],
1082 *args: _ColumnExpressionOrLiteralArgument[Any],
1083 **kwargs: Any,
1084 ) -> coalesce[_T]: ...
1086 @overload
1087 def coalesce(
1088 self,
1089 col: _T,
1090 *args: _ColumnExpressionOrLiteralArgument[Any],
1091 **kwargs: Any,
1092 ) -> coalesce[_T]: ...
1094 def coalesce(
1095 self,
1096 col: _ColumnExpressionOrLiteralArgument[_T],
1097 *args: _ColumnExpressionOrLiteralArgument[Any],
1098 **kwargs: Any,
1099 ) -> coalesce[_T]: ...
1101 @property
1102 def concat(self) -> Type[concat]: ...
1104 @property
1105 def count(self) -> Type[count]: ...
1107 @property
1108 def cube(self) -> Type[cube[Any]]: ...
1110 @property
1111 def cume_dist(self) -> Type[cume_dist]: ...
1113 @property
1114 def current_date(self) -> Type[current_date]: ...
1116 @property
1117 def current_time(self) -> Type[current_time]: ...
1119 @property
1120 def current_timestamp(self) -> Type[current_timestamp]: ...
1122 @property
1123 def current_user(self) -> Type[current_user]: ...
1125 @property
1126 def dense_rank(self) -> Type[dense_rank]: ...
1128 @property
1129 def extract(self) -> Type[Extract]: ...
1131 @property
1132 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1134 @property
1135 def localtime(self) -> Type[localtime]: ...
1137 @property
1138 def localtimestamp(self) -> Type[localtimestamp]: ...
1140 # set ColumnElement[_T] as a separate overload, to appease
1141 # mypy which seems to not want to accept _T from
1142 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1143 # _HasClauseElement as of mypy 1.15
1145 @overload
1146 def max( # noqa: A001
1147 self,
1148 col: ColumnElement[_T],
1149 *args: _ColumnExpressionOrLiteralArgument[Any],
1150 **kwargs: Any,
1151 ) -> max[_T]: ...
1153 @overload
1154 def max( # noqa: A001
1155 self,
1156 col: _ColumnExpressionArgument[_T],
1157 *args: _ColumnExpressionOrLiteralArgument[Any],
1158 **kwargs: Any,
1159 ) -> max[_T]: ...
1161 @overload
1162 def max( # noqa: A001
1163 self,
1164 col: _T,
1165 *args: _ColumnExpressionOrLiteralArgument[Any],
1166 **kwargs: Any,
1167 ) -> max[_T]: ...
1169 def max( # noqa: A001
1170 self,
1171 col: _ColumnExpressionOrLiteralArgument[_T],
1172 *args: _ColumnExpressionOrLiteralArgument[Any],
1173 **kwargs: Any,
1174 ) -> max[_T]: ...
1176 # set ColumnElement[_T] as a separate overload, to appease
1177 # mypy which seems to not want to accept _T from
1178 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1179 # _HasClauseElement as of mypy 1.15
1181 @overload
1182 def min( # noqa: A001
1183 self,
1184 col: ColumnElement[_T],
1185 *args: _ColumnExpressionOrLiteralArgument[Any],
1186 **kwargs: Any,
1187 ) -> min[_T]: ...
1189 @overload
1190 def min( # noqa: A001
1191 self,
1192 col: _ColumnExpressionArgument[_T],
1193 *args: _ColumnExpressionOrLiteralArgument[Any],
1194 **kwargs: Any,
1195 ) -> min[_T]: ...
1197 @overload
1198 def min( # noqa: A001
1199 self,
1200 col: _T,
1201 *args: _ColumnExpressionOrLiteralArgument[Any],
1202 **kwargs: Any,
1203 ) -> min[_T]: ...
1205 def min( # noqa: A001
1206 self,
1207 col: _ColumnExpressionOrLiteralArgument[_T],
1208 *args: _ColumnExpressionOrLiteralArgument[Any],
1209 **kwargs: Any,
1210 ) -> min[_T]: ...
1212 @property
1213 def mode(self) -> Type[mode[Any]]: ...
1215 @property
1216 def next_value(self) -> Type[next_value]: ...
1218 @property
1219 def now(self) -> Type[now]: ...
1221 @property
1222 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1224 @property
1225 def percent_rank(self) -> Type[percent_rank]: ...
1227 @property
1228 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1230 @property
1231 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1233 # set ColumnElement[_T] as a separate overload, to appease
1234 # mypy which seems to not want to accept _T from
1235 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1236 # _HasClauseElement as of mypy 1.15
1238 @overload
1239 def pow( # noqa: A001
1240 self,
1241 col: ColumnElement[_T],
1242 *args: _ColumnExpressionOrLiteralArgument[Any],
1243 **kwargs: Any,
1244 ) -> pow[_T]: ...
1246 @overload
1247 def pow( # noqa: A001
1248 self,
1249 col: _ColumnExpressionArgument[_T],
1250 *args: _ColumnExpressionOrLiteralArgument[Any],
1251 **kwargs: Any,
1252 ) -> pow[_T]: ...
1254 @overload
1255 def pow( # noqa: A001
1256 self,
1257 col: _T,
1258 *args: _ColumnExpressionOrLiteralArgument[Any],
1259 **kwargs: Any,
1260 ) -> pow[_T]: ...
1262 def pow( # noqa: A001
1263 self,
1264 col: _ColumnExpressionOrLiteralArgument[_T],
1265 *args: _ColumnExpressionOrLiteralArgument[Any],
1266 **kwargs: Any,
1267 ) -> pow[_T]: ...
1269 @property
1270 def random(self) -> Type[random]: ...
1272 @property
1273 def rank(self) -> Type[rank]: ...
1275 @property
1276 def rollup(self) -> Type[rollup[Any]]: ...
1278 @property
1279 def session_user(self) -> Type[session_user]: ...
1281 # set ColumnElement[_T] as a separate overload, to appease
1282 # mypy which seems to not want to accept _T from
1283 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1284 # _HasClauseElement as of mypy 1.15
1286 @overload
1287 def sum( # noqa: A001
1288 self,
1289 col: ColumnElement[_T],
1290 *args: _ColumnExpressionOrLiteralArgument[Any],
1291 **kwargs: Any,
1292 ) -> sum[_T]: ...
1294 @overload
1295 def sum( # noqa: A001
1296 self,
1297 col: _ColumnExpressionArgument[_T],
1298 *args: _ColumnExpressionOrLiteralArgument[Any],
1299 **kwargs: Any,
1300 ) -> sum[_T]: ...
1302 @overload
1303 def sum( # noqa: A001
1304 self,
1305 col: _T,
1306 *args: _ColumnExpressionOrLiteralArgument[Any],
1307 **kwargs: Any,
1308 ) -> sum[_T]: ...
1310 def sum( # noqa: A001
1311 self,
1312 col: _ColumnExpressionOrLiteralArgument[_T],
1313 *args: _ColumnExpressionOrLiteralArgument[Any],
1314 **kwargs: Any,
1315 ) -> sum[_T]: ...
1317 @property
1318 def sysdate(self) -> Type[sysdate]: ...
1320 @property
1321 def user(self) -> Type[user]: ...
1323 # END GENERATED FUNCTION ACCESSORS
1326func = _FunctionGenerator()
1327func.__doc__ = _FunctionGenerator.__doc__
1329modifier = _FunctionGenerator(group=False)
1332class Function(FunctionElement[_T]):
1333 r"""Describe a named SQL function.
1335 The :class:`.Function` object is typically generated from the
1336 :data:`.func` generation object.
1339 :param \*clauses: list of column expressions that form the arguments
1340 of the SQL function call.
1342 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1343 used as the return value of the column expression generated by this
1344 function call.
1346 :param packagenames: a string which indicates package prefix names
1347 to be prepended to the function name when the SQL is generated.
1348 The :data:`.func` generator creates these when it is called using
1349 dotted format, e.g.::
1351 func.mypackage.some_function(col1, col2)
1353 .. seealso::
1355 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1357 :data:`.func` - namespace which produces registered or ad-hoc
1358 :class:`.Function` instances.
1360 :class:`.GenericFunction` - allows creation of registered function
1361 types.
1363 """
1365 __visit_name__ = "function"
1367 _traverse_internals = FunctionElement._traverse_internals + [
1368 ("packagenames", InternalTraversal.dp_plain_obj),
1369 ("name", InternalTraversal.dp_string),
1370 ("type", InternalTraversal.dp_type),
1371 ]
1373 name: str
1375 identifier: str
1377 type: TypeEngine[_T]
1378 """A :class:`_types.TypeEngine` object which refers to the SQL return
1379 type represented by this SQL function.
1381 This datatype may be configured when generating a
1382 :class:`_functions.Function` object by passing the
1383 :paramref:`_functions.Function.type_` parameter, e.g.::
1385 >>> select(func.lower("some VALUE", type_=String))
1387 The small number of built-in classes of :class:`_functions.Function` come
1388 with a built-in datatype that's appropriate to the class of function and
1389 its arguments. For functions that aren't known, the type defaults to the
1390 "null type".
1392 """
1394 @overload
1395 def __init__(
1396 self,
1397 name: str,
1398 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1399 type_: None = ...,
1400 packagenames: Optional[Tuple[str, ...]] = ...,
1401 ) -> None: ...
1403 @overload
1404 def __init__(
1405 self,
1406 name: str,
1407 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1408 type_: _TypeEngineArgument[_T] = ...,
1409 packagenames: Optional[Tuple[str, ...]] = ...,
1410 ) -> None: ...
1412 def __init__(
1413 self,
1414 name: str,
1415 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1416 type_: Optional[_TypeEngineArgument[_T]] = None,
1417 packagenames: Optional[Tuple[str, ...]] = None,
1418 ) -> None:
1419 """Construct a :class:`.Function`.
1421 The :data:`.func` construct is normally used to construct
1422 new :class:`.Function` instances.
1424 """
1425 self.packagenames = packagenames or ()
1426 self.name = name
1428 # if type is None, we get NULLTYPE, which is our _T. But I don't
1429 # know how to get the overloads to express that correctly
1430 self.type = type_api.to_instance(type_) # type: ignore
1432 FunctionElement.__init__(self, *clauses)
1434 def _bind_param(
1435 self,
1436 operator: OperatorType,
1437 obj: Any,
1438 type_: Optional[TypeEngine[_T]] = None,
1439 expanding: bool = False,
1440 **kw: Any,
1441 ) -> BindParameter[_T]:
1442 return BindParameter(
1443 self.name,
1444 obj,
1445 _compared_to_operator=operator,
1446 _compared_to_type=self.type,
1447 type_=type_,
1448 unique=True,
1449 expanding=expanding,
1450 **kw,
1451 )
1454class GenericFunction(Function[_T]):
1455 """Define a 'generic' function.
1457 A generic function is a pre-established :class:`.Function`
1458 class that is instantiated automatically when called
1459 by name from the :data:`.func` attribute. Note that
1460 calling any name from :data:`.func` has the effect that
1461 a new :class:`.Function` instance is created automatically,
1462 given that name. The primary use case for defining
1463 a :class:`.GenericFunction` class is so that a function
1464 of a particular name may be given a fixed return type.
1465 It can also include custom argument parsing schemes as well
1466 as additional methods.
1468 Subclasses of :class:`.GenericFunction` are automatically
1469 registered under the name of the class. For
1470 example, a user-defined function ``as_utc()`` would
1471 be available immediately::
1473 from sqlalchemy.sql.functions import GenericFunction
1474 from sqlalchemy.types import DateTime
1477 class as_utc(GenericFunction):
1478 type = DateTime()
1479 inherit_cache = True
1482 print(select(func.as_utc()))
1484 User-defined generic functions can be organized into
1485 packages by specifying the "package" attribute when defining
1486 :class:`.GenericFunction`. Third party libraries
1487 containing many functions may want to use this in order
1488 to avoid name conflicts with other systems. For example,
1489 if our ``as_utc()`` function were part of a package
1490 "time"::
1492 class as_utc(GenericFunction):
1493 type = DateTime()
1494 package = "time"
1495 inherit_cache = True
1497 The above function would be available from :data:`.func`
1498 using the package name ``time``::
1500 print(select(func.time.as_utc()))
1502 A final option is to allow the function to be accessed
1503 from one name in :data:`.func` but to render as a different name.
1504 The ``identifier`` attribute will override the name used to
1505 access the function as loaded from :data:`.func`, but will retain
1506 the usage of ``name`` as the rendered name::
1508 class GeoBuffer(GenericFunction):
1509 type = Geometry()
1510 package = "geo"
1511 name = "ST_Buffer"
1512 identifier = "buffer"
1513 inherit_cache = True
1515 The above function will render as follows:
1517 .. sourcecode:: pycon+sql
1519 >>> print(func.geo.buffer())
1520 {printsql}ST_Buffer()
1522 The name will be rendered as is, however without quoting unless the name
1523 contains special characters that require quoting. To force quoting
1524 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1525 construct::
1527 from sqlalchemy.sql import quoted_name
1530 class GeoBuffer(GenericFunction):
1531 type = Geometry()
1532 package = "geo"
1533 name = quoted_name("ST_Buffer", True)
1534 identifier = "buffer"
1535 inherit_cache = True
1537 The above function will render as:
1539 .. sourcecode:: pycon+sql
1541 >>> print(func.geo.buffer())
1542 {printsql}"ST_Buffer"()
1544 Type parameters for this class as a
1545 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1546 and should match the type seen in a :class:`_engine.Result`. For example::
1548 class as_utc(GenericFunction[datetime.datetime]):
1549 type = DateTime()
1550 inherit_cache = True
1552 The above indicates that the following expression returns a ``datetime``
1553 object::
1555 connection.scalar(select(func.as_utc()))
1557 """
1559 coerce_arguments = True
1560 inherit_cache = True
1562 _register: bool
1564 name = "GenericFunction"
1566 def __init_subclass__(cls) -> None:
1567 if annotation.Annotated not in cls.__mro__:
1568 cls._register_generic_function(cls.__name__, cls.__dict__)
1569 super().__init_subclass__()
1571 @classmethod
1572 def _register_generic_function(
1573 cls, clsname: str, clsdict: Mapping[str, Any]
1574 ) -> None:
1575 cls.name = name = clsdict.get("name", clsname)
1576 cls.identifier = identifier = clsdict.get("identifier", name)
1577 package = clsdict.get("package", "_default")
1578 # legacy
1579 if "__return_type__" in clsdict:
1580 cls.type = clsdict["__return_type__"]
1582 # Check _register attribute status
1583 cls._register = getattr(cls, "_register", True)
1585 # Register the function if required
1586 if cls._register:
1587 register_function(identifier, cls, package)
1588 else:
1589 # Set _register to True to register child classes by default
1590 cls._register = True
1592 def __init__(
1593 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1594 ) -> None:
1595 parsed_args = kwargs.pop("_parsed_args", None)
1596 if parsed_args is None:
1597 parsed_args = [
1598 coercions.expect(
1599 roles.ExpressionElementRole,
1600 c,
1601 name=self.name,
1602 apply_propagate_attrs=self,
1603 )
1604 for c in args
1605 ]
1606 self._has_args = self._has_args or bool(parsed_args)
1607 self.packagenames = ()
1609 self.clause_expr = Grouping(
1610 ClauseList(
1611 operator=operators.comma_op, group_contents=True, *parsed_args
1612 )
1613 )
1615 self.type = type_api.to_instance( # type: ignore
1616 kwargs.pop("type_", None) or getattr(self, "type", None)
1617 )
1620register_function("cast", Cast) # type: ignore
1621register_function("extract", Extract) # type: ignore
1624class next_value(GenericFunction[int]):
1625 """Represent the 'next value', given a :class:`.Sequence`
1626 as its single argument.
1628 Compiles into the appropriate function on each backend,
1629 or will raise NotImplementedError if used on a backend
1630 that does not provide support for sequences.
1632 """
1634 type = sqltypes.Integer()
1635 name = "next_value"
1637 _traverse_internals = [
1638 ("sequence", InternalTraversal.dp_named_ddl_element)
1639 ]
1641 def __init__(self, seq: schema.Sequence, **kw: Any) -> None:
1642 assert isinstance(
1643 seq, schema.Sequence
1644 ), "next_value() accepts a Sequence object as input."
1645 self.sequence = seq
1646 self.type = sqltypes.to_instance( # type: ignore
1647 seq.data_type or getattr(self, "type", None)
1648 )
1650 def compare(self, other: Any, **kw: Any) -> bool:
1651 return (
1652 isinstance(other, next_value)
1653 and self.sequence.name == other.sequence.name
1654 )
1656 @property
1657 def _from_objects(self) -> Any:
1658 return []
1661class AnsiFunction(GenericFunction[_T]):
1662 """Define a function in "ansi" format, which doesn't render parenthesis."""
1664 inherit_cache = True
1666 def __init__(
1667 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1668 ) -> None:
1669 GenericFunction.__init__(self, *args, **kwargs)
1672class ReturnTypeFromArgs(GenericFunction[_T]):
1673 """Define a function whose return type is bound to the type of its
1674 arguments.
1675 """
1677 inherit_cache = True
1679 # set ColumnElement[_T] as a separate overload, to appease
1680 # mypy which seems to not want to accept _T from
1681 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1682 # _HasClauseElement as of mypy 1.15
1684 @overload
1685 def __init__(
1686 self,
1687 col: ColumnElement[_T],
1688 *args: _ColumnExpressionOrLiteralArgument[Any],
1689 **kwargs: Any,
1690 ) -> None: ...
1692 @overload
1693 def __init__(
1694 self,
1695 col: _ColumnExpressionArgument[_T],
1696 *args: _ColumnExpressionOrLiteralArgument[Any],
1697 **kwargs: Any,
1698 ) -> None: ...
1700 @overload
1701 def __init__(
1702 self,
1703 col: _T,
1704 *args: _ColumnExpressionOrLiteralArgument[Any],
1705 **kwargs: Any,
1706 ) -> None: ...
1708 def __init__(
1709 self, *args: _ColumnExpressionOrLiteralArgument[_T], **kwargs: Any
1710 ) -> None:
1711 fn_args: Sequence[ColumnElement[Any]] = [
1712 coercions.expect(
1713 roles.ExpressionElementRole,
1714 c,
1715 name=self.name,
1716 apply_propagate_attrs=self,
1717 )
1718 for c in args
1719 ]
1720 kwargs.setdefault("type_", _type_from_args(fn_args))
1721 kwargs["_parsed_args"] = fn_args
1722 super().__init__(*fn_args, **kwargs)
1725class coalesce(ReturnTypeFromArgs[_T]):
1726 _has_args = True
1727 inherit_cache = True
1730class max(ReturnTypeFromArgs[_T]): # noqa: A001
1731 """The SQL MAX() aggregate function."""
1733 inherit_cache = True
1736class min(ReturnTypeFromArgs[_T]): # noqa: A001
1737 """The SQL MIN() aggregate function."""
1739 inherit_cache = True
1742class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1743 """The SQL SUM() aggregate function."""
1745 inherit_cache = True
1748class now(GenericFunction[datetime.datetime]):
1749 """The SQL now() datetime function.
1751 SQLAlchemy dialects will usually render this particular function
1752 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1754 """
1756 type = sqltypes.DateTime()
1757 inherit_cache = True
1760class pow(ReturnTypeFromArgs[_T]): # noqa: A001
1761 """The SQL POW() function which performs the power operator.
1763 E.g.:
1765 .. sourcecode:: pycon+sql
1767 >>> print(select(func.pow(2, 8)))
1768 {printsql}SELECT pow(:pow_2, :pow_3) AS pow_1
1770 .. versionadded:: 2.1
1772 """
1774 inherit_cache = True
1777class concat(GenericFunction[str]):
1778 """The SQL CONCAT() function, which concatenates strings.
1780 E.g.:
1782 .. sourcecode:: pycon+sql
1784 >>> print(select(func.concat("a", "b")))
1785 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1787 String concatenation in SQLAlchemy is more commonly available using the
1788 Python ``+`` operator with string datatypes, which will render a
1789 backend-specific concatenation operator, such as :
1791 .. sourcecode:: pycon+sql
1793 >>> print(select(literal("a") + "b"))
1794 {printsql}SELECT :param_1 || :param_2 AS anon_1
1797 """
1799 type = sqltypes.String()
1800 inherit_cache = True
1803class char_length(GenericFunction[int]):
1804 """The CHAR_LENGTH() SQL function."""
1806 type = sqltypes.Integer()
1807 inherit_cache = True
1809 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any) -> None:
1810 # slight hack to limit to just one positional argument
1811 # not sure why this one function has this special treatment
1812 super().__init__(arg, **kw)
1815class random(GenericFunction[float]):
1816 """The RANDOM() SQL function."""
1818 _has_args = True
1819 inherit_cache = True
1822class count(GenericFunction[int]):
1823 r"""The ANSI COUNT aggregate function. With no arguments,
1824 emits COUNT \*.
1826 E.g.::
1828 from sqlalchemy import func
1829 from sqlalchemy import select
1830 from sqlalchemy import table, column
1832 my_table = table("some_table", column("id"))
1834 stmt = select(func.count()).select_from(my_table)
1836 Executing ``stmt`` would emit:
1838 .. sourcecode:: sql
1840 SELECT count(*) AS count_1
1841 FROM some_table
1844 """
1846 type = sqltypes.Integer()
1847 inherit_cache = True
1849 def __init__(
1850 self,
1851 expression: Union[
1852 _ColumnExpressionArgument[Any], _StarOrOne, None
1853 ] = None,
1854 **kwargs: Any,
1855 ) -> None:
1856 if expression is None:
1857 expression = literal_column("*")
1858 super().__init__(expression, **kwargs)
1861class current_date(AnsiFunction[datetime.date]):
1862 """The CURRENT_DATE() SQL function."""
1864 type = sqltypes.Date()
1865 inherit_cache = True
1868class current_time(AnsiFunction[datetime.time]):
1869 """The CURRENT_TIME() SQL function."""
1871 type = sqltypes.Time()
1872 inherit_cache = True
1875class current_timestamp(AnsiFunction[datetime.datetime]):
1876 """The CURRENT_TIMESTAMP() SQL function."""
1878 type = sqltypes.DateTime()
1879 inherit_cache = True
1882class current_user(AnsiFunction[str]):
1883 """The CURRENT_USER() SQL function."""
1885 type = sqltypes.String()
1886 inherit_cache = True
1889class localtime(AnsiFunction[datetime.datetime]):
1890 """The localtime() SQL function."""
1892 type = sqltypes.DateTime()
1893 inherit_cache = True
1896class localtimestamp(AnsiFunction[datetime.datetime]):
1897 """The localtimestamp() SQL function."""
1899 type = sqltypes.DateTime()
1900 inherit_cache = True
1903class session_user(AnsiFunction[str]):
1904 """The SESSION_USER() SQL function."""
1906 type = sqltypes.String()
1907 inherit_cache = True
1910class sysdate(AnsiFunction[datetime.datetime]):
1911 """The SYSDATE() SQL function."""
1913 type = sqltypes.DateTime()
1914 inherit_cache = True
1917class user(AnsiFunction[str]):
1918 """The USER() SQL function."""
1920 type = sqltypes.String()
1921 inherit_cache = True
1924class array_agg(ReturnTypeFromArgs[Sequence[_T]]):
1925 """Support for the ARRAY_AGG function.
1927 The ``func.array_agg(expr)`` construct returns an expression of
1928 type :class:`_types.ARRAY`.
1930 e.g.::
1932 stmt = select(func.array_agg(table.c.values)[2:5])
1934 .. seealso::
1936 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1937 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1938 added.
1940 """
1942 inherit_cache = True
1944 def __init__(
1945 self, *args: _ColumnExpressionArgument[Any], **kwargs: Any
1946 ) -> None:
1947 fn_args: Sequence[ColumnElement[Any]] = [
1948 coercions.expect(
1949 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1950 )
1951 for c in args
1952 ]
1954 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1955 if "type_" not in kwargs:
1956 type_from_args = _type_from_args(fn_args)
1957 if isinstance(type_from_args, sqltypes.ARRAY):
1958 kwargs["type_"] = type_from_args
1959 else:
1960 kwargs["type_"] = default_array_type(
1961 type_from_args, dimensions=1
1962 )
1963 kwargs["_parsed_args"] = fn_args
1964 super().__init__(*fn_args, **kwargs)
1967class OrderedSetAgg(GenericFunction[_T]):
1968 """Define a function where the return type is based on the sort
1969 expression type as defined by the expression passed to the
1970 :meth:`.FunctionElement.within_group` method."""
1972 array_for_multi_clause = False
1973 inherit_cache = True
1975 def within_group_type(
1976 self, within_group: WithinGroup[Any]
1977 ) -> TypeEngine[Any]:
1978 func_clauses = cast(ClauseList, self.clause_expr.element)
1979 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
1980 within_group.order_by
1981 )
1982 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1983 return sqltypes.ARRAY(order_by[0].type)
1984 else:
1985 return order_by[0].type
1988class mode(OrderedSetAgg[_T]):
1989 """Implement the ``mode`` ordered-set aggregate function.
1991 This function must be used with the :meth:`.FunctionElement.within_group`
1992 modifier to supply a sort expression to operate upon.
1994 The return type of this function is the same as the sort expression.
1996 """
1998 inherit_cache = True
2001class percentile_cont(OrderedSetAgg[_T]):
2002 """Implement the ``percentile_cont`` ordered-set aggregate function.
2004 This function must be used with the :meth:`.FunctionElement.within_group`
2005 modifier to supply a sort expression to operate upon.
2007 The return type of this function is the same as the sort expression,
2008 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2009 expression's type.
2011 """
2013 array_for_multi_clause = True
2014 inherit_cache = True
2017class percentile_disc(OrderedSetAgg[_T]):
2018 """Implement the ``percentile_disc`` ordered-set aggregate function.
2020 This function must be used with the :meth:`.FunctionElement.within_group`
2021 modifier to supply a sort expression to operate upon.
2023 The return type of this function is the same as the sort expression,
2024 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
2025 expression's type.
2027 """
2029 array_for_multi_clause = True
2030 inherit_cache = True
2033class rank(GenericFunction[int]):
2034 """Implement the ``rank`` hypothetical-set aggregate function.
2036 This function must be used with the :meth:`.FunctionElement.within_group`
2037 modifier to supply a sort expression to operate upon.
2039 The return type of this function is :class:`.Integer`.
2041 """
2043 type = sqltypes.Integer()
2044 inherit_cache = True
2047class dense_rank(GenericFunction[int]):
2048 """Implement the ``dense_rank`` hypothetical-set aggregate function.
2050 This function must be used with the :meth:`.FunctionElement.within_group`
2051 modifier to supply a sort expression to operate upon.
2053 The return type of this function is :class:`.Integer`.
2055 """
2057 type = sqltypes.Integer()
2058 inherit_cache = True
2061class percent_rank(GenericFunction[decimal.Decimal]):
2062 """Implement the ``percent_rank`` hypothetical-set aggregate function.
2064 This function must be used with the :meth:`.FunctionElement.within_group`
2065 modifier to supply a sort expression to operate upon.
2067 The return type of this function is :class:`.Numeric`.
2069 """
2071 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2072 inherit_cache = True
2075class cume_dist(GenericFunction[decimal.Decimal]):
2076 """Implement the ``cume_dist`` hypothetical-set aggregate function.
2078 This function must be used with the :meth:`.FunctionElement.within_group`
2079 modifier to supply a sort expression to operate upon.
2081 The return type of this function is :class:`.Numeric`.
2083 """
2085 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
2086 inherit_cache = True
2089class cube(GenericFunction[_T]):
2090 r"""Implement the ``CUBE`` grouping operation.
2092 This function is used as part of the GROUP BY of a statement,
2093 e.g. :meth:`_expression.Select.group_by`::
2095 stmt = select(
2096 func.sum(table.c.value), table.c.col_1, table.c.col_2
2097 ).group_by(func.cube(table.c.col_1, table.c.col_2))
2099 """
2101 _has_args = True
2102 inherit_cache = True
2105class rollup(GenericFunction[_T]):
2106 r"""Implement the ``ROLLUP`` grouping operation.
2108 This function is used as part of the GROUP BY of a statement,
2109 e.g. :meth:`_expression.Select.group_by`::
2111 stmt = select(
2112 func.sum(table.c.value), table.c.col_1, table.c.col_2
2113 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
2115 """
2117 _has_args = True
2118 inherit_cache = True
2121class grouping_sets(GenericFunction[_T]):
2122 r"""Implement the ``GROUPING SETS`` grouping operation.
2124 This function is used as part of the GROUP BY of a statement,
2125 e.g. :meth:`_expression.Select.group_by`::
2127 stmt = select(
2128 func.sum(table.c.value), table.c.col_1, table.c.col_2
2129 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2131 In order to group by multiple sets, use the :func:`.tuple_` construct::
2133 from sqlalchemy import tuple_
2135 stmt = select(
2136 func.sum(table.c.value), table.c.col_1, table.c.col_2, table.c.col_3
2137 ).group_by(
2138 func.grouping_sets(
2139 tuple_(table.c.col_1, table.c.col_2),
2140 tuple_(table.c.value, table.c.col_3),
2141 )
2142 )
2144 """ # noqa: E501
2146 _has_args = True
2147 inherit_cache = True
2150class aggregate_strings(GenericFunction[str]):
2151 """Implement a generic string aggregation function.
2153 This function will concatenate non-null values into a string and
2154 separate the values by a delimiter.
2156 This function is compiled on a per-backend basis, into functions
2157 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2159 e.g. Example usage with delimiter '.'::
2161 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2163 .. versionadded:: 2.0.21
2165 To add ordering to the expression, use the
2166 :meth:`_functions.FunctionElement.aggregate_order_by` modifier method,
2167 which will emit ORDER BY within the appropriate part of the column
2168 expression (varies by backend)::
2170 stmt = select(
2171 func.aggregate_strings(table.c.str_col, ".").aggregate_order_by(
2172 table.c.str_col
2173 )
2174 )
2176 .. versionadded:: 2.1 added :meth:`_functions.FunctionElement.aggregate_order_by`
2177 for all aggregate functions.
2179 :param clause: the SQL expression to be concatenated
2181 :param separator: separator string
2184 """ # noqa: E501
2186 type = sqltypes.String()
2187 _has_args = True
2188 inherit_cache = True
2190 def __init__(
2191 self,
2192 clause: _ColumnExpressionArgument[Any],
2193 separator: str,
2194 ) -> None:
2195 super().__init__(clause, separator)