codekingpro/portable-devtools
115k
1# sql/functions.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7
8"""SQL function API, factories, and built-in functions."""
9
10from __future__ import annotations
11
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
27
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 BinaryExpression
44from .elements import BindParameter
45from .elements import Cast
46from .elements import ClauseList
47from .elements import ColumnElement
48from .elements import Extract
49from .elements import FunctionFilter
50from .elements import Grouping
51from .elements import literal_column
52from .elements import NamedColumn
53from .elements import Over
54from .elements import WithinGroup
55from .selectable import FromClause
56from .selectable import Select
57from .selectable import TableValuedAlias
58from .sqltypes import TableValueType
59from .type_api import TypeEngine
60from .visitors import InternalTraversal
61from .. import util
62from ..util.typing import TypeAlias
63
64if TYPE_CHECKING:
65 from ._typing import _ByArgument
66 from ._typing import _ColumnExpressionArgument
67 from ._typing import _ColumnExpressionOrLiteralArgument
68 from ._typing import _ColumnExpressionOrStrLabelArgument
69 from ._typing import _StarOrOne
70 from ._typing import _TypeEngineArgument
71 from .base import _EntityNamespace
72 from .elements import ClauseElement
73 from .elements import KeyedColumnElement
74 from .elements import TableValuedColumn
75 from .operators import OperatorType
76 from ..engine.base import Connection
77 from ..engine.cursor import CursorResult
78 from ..engine.interfaces import _CoreMultiExecuteParams
79 from ..engine.interfaces import CoreExecuteOptionsParameter
80 from ..util.typing import Self
81
82_T = TypeVar("_T", bound=Any)
83_S = TypeVar("_S", bound=Any)
84
85_registry: util.defaultdict[str, Dict[str, Type[Function[Any]]]] = (
86 util.defaultdict(dict)
87)
88
89
90def register_function(
91 identifier: str, fn: Type[Function[Any]], package: str = "_default"
92) -> None:
93 """Associate a callable with a particular func. name.
94
95 This is normally called by GenericFunction, but is also
96 available by itself so that a non-Function construct
97 can be associated with the :data:`.func` accessor (i.e.
98 CAST, EXTRACT).
99
100 """
101 reg = _registry[package]
102
103 identifier = str(identifier).lower()
104
105 # Check if a function with the same identifier is registered.
106 if identifier in reg:
107 util.warn(
108 "The GenericFunction '{}' is already registered and "
109 "is going to be overridden.".format(identifier)
110 )
111 reg[identifier] = fn
112
113
114class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
115 """Base for SQL function-oriented constructs.
116
117 This is a `generic type <https://peps.python.org/pep-0484/#generics>`_,
118 meaning that type checkers and IDEs can be instructed on the types to
119 expect in a :class:`_engine.Result` for this function. See
120 :class:`.GenericFunction` for an example of how this is done.
121
122 .. seealso::
123
124 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
125
126 :class:`.Function` - named SQL function.
127
128 :data:`.func` - namespace which produces registered or ad-hoc
129 :class:`.Function` instances.
130
131 :class:`.GenericFunction` - allows creation of registered function
132 types.
133
134 """
135
136 _traverse_internals = [
137 ("clause_expr", InternalTraversal.dp_clauseelement),
138 ("_with_ordinality", InternalTraversal.dp_boolean),
139 ("_table_value_type", InternalTraversal.dp_has_cache_key),
140 ] + Executable._executable_traverse_internals
141
142 packagenames: Tuple[str, ...] = ()
143
144 _has_args = False
145 _with_ordinality = False
146 _table_value_type: Optional[TableValueType] = None
147
148 # some attributes that are defined between both ColumnElement and
149 # FromClause are set to Any here to avoid typing errors
150 primary_key: Any
151 _is_clone_of: Any
152
153 clause_expr: Grouping[Any]
154
155 def __init__(
156 self, *clauses: _ColumnExpressionOrLiteralArgument[Any]
157 ) -> None:
158 r"""Construct a :class:`.FunctionElement`.
159
160 :param \*clauses: list of column expressions that form the arguments
161 of the SQL function call.
162
163 :param \**kwargs: additional kwargs are typically consumed by
164 subclasses.
165
166 .. seealso::
167
168 :data:`.func`
169
170 :class:`.Function`
171
172 """
173 args: Sequence[_ColumnExpressionArgument[Any]] = [
174 coercions.expect(
175 roles.ExpressionElementRole,
176 c,
177 name=getattr(self, "name", None),
178 apply_propagate_attrs=self,
179 )
180 for c in clauses
181 ]
182 self._has_args = self._has_args or bool(args)
183 self.clause_expr = Grouping(
184 ClauseList(operator=operators.comma_op, group_contents=True, *args)
185 )
186
187 _non_anon_label = None
188
189 @property
190 def _proxy_key(self) -> Any:
191 return super()._proxy_key or getattr(self, "name", None)
192
193 def _execute_on_connection(
194 self,
195 connection: Connection,
196 distilled_params: _CoreMultiExecuteParams,
197 execution_options: CoreExecuteOptionsParameter,
198 ) -> CursorResult[Any]:
199 return connection._execute_function(
200 self, distilled_params, execution_options
201 )
202
203 def scalar_table_valued(
204 self, name: str, type_: Optional[_TypeEngineArgument[_T]] = None
205 ) -> ScalarFunctionColumn[_T]:
206 """Return a column expression that's against this
207 :class:`_functions.FunctionElement` as a scalar
208 table-valued expression.
209
210 The returned expression is similar to that returned by a single column
211 accessed off of a :meth:`_functions.FunctionElement.table_valued`
212 construct, except no FROM clause is generated; the function is rendered
213 in the similar way as a scalar subquery.
214
215 E.g.:
216
217 .. sourcecode:: pycon+sql
218
219 >>> from sqlalchemy import func, select
220 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
221 >>> print(select(fn))
222 {printsql}SELECT (jsonb_each(:jsonb_each_1)).key
223
224 .. versionadded:: 1.4.0b2
225
226 .. seealso::
227
228 :meth:`_functions.FunctionElement.table_valued`
229
230 :meth:`_functions.FunctionElement.alias`
231
232 :meth:`_functions.FunctionElement.column_valued`
233
234 """ # noqa: E501
235
236 return ScalarFunctionColumn(self, name, type_)
237
238 def table_valued(
239 self, *expr: _ColumnExpressionOrStrLabelArgument[Any], **kw: Any
240 ) -> TableValuedAlias:
241 r"""Return a :class:`_sql.TableValuedAlias` representation of this
242 :class:`_functions.FunctionElement` with table-valued expressions added.
243
244 e.g. to use the SQLite form of ``generate_series()`` (including
245 hidden columns "start", "stop", "step"):
246
247 .. sourcecode:: pycon+sql
248
249 >>> fn = func.generate_series(1, 5).table_valued(
250 ... "value", "start", "stop", "step"
251 ... )
252
253 >>> print(select(fn))
254 {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
255 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1{stop}
256
257 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
258 {printsql}SELECT anon_1.value, anon_1.stop
259 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
260 WHERE anon_1.value > :value_1{stop}
261
262 Backends like PostgreSQL need the accessed columns to be explicitly
263 named in "AS" clause. To achieve this, use
264 :meth:`_sql.TableValuedAlias.render_derived`; be sure to consult the
265 :ref:`PostgreSQL-specific documentation for table valued functions
266 <postgresql_table_valued>` for additional examples:
267
268 .. sourcecode:: pycon+sql
269
270 >>> fn = func.generate_series(1, 5).table_valued("value").render_derived()
271
272 >>> print(select(fn))
273 {printsql}SELECT anon_1.value FROM
274 generate_series(:generate_series_1, :generate_series_2) AS anon_1(value){stop}
275
276 A WITH ORDINALITY expression may be generated by passing the keyword
277 argument :paramref:`.FunctionElement.table_valued.with_ordinality`,
278 illustrated below using PostgreSQL's syntax:
279
280 .. sourcecode:: pycon+sql
281
282 >>> fn = func.generate_series(4, 1, -1).table_valued(
283 ... "gen", with_ordinality="ordinality"
284 ... )
285 >>> print(select(fn.render_derived()))
286 {printsql}SELECT anon_1.gen, anon_1.ordinality
287 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3)
288 WITH ORDINALITY AS anon_1(gen, ordinality)
289
290 :param \*expr: A series of string column names that will be added to the
291 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
292 construct as columns. :func:`_sql.column` objects with or without
293 datatypes may also be used.
294
295 :param name: optional name to assign to the alias name that's generated.
296 If omitted, a unique anonymizing name is used.
297
298 :param with_ordinality: string name that when present results in the
299 ``WITH ORDINALITY`` clause being added to the alias, and the given
300 string name will be added as a column to the .c collection
301 of the resulting :class:`_sql.TableValuedAlias`.
302
303 :param joins_implicitly: when True, the table valued function may be
304 used in the FROM clause without any explicit JOIN to other tables
305 in the SQL query, and no "cartesian product" warning will be generated.
306 May be useful for SQL functions such as ``func.json_each()``.
307
308 .. versionadded:: 1.4.33
309
310 .. versionadded:: 1.4.0b2
311
312
313 .. seealso::
314
315 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
316
317 :ref:`Table-Valued Functions on PostgreSQL <postgresql_table_valued>` - in the :ref:`postgresql_toplevel` documentation
318
319 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
320 :meth:`_functions.FunctionElement.table_valued` which delivers the
321 complete table valued expression as a scalar column expression
322
323 :meth:`_functions.FunctionElement.column_valued`
324
325 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
326 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
327
328 """ # noqa: 501
329
330 new_func = self._generate()
331
332 with_ordinality = kw.pop("with_ordinality", None)
333 joins_implicitly = kw.pop("joins_implicitly", None)
334 name = kw.pop("name", None)
335
336 if with_ordinality:
337 expr += (with_ordinality,)
338 new_func._with_ordinality = True
339
340 new_func.type = new_func._table_value_type = TableValueType(*expr)
341
342 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
343
344 def column_valued(
345 self, name: Optional[str] = None, joins_implicitly: bool = False
346 ) -> TableValuedColumn[_T]:
347 """Return this :class:`_functions.FunctionElement` as a column expression that
348 selects from itself as a FROM clause.
349
350 E.g.:
351
352 .. sourcecode:: pycon+sql
353
354 >>> from sqlalchemy import select, func
355 >>> gs = func.generate_series(1, 5, -1).column_valued()
356 >>> print(select(gs))
357 {printsql}SELECT anon_1
358 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
359
360 This is shorthand for::
361
362 gs = func.generate_series(1, 5, -1).alias().column
363
364 :param name: optional name to assign to the alias name that's generated.
365 If omitted, a unique anonymizing name is used.
366
367 :param joins_implicitly: when True, the "table" portion of the column
368 valued function may be a member of the FROM clause without any
369 explicit JOIN to other tables in the SQL query, and no "cartesian
370 product" warning will be generated. May be useful for SQL functions
371 such as ``func.json_array_elements()``.
372
373 .. versionadded:: 1.4.46
374
375 .. seealso::
376
377 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
378
379 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
380
381 :meth:`_functions.FunctionElement.table_valued`
382
383 """ # noqa: 501
384
385 return self.alias(name=name, joins_implicitly=joins_implicitly).column
386
387 @util.ro_non_memoized_property
388 def columns(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
389 r"""The set of columns exported by this :class:`.FunctionElement`.
390
391 This is a placeholder collection that allows the function to be
392 placed in the FROM clause of a statement:
393
394 .. sourcecode:: pycon+sql
395
396 >>> from sqlalchemy import column, select, func
397 >>> stmt = select(column("x"), column("y")).select_from(func.myfunction())
398 >>> print(stmt)
399 {printsql}SELECT x, y FROM myfunction()
400
401 The above form is a legacy feature that is now superseded by the
402 fully capable :meth:`_functions.FunctionElement.table_valued`
403 method; see that method for details.
404
405 .. seealso::
406
407 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
408 SQL function expressions.
409
410 """ # noqa: E501
411 return self.c
412
413 @util.ro_memoized_property
414 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
415 """synonym for :attr:`.FunctionElement.columns`."""
416
417 return ColumnCollection(
418 columns=[(col.key, col) for col in self._all_selected_columns]
419 )
420
421 @property
422 def _all_selected_columns(self) -> Sequence[KeyedColumnElement[Any]]:
423 if is_table_value_type(self.type):
424 # TODO: this might not be fully accurate
425 cols = cast(
426 "Sequence[KeyedColumnElement[Any]]", self.type._elements
427 )
428 else:
429 cols = [self.label(None)]
430
431 return cols
432
433 @property
434 def exported_columns( # type: ignore[override]
435 self,
436 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
437 return self.columns
438
439 @HasMemoized.memoized_attribute
440 def clauses(self) -> ClauseList:
441 """Return the underlying :class:`.ClauseList` which contains
442 the arguments for this :class:`.FunctionElement`.
443
444 """
445 return cast(ClauseList, self.clause_expr.element)
446
447 def over(
448 self,
449 *,
450 partition_by: Optional[_ByArgument] = None,
451 order_by: Optional[_ByArgument] = None,
452 rows: Optional[Tuple[Optional[int], Optional[int]]] = None,
453 range_: Optional[Tuple[Optional[int], Optional[int]]] = None,
454 groups: Optional[Tuple[Optional[int], Optional[int]]] = None,
455 ) -> Over[_T]:
456 """Produce an OVER clause against this function.
457
458 Used against aggregate or so-called "window" functions,
459 for database backends that support window functions.
460
461 The expression::
462
463 func.row_number().over(order_by="x")
464
465 is shorthand for::
466
467 from sqlalchemy import over
468
469 over(func.row_number(), order_by="x")
470
471 See :func:`_expression.over` for a full description.
472
473 .. seealso::
474
475 :func:`_expression.over`
476
477 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
478
479 """
480 return Over(
481 self,
482 partition_by=partition_by,
483 order_by=order_by,
484 rows=rows,
485 range_=range_,
486 groups=groups,
487 )
488
489 def within_group(
490 self, *order_by: _ColumnExpressionArgument[Any]
491 ) -> WithinGroup[_T]:
492 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
493
494 Used against so-called "ordered set aggregate" and "hypothetical
495 set aggregate" functions, including :class:`.percentile_cont`,
496 :class:`.rank`, :class:`.dense_rank`, etc.
497
498 See :func:`_expression.within_group` for a full description.
499
500 .. seealso::
501
502 :ref:`tutorial_functions_within_group` -
503 in the :ref:`unified_tutorial`
504
505
506 """
507 return WithinGroup(self, *order_by)
508
509 @overload
510 def filter(self) -> Self: ...
511
512 @overload
513 def filter(
514 self,
515 __criterion0: _ColumnExpressionArgument[bool],
516 *criterion: _ColumnExpressionArgument[bool],
517 ) -> FunctionFilter[_T]: ...
518
519 def filter(
520 self, *criterion: _ColumnExpressionArgument[bool]
521 ) -> Union[Self, FunctionFilter[_T]]:
522 """Produce a FILTER clause against this function.
523
524 Used against aggregate and window functions,
525 for database backends that support the "FILTER" clause.
526
527 The expression::
528
529 func.count(1).filter(True)
530
531 is shorthand for::
532
533 from sqlalchemy import funcfilter
534
535 funcfilter(func.count(1), True)
536
537 .. seealso::
538
539 :ref:`tutorial_functions_within_group` -
540 in the :ref:`unified_tutorial`
541
542 :class:`.FunctionFilter`
543
544 :func:`.funcfilter`
545
546
547 """
548 if not criterion:
549 return self
550 return FunctionFilter(self, *criterion)
551
552 def as_comparison(
553 self, left_index: int, right_index: int
554 ) -> FunctionAsBinary:
555 """Interpret this expression as a boolean comparison between two
556 values.
557
558 This method is used for an ORM use case described at
559 :ref:`relationship_custom_operator_sql_function`.
560
561 A hypothetical SQL function "is_equal()" which compares to values
562 for equality would be written in the Core expression language as::
563
564 expr = func.is_equal("a", "b")
565
566 If "is_equal()" above is comparing "a" and "b" for equality, the
567 :meth:`.FunctionElement.as_comparison` method would be invoked as::
568
569 expr = func.is_equal("a", "b").as_comparison(1, 2)
570
571 Where above, the integer value "1" refers to the first argument of the
572 "is_equal()" function and the integer value "2" refers to the second.
573
574 This would create a :class:`.BinaryExpression` that is equivalent to::
575
576 BinaryExpression("a", "b", operator=op.eq)
577
578 However, at the SQL level it would still render as
579 "is_equal('a', 'b')".
580
581 The ORM, when it loads a related object or collection, needs to be able
582 to manipulate the "left" and "right" sides of the ON clause of a JOIN
583 expression. The purpose of this method is to provide a SQL function
584 construct that can also supply this information to the ORM, when used
585 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
586 return value is a containment object called :class:`.FunctionAsBinary`.
587
588 An ORM example is as follows::
589
590 class Venue(Base):
591 __tablename__ = "venue"
592 id = Column(Integer, primary_key=True)
593 name = Column(String)
594
595 descendants = relationship(
596 "Venue",
597 primaryjoin=func.instr(
598 remote(foreign(name)), name + "/"
599 ).as_comparison(1, 2)
600 == 1,
601 viewonly=True,
602 order_by=name,
603 )
604
605 Above, the "Venue" class can load descendant "Venue" objects by
606 determining if the name of the parent Venue is contained within the
607 start of the hypothetical descendant value's name, e.g. "parent1" would
608 match up to "parent1/child1", but not to "parent2/child1".
609
610 Possible use cases include the "materialized path" example given above,
611 as well as making use of special SQL functions such as geometric
612 functions to create join conditions.
613
614 :param left_index: the integer 1-based index of the function argument
615 that serves as the "left" side of the expression.
616 :param right_index: the integer 1-based index of the function argument
617 that serves as the "right" side of the expression.
618
619 .. versionadded:: 1.3
620
621 .. seealso::
622
623 :ref:`relationship_custom_operator_sql_function` -
624 example use within the ORM
625
626 """
627 return FunctionAsBinary(self, left_index, right_index)
628
629 @property
630 def _from_objects(self) -> Any:
631 return self.clauses._from_objects
632
633 def within_group_type(
634 self, within_group: WithinGroup[_S]
635 ) -> Optional[TypeEngine[_S]]:
636 """For types that define their return type as based on the criteria
637 within a WITHIN GROUP (ORDER BY) expression, called by the
638 :class:`.WithinGroup` construct.
639
640 Returns None by default, in which case the function's normal ``.type``
641 is used.
642
643 """
644
645 return None
646
647 def alias(
648 self, name: Optional[str] = None, joins_implicitly: bool = False
649 ) -> TableValuedAlias:
650 r"""Produce a :class:`_expression.Alias` construct against this
651 :class:`.FunctionElement`.
652
653 .. tip::
654
655 The :meth:`_functions.FunctionElement.alias` method is part of the
656 mechanism by which "table valued" SQL functions are created.
657 However, most use cases are covered by higher level methods on
658 :class:`_functions.FunctionElement` including
659 :meth:`_functions.FunctionElement.table_valued`, and
660 :meth:`_functions.FunctionElement.column_valued`.
661
662 This construct wraps the function in a named alias which
663 is suitable for the FROM clause, in the style accepted for example
664 by PostgreSQL. A column expression is also provided using the
665 special ``.column`` attribute, which may
666 be used to refer to the output of the function as a scalar value
667 in the columns or where clause, for a backend such as PostgreSQL.
668
669 For a full table-valued expression, use the
670 :meth:`_functions.FunctionElement.table_valued` method first to
671 establish named columns.
672
673 e.g.:
674
675 .. sourcecode:: pycon+sql
676
677 >>> from sqlalchemy import func, select, column
678 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
679 >>> print(select(data_view.column))
680 {printsql}SELECT data_view
681 FROM unnest(:unnest_1) AS data_view
682
683 The :meth:`_functions.FunctionElement.column_valued` method provides
684 a shortcut for the above pattern:
685
686 .. sourcecode:: pycon+sql
687
688 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
689 >>> print(select(data_view))
690 {printsql}SELECT data_view
691 FROM unnest(:unnest_1) AS data_view
692
693 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
694
695 :param name: alias name, will be rendered as ``AS <name>`` in the
696 FROM clause
697
698 :param joins_implicitly: when True, the table valued function may be
699 used in the FROM clause without any explicit JOIN to other tables
700 in the SQL query, and no "cartesian product" warning will be
701 generated. May be useful for SQL functions such as
702 ``func.json_each()``.
703
704 .. versionadded:: 1.4.33
705
706 .. seealso::
707
708 :ref:`tutorial_functions_table_valued` -
709 in the :ref:`unified_tutorial`
710
711 :meth:`_functions.FunctionElement.table_valued`
712
713 :meth:`_functions.FunctionElement.scalar_table_valued`
714
715 :meth:`_functions.FunctionElement.column_valued`
716
717
718 """
719
720 return TableValuedAlias._construct(
721 self,
722 name=name,
723 table_value_type=self.type,
724 joins_implicitly=joins_implicitly,
725 )
726
727 def select(self) -> Select[Tuple[_T]]:
728 """Produce a :func:`_expression.select` construct
729 against this :class:`.FunctionElement`.
730
731 This is shorthand for::
732
733 s = select(function_element)
734
735 """
736 s: Select[Any] = Select(self)
737 if self._execution_options:
738 s = s.execution_options(**self._execution_options)
739 return s
740
741 def _bind_param(
742 self,
743 operator: OperatorType,
744 obj: Any,
745 type_: Optional[TypeEngine[_T]] = None,
746 expanding: bool = False,
747 **kw: Any,
748 ) -> BindParameter[_T]:
749 return BindParameter(
750 None,
751 obj,
752 _compared_to_operator=operator,
753 _compared_to_type=self.type,
754 unique=True,
755 type_=type_,
756 expanding=expanding,
757 **kw,
758 )
759
760 def self_group(self, against: Optional[OperatorType] = None) -> ClauseElement: # type: ignore[override] # noqa E501
761 # for the moment, we are parenthesizing all array-returning
762 # expressions against getitem. This may need to be made
763 # more portable if in the future we support other DBs
764 # besides postgresql.
765 if against is operators.getitem and isinstance(
766 self.type, sqltypes.ARRAY
767 ):
768 return Grouping(self)
769 else:
770 return super().self_group(against=against)
771
772 @property
773 def entity_namespace(self) -> _EntityNamespace:
774 """overrides FromClause.entity_namespace as functions are generally
775 column expressions and not FromClauses.
776
777 """
778 # ideally functions would not be fromclauses but we failed to make
779 # this adjustment in 1.4
780 return _entity_namespace(self.clause_expr)
781
782
783class FunctionAsBinary(BinaryExpression[Any]):
784 _traverse_internals = [
785 ("sql_function", InternalTraversal.dp_clauseelement),
786 ("left_index", InternalTraversal.dp_plain_obj),
787 ("right_index", InternalTraversal.dp_plain_obj),
788 ("modifiers", InternalTraversal.dp_plain_dict),
789 ]
790
791 sql_function: FunctionElement[Any]
792 left_index: int
793 right_index: int
794
795 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
796 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
797
798 def __init__(
799 self, fn: FunctionElement[Any], left_index: int, right_index: int
800 ) -> None:
801 self.sql_function = fn
802 self.left_index = left_index
803 self.right_index = right_index
804
805 self.operator = operators.function_as_comparison_op
806 self.type = sqltypes.BOOLEANTYPE
807 self.negate = None
808 self._is_implicitly_boolean = True
809 self.modifiers = util.immutabledict({})
810
811 @property
812 def left_expr(self) -> ColumnElement[Any]:
813 return self.sql_function.clauses.clauses[self.left_index - 1]
814
815 @left_expr.setter
816 def left_expr(self, value: ColumnElement[Any]) -> None:
817 self.sql_function.clauses.clauses[self.left_index - 1] = value
818
819 @property
820 def right_expr(self) -> ColumnElement[Any]:
821 return self.sql_function.clauses.clauses[self.right_index - 1]
822
823 @right_expr.setter
824 def right_expr(self, value: ColumnElement[Any]) -> None:
825 self.sql_function.clauses.clauses[self.right_index - 1] = value
826
827 if not TYPE_CHECKING:
828 # mypy can't accommodate @property to replace an instance
829 # variable
830
831 left = left_expr
832 right = right_expr
833
834
835class ScalarFunctionColumn(NamedColumn[_T]):
836 __visit_name__ = "scalar_function_column"
837
838 _traverse_internals = [
839 ("name", InternalTraversal.dp_anon_name),
840 ("type", InternalTraversal.dp_type),
841 ("fn", InternalTraversal.dp_clauseelement),
842 ]
843
844 is_literal = False
845 table = None
846
847 def __init__(
848 self,
849 fn: FunctionElement[_T],
850 name: str,
851 type_: Optional[_TypeEngineArgument[_T]] = None,
852 ) -> None:
853 self.fn = fn
854 self.name = name
855
856 # if type is None, we get NULLTYPE, which is our _T. But I don't
857 # know how to get the overloads to express that correctly
858 self.type = type_api.to_instance(type_) # type: ignore
859
860
861class _FunctionGenerator:
862 """Generate SQL function expressions.
863
864 :data:`.func` is a special object instance which generates SQL
865 functions based on name-based attributes, e.g.:
866
867 .. sourcecode:: pycon+sql
868
869 >>> print(func.count(1))
870 {printsql}count(:param_1)
871
872 The returned object is an instance of :class:`.Function`, and is a
873 column-oriented SQL element like any other, and is used in that way:
874
875 .. sourcecode:: pycon+sql
876
877 >>> print(select(func.count(table.c.id)))
878 {printsql}SELECT count(sometable.id) FROM sometable
879
880 Any name can be given to :data:`.func`. If the function name is unknown to
881 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
882 which SQLAlchemy is aware of, the name may be interpreted as a *generic
883 function* which will be compiled appropriately to the target database:
884
885 .. sourcecode:: pycon+sql
886
887 >>> print(func.current_timestamp())
888 {printsql}CURRENT_TIMESTAMP
889
890 To call functions which are present in dot-separated packages,
891 specify them in the same manner:
892
893 .. sourcecode:: pycon+sql
894
895 >>> print(func.stats.yield_curve(5, 10))
896 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
897
898 SQLAlchemy can be made aware of the return type of functions to enable
899 type-specific lexical and result-based behavior. For example, to ensure
900 that a string-based function returns a Unicode value and is similarly
901 treated as a string in expressions, specify
902 :class:`~sqlalchemy.types.Unicode` as the type:
903
904 .. sourcecode:: pycon+sql
905
906 >>> print(
907 ... func.my_string("hi", type_=Unicode)
908 ... + " "
909 ... + func.my_string("there", type_=Unicode)
910 ... )
911 {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
912
913 The object returned by a :data:`.func` call is usually an instance of
914 :class:`.Function`.
915 This object meets the "column" interface, including comparison and labeling
916 functions. The object can also be passed the :meth:`~.Connectable.execute`
917 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
918 where it will be
919 wrapped inside of a SELECT statement first::
920
921 print(connection.execute(func.current_timestamp()).scalar())
922
923 In a few exception cases, the :data:`.func` accessor
924 will redirect a name to a built-in expression such as :func:`.cast`
925 or :func:`.extract`, as these names have well-known meaning
926 but are not exactly the same as "functions" from a SQLAlchemy
927 perspective.
928
929 Functions which are interpreted as "generic" functions know how to
930 calculate their return type automatically. For a listing of known generic
931 functions, see :ref:`generic_functions`.
932
933 .. note::
934
935 The :data:`.func` construct has only limited support for calling
936 standalone "stored procedures", especially those with special
937 parameterization concerns.
938
939 See the section :ref:`stored_procedures` for details on how to use
940 the DBAPI-level ``callproc()`` method for fully traditional stored
941 procedures.
942
943 .. seealso::
944
945 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
946
947 :class:`.Function`
948
949 """ # noqa
950
951 def __init__(self, **opts: Any) -> None:
952 self.__names: List[str] = []
953 self.opts = opts
954
955 def __getattr__(self, name: str) -> _FunctionGenerator:
956 # passthru __ attributes; fixes pydoc
957 if name.startswith("__"):
958 try:
959 return self.__dict__[name] # type: ignore
960 except KeyError:
961 raise AttributeError(name)
962
963 elif name.endswith("_"):
964 name = name[0:-1]
965 f = _FunctionGenerator(**self.opts)
966 f.__names = list(self.__names) + [name]
967 return f
968
969 @overload
970 def __call__(
971 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
972 ) -> Function[_T]: ...
973
974 @overload
975 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
976
977 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
978 o = self.opts.copy()
979 o.update(kwargs)
980
981 tokens = len(self.__names)
982
983 if tokens == 2:
984 package, fname = self.__names
985 elif tokens == 1:
986 package, fname = "_default", self.__names[0]
987 else:
988 package = None
989
990 if package is not None:
991 func = _registry[package].get(fname.lower())
992 if func is not None:
993 return func(*c, **o)
994
995 return Function(
996 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
997 )
998
999 if TYPE_CHECKING:
1000 # START GENERATED FUNCTION ACCESSORS
1001
1002 # code within this block is **programmatically,
1003 # statically generated** by tools/generate_sql_functions.py
1004
1005 @property
1006 def aggregate_strings(self) -> Type[_aggregate_strings_func]: ...
1007
1008 @property
1009 def ansifunction(self) -> Type[_AnsiFunction_func[Any]]: ...
1010
1011 # set ColumnElement[_T] as a separate overload, to appease
1012 # mypy which seems to not want to accept _T from
1013 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1014 # _HasClauseElement as of mypy 1.15
1015
1016 @overload
1017 def array_agg(
1018 self,
1019 col: ColumnElement[_T],
1020 *args: _ColumnExpressionOrLiteralArgument[Any],
1021 **kwargs: Any,
1022 ) -> _array_agg_func[_T]: ...
1023
1024 @overload
1025 def array_agg(
1026 self,
1027 col: _ColumnExpressionArgument[_T],
1028 *args: _ColumnExpressionOrLiteralArgument[Any],
1029 **kwargs: Any,
1030 ) -> _array_agg_func[_T]: ...
1031
1032 @overload
1033 def array_agg(
1034 self,
1035 col: _T,
1036 *args: _ColumnExpressionOrLiteralArgument[Any],
1037 **kwargs: Any,
1038 ) -> _array_agg_func[_T]: ...
1039
1040 def array_agg(
1041 self,
1042 col: _ColumnExpressionOrLiteralArgument[_T],
1043 *args: _ColumnExpressionOrLiteralArgument[Any],
1044 **kwargs: Any,
1045 ) -> _array_agg_func[_T]: ...
1046
1047 @property
1048 def cast(self) -> Type[_Cast_func[Any]]: ...
1049
1050 @property
1051 def char_length(self) -> Type[_char_length_func]: ...
1052
1053 # set ColumnElement[_T] as a separate overload, to appease
1054 # mypy which seems to not want to accept _T from
1055 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1056 # _HasClauseElement as of mypy 1.15
1057
1058 @overload
1059 def coalesce(
1060 self,
1061 col: ColumnElement[_T],
1062 *args: _ColumnExpressionOrLiteralArgument[Any],
1063 **kwargs: Any,
1064 ) -> _coalesce_func[_T]: ...
1065
1066 @overload
1067 def coalesce(
1068 self,
1069 col: _ColumnExpressionArgument[Optional[_T]],
1070 *args: _ColumnExpressionOrLiteralArgument[Any],
1071 **kwargs: Any,
1072 ) -> _coalesce_func[_T]: ...
1073
1074 @overload
1075 def coalesce(
1076 self,
1077 col: Optional[_T],
1078 *args: _ColumnExpressionOrLiteralArgument[Any],
1079 **kwargs: Any,
1080 ) -> _coalesce_func[_T]: ...
1081
1082 def coalesce(
1083 self,
1084 col: _ColumnExpressionOrLiteralArgument[Optional[_T]],
1085 *args: _ColumnExpressionOrLiteralArgument[Any],
1086 **kwargs: Any,
1087 ) -> _coalesce_func[_T]: ...
1088
1089 @property
1090 def concat(self) -> Type[_concat_func]: ...
1091
1092 @property
1093 def count(self) -> Type[_count_func]: ...
1094
1095 @property
1096 def cube(self) -> Type[_cube_func[Any]]: ...
1097
1098 @property
1099 def cume_dist(self) -> Type[_cume_dist_func]: ...
1100
1101 @property
1102 def current_date(self) -> Type[_current_date_func]: ...
1103
1104 @property
1105 def current_time(self) -> Type[_current_time_func]: ...
1106
1107 @property
1108 def current_timestamp(self) -> Type[_current_timestamp_func]: ...
1109
1110 @property
1111 def current_user(self) -> Type[_current_user_func]: ...
1112
1113 @property
1114 def dense_rank(self) -> Type[_dense_rank_func]: ...
1115
1116 @property
1117 def extract(self) -> Type[_Extract_func]: ...
1118
1119 @property
1120 def grouping_sets(self) -> Type[_grouping_sets_func[Any]]: ...
1121
1122 @property
1123 def localtime(self) -> Type[_localtime_func]: ...
1124
1125 @property
1126 def localtimestamp(self) -> Type[_localtimestamp_func]: ...
1127
1128 # set ColumnElement[_T] as a separate overload, to appease
1129 # mypy which seems to not want to accept _T from
1130 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1131 # _HasClauseElement as of mypy 1.15
1132
1133 @overload
1134 def max( # noqa: A001
1135 self,
1136 col: ColumnElement[_T],
1137 *args: _ColumnExpressionOrLiteralArgument[Any],
1138 **kwargs: Any,
1139 ) -> _max_func[_T]: ...
1140
1141 @overload
1142 def max( # noqa: A001
1143 self,
1144 col: _ColumnExpressionArgument[_T],
1145 *args: _ColumnExpressionOrLiteralArgument[Any],
1146 **kwargs: Any,
1147 ) -> _max_func[_T]: ...
1148
1149 @overload
1150 def max( # noqa: A001
1151 self,
1152 col: _T,
1153 *args: _ColumnExpressionOrLiteralArgument[Any],
1154 **kwargs: Any,
1155 ) -> _max_func[_T]: ...
1156
1157 def max( # noqa: A001
1158 self,
1159 col: _ColumnExpressionOrLiteralArgument[_T],
1160 *args: _ColumnExpressionOrLiteralArgument[Any],
1161 **kwargs: Any,
1162 ) -> _max_func[_T]: ...
1163
1164 # set ColumnElement[_T] as a separate overload, to appease
1165 # mypy which seems to not want to accept _T from
1166 # _ColumnExpressionArgument. Seems somewhat related to the covariant
1167 # _HasClauseElement as of mypy 1.15
1168
1169 @overload
1170 def min( # noqa: A001
1171 self,
1172 col: ColumnElement[_T],
1173 *args: _ColumnExpressionOrLiteralArgument[Any],
1174 **kwargs: Any,
1175 ) -> _min_func[_T]: ...
1176
1177 @overload
1178 def min( # noqa: A001
1179 self,
1180 col: _ColumnExpressionArgument[_T],
1181 *args: _ColumnExpressionOrLiteralArgument[Any],
1182 **kwargs: Any,
1183 ) -> _min_func[_T]: ...
1184
1185 @overload
1186 def min( # noqa: A001
1187 self,
1188 col: _T,
1189 *args: _ColumnExpressionOrLiteralArgument[Any],
1190 **kwargs: Any,
1191 ) -> _min_func[_T]: ...
1192
1193 def min( # noqa: A001
1194 self,
1195 col: _ColumnExpressionOrLiteralArgument[_T],
1196 *args: _ColumnExpressionOrLiteralArgument[Any],
1197 **kwargs: Any,
1198 ) -> _min_func[_T]: ...
1199
1200 @property