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-2024 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
9"""SQL function API, factories, and built-in functions.
11"""
13from __future__ import annotations
15import datetime
16import decimal
17from typing import Any
18from typing import cast
19from typing import Dict
20from typing import List
21from typing import Mapping
22from typing import Optional
23from typing import overload
24from typing import Sequence
25from typing import Tuple
26from typing import Type
27from typing import TYPE_CHECKING
28from typing import TypeVar
29from typing import Union
31from . import annotation
32from . import coercions
33from . import operators
34from . import roles
35from . import schema
36from . import sqltypes
37from . import type_api
38from . import util as sqlutil
39from ._typing import is_table_value_type
40from .base import _entity_namespace
41from .base import ColumnCollection
42from .base import Executable
43from .base import Generative
44from .base import HasMemoized
45from .elements import _type_from_args
46from .elements import BinaryExpression
47from .elements import BindParameter
48from .elements import Cast
49from .elements import ClauseList
50from .elements import ColumnElement
51from .elements import Extract
52from .elements import FunctionFilter
53from .elements import Grouping
54from .elements import literal_column
55from .elements import NamedColumn
56from .elements import Over
57from .elements import WithinGroup
58from .selectable import FromClause
59from .selectable import Select
60from .selectable import TableValuedAlias
61from .sqltypes import TableValueType
62from .type_api import TypeEngine
63from .visitors import InternalTraversal
64from .. 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 ClauseElement
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 ]
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__(self, *clauses: _ColumnExpressionOrLiteralArgument[Any]):
159 r"""Construct a :class:`.FunctionElement`.
161 :param \*clauses: list of column expressions that form the arguments
162 of the SQL function call.
164 :param \**kwargs: additional kwargs are typically consumed by
165 subclasses.
167 .. seealso::
169 :data:`.func`
171 :class:`.Function`
173 """
174 args: Sequence[_ColumnExpressionArgument[Any]] = [
175 coercions.expect(
176 roles.ExpressionElementRole,
177 c,
178 name=getattr(self, "name", None),
179 apply_propagate_attrs=self,
180 )
181 for c in clauses
182 ]
183 self._has_args = self._has_args or bool(args)
184 self.clause_expr = Grouping(
185 ClauseList(operator=operators.comma_op, group_contents=True, *args)
186 )
188 _non_anon_label = None
190 @property
191 def _proxy_key(self) -> Any:
192 return super()._proxy_key or getattr(self, "name", None)
194 def _execute_on_connection(
195 self,
196 connection: Connection,
197 distilled_params: _CoreMultiExecuteParams,
198 execution_options: CoreExecuteOptionsParameter,
199 ) -> CursorResult[Any]:
200 return connection._execute_function(
201 self, distilled_params, execution_options
202 )
204 def scalar_table_valued(
205 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
206 ) -> ScalarFunctionColumn[_T]:
207 """Return a column expression that's against this
208 :class:`_functions.FunctionElement` as a scalar
209 table-valued expression.
211 The returned expression is similar to that returned by a single column
212 accessed off of a :meth:`_functions.FunctionElement.table_valued`
213 construct, except no FROM clause is generated; the function is rendered
214 in the similar way as a scalar subquery.
216 E.g.:
218 .. sourcecode:: pycon+sql
220 >>> from sqlalchemy import func, select
221 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
222 >>> print(select(fn))
223 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
225 .. versionadded:: 1.4.0b2
227 .. seealso::
229 :meth:`_functions.FunctionElement.table_valued`
231 :meth:`_functions.FunctionElement.alias`
233 :meth:`_functions.FunctionElement.column_valued`
235 """ # noqa: E501
237 return ScalarFunctionColumn(self, name, type_)
239 def table_valued(
240 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
241 ) -> TableValuedAlias:
242 r"""Return a :class:`_sql.TableValuedAlias` representation of this
243 :class:`_functions.FunctionElement` with table-valued expressions added.
245 e.g.:
247 .. sourcecode:: pycon+sql
249 >>> fn = (
250 ... func.generate_series(1, 5).
251 ... table_valued("value", "start", "stop", "step")
252 ... )
254 >>> print(select(fn))
255 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
256 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
258 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
259 {printsql}SELECT anon_1.value, anon_1.stop
260 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
261 WHERE anon_1.value > :value_1{stop}
263 A WITH ORDINALITY expression may be generated by passing the keyword
264 argument "with_ordinality":
266 .. sourcecode:: pycon+sql
268 >>> fn = func.generate_series(4, 1, -1).table_valued("gen", with_ordinality="ordinality")
269 >>> print(select(fn))
270 {printsql}SELECT anon_1.gen, anon_1.ordinality
271 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1
273 :param \*expr: A series of string column names that will be added to the
274 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
275 construct as columns. :func:`_sql.column` objects with or without
276 datatypes may also be used.
278 :param name: optional name to assign to the alias name that's generated.
279 If omitted, a unique anonymizing name is used.
281 :param with_ordinality: string name that when present results in the
282 ``WITH ORDINALITY`` clause being added to the alias, and the given
283 string name will be added as a column to the .c collection
284 of the resulting :class:`_sql.TableValuedAlias`.
286 :param joins_implicitly: when True, the table valued function may be
287 used in the FROM clause without any explicit JOIN to other tables
288 in the SQL query, and no "cartesian product" warning will be generated.
289 May be useful for SQL functions such as ``func.json_each()``.
291 .. versionadded:: 1.4.33
293 .. versionadded:: 1.4.0b2
296 .. seealso::
298 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
300 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
302 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
303 :meth:`_functions.FunctionElement.table_valued` which delivers the
304 complete table valued expression as a scalar column expression
306 :meth:`_functions.FunctionElement.column_valued`
308 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
309 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
311 """ # noqa: 501
313 new_func = self._generate()
315 with_ordinality = kw.pop("with_ordinality", None)
316 joins_implicitly = kw.pop("joins_implicitly", None)
317 name = kw.pop("name", None)
319 if with_ordinality:
320 expr += (with_ordinality,)
321 new_func._with_ordinality = True
323 new_func.type = new_func._table_value_type = TableValueType(*expr)
325 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
327 def column_valued(
328 self, name: Optional[str] = None, joins_implicitly: bool = False
329 ) -> TableValuedColumn[_T]:
330 """Return this :class:`_functions.FunctionElement` as a column expression that
331 selects from itself as a FROM clause.
333 E.g.:
335 .. sourcecode:: pycon+sql
337 >>> from sqlalchemy import select, func
338 >>> gs = func.generate_series(1, 5, -1).column_valued()
339 >>> print(select(gs))
340 {printsql}SELECT anon_1
341 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
343 This is shorthand for::
345 gs = func.generate_series(1, 5, -1).alias().column
347 :param name: optional name to assign to the alias name that's generated.
348 If omitted, a unique anonymizing name is used.
350 :param joins_implicitly: when True, the "table" portion of the column
351 valued function may be a member of the FROM clause without any
352 explicit JOIN to other tables in the SQL query, and no "cartesian
353 product" warning will be generated. May be useful for SQL functions
354 such as ``func.json_array_elements()``.
356 .. versionadded:: 1.4.46
358 .. seealso::
360 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
362 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
364 :meth:`_functions.FunctionElement.table_valued`
366 """ # noqa: 501
368 return self.alias(name=name, joins_implicitly=joins_implicitly).column
370 @util.ro_non_memoized_property
371 def columns(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
372 r"""The set of columns exported by this :class:`.FunctionElement`.
374 This is a placeholder collection that allows the function to be
375 placed in the FROM clause of a statement:
377 .. sourcecode:: pycon+sql
379 >>> from sqlalchemy import column, select, func
380 >>> stmt = select(column('x'), column('y')).select_from(func.myfunction())
381 >>> print(stmt)
382 {printsql}SELECT x, y FROM myfunction()
384 The above form is a legacy feature that is now superseded by the
385 fully capable :meth:`_functions.FunctionElement.table_valued`
386 method; see that method for details.
388 .. seealso::
390 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
391 SQL function expressions.
393 """ # noqa: E501
394 return self.c
396 @util.ro_memoized_property
397 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
398 """synonym for :attr:`.FunctionElement.columns`."""
400 return ColumnCollection(
401 columns=[(col.key, col) for col in self._all_selected_columns]
402 )
404 @property
405 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
406 if is_table_value_type(self.type):
407 # TODO: this might not be fully accurate
408 cols = cast(
409 "Sequence[KeyedColumnElement[Any]]", self.type._elements
410 )
411 else:
412 cols = [self.label(None)]
414 return cols
416 @property
417 def exported_columns( # type: ignore[override]
418 self,
419 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
420 return self.columns
422 @HasMemoized.memoized_attribute
423 def clauses(self) -> ClauseList:
424 """Return the underlying :class:`.ClauseList` which contains
425 the arguments for this :class:`.FunctionElement`.
427 """
428 return cast(ClauseList, self.clause_expr.element)
430 def over(
431 self,
432 *,
433 partition_by: Optional[_ByArgument] = None,
434 order_by: Optional[_ByArgument] = None,
435 rows: Optional[Tuple[Optional[int], Optional[int]]] = None,
436 range_: Optional[Tuple[Optional[int], Optional[int]]] = None,
437 ) -> Over[_T]:
438 """Produce an OVER clause against this function.
440 Used against aggregate or so-called "window" functions,
441 for database backends that support window functions.
443 The expression::
445 func.row_number().over(order_by='x')
447 is shorthand for::
449 from sqlalchemy import over
450 over(func.row_number(), order_by='x')
452 See :func:`_expression.over` for a full description.
454 .. seealso::
456 :func:`_expression.over`
458 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
460 """
461 return Over(
462 self,
463 partition_by=partition_by,
464 order_by=order_by,
465 rows=rows,
466 range_=range_,
467 )
469 def within_group(
470 self, *order_by: _ColumnExpressionArgument[Any]
471 ) -> WithinGroup[_T]:
472 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
474 Used against so-called "ordered set aggregate" and "hypothetical
475 set aggregate" functions, including :class:`.percentile_cont`,
476 :class:`.rank`, :class:`.dense_rank`, etc.
478 See :func:`_expression.within_group` for a full description.
480 .. seealso::
482 :ref:`tutorial_functions_within_group` -
483 in the :ref:`unified_tutorial`
486 """
487 return WithinGroup(self, *order_by)
489 @overload
490 def filter(self) -> Self: ...
492 @overload
493 def filter(
494 self,
495 __criterion0: _ColumnExpressionArgument[bool],
496 *criterion: _ColumnExpressionArgument[bool],
497 ) -> FunctionFilter[_T]: ...
499 def filter(
500 self, *criterion: _ColumnExpressionArgument[bool]
501 ) -> Union[Self, FunctionFilter[_T]]:
502 """Produce a FILTER clause against this function.
504 Used against aggregate and window functions,
505 for database backends that support the "FILTER" clause.
507 The expression::
509 func.count(1).filter(True)
511 is shorthand for::
513 from sqlalchemy import funcfilter
514 funcfilter(func.count(1), True)
516 .. seealso::
518 :ref:`tutorial_functions_within_group` -
519 in the :ref:`unified_tutorial`
521 :class:`.FunctionFilter`
523 :func:`.funcfilter`
526 """
527 if not criterion:
528 return self
529 return FunctionFilter(self, *criterion)
531 def as_comparison(
532 self, left_index: int, right_index: int
533 ) -> FunctionAsBinary:
534 """Interpret this expression as a boolean comparison between two
535 values.
537 This method is used for an ORM use case described at
538 :ref:`relationship_custom_operator_sql_function`.
540 A hypothetical SQL function "is_equal()" which compares to values
541 for equality would be written in the Core expression language as::
543 expr = func.is_equal("a", "b")
545 If "is_equal()" above is comparing "a" and "b" for equality, the
546 :meth:`.FunctionElement.as_comparison` method would be invoked as::
548 expr = func.is_equal("a", "b").as_comparison(1, 2)
550 Where above, the integer value "1" refers to the first argument of the
551 "is_equal()" function and the integer value "2" refers to the second.
553 This would create a :class:`.BinaryExpression` that is equivalent to::
555 BinaryExpression("a", "b", operator=op.eq)
557 However, at the SQL level it would still render as
558 "is_equal('a', 'b')".
560 The ORM, when it loads a related object or collection, needs to be able
561 to manipulate the "left" and "right" sides of the ON clause of a JOIN
562 expression. The purpose of this method is to provide a SQL function
563 construct that can also supply this information to the ORM, when used
564 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
565 return value is a containment object called :class:`.FunctionAsBinary`.
567 An ORM example is as follows::
569 class Venue(Base):
570 __tablename__ = 'venue'
571 id = Column(Integer, primary_key=True)
572 name = Column(String)
574 descendants = relationship(
575 "Venue",
576 primaryjoin=func.instr(
577 remote(foreign(name)), name + "/"
578 ).as_comparison(1, 2) == 1,
579 viewonly=True,
580 order_by=name
581 )
583 Above, the "Venue" class can load descendant "Venue" objects by
584 determining if the name of the parent Venue is contained within the
585 start of the hypothetical descendant value's name, e.g. "parent1" would
586 match up to "parent1/child1", but not to "parent2/child1".
588 Possible use cases include the "materialized path" example given above,
589 as well as making use of special SQL functions such as geometric
590 functions to create join conditions.
592 :param left_index: the integer 1-based index of the function argument
593 that serves as the "left" side of the expression.
594 :param right_index: the integer 1-based index of the function argument
595 that serves as the "right" side of the expression.
597 .. versionadded:: 1.3
599 .. seealso::
601 :ref:`relationship_custom_operator_sql_function` -
602 example use within the ORM
604 """
605 return FunctionAsBinary(self, left_index, right_index)
607 @property
608 def _from_objects(self) -> Any:
609 return self.clauses._from_objects
611 def within_group_type(
612 self, within_group: WithinGroup[_S]
613 ) -> Optional[TypeEngine[_S]]:
614 """For types that define their return type as based on the criteria
615 within a WITHIN GROUP (ORDER BY) expression, called by the
616 :class:`.WithinGroup` construct.
618 Returns None by default, in which case the function's normal ``.type``
619 is used.
621 """
623 return None
625 def alias(
626 self, name: Optional[str] = None, joins_implicitly: bool = False
627 ) -> TableValuedAlias:
628 r"""Produce a :class:`_expression.Alias` construct against this
629 :class:`.FunctionElement`.
631 .. tip::
633 The :meth:`_functions.FunctionElement.alias` method is part of the
634 mechanism by which "table valued" SQL functions are created.
635 However, most use cases are covered by higher level methods on
636 :class:`_functions.FunctionElement` including
637 :meth:`_functions.FunctionElement.table_valued`, and
638 :meth:`_functions.FunctionElement.column_valued`.
640 This construct wraps the function in a named alias which
641 is suitable for the FROM clause, in the style accepted for example
642 by PostgreSQL. A column expression is also provided using the
643 special ``.column`` attribute, which may
644 be used to refer to the output of the function as a scalar value
645 in the columns or where clause, for a backend such as PostgreSQL.
647 For a full table-valued expression, use the
648 :meth:`_functions.FunctionElement.table_valued` method first to
649 establish named columns.
651 e.g.:
653 .. sourcecode:: pycon+sql
655 >>> from sqlalchemy import func, select, column
656 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
657 >>> print(select(data_view.column))
658 {printsql}SELECT data_view
659 FROM unnest(:unnest_1) AS data_view
661 The :meth:`_functions.FunctionElement.column_valued` method provides
662 a shortcut for the above pattern:
664 .. sourcecode:: pycon+sql
666 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
667 >>> print(select(data_view))
668 {printsql}SELECT data_view
669 FROM unnest(:unnest_1) AS data_view
671 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
673 :param name: alias name, will be rendered as ``AS <name>`` in the
674 FROM clause
676 :param joins_implicitly: when True, the table valued function may be
677 used in the FROM clause without any explicit JOIN to other tables
678 in the SQL query, and no "cartesian product" warning will be
679 generated. May be useful for SQL functions such as
680 ``func.json_each()``.
682 .. versionadded:: 1.4.33
684 .. seealso::
686 :ref:`tutorial_functions_table_valued` -
687 in the :ref:`unified_tutorial`
689 :meth:`_functions.FunctionElement.table_valued`
691 :meth:`_functions.FunctionElement.scalar_table_valued`
693 :meth:`_functions.FunctionElement.column_valued`
696 """
698 return TableValuedAlias._construct(
699 self,
700 name=name,
701 table_value_type=self.type,
702 joins_implicitly=joins_implicitly,
703 )
705 def select(self) -> Select[_T]:
706 """Produce a :func:`_expression.select` construct
707 against this :class:`.FunctionElement`.
709 This is shorthand for::
711 s = select(function_element)
713 """
714 s: Select[_T] = Select(self)
715 if self._execution_options:
716 s = s.execution_options(**self._execution_options)
717 return s
719 def _bind_param(
720 self,
721 operator: OperatorType,
722 obj: Any,
723 type_: Optional[TypeEngine[_T]] = None,
724 expanding: bool = False,
725 **kw: Any,
726 ) -> BindParameter[_T]:
727 return BindParameter(
728 None,
729 obj,
730 _compared_to_operator=operator,
731 _compared_to_type=self.type,
732 unique=True,
733 type_=type_,
734 expanding=expanding,
735 **kw,
736 )
738 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
739 # for the moment, we are parenthesizing all array-returning
740 # expressions against getitem. This may need to be made
741 # more portable if in the future we support other DBs
742 # besides postgresql.
743 if against is operators.getitem and isinstance(
744 self.type, sqltypes.ARRAY
745 ):
746 return Grouping(self)
747 else:
748 return super().self_group(against=against)
750 @property
751 def entity_namespace(self) -> _EntityNamespace:
752 """overrides FromClause.entity_namespace as functions are generally
753 column expressions and not FromClauses.
755 """
756 # ideally functions would not be fromclauses but we failed to make
757 # this adjustment in 1.4
758 return _entity_namespace(self.clause_expr)
761class FunctionAsBinary(BinaryExpression[Any]):
762 _traverse_internals = [
763 ("sql_function", InternalTraversal.dp_clauseelement),
764 ("left_index", InternalTraversal.dp_plain_obj),
765 ("right_index", InternalTraversal.dp_plain_obj),
766 ("modifiers", InternalTraversal.dp_plain_dict),
767 ]
769 sql_function: FunctionElement[Any]
770 left_index: int
771 right_index: int
773 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
774 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
776 def __init__(
777 self, fn: FunctionElement[Any], left_index: int, right_index: int
778 ):
779 self.sql_function = fn
780 self.left_index = left_index
781 self.right_index = right_index
783 self.operator = operators.function_as_comparison_op
784 self.type = sqltypes.BOOLEANTYPE
785 self.negate = None
786 self._is_implicitly_boolean = True
787 self.modifiers = {}
789 @property
790 def left_expr(self) -> ColumnElement[Any]:
791 return self.sql_function.clauses.clauses[self.left_index - 1]
793 @left_expr.setter
794 def left_expr(self, value: ColumnElement[Any]) -> None:
795 self.sql_function.clauses.clauses[self.left_index - 1] = value
797 @property
798 def right_expr(self) -> ColumnElement[Any]:
799 return self.sql_function.clauses.clauses[self.right_index - 1]
801 @right_expr.setter
802 def right_expr(self, value: ColumnElement[Any]) -> None:
803 self.sql_function.clauses.clauses[self.right_index - 1] = value
805 if not TYPE_CHECKING:
806 # mypy can't accommodate @property to replace an instance
807 # variable
809 left = left_expr
810 right = right_expr
813class ScalarFunctionColumn(NamedColumn[_T]):
814 __visit_name__ = "scalar_function_column"
816 _traverse_internals = [
817 ("name", InternalTraversal.dp_anon_name),
818 ("type", InternalTraversal.dp_type),
819 ("fn", InternalTraversal.dp_clauseelement),
820 ]
822 is_literal = False
823 table = None
825 def __init__(
826 self,
827 fn: FunctionElement[_T],
828 name: str,
829 type_: Optional[_TypeEngineArgument[_T]] = None,
830 ):
831 self.fn = fn
832 self.name = name
834 # if type is None, we get NULLTYPE, which is our _T. But I don't
835 # know how to get the overloads to express that correctly
836 self.type = type_api.to_instance(type_) # type: ignore
839class _FunctionGenerator:
840 """Generate SQL function expressions.
842 :data:`.func` is a special object instance which generates SQL
843 functions based on name-based attributes, e.g.:
845 .. sourcecode:: pycon+sql
847 >>> print(func.count(1))
848 {printsql}count(:param_1)
850 The returned object is an instance of :class:`.Function`, and is a
851 column-oriented SQL element like any other, and is used in that way:
853 .. sourcecode:: pycon+sql
855 >>> print(select(func.count(table.c.id)))
856 {printsql}SELECT count(sometable.id) FROM sometable
858 Any name can be given to :data:`.func`. If the function name is unknown to
859 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
860 which SQLAlchemy is aware of, the name may be interpreted as a *generic
861 function* which will be compiled appropriately to the target database:
863 .. sourcecode:: pycon+sql
865 >>> print(func.current_timestamp())
866 {printsql}CURRENT_TIMESTAMP
868 To call functions which are present in dot-separated packages,
869 specify them in the same manner:
871 .. sourcecode:: pycon+sql
873 >>> print(func.stats.yield_curve(5, 10))
874 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
876 SQLAlchemy can be made aware of the return type of functions to enable
877 type-specific lexical and result-based behavior. For example, to ensure
878 that a string-based function returns a Unicode value and is similarly
879 treated as a string in expressions, specify
880 :class:`~sqlalchemy.types.Unicode` as the type:
882 .. sourcecode:: pycon+sql
884 >>> print(func.my_string(u'hi', type_=Unicode) + ' ' +
885 ... func.my_string(u'there', type_=Unicode))
886 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
888 The object returned by a :data:`.func` call is usually an instance of
889 :class:`.Function`.
890 This object meets the "column" interface, including comparison and labeling
891 functions. The object can also be passed the :meth:`~.Connectable.execute`
892 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
893 where it will be
894 wrapped inside of a SELECT statement first::
896 print(connection.execute(func.current_timestamp()).scalar())
898 In a few exception cases, the :data:`.func` accessor
899 will redirect a name to a built-in expression such as :func:`.cast`
900 or :func:`.extract`, as these names have well-known meaning
901 but are not exactly the same as "functions" from a SQLAlchemy
902 perspective.
904 Functions which are interpreted as "generic" functions know how to
905 calculate their return type automatically. For a listing of known generic
906 functions, see :ref:`generic_functions`.
908 .. note::
910 The :data:`.func` construct has only limited support for calling
911 standalone "stored procedures", especially those with special
912 parameterization concerns.
914 See the section :ref:`stored_procedures` for details on how to use
915 the DBAPI-level ``callproc()`` method for fully traditional stored
916 procedures.
918 .. seealso::
920 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
922 :class:`.Function`
924 """ # noqa
926 def __init__(self, **opts: Any):
927 self.__names: List[str] = []
928 self.opts = opts
930 def __getattr__(self, name: str) -> _FunctionGenerator:
931 # passthru __ attributes; fixes pydoc
932 if name.startswith("__"):
933 try:
934 return self.__dict__[name] # type: ignore
935 except KeyError:
936 raise AttributeError(name)
938 elif name.endswith("_"):
939 name = name[0:-1]
940 f = _FunctionGenerator(**self.opts)
941 f.__names = list(self.__names) + [name]
942 return f
944 @overload
945 def __call__(
946 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
947 ) -> Function[_T]: ...
949 @overload
950 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
952 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
953 o = self.opts.copy()
954 o.update(kwargs)
956 tokens = len(self.__names)
958 if tokens == 2:
959 package, fname = self.__names
960 elif tokens == 1:
961 package, fname = "_default", self.__names[0]
962 else:
963 package = None
965 if package is not None:
966 func = _registry[package].get(fname.lower())
967 if func is not None:
968 return func(*c, **o)
970 return Function(
971 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
972 )
974 if TYPE_CHECKING:
975 # START GENERATED FUNCTION ACCESSORS
977 # code within this block is **programmatically,
978 # statically generated** by tools/generate_sql_functions.py
980 @property
981 def aggregate_strings(self) -> Type[aggregate_strings]: ...
983 @property
984 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
986 @property
987 def array_agg(self) -> Type[array_agg[Any]]: ...
989 @property
990 def cast(self) -> Type[Cast[Any]]: ...
992 @property
993 def char_length(self) -> Type[char_length]: ...
995 # set ColumnElement[_T] as a separate overload, to appease mypy
996 # which seems to not want to accept _T from _ColumnExpressionArgument.
997 # this is even if all non-generic types are removed from it, so
998 # reasons remain unclear for why this does not work
1000 @overload
1001 def coalesce(
1002 self,
1003 col: ColumnElement[_T],
1004 *args: _ColumnExpressionOrLiteralArgument[Any],
1005 **kwargs: Any,
1006 ) -> coalesce[_T]: ...
1008 @overload
1009 def coalesce(
1010 self,
1011 col: _ColumnExpressionArgument[_T],
1012 *args: _ColumnExpressionOrLiteralArgument[Any],
1013 **kwargs: Any,
1014 ) -> coalesce[_T]: ...
1016 @overload
1017 def coalesce(
1018 self,
1019 col: _ColumnExpressionOrLiteralArgument[_T],
1020 *args: _ColumnExpressionOrLiteralArgument[Any],
1021 **kwargs: Any,
1022 ) -> coalesce[_T]: ...
1024 def coalesce(
1025 self,
1026 col: _ColumnExpressionOrLiteralArgument[_T],
1027 *args: _ColumnExpressionOrLiteralArgument[Any],
1028 **kwargs: Any,
1029 ) -> coalesce[_T]: ...
1031 @property
1032 def concat(self) -> Type[concat]: ...
1034 @property
1035 def count(self) -> Type[count]: ...
1037 @property
1038 def cube(self) -> Type[cube[Any]]: ...
1040 @property
1041 def cume_dist(self) -> Type[cume_dist]: ...
1043 @property
1044 def current_date(self) -> Type[current_date]: ...
1046 @property
1047 def current_time(self) -> Type[current_time]: ...
1049 @property
1050 def current_timestamp(self) -> Type[current_timestamp]: ...
1052 @property
1053 def current_user(self) -> Type[current_user]: ...
1055 @property
1056 def dense_rank(self) -> Type[dense_rank]: ...
1058 @property
1059 def extract(self) -> Type[Extract]: ...
1061 @property
1062 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1064 @property
1065 def localtime(self) -> Type[localtime]: ...
1067 @property
1068 def localtimestamp(self) -> Type[localtimestamp]: ...
1070 # set ColumnElement[_T] as a separate overload, to appease mypy
1071 # which seems to not want to accept _T from _ColumnExpressionArgument.
1072 # this is even if all non-generic types are removed from it, so
1073 # reasons remain unclear for why this does not work
1075 @overload
1076 def max( # noqa: A001
1077 self,
1078 col: ColumnElement[_T],
1079 *args: _ColumnExpressionOrLiteralArgument[Any],
1080 **kwargs: Any,
1081 ) -> max[_T]: ...
1083 @overload
1084 def max( # noqa: A001
1085 self,
1086 col: _ColumnExpressionArgument[_T],
1087 *args: _ColumnExpressionOrLiteralArgument[Any],
1088 **kwargs: Any,
1089 ) -> max[_T]: ...
1091 @overload
1092 def max( # noqa: A001
1093 self,
1094 col: _ColumnExpressionOrLiteralArgument[_T],
1095 *args: _ColumnExpressionOrLiteralArgument[Any],
1096 **kwargs: Any,
1097 ) -> max[_T]: ...
1099 def max( # noqa: A001
1100 self,
1101 col: _ColumnExpressionOrLiteralArgument[_T],
1102 *args: _ColumnExpressionOrLiteralArgument[Any],
1103 **kwargs: Any,
1104 ) -> max[_T]: ...
1106 # set ColumnElement[_T] as a separate overload, to appease mypy
1107 # which seems to not want to accept _T from _ColumnExpressionArgument.
1108 # this is even if all non-generic types are removed from it, so
1109 # reasons remain unclear for why this does not work
1111 @overload
1112 def min( # noqa: A001
1113 self,
1114 col: ColumnElement[_T],
1115 *args: _ColumnExpressionOrLiteralArgument[Any],
1116 **kwargs: Any,
1117 ) -> min[_T]: ...
1119 @overload
1120 def min( # noqa: A001
1121 self,
1122 col: _ColumnExpressionArgument[_T],
1123 *args: _ColumnExpressionOrLiteralArgument[Any],
1124 **kwargs: Any,
1125 ) -> min[_T]: ...
1127 @overload
1128 def min( # noqa: A001
1129 self,
1130 col: _ColumnExpressionOrLiteralArgument[_T],
1131 *args: _ColumnExpressionOrLiteralArgument[Any],
1132 **kwargs: Any,
1133 ) -> min[_T]: ...
1135 def min( # noqa: A001
1136 self,
1137 col: _ColumnExpressionOrLiteralArgument[_T],
1138 *args: _ColumnExpressionOrLiteralArgument[Any],
1139 **kwargs: Any,
1140 ) -> min[_T]: ...
1142 @property
1143 def mode(self) -> Type[mode[Any]]: ...
1145 @property
1146 def next_value(self) -> Type[next_value]: ...
1148 @property
1149 def now(self) -> Type[now]: ...
1151 @property
1152 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1154 @property
1155 def percent_rank(self) -> Type[percent_rank]: ...
1157 @property
1158 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1160 @property
1161 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1163 @property
1164 def random(self) -> Type[random]: ...
1166 @property
1167 def rank(self) -> Type[rank]: ...
1169 @property
1170 def rollup(self) -> Type[rollup[Any]]: ...
1172 @property
1173 def session_user(self) -> Type[session_user]: ...
1175 # set ColumnElement[_T] as a separate overload, to appease mypy
1176 # which seems to not want to accept _T from _ColumnExpressionArgument.
1177 # this is even if all non-generic types are removed from it, so
1178 # reasons remain unclear for why this does not work
1180 @overload
1181 def sum( # noqa: A001
1182 self,
1183 col: ColumnElement[_T],
1184 *args: _ColumnExpressionOrLiteralArgument[Any],
1185 **kwargs: Any,
1186 ) -> sum[_T]: ...
1188 @overload
1189 def sum( # noqa: A001
1190 self,
1191 col: _ColumnExpressionArgument[_T],
1192 *args: _ColumnExpressionOrLiteralArgument[Any],
1193 **kwargs: Any,
1194 ) -> sum[_T]: ...
1196 @overload
1197 def sum( # noqa: A001
1198 self,
1199 col: _ColumnExpressionOrLiteralArgument[_T],
1200 *args: _ColumnExpressionOrLiteralArgument[Any],
1201 **kwargs: Any,
1202 ) -> sum[_T]: ...
1204 def sum( # noqa: A001
1205 self,
1206 col: _ColumnExpressionOrLiteralArgument[_T],
1207 *args: _ColumnExpressionOrLiteralArgument[Any],
1208 **kwargs: Any,
1209 ) -> sum[_T]: ...
1211 @property
1212 def sysdate(self) -> Type[sysdate]: ...
1214 @property
1215 def user(self) -> Type[user]: ...
1217 # END GENERATED FUNCTION ACCESSORS
1220func = _FunctionGenerator()
1221func.__doc__ = _FunctionGenerator.__doc__
1223modifier = _FunctionGenerator(group=False)
1226class Function(FunctionElement[_T]):
1227 r"""Describe a named SQL function.
1229 The :class:`.Function` object is typically generated from the
1230 :data:`.func` generation object.
1233 :param \*clauses: list of column expressions that form the arguments
1234 of the SQL function call.
1236 :param type\_: optional :class:`.TypeEngine` datatype object that will be
1237 used as the return value of the column expression generated by this
1238 function call.
1240 :param packagenames: a string which indicates package prefix names
1241 to be prepended to the function name when the SQL is generated.
1242 The :data:`.func` generator creates these when it is called using
1243 dotted format, e.g.::
1245 func.mypackage.some_function(col1, col2)
1247 .. seealso::
1249 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
1251 :data:`.func` - namespace which produces registered or ad-hoc
1252 :class:`.Function` instances.
1254 :class:`.GenericFunction` - allows creation of registered function
1255 types.
1257 """
1259 __visit_name__ = "function"
1261 _traverse_internals = FunctionElement._traverse_internals + [
1262 ("packagenames", InternalTraversal.dp_plain_obj),
1263 ("name", InternalTraversal.dp_string),
1264 ("type", InternalTraversal.dp_type),
1265 ]
1267 name: str
1269 identifier: str
1271 type: TypeEngine[_T]
1272 """A :class:`_types.TypeEngine` object which refers to the SQL return
1273 type represented by this SQL function.
1275 This datatype may be configured when generating a
1276 :class:`_functions.Function` object by passing the
1277 :paramref:`_functions.Function.type_` parameter, e.g.::
1279 >>> select(func.lower("some VALUE", type_=String))
1281 The small number of built-in classes of :class:`_functions.Function` come
1282 with a built-in datatype that's appropriate to the class of function and
1283 its arguments. For functions that aren't known, the type defaults to the
1284 "null type".
1286 """
1288 @overload
1289 def __init__(
1290 self,
1291 name: str,
1292 *clauses: _ColumnExpressionOrLiteralArgument[_T],
1293 type_: None = ...,
1294 packagenames: Optional[Tuple[str, ...]] = ...,
1295 ): ...
1297 @overload
1298 def __init__(
1299 self,
1300 name: str,
1301 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1302 type_: _TypeEngineArgument[_T] = ...,
1303 packagenames: Optional[Tuple[str, ...]] = ...,
1304 ): ...
1306 def __init__(
1307 self,
1308 name: str,
1309 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1310 type_: Optional[_TypeEngineArgument[_T]] = None,
1311 packagenames: Optional[Tuple[str, ...]] = None,
1312 ):
1313 """Construct a :class:`.Function`.
1315 The :data:`.func` construct is normally used to construct
1316 new :class:`.Function` instances.
1318 """
1319 self.packagenames = packagenames or ()
1320 self.name = name
1322 # if type is None, we get NULLTYPE, which is our _T. But I don't
1323 # know how to get the overloads to express that correctly
1324 self.type = type_api.to_instance(type_) # type: ignore
1326 FunctionElement.__init__(self, *clauses)
1328 def _bind_param(
1329 self,
1330 operator: OperatorType,
1331 obj: Any,
1332 type_: Optional[TypeEngine[_T]] = None,
1333 expanding: bool = False,
1334 **kw: Any,
1335 ) -> BindParameter[_T]:
1336 return BindParameter(
1337 self.name,
1338 obj,
1339 _compared_to_operator=operator,
1340 _compared_to_type=self.type,
1341 type_=type_,
1342 unique=True,
1343 expanding=expanding,
1344 **kw,
1345 )
1348class GenericFunction(Function[_T]):
1349 """Define a 'generic' function.
1351 A generic function is a pre-established :class:`.Function`
1352 class that is instantiated automatically when called
1353 by name from the :data:`.func` attribute. Note that
1354 calling any name from :data:`.func` has the effect that
1355 a new :class:`.Function` instance is created automatically,
1356 given that name. The primary use case for defining
1357 a :class:`.GenericFunction` class is so that a function
1358 of a particular name may be given a fixed return type.
1359 It can also include custom argument parsing schemes as well
1360 as additional methods.
1362 Subclasses of :class:`.GenericFunction` are automatically
1363 registered under the name of the class. For
1364 example, a user-defined function ``as_utc()`` would
1365 be available immediately::
1367 from sqlalchemy.sql.functions import GenericFunction
1368 from sqlalchemy.types import DateTime
1370 class as_utc(GenericFunction):
1371 type = DateTime()
1372 inherit_cache = True
1374 print(select(func.as_utc()))
1376 User-defined generic functions can be organized into
1377 packages by specifying the "package" attribute when defining
1378 :class:`.GenericFunction`. Third party libraries
1379 containing many functions may want to use this in order
1380 to avoid name conflicts with other systems. For example,
1381 if our ``as_utc()`` function were part of a package
1382 "time"::
1384 class as_utc(GenericFunction):
1385 type = DateTime()
1386 package = "time"
1387 inherit_cache = True
1389 The above function would be available from :data:`.func`
1390 using the package name ``time``::
1392 print(select(func.time.as_utc()))
1394 A final option is to allow the function to be accessed
1395 from one name in :data:`.func` but to render as a different name.
1396 The ``identifier`` attribute will override the name used to
1397 access the function as loaded from :data:`.func`, but will retain
1398 the usage of ``name`` as the rendered name::
1400 class GeoBuffer(GenericFunction):
1401 type = Geometry()
1402 package = "geo"
1403 name = "ST_Buffer"
1404 identifier = "buffer"
1405 inherit_cache = True
1407 The above function will render as follows:
1409 .. sourcecode:: pycon+sql
1411 >>> print(func.geo.buffer())
1412 {printsql}ST_Buffer()
1414 The name will be rendered as is, however without quoting unless the name
1415 contains special characters that require quoting. To force quoting
1416 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1417 construct::
1419 from sqlalchemy.sql import quoted_name
1421 class GeoBuffer(GenericFunction):
1422 type = Geometry()
1423 package = "geo"
1424 name = quoted_name("ST_Buffer", True)
1425 identifier = "buffer"
1426 inherit_cache = True
1428 The above function will render as:
1430 .. sourcecode:: pycon+sql
1432 >>> print(func.geo.buffer())
1433 {printsql}"ST_Buffer"()
1435 Type parameters for this class as a
1436 `generic type <https://peps.python.org/pep-0484/#generics>`_ can be passed
1437 and should match the type seen in a :class:`_engine.Result`. For example::
1439 class as_utc(GenericFunction[datetime.datetime]):
1440 type = DateTime()
1441 inherit_cache = True
1443 The above indicates that the following expression returns a ``datetime``
1444 object::
1446 connection.scalar(select(func.as_utc()))
1448 .. versionadded:: 1.3.13 The :class:`.quoted_name` construct is now
1449 recognized for quoting when used with the "name" attribute of the
1450 object, so that quoting can be forced on or off for the function
1451 name.
1454 """
1456 coerce_arguments = True
1457 inherit_cache = True
1459 _register: bool
1461 name = "GenericFunction"
1463 def __init_subclass__(cls) -> None:
1464 if annotation.Annotated not in cls.__mro__:
1465 cls._register_generic_function(cls.__name__, cls.__dict__)
1466 super().__init_subclass__()
1468 @classmethod
1469 def _register_generic_function(
1470 cls, clsname: str, clsdict: Mapping[str, Any]
1471 ) -> None:
1472 cls.name = name = clsdict.get("name", clsname)
1473 cls.identifier = identifier = clsdict.get("identifier", name)
1474 package = clsdict.get("package", "_default")
1475 # legacy
1476 if "__return_type__" in clsdict:
1477 cls.type = clsdict["__return_type__"]
1479 # Check _register attribute status
1480 cls._register = getattr(cls, "_register", True)
1482 # Register the function if required
1483 if cls._register:
1484 register_function(identifier, cls, package)
1485 else:
1486 # Set _register to True to register child classes by default
1487 cls._register = True
1489 def __init__(
1490 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1491 ):
1492 parsed_args = kwargs.pop("_parsed_args", None)
1493 if parsed_args is None:
1494 parsed_args = [
1495 coercions.expect(
1496 roles.ExpressionElementRole,
1497 c,
1498 name=self.name,
1499 apply_propagate_attrs=self,
1500 )
1501 for c in args
1502 ]
1503 self._has_args = self._has_args or bool(parsed_args)
1504 self.packagenames = ()
1506 self.clause_expr = Grouping(
1507 ClauseList(
1508 operator=operators.comma_op, group_contents=True, *parsed_args
1509 )
1510 )
1512 self.type = type_api.to_instance( # type: ignore
1513 kwargs.pop("type_", None) or getattr(self, "type", None)
1514 )
1517register_function("cast", Cast) # type: ignore
1518register_function("extract", Extract) # type: ignore
1521class next_value(GenericFunction[int]):
1522 """Represent the 'next value', given a :class:`.Sequence`
1523 as its single argument.
1525 Compiles into the appropriate function on each backend,
1526 or will raise NotImplementedError if used on a backend
1527 that does not provide support for sequences.
1529 """
1531 type = sqltypes.Integer()
1532 name = "next_value"
1534 _traverse_internals = [
1535 ("sequence", InternalTraversal.dp_named_ddl_element)
1536 ]
1538 def __init__(self, seq: schema.Sequence, **kw: Any):
1539 assert isinstance(
1540 seq, schema.Sequence
1541 ), "next_value() accepts a Sequence object as input."
1542 self.sequence = seq
1543 self.type = sqltypes.to_instance( # type: ignore
1544 seq.data_type or getattr(self, "type", None)
1545 )
1547 def compare(self, other: Any, **kw: Any) -> bool:
1548 return (
1549 isinstance(other, next_value)
1550 and self.sequence.name == other.sequence.name
1551 )
1553 @property
1554 def _from_objects(self) -> Any:
1555 return []
1558class AnsiFunction(GenericFunction[_T]):
1559 """Define a function in "ansi" format, which doesn't render parenthesis."""
1561 inherit_cache = True
1563 def __init__(self, *args: _ColumnExpressionArgument[Any], **kwargs: Any):
1564 GenericFunction.__init__(self, *args, **kwargs)
1567class ReturnTypeFromArgs(GenericFunction[_T]):
1568 """Define a function whose return type is the same as its arguments."""
1570 inherit_cache = True
1572 # set ColumnElement[_T] as a separate overload, to appease mypy which seems
1573 # to not want to accept _T from _ColumnExpressionArgument. this is even if
1574 # all non-generic types are removed from it, so reasons remain unclear for
1575 # why this does not work
1577 @overload
1578 def __init__(
1579 self,
1580 col: ColumnElement[_T],
1581 *args: _ColumnExpressionOrLiteralArgument[Any],
1582 **kwargs: Any,
1583 ): ...
1585 @overload
1586 def __init__(
1587 self,
1588 col: _ColumnExpressionArgument[_T],
1589 *args: _ColumnExpressionOrLiteralArgument[Any],
1590 **kwargs: Any,
1591 ): ...
1593 @overload
1594 def __init__(
1595 self,
1596 col: _ColumnExpressionOrLiteralArgument[_T],
1597 *args: _ColumnExpressionOrLiteralArgument[Any],
1598 **kwargs: Any,
1599 ): ...
1601 def __init__(
1602 self, *args: _ColumnExpressionOrLiteralArgument[Any], **kwargs: Any
1603 ):
1604 fn_args: Sequence[ColumnElement[Any]] = [
1605 coercions.expect(
1606 roles.ExpressionElementRole,
1607 c,
1608 name=self.name,
1609 apply_propagate_attrs=self,
1610 )
1611 for c in args
1612 ]
1613 kwargs.setdefault("type_", _type_from_args(fn_args))
1614 kwargs["_parsed_args"] = fn_args
1615 super().__init__(*fn_args, **kwargs)
1618class coalesce(ReturnTypeFromArgs[_T]):
1619 _has_args = True
1620 inherit_cache = True
1623class max(ReturnTypeFromArgs[_T]): # noqa: A001
1624 """The SQL MAX() aggregate function."""
1626 inherit_cache = True
1629class min(ReturnTypeFromArgs[_T]): # noqa: A001
1630 """The SQL MIN() aggregate function."""
1632 inherit_cache = True
1635class sum(ReturnTypeFromArgs[_T]): # noqa: A001
1636 """The SQL SUM() aggregate function."""
1638 inherit_cache = True
1641class now(GenericFunction[datetime.datetime]):
1642 """The SQL now() datetime function.
1644 SQLAlchemy dialects will usually render this particular function
1645 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1647 """
1649 type = sqltypes.DateTime()
1650 inherit_cache = True
1653class concat(GenericFunction[str]):
1654 """The SQL CONCAT() function, which concatenates strings.
1656 E.g.:
1658 .. sourcecode:: pycon+sql
1660 >>> print(select(func.concat('a', 'b')))
1661 {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1
1663 String concatenation in SQLAlchemy is more commonly available using the
1664 Python ``+`` operator with string datatypes, which will render a
1665 backend-specific concatenation operator, such as :
1667 .. sourcecode:: pycon+sql
1669 >>> print(select(literal("a") + "b"))
1670 {printsql}SELECT :param_1 || :param_2 AS anon_1
1673 """
1675 type = sqltypes.String()
1676 inherit_cache = True
1679class char_length(GenericFunction[int]):
1680 """The CHAR_LENGTH() SQL function."""
1682 type = sqltypes.Integer()
1683 inherit_cache = True
1685 def __init__(self, arg: _ColumnExpressionArgument[str], **kw: Any):
1686 # slight hack to limit to just one positional argument
1687 # not sure why this one function has this special treatment
1688 super().__init__(arg, **kw)
1691class random(GenericFunction[float]):
1692 """The RANDOM() SQL function."""
1694 _has_args = True
1695 inherit_cache = True
1698class count(GenericFunction[int]):
1699 r"""The ANSI COUNT aggregate function. With no arguments,
1700 emits COUNT \*.
1702 E.g.::
1704 from sqlalchemy import func
1705 from sqlalchemy import select
1706 from sqlalchemy import table, column
1708 my_table = table('some_table', column('id'))
1710 stmt = select(func.count()).select_from(my_table)
1712 Executing ``stmt`` would emit::
1714 SELECT count(*) AS count_1
1715 FROM some_table
1718 """
1720 type = sqltypes.Integer()
1721 inherit_cache = True
1723 def __init__(
1724 self,
1725 expression: Union[
1726 _ColumnExpressionArgument[Any], _StarOrOne, None
1727 ] = None,
1728 **kwargs: Any,
1729 ):
1730 if expression is None:
1731 expression = literal_column("*")
1732 super().__init__(expression, **kwargs)
1735class current_date(AnsiFunction[datetime.date]):
1736 """The CURRENT_DATE() SQL function."""
1738 type = sqltypes.Date()
1739 inherit_cache = True
1742class current_time(AnsiFunction[datetime.time]):
1743 """The CURRENT_TIME() SQL function."""
1745 type = sqltypes.Time()
1746 inherit_cache = True
1749class current_timestamp(AnsiFunction[datetime.datetime]):
1750 """The CURRENT_TIMESTAMP() SQL function."""
1752 type = sqltypes.DateTime()
1753 inherit_cache = True
1756class current_user(AnsiFunction[str]):
1757 """The CURRENT_USER() SQL function."""
1759 type = sqltypes.String()
1760 inherit_cache = True
1763class localtime(AnsiFunction[datetime.datetime]):
1764 """The localtime() SQL function."""
1766 type = sqltypes.DateTime()
1767 inherit_cache = True
1770class localtimestamp(AnsiFunction[datetime.datetime]):
1771 """The localtimestamp() SQL function."""
1773 type = sqltypes.DateTime()
1774 inherit_cache = True
1777class session_user(AnsiFunction[str]):
1778 """The SESSION_USER() SQL function."""
1780 type = sqltypes.String()
1781 inherit_cache = True
1784class sysdate(AnsiFunction[datetime.datetime]):
1785 """The SYSDATE() SQL function."""
1787 type = sqltypes.DateTime()
1788 inherit_cache = True
1791class user(AnsiFunction[str]):
1792 """The USER() SQL function."""
1794 type = sqltypes.String()
1795 inherit_cache = True
1798class array_agg(GenericFunction[_T]):
1799 """Support for the ARRAY_AGG function.
1801 The ``func.array_agg(expr)`` construct returns an expression of
1802 type :class:`_types.ARRAY`.
1804 e.g.::
1806 stmt = select(func.array_agg(table.c.values)[2:5])
1808 .. seealso::
1810 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1811 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1812 added.
1814 """
1816 inherit_cache = True
1818 def __init__(self, *args: _ColumnExpressionArgument[Any], **kwargs: Any):
1819 fn_args: Sequence[ColumnElement[Any]] = [
1820 coercions.expect(
1821 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1822 )
1823 for c in args
1824 ]
1826 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1827 if "type_" not in kwargs:
1828 type_from_args = _type_from_args(fn_args)
1829 if isinstance(type_from_args, sqltypes.ARRAY):
1830 kwargs["type_"] = type_from_args
1831 else:
1832 kwargs["type_"] = default_array_type(
1833 type_from_args, dimensions=1
1834 )
1835 kwargs["_parsed_args"] = fn_args
1836 super().__init__(*fn_args, **kwargs)
1839class OrderedSetAgg(GenericFunction[_T]):
1840 """Define a function where the return type is based on the sort
1841 expression type as defined by the expression passed to the
1842 :meth:`.FunctionElement.within_group` method."""
1844 array_for_multi_clause = False
1845 inherit_cache = True
1847 def within_group_type(
1848 self, within_group: WithinGroup[Any]
1849 ) -> TypeEngine[Any]:
1850 func_clauses = cast(ClauseList, self.clause_expr.element)
1851 order_by: Sequence[ColumnElement[Any]] = sqlutil.unwrap_order_by(
1852 within_group.order_by
1853 )
1854 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1855 return sqltypes.ARRAY(order_by[0].type)
1856 else:
1857 return order_by[0].type
1860class mode(OrderedSetAgg[_T]):
1861 """Implement the ``mode`` ordered-set aggregate function.
1863 This function must be used with the :meth:`.FunctionElement.within_group`
1864 modifier to supply a sort expression to operate upon.
1866 The return type of this function is the same as the sort expression.
1868 """
1870 inherit_cache = True
1873class percentile_cont(OrderedSetAgg[_T]):
1874 """Implement the ``percentile_cont`` ordered-set aggregate function.
1876 This function must be used with the :meth:`.FunctionElement.within_group`
1877 modifier to supply a sort expression to operate upon.
1879 The return type of this function is the same as the sort expression,
1880 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1881 expression's type.
1883 """
1885 array_for_multi_clause = True
1886 inherit_cache = True
1889class percentile_disc(OrderedSetAgg[_T]):
1890 """Implement the ``percentile_disc`` ordered-set aggregate function.
1892 This function must be used with the :meth:`.FunctionElement.within_group`
1893 modifier to supply a sort expression to operate upon.
1895 The return type of this function is the same as the sort expression,
1896 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1897 expression's type.
1899 """
1901 array_for_multi_clause = True
1902 inherit_cache = True
1905class rank(GenericFunction[int]):
1906 """Implement the ``rank`` hypothetical-set aggregate function.
1908 This function must be used with the :meth:`.FunctionElement.within_group`
1909 modifier to supply a sort expression to operate upon.
1911 The return type of this function is :class:`.Integer`.
1913 """
1915 type = sqltypes.Integer()
1916 inherit_cache = True
1919class dense_rank(GenericFunction[int]):
1920 """Implement the ``dense_rank`` hypothetical-set aggregate function.
1922 This function must be used with the :meth:`.FunctionElement.within_group`
1923 modifier to supply a sort expression to operate upon.
1925 The return type of this function is :class:`.Integer`.
1927 """
1929 type = sqltypes.Integer()
1930 inherit_cache = True
1933class percent_rank(GenericFunction[decimal.Decimal]):
1934 """Implement the ``percent_rank`` hypothetical-set aggregate function.
1936 This function must be used with the :meth:`.FunctionElement.within_group`
1937 modifier to supply a sort expression to operate upon.
1939 The return type of this function is :class:`.Numeric`.
1941 """
1943 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
1944 inherit_cache = True
1947class cume_dist(GenericFunction[decimal.Decimal]):
1948 """Implement the ``cume_dist`` hypothetical-set aggregate function.
1950 This function must be used with the :meth:`.FunctionElement.within_group`
1951 modifier to supply a sort expression to operate upon.
1953 The return type of this function is :class:`.Numeric`.
1955 """
1957 type: sqltypes.Numeric[decimal.Decimal] = sqltypes.Numeric()
1958 inherit_cache = True
1961class cube(GenericFunction[_T]):
1962 r"""Implement the ``CUBE`` grouping operation.
1964 This function is used as part of the GROUP BY of a statement,
1965 e.g. :meth:`_expression.Select.group_by`::
1967 stmt = select(
1968 func.sum(table.c.value), table.c.col_1, table.c.col_2
1969 ).group_by(func.cube(table.c.col_1, table.c.col_2))
1971 .. versionadded:: 1.2
1973 """
1975 _has_args = True
1976 inherit_cache = True
1979class rollup(GenericFunction[_T]):
1980 r"""Implement the ``ROLLUP`` grouping operation.
1982 This function is used as part of the GROUP BY of a statement,
1983 e.g. :meth:`_expression.Select.group_by`::
1985 stmt = select(
1986 func.sum(table.c.value), table.c.col_1, table.c.col_2
1987 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
1989 .. versionadded:: 1.2
1991 """
1993 _has_args = True
1994 inherit_cache = True
1997class grouping_sets(GenericFunction[_T]):
1998 r"""Implement the ``GROUPING SETS`` grouping operation.
2000 This function is used as part of the GROUP BY of a statement,
2001 e.g. :meth:`_expression.Select.group_by`::
2003 stmt = select(
2004 func.sum(table.c.value), table.c.col_1, table.c.col_2
2005 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
2007 In order to group by multiple sets, use the :func:`.tuple_` construct::
2009 from sqlalchemy import tuple_
2011 stmt = select(
2012 func.sum(table.c.value),
2013 table.c.col_1, table.c.col_2,
2014 table.c.col_3
2015 ).group_by(
2016 func.grouping_sets(
2017 tuple_(table.c.col_1, table.c.col_2),
2018 tuple_(table.c.value, table.c.col_3),
2019 )
2020 )
2023 .. versionadded:: 1.2
2025 """
2027 _has_args = True
2028 inherit_cache = True
2031class aggregate_strings(GenericFunction[str]):
2032 """Implement a generic string aggregation function.
2034 This function will concatenate non-null values into a string and
2035 separate the values by a delimiter.
2037 This function is compiled on a per-backend basis, into functions
2038 such as ``group_concat()``, ``string_agg()``, or ``LISTAGG()``.
2040 e.g. Example usage with delimiter '.'::
2042 stmt = select(func.aggregate_strings(table.c.str_col, "."))
2044 The return type of this function is :class:`.String`.
2046 .. versionadded: 2.0.21
2048 """
2050 type = sqltypes.String()
2051 _has_args = True
2052 inherit_cache = True
2054 def __init__(self, clause: _ColumnExpressionArgument[Any], separator: str):
2055 super().__init__(clause, separator)