codekingpro/portable-devtools
115k
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
7
8
9"""SQL function API, factories, and built-in functions.
10
11"""
12
13from __future__ import annotations
14
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
30
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
65
66
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
84
85_T = TypeVar("_T", bound=Any)
86_S = TypeVar("_S", bound=Any)
87
88_registry: util.defaultdict[str, Dict[str, Type[Function[Any]]]] = (
89 util.defaultdict(dict)
90)
91
92
93def register_function(
94 identifier: str, fn: Type[Function[Any]], package: str = "_default"
95) -> None:
96 """Associate a callable with a particular func. name.
97
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).
102
103 """
104 reg = _registry[package]
105
106 identifier = str(identifier).lower()
107
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
115
116
117class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
118 """Base for SQL function-oriented constructs.
119
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.
124
125 .. seealso::
126
127 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
128
129 :class:`.Function` - named SQL function.
130
131 :data:`.func` - namespace which produces registered or ad-hoc
132 :class:`.Function` instances.
133
134 :class:`.GenericFunction` - allows creation of registered function
135 types.
136
137 """
138
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 ]
144
145 packagenames: Tuple[str, ...] = ()
146
147 _has_args = False
148 _with_ordinality = False
149 _table_value_type: Optional[TableValueType] = None
150
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
155
156 clause_expr: Grouping[Any]
157
158 def __init__(self, *clauses: _ColumnExpressionOrLiteralArgument[Any]):
159 r"""Construct a :class:`.FunctionElement`.
160
161 :param \*clauses: list of column expressions that form the arguments
162 of the SQL function call.
163
164 :param \**kwargs: additional kwargs are typically consumed by
165 subclasses.
166
167 .. seealso::
168
169 :data:`.func`
170
171 :class:`.Function`
172
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 )
187
188 _non_anon_label = None
189
190 @property
191 def _proxy_key(self) -> Any:
192 return super()._proxy_key or getattr(self, "name", None)
193
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 )
203
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.
210
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.
215
216 E.g.:
217
218 .. sourcecode:: pycon+sql
219
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
224
225 .. versionadded:: 1.4.0b2
226
227 .. seealso::
228
229 :meth:`_functions.FunctionElement.table_valued`
230
231 :meth:`_functions.FunctionElement.alias`
232
233 :meth:`_functions.FunctionElement.column_valued`
234
235 """ # noqa: E501
236
237 return ScalarFunctionColumn(self, name, type_)
238
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.
244
245 e.g.:
246
247 .. sourcecode:: pycon+sql
248
249 >>> fn = (
250 ... func.generate_series(1, 5).
251 ... table_valued("value", "start", "stop", "step")
252 ... )
253
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}
257
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}
262
263 A WITH ORDINALITY expression may be generated by passing the keyword
264 argument "with_ordinality":
265
266 .. sourcecode:: pycon+sql
267
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
272
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.
277
278 :param name: optional name to assign to the alias name that's generated.
279 If omitted, a unique anonymizing name is used.
280
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`.
285
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()``.
290
291 .. versionadded:: 1.4.33
292
293 .. versionadded:: 1.4.0b2
294
295
296 .. seealso::
297
298 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
299
300 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
301
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
305
306 :meth:`_functions.FunctionElement.column_valued`
307
308 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
309 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
310
311 """ # noqa: 501
312
313 new_func = self._generate()
314
315 with_ordinality = kw.pop("with_ordinality", None)
316 joins_implicitly = kw.pop("joins_implicitly", None)
317 name = kw.pop("name", None)
318
319 if with_ordinality:
320 expr += (with_ordinality,)
321 new_func._with_ordinality = True
322
323 new_func.type = new_func._table_value_type = TableValueType(*expr)
324
325 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
326
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.
332
333 E.g.:
334
335 .. sourcecode:: pycon+sql
336
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
342
343 This is shorthand for::
344
345 gs = func.generate_series(1, 5, -1).alias().column
346
347 :param name: optional name to assign to the alias name that's generated.
348 If omitted, a unique anonymizing name is used.
349
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()``.
355
356 .. versionadded:: 1.4.46
357
358 .. seealso::
359
360 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
361
362 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
363
364 :meth:`_functions.FunctionElement.table_valued`
365
366 """ # noqa: 501
367
368 return self.alias(name=name, joins_implicitly=joins_implicitly).column
369
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`.
373
374 This is a placeholder collection that allows the function to be
375 placed in the FROM clause of a statement:
376
377 .. sourcecode:: pycon+sql
378
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()
383
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.
387
388 .. seealso::
389
390 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
391 SQL function expressions.
392
393 """ # noqa: E501
394 return self.c
395
396 @util.ro_memoized_property
397 def c(self) -> ColumnCollection[str, KeyedColumnElement[Any]]: # type: ignore[override] # noqa: E501
398 """synonym for :attr:`.FunctionElement.columns`."""
399
400 return ColumnCollection(
401 columns=[(col.key, col) for col in self._all_selected_columns]
402 )
403
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)]
413
414 return cols
415
416 @property
417 def exported_columns( # type: ignore[override]
418 self,
419 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:
420 return self.columns
421
422 @HasMemoized.memoized_attribute
423 def clauses(self) -> ClauseList:
424 """Return the underlying :class:`.ClauseList` which contains
425 the arguments for this :class:`.FunctionElement`.
426
427 """
428 return cast(ClauseList, self.clause_expr.element)
429
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.
439
440 Used against aggregate or so-called "window" functions,
441 for database backends that support window functions.
442
443 The expression::
444
445 func.row_number().over(order_by='x')
446
447 is shorthand for::
448
449 from sqlalchemy import over
450 over(func.row_number(), order_by='x')
451
452 See :func:`_expression.over` for a full description.
453
454 .. seealso::
455
456 :func:`_expression.over`
457
458 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
459
460 """
461 return Over(
462 self,
463 partition_by=partition_by,
464 order_by=order_by,
465 rows=rows,
466 range_=range_,
467 )
468
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.
473
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.
477
478 See :func:`_expression.within_group` for a full description.
479
480 .. seealso::
481
482 :ref:`tutorial_functions_within_group` -
483 in the :ref:`unified_tutorial`
484
485
486 """
487 return WithinGroup(self, *order_by)
488
489 @overload
490 def filter(self) -> Self: ...
491
492 @overload
493 def filter(
494 self,
495 __criterion0: _ColumnExpressionArgument[bool],
496 *criterion: _ColumnExpressionArgument[bool],
497 ) -> FunctionFilter[_T]: ...
498
499 def filter(
500 self, *criterion: _ColumnExpressionArgument[bool]
501 ) -> Union[Self, FunctionFilter[_T]]:
502 """Produce a FILTER clause against this function.
503
504 Used against aggregate and window functions,
505 for database backends that support the "FILTER" clause.
506
507 The expression::
508
509 func.count(1).filter(True)
510
511 is shorthand for::
512
513 from sqlalchemy import funcfilter
514 funcfilter(func.count(1), True)
515
516 .. seealso::
517
518 :ref:`tutorial_functions_within_group` -
519 in the :ref:`unified_tutorial`
520
521 :class:`.FunctionFilter`
522
523 :func:`.funcfilter`
524
525
526 """
527 if not criterion:
528 return self
529 return FunctionFilter(self, *criterion)
530
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.
536
537 This method is used for an ORM use case described at
538 :ref:`relationship_custom_operator_sql_function`.
539
540 A hypothetical SQL function "is_equal()" which compares to values
541 for equality would be written in the Core expression language as::
542
543 expr = func.is_equal("a", "b")
544
545 If "is_equal()" above is comparing "a" and "b" for equality, the
546 :meth:`.FunctionElement.as_comparison` method would be invoked as::
547
548 expr = func.is_equal("a", "b").as_comparison(1, 2)
549
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.
552
553 This would create a :class:`.BinaryExpression` that is equivalent to::
554
555 BinaryExpression("a", "b", operator=op.eq)
556
557 However, at the SQL level it would still render as
558 "is_equal('a', 'b')".
559
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`.
566
567 An ORM example is as follows::
568
569 class Venue(Base):
570 __tablename__ = 'venue'
571 id = Column(Integer, primary_key=True)
572 name = Column(String)
573
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 )
582
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".
587
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.
591
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.
596
597 .. versionadded:: 1.3
598
599 .. seealso::
600
601 :ref:`relationship_custom_operator_sql_function` -
602 example use within the ORM
603
604 """
605 return FunctionAsBinary(self, left_index, right_index)
606
607 @property
608 def _from_objects(self) -> Any:
609 return self.clauses._from_objects
610
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.
617
618 Returns None by default, in which case the function's normal ``.type``
619 is used.
620
621 """
622
623 return None
624
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`.
630
631 .. tip::
632
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`.
639
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.
646
647 For a full table-valued expression, use the
648 :meth:`_functions.FunctionElement.table_valued` method first to
649 establish named columns.
650
651 e.g.:
652
653 .. sourcecode:: pycon+sql
654
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
660
661 The :meth:`_functions.FunctionElement.column_valued` method provides
662 a shortcut for the above pattern:
663
664 .. sourcecode:: pycon+sql
665
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
670
671 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
672
673 :param name: alias name, will be rendered as ``AS <name>`` in the
674 FROM clause
675
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()``.
681
682 .. versionadded:: 1.4.33
683
684 .. seealso::
685
686 :ref:`tutorial_functions_table_valued` -
687 in the :ref:`unified_tutorial`
688
689 :meth:`_functions.FunctionElement.table_valued`
690
691 :meth:`_functions.FunctionElement.scalar_table_valued`
692
693 :meth:`_functions.FunctionElement.column_valued`
694
695
696 """
697
698 return TableValuedAlias._construct(
699 self,
700 name=name,
701 table_value_type=self.type,
702 joins_implicitly=joins_implicitly,
703 )
704
705 def select(self) -> Select[Tuple[_T]]:
706 """Produce a :func:`_expression.select` construct
707 against this :class:`.FunctionElement`.
708
709 This is shorthand for::
710
711 s = select(function_element)
712
713 """
714 s: Select[Any] = Select(self)
715 if self._execution_options:
716 s = s.execution_options(**self._execution_options)
717 return s
718
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 )
737
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)
749
750 @property
751 def entity_namespace(self) -> _EntityNamespace:
752 """overrides FromClause.entity_namespace as functions are generally
753 column expressions and not FromClauses.
754
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)
759
760
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 ]
768
769 sql_function: FunctionElement[Any]
770 left_index: int
771 right_index: int
772
773 def _gen_cache_key(self, anon_map: Any, bindparams: Any) -> Any:
774 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
775
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
782
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 = {}
788
789 @property
790 def left_expr(self) -> ColumnElement[Any]:
791 return self.sql_function.clauses.clauses[self.left_index - 1]
792
793 @left_expr.setter
794 def left_expr(self, value: ColumnElement[Any]) -> None:
795 self.sql_function.clauses.clauses[self.left_index - 1] = value
796
797 @property
798 def right_expr(self) -> ColumnElement[Any]:
799 return self.sql_function.clauses.clauses[self.right_index - 1]
800
801 @right_expr.setter
802 def right_expr(self, value: ColumnElement[Any]) -> None:
803 self.sql_function.clauses.clauses[self.right_index - 1] = value
804
805 if not TYPE_CHECKING:
806 # mypy can't accommodate @property to replace an instance
807 # variable
808
809 left = left_expr
810 right = right_expr
811
812
813class ScalarFunctionColumn(NamedColumn[_T]):
814 __visit_name__ = "scalar_function_column"
815
816 _traverse_internals = [
817 ("name", InternalTraversal.dp_anon_name),
818 ("type", InternalTraversal.dp_type),
819 ("fn", InternalTraversal.dp_clauseelement),
820 ]
821
822 is_literal = False
823 table = None
824
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
833
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
837
838
839class _FunctionGenerator:
840 """Generate SQL function expressions.
841
842 :data:`.func` is a special object instance which generates SQL
843 functions based on name-based attributes, e.g.:
844
845 .. sourcecode:: pycon+sql
846
847 >>> print(func.count(1))
848 {printsql}count(:param_1)
849
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:
852
853 .. sourcecode:: pycon+sql
854
855 >>> print(select(func.count(table.c.id)))
856 {printsql}SELECT count(sometable.id) FROM sometable
857
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:
862
863 .. sourcecode:: pycon+sql
864
865 >>> print(func.current_timestamp())
866 {printsql}CURRENT_TIMESTAMP
867
868 To call functions which are present in dot-separated packages,
869 specify them in the same manner:
870
871 .. sourcecode:: pycon+sql
872
873 >>> print(func.stats.yield_curve(5, 10))
874 {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2)
875
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:
881
882 .. sourcecode:: pycon+sql
883
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)
887
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::
895
896 print(connection.execute(func.current_timestamp()).scalar())
897
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.
903
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`.
907
908 .. note::
909
910 The :data:`.func` construct has only limited support for calling
911 standalone "stored procedures", especially those with special
912 parameterization concerns.
913
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.
917
918 .. seealso::
919
920 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
921
922 :class:`.Function`
923
924 """ # noqa
925
926 def __init__(self, **opts: Any):
927 self.__names: List[str] = []
928 self.opts = opts
929
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)
937
938 elif name.endswith("_"):
939 name = name[0:-1]
940 f = _FunctionGenerator(**self.opts)
941 f.__names = list(self.__names) + [name]
942 return f
943
944 @overload
945 def __call__(
946 self, *c: Any, type_: _TypeEngineArgument[_T], **kwargs: Any
947 ) -> Function[_T]: ...
948
949 @overload
950 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]: ...
951
952 def __call__(self, *c: Any, **kwargs: Any) -> Function[Any]:
953 o = self.opts.copy()
954 o.update(kwargs)
955
956 tokens = len(self.__names)
957
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
964
965 if package is not None:
966 func = _registry[package].get(fname.lower())
967 if func is not None:
968 return func(*c, **o)
969
970 return Function(
971 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
972 )
973
974 if TYPE_CHECKING:
975 # START GENERATED FUNCTION ACCESSORS
976
977 # code within this block is **programmatically,
978 # statically generated** by tools/generate_sql_functions.py
979
980 @property
981 def aggregate_strings(self) -> Type[aggregate_strings]: ...
982
983 @property
984 def ansifunction(self) -> Type[AnsiFunction[Any]]: ...
985
986 @property
987 def array_agg(self) -> Type[array_agg[Any]]: ...
988
989 @property
990 def cast(self) -> Type[Cast[Any]]: ...
991
992 @property
993 def char_length(self) -> Type[char_length]: ...
994
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
999
1000 @overload
1001 def coalesce(
1002 self,
1003 col: ColumnElement[_T],
1004 *args: _ColumnExpressionOrLiteralArgument[Any],
1005 **kwargs: Any,
1006 ) -> coalesce[_T]: ...
1007
1008 @overload
1009 def coalesce(
1010 self,
1011 col: _ColumnExpressionArgument[_T],
1012 *args: _ColumnExpressionOrLiteralArgument[Any],
1013 **kwargs: Any,
1014 ) -> coalesce[_T]: ...
1015
1016 @overload
1017 def coalesce(
1018 self,
1019 col: _ColumnExpressionOrLiteralArgument[_T],
1020 *args: _ColumnExpressionOrLiteralArgument[Any],
1021 **kwargs: Any,
1022 ) -> coalesce[_T]: ...
1023
1024 def coalesce(
1025 self,
1026 col: _ColumnExpressionOrLiteralArgument[_T],
1027 *args: _ColumnExpressionOrLiteralArgument[Any],
1028 **kwargs: Any,
1029 ) -> coalesce[_T]: ...
1030
1031 @property
1032 def concat(self) -> Type[concat]: ...
1033
1034 @property
1035 def count(self) -> Type[count]: ...
1036
1037 @property
1038 def cube(self) -> Type[cube[Any]]: ...
1039
1040 @property
1041 def cume_dist(self) -> Type[cume_dist]: ...
1042
1043 @property
1044 def current_date(self) -> Type[current_date]: ...
1045
1046 @property
1047 def current_time(self) -> Type[current_time]: ...
1048
1049 @property
1050 def current_timestamp(self) -> Type[current_timestamp]: ...
1051
1052 @property
1053 def current_user(self) -> Type[current_user]: ...
1054
1055 @property
1056 def dense_rank(self) -> Type[dense_rank]: ...
1057
1058 @property
1059 def extract(self) -> Type[Extract]: ...
1060
1061 @property
1062 def grouping_sets(self) -> Type[grouping_sets[Any]]: ...
1063
1064 @property
1065 def localtime(self) -> Type[localtime]: ...
1066
1067 @property
1068 def localtimestamp(self) -> Type[localtimestamp]: ...
1069
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
1074
1075 @overload
1076 def max( # noqa: A001
1077 self,
1078 col: ColumnElement[_T],
1079 *args: _ColumnExpressionOrLiteralArgument[Any],
1080 **kwargs: Any,
1081 ) -> max[_T]: ...
1082
1083 @overload
1084 def max( # noqa: A001
1085 self,
1086 col: _ColumnExpressionArgument[_T],
1087 *args: _ColumnExpressionOrLiteralArgument[Any],
1088 **kwargs: Any,
1089 ) -> max[_T]: ...
1090
1091 @overload
1092 def max( # noqa: A001
1093 self,
1094 col: _ColumnExpressionOrLiteralArgument[_T],
1095 *args: _ColumnExpressionOrLiteralArgument[Any],
1096 **kwargs: Any,
1097 ) -> max[_T]: ...
1098
1099 def max( # noqa: A001
1100 self,
1101 col: _ColumnExpressionOrLiteralArgument[_T],
1102 *args: _ColumnExpressionOrLiteralArgument[Any],
1103 **kwargs: Any,
1104 ) -> max[_T]: ...
1105
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
1110
1111 @overload
1112 def min( # noqa: A001
1113 self,
1114 col: ColumnElement[_T],
1115 *args: _ColumnExpressionOrLiteralArgument[Any],
1116 **kwargs: Any,
1117 ) -> min[_T]: ...
1118
1119 @overload
1120 def min( # noqa: A001
1121 self,
1122 col: _ColumnExpressionArgument[_T],
1123 *args: _ColumnExpressionOrLiteralArgument[Any],
1124 **kwargs: Any,
1125 ) -> min[_T]: ...
1126
1127 @overload
1128 def min( # noqa: A001
1129 self,
1130 col: _ColumnExpressionOrLiteralArgument[_T],
1131 *args: _ColumnExpressionOrLiteralArgument[Any],
1132 **kwargs: Any,
1133 ) -> min[_T]: ...
1134
1135 def min( # noqa: A001
1136 self,
1137 col: _ColumnExpressionOrLiteralArgument[_T],
1138 *args: _ColumnExpressionOrLiteralArgument[Any],
1139 **kwargs: Any,
1140 ) -> min[_T]: ...
1141
1142 @property
1143 def mode(self) -> Type[mode[Any]]: ...
1144
1145 @property
1146 def next_value(self) -> Type[next_value]: ...
1147
1148 @property
1149 def now(self) -> Type[now]: ...
1150
1151 @property
1152 def orderedsetagg(self) -> Type[OrderedSetAgg[Any]]: ...
1153
1154 @property
1155 def percent_rank(self) -> Type[percent_rank]: ...
1156
1157 @property
1158 def percentile_cont(self) -> Type[percentile_cont[Any]]: ...
1159
1160 @property
1161 def percentile_disc(self) -> Type[percentile_disc[Any]]: ...
1162
1163 @property
1164 def random(self) -> Type[random]: ...
1165
1166 @property
1167 def rank(self) -> Type[rank]: ...
1168
1169 @property
1170 def rollup(self) -> Type[rollup[Any]]: ...
1171
1172 @property
1173 def session_user(self) -> Type[session_user]: ...
1174
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
1179
1180 @overload
1181 def sum( # noqa: A001
1182 self,
1183 col: ColumnElement[_T],
1184 *args: _ColumnExpressionOrLiteralArgument[Any],
1185 **kwargs: Any,
1186 ) -> sum[_T]: ...
1187
1188 @overload
1189 def sum( # noqa: A001
1190 self,
1191 col: _ColumnExpressionArgument[_T],
1192 *args: _ColumnExpressionOrLiteralArgument[Any],
1193 **kwargs: Any,
1194 ) -> sum[_T]: ...
1195
1196 @overload
1197 def sum( # noqa: A001
1198 self,
1199 col: _ColumnExpressionOrLiteralArgument[_T],
1200 *args: _ColumnExpressionOrLiteralArgument[Any],