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"""SQL function API, factories, and built-in functions.
9
10"""
11from . import annotation
12from . import coercions
13from . import operators
14from . import roles
15from . import schema
16from . import sqltypes
17from . import util as sqlutil
18from .base import _entity_namespace
19from .base import ColumnCollection
20from .base import Executable
21from .base import Generative
22from .base import HasMemoized
23from .elements import _type_from_args
24from .elements import BinaryExpression
25from .elements import BindParameter
26from .elements import Cast
27from .elements import ClauseList
28from .elements import ColumnElement
29from .elements import Extract
30from .elements import FunctionFilter
31from .elements import Grouping
32from .elements import literal_column
33from .elements import NamedColumn
34from .elements import Over
35from .elements import WithinGroup
36from .selectable import FromClause
37from .selectable import Select
38from .selectable import TableValuedAlias
39from .visitors import InternalTraversal
40from .visitors import TraversibleType
41from .. import util
42
43
44_registry = util.defaultdict(dict)
45
46
47def register_function(identifier, fn, package="_default"):
48 """Associate a callable with a particular func. name.
49
50 This is normally called by _GenericMeta, but is also
51 available by itself so that a non-Function construct
52 can be associated with the :data:`.func` accessor (i.e.
53 CAST, EXTRACT).
54
55 """
56 reg = _registry[package]
57
58 identifier = util.text_type(identifier).lower()
59
60 # Check if a function with the same identifier is registered.
61 if identifier in reg:
62 util.warn(
63 "The GenericFunction '{}' is already registered and "
64 "is going to be overridden.".format(identifier)
65 )
66 reg[identifier] = fn
67
68
69class FunctionElement(Executable, ColumnElement, FromClause, Generative):
70 """Base for SQL function-oriented constructs.
71
72 .. seealso::
73
74 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
75
76 :class:`.Function` - named SQL function.
77
78 :data:`.func` - namespace which produces registered or ad-hoc
79 :class:`.Function` instances.
80
81 :class:`.GenericFunction` - allows creation of registered function
82 types.
83
84 """
85
86 _traverse_internals = [
87 ("clause_expr", InternalTraversal.dp_clauseelement),
88 ("_with_ordinality", InternalTraversal.dp_boolean),
89 ("_table_value_type", InternalTraversal.dp_has_cache_key),
90 ]
91
92 packagenames = ()
93
94 _has_args = False
95 _with_ordinality = False
96 _table_value_type = None
97
98 def __init__(self, *clauses, **kwargs):
99 r"""Construct a :class:`.FunctionElement`.
100
101 :param \*clauses: list of column expressions that form the arguments
102 of the SQL function call.
103
104 :param \**kwargs: additional kwargs are typically consumed by
105 subclasses.
106
107 .. seealso::
108
109 :data:`.func`
110
111 :class:`.Function`
112
113 """
114 args = [
115 coercions.expect(
116 roles.ExpressionElementRole,
117 c,
118 name=getattr(self, "name", None),
119 apply_propagate_attrs=self,
120 )
121 for c in clauses
122 ]
123 self._has_args = self._has_args or bool(args)
124 self.clause_expr = ClauseList(
125 operator=operators.comma_op, group_contents=True, *args
126 ).self_group()
127
128 _non_anon_label = None
129
130 @property
131 def _proxy_key(self):
132 return super(FunctionElement, self)._proxy_key or getattr(
133 self, "name", None
134 )
135
136 def _execute_on_connection(
137 self, connection, multiparams, params, execution_options
138 ):
139 return connection._execute_function(
140 self, multiparams, params, execution_options
141 )
142
143 def scalar_table_valued(self, name, type_=None):
144 """Return a column expression that's against this
145 :class:`_functions.FunctionElement` as a scalar
146 table-valued expression.
147
148 The returned expression is similar to that returned by a single column
149 accessed off of a :meth:`_functions.FunctionElement.table_valued`
150 construct, except no FROM clause is generated; the function is rendered
151 in the similar way as a scalar subquery.
152
153 E.g.::
154
155 >>> from sqlalchemy import func, select
156 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key")
157 >>> print(select(fn))
158 SELECT (jsonb_each(:jsonb_each_1)).key
159
160 .. versionadded:: 1.4.0b2
161
162 .. seealso::
163
164 :meth:`_functions.FunctionElement.table_valued`
165
166 :meth:`_functions.FunctionElement.alias`
167
168 :meth:`_functions.FunctionElement.column_valued`
169
170 """ # noqa: E501
171
172 return ScalarFunctionColumn(self, name, type_)
173
174 def table_valued(self, *expr, **kw):
175 r"""Return a :class:`_sql.TableValuedAlias` representation of this
176 :class:`_functions.FunctionElement` with table-valued expressions added.
177
178 e.g.::
179
180 >>> fn = (
181 ... func.generate_series(1, 5).
182 ... table_valued("value", "start", "stop", "step")
183 ... )
184
185 >>> print(select(fn))
186 SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step
187 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
188
189 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2))
190 SELECT anon_1.value, anon_1.stop
191 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1
192 WHERE anon_1.value > :value_1
193
194 A WITH ORDINALITY expression may be generated by passing the keyword
195 argument "with_ordinality"::
196
197 >>> fn = func.generate_series(4, 1, -1).table_valued("gen", with_ordinality="ordinality")
198 >>> print(select(fn))
199 SELECT anon_1.gen, anon_1.ordinality
200 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1
201
202 :param \*expr: A series of string column names that will be added to the
203 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias`
204 construct as columns. :func:`_sql.column` objects with or without
205 datatypes may also be used.
206
207 :param name: optional name to assign to the alias name that's generated.
208 If omitted, a unique anonymizing name is used.
209
210 :param with_ordinality: string name that when present results in the
211 ``WITH ORDINALITY`` clause being added to the alias, and the given
212 string name will be added as a column to the .c collection
213 of the resulting :class:`_sql.TableValuedAlias`.
214
215 :param joins_implicitly: when True, the table valued function may be
216 used in the FROM clause without any explicit JOIN to other tables
217 in the SQL query, and no "cartesian product" warning will be generated.
218 May be useful for SQL functions such as ``func.json_each()``.
219
220 .. versionadded:: 1.4.33
221
222 .. versionadded:: 1.4.0b2
223
224
225 .. seealso::
226
227 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
228
229 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
230
231 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of
232 :meth:`_functions.FunctionElement.table_valued` which delivers the
233 complete table valued expression as a scalar column expression
234
235 :meth:`_functions.FunctionElement.column_valued`
236
237 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
238 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
239
240 """ # noqa: 501
241
242 new_func = self._generate()
243
244 with_ordinality = kw.pop("with_ordinality", None)
245 joins_implicitly = kw.pop("joins_implicitly", None)
246 name = kw.pop("name", None)
247
248 if with_ordinality:
249 expr += (with_ordinality,)
250 new_func._with_ordinality = True
251
252 new_func.type = new_func._table_value_type = sqltypes.TableValueType(
253 *expr
254 )
255
256 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
257
258 def column_valued(self, name=None, joins_implicitly=False):
259 """Return this :class:`_functions.FunctionElement` as a column expression that
260 selects from itself as a FROM clause.
261
262 E.g.::
263
264 >>> from sqlalchemy import select, func
265 >>> gs = func.generate_series(1, 5, -1).column_valued()
266 >>> print(select(gs))
267 SELECT anon_1
268 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1
269
270 This is shorthand for::
271
272 gs = func.generate_series(1, 5, -1).alias().column
273
274 :param name: optional name to assign to the alias name that's generated.
275 If omitted, a unique anonymizing name is used.
276
277 :param joins_implicitly: when True, the "table" portion of the column
278 valued function may be a member of the FROM clause without any
279 explicit JOIN to other tables in the SQL query, and no "cartesian
280 product" warning will be generated. May be useful for SQL functions
281 such as ``func.json_array_elements()``.
282
283 .. versionadded:: 1.4.46
284
285 .. seealso::
286
287 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
288
289 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
290
291 :meth:`_functions.FunctionElement.table_valued`
292
293 """ # noqa: 501
294
295 return self.alias(name=name, joins_implicitly=joins_implicitly).column
296
297 @property
298 def columns(self):
299 r"""The set of columns exported by this :class:`.FunctionElement`.
300
301 This is a placeholder collection that allows the function to be
302 placed in the FROM clause of a statement::
303
304 >>> from sqlalchemy import column, select, func
305 >>> stmt = select(column('x'), column('y')).select_from(func.myfunction())
306 >>> print(stmt)
307 SELECT x, y FROM myfunction()
308
309 The above form is a legacy feature that is now superseded by the
310 fully capable :meth:`_functions.FunctionElement.table_valued`
311 method; see that method for details.
312
313 .. seealso::
314
315 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
316 SQL function expressions.
317
318 """ # noqa: E501
319
320 return ColumnCollection(
321 columns=[(col.key, col) for col in self._all_selected_columns]
322 )
323
324 @property
325 def _all_selected_columns(self):
326 if self.type._is_table_value:
327 cols = self.type._elements
328 else:
329 cols = [self.label(None)]
330
331 return cols
332
333 @property
334 def exported_columns(self):
335 return self.columns
336
337 @HasMemoized.memoized_attribute
338 def clauses(self):
339 """Return the underlying :class:`.ClauseList` which contains
340 the arguments for this :class:`.FunctionElement`.
341
342 """
343 return self.clause_expr.element
344
345 def over(self, partition_by=None, order_by=None, rows=None, range_=None):
346 """Produce an OVER clause against this function.
347
348 Used against aggregate or so-called "window" functions,
349 for database backends that support window functions.
350
351 The expression::
352
353 func.row_number().over(order_by='x')
354
355 is shorthand for::
356
357 from sqlalchemy import over
358 over(func.row_number(), order_by='x')
359
360 See :func:`_expression.over` for a full description.
361
362 .. seealso::
363
364 :func:`_expression.over`
365
366 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
367
368 """
369 return Over(
370 self,
371 partition_by=partition_by,
372 order_by=order_by,
373 rows=rows,
374 range_=range_,
375 )
376
377 def within_group(self, *order_by):
378 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
379
380 Used against so-called "ordered set aggregate" and "hypothetical
381 set aggregate" functions, including :class:`.percentile_cont`,
382 :class:`.rank`, :class:`.dense_rank`, etc.
383
384 See :func:`_expression.within_group` for a full description.
385
386 .. versionadded:: 1.1
387
388
389 .. seealso::
390
391 :ref:`tutorial_functions_within_group` -
392 in the :ref:`unified_tutorial`
393
394
395 """
396 return WithinGroup(self, *order_by)
397
398 def filter(self, *criterion):
399 """Produce a FILTER clause against this function.
400
401 Used against aggregate and window functions,
402 for database backends that support the "FILTER" clause.
403
404 The expression::
405
406 func.count(1).filter(True)
407
408 is shorthand for::
409
410 from sqlalchemy import funcfilter
411 funcfilter(func.count(1), True)
412
413 .. versionadded:: 1.0.0
414
415 .. seealso::
416
417 :ref:`tutorial_functions_within_group` -
418 in the :ref:`unified_tutorial`
419
420 :class:`.FunctionFilter`
421
422 :func:`.funcfilter`
423
424
425 """
426 if not criterion:
427 return self
428 return FunctionFilter(self, *criterion)
429
430 def as_comparison(self, left_index, right_index):
431 """Interpret this expression as a boolean comparison between two
432 values.
433
434 This method is used for an ORM use case described at
435 :ref:`relationship_custom_operator_sql_function`.
436
437 A hypothetical SQL function "is_equal()" which compares to values
438 for equality would be written in the Core expression language as::
439
440 expr = func.is_equal("a", "b")
441
442 If "is_equal()" above is comparing "a" and "b" for equality, the
443 :meth:`.FunctionElement.as_comparison` method would be invoked as::
444
445 expr = func.is_equal("a", "b").as_comparison(1, 2)
446
447 Where above, the integer value "1" refers to the first argument of the
448 "is_equal()" function and the integer value "2" refers to the second.
449
450 This would create a :class:`.BinaryExpression` that is equivalent to::
451
452 BinaryExpression("a", "b", operator=op.eq)
453
454 However, at the SQL level it would still render as
455 "is_equal('a', 'b')".
456
457 The ORM, when it loads a related object or collection, needs to be able
458 to manipulate the "left" and "right" sides of the ON clause of a JOIN
459 expression. The purpose of this method is to provide a SQL function
460 construct that can also supply this information to the ORM, when used
461 with the :paramref:`_orm.relationship.primaryjoin` parameter. The
462 return value is a containment object called :class:`.FunctionAsBinary`.
463
464 An ORM example is as follows::
465
466 class Venue(Base):
467 __tablename__ = 'venue'
468 id = Column(Integer, primary_key=True)
469 name = Column(String)
470
471 descendants = relationship(
472 "Venue",
473 primaryjoin=func.instr(
474 remote(foreign(name)), name + "/"
475 ).as_comparison(1, 2) == 1,
476 viewonly=True,
477 order_by=name
478 )
479
480 Above, the "Venue" class can load descendant "Venue" objects by
481 determining if the name of the parent Venue is contained within the
482 start of the hypothetical descendant value's name, e.g. "parent1" would
483 match up to "parent1/child1", but not to "parent2/child1".
484
485 Possible use cases include the "materialized path" example given above,
486 as well as making use of special SQL functions such as geometric
487 functions to create join conditions.
488
489 :param left_index: the integer 1-based index of the function argument
490 that serves as the "left" side of the expression.
491 :param right_index: the integer 1-based index of the function argument
492 that serves as the "right" side of the expression.
493
494 .. versionadded:: 1.3
495
496 .. seealso::
497
498 :ref:`relationship_custom_operator_sql_function` -
499 example use within the ORM
500
501 """
502 return FunctionAsBinary(self, left_index, right_index)
503
504 @property
505 def _from_objects(self):
506 return self.clauses._from_objects
507
508 def within_group_type(self, within_group):
509 """For types that define their return type as based on the criteria
510 within a WITHIN GROUP (ORDER BY) expression, called by the
511 :class:`.WithinGroup` construct.
512
513 Returns None by default, in which case the function's normal ``.type``
514 is used.
515
516 """
517
518 return None
519
520 def alias(self, name=None, joins_implicitly=False):
521 r"""Produce a :class:`_expression.Alias` construct against this
522 :class:`.FunctionElement`.
523
524 .. tip::
525
526 The :meth:`_functions.FunctionElement.alias` method is part of the
527 mechanism by which "table valued" SQL functions are created.
528 However, most use cases are covered by higher level methods on
529 :class:`_functions.FunctionElement` including
530 :meth:`_functions.FunctionElement.table_valued`, and
531 :meth:`_functions.FunctionElement.column_valued`.
532
533 This construct wraps the function in a named alias which
534 is suitable for the FROM clause, in the style accepted for example
535 by PostgreSQL. A column expression is also provided using the
536 special ``.column`` attribute, which may
537 be used to refer to the output of the function as a scalar value
538 in the columns or where clause, for a backend such as PostgreSQL.
539
540 For a full table-valued expression, use the
541 :meth:`_functions.FunctionElement.table_valued` method first to
542 establish named columns.
543
544 e.g.::
545
546 >>> from sqlalchemy import func, select, column
547 >>> data_view = func.unnest([1, 2, 3]).alias("data_view")
548 >>> print(select(data_view.column))
549 SELECT data_view
550 FROM unnest(:unnest_1) AS data_view
551
552 The :meth:`_functions.FunctionElement.column_valued` method provides
553 a shortcut for the above pattern::
554
555 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view")
556 >>> print(select(data_view))
557 SELECT data_view
558 FROM unnest(:unnest_1) AS data_view
559
560 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
561
562 :param name: alias name, will be rendered as ``AS <name>`` in the
563 FROM clause
564
565 :param joins_implicitly: when True, the table valued function may be
566 used in the FROM clause without any explicit JOIN to other tables
567 in the SQL query, and no "cartesian product" warning will be
568 generated. May be useful for SQL functions such as
569 ``func.json_each()``.
570
571 .. versionadded:: 1.4.33
572
573 .. seealso::
574
575 :ref:`tutorial_functions_table_valued` -
576 in the :ref:`unified_tutorial`
577
578 :meth:`_functions.FunctionElement.table_valued`
579
580 :meth:`_functions.FunctionElement.scalar_table_valued`
581
582 :meth:`_functions.FunctionElement.column_valued`
583
584
585 """
586
587 return TableValuedAlias._construct(
588 self,
589 name,
590 table_value_type=self.type,
591 joins_implicitly=joins_implicitly,
592 )
593
594 def select(self):
595 """Produce a :func:`_expression.select` construct
596 against this :class:`.FunctionElement`.
597
598 This is shorthand for::
599
600 s = select(function_element)
601
602 """
603 s = Select._create_select(self)
604 if self._execution_options:
605 s = s.execution_options(**self._execution_options)
606 return s
607
608 @util.deprecated_20(
609 ":meth:`.FunctionElement.scalar`",
610 alternative="Scalar execution in SQLAlchemy 2.0 is performed "
611 "by the :meth:`_engine.Connection.scalar` method of "
612 ":class:`_engine.Connection`, "
613 "or in the ORM by the :meth:`.Session.scalar` method of "
614 ":class:`.Session`.",
615 )
616 def scalar(self):
617 """Execute this :class:`.FunctionElement` against an embedded
618 'bind' and return a scalar value.
619
620 This first calls :meth:`~.FunctionElement.select` to
621 produce a SELECT construct.
622
623 Note that :class:`.FunctionElement` can be passed to
624 the :meth:`.Connectable.scalar` method of :class:`_engine.Connection`
625 or :class:`_engine.Engine`.
626
627 """
628 return self.select().execute().scalar()
629
630 @util.deprecated_20(
631 ":meth:`.FunctionElement.execute`",
632 alternative="All statement execution in SQLAlchemy 2.0 is performed "
633 "by the :meth:`_engine.Connection.execute` method of "
634 ":class:`_engine.Connection`, "
635 "or in the ORM by the :meth:`.Session.execute` method of "
636 ":class:`.Session`.",
637 )
638 def execute(self):
639 """Execute this :class:`.FunctionElement` against an embedded
640 'bind'.
641
642 This first calls :meth:`~.FunctionElement.select` to
643 produce a SELECT construct.
644
645 Note that :class:`.FunctionElement` can be passed to
646 the :meth:`.Connectable.execute` method of :class:`_engine.Connection`
647 or :class:`_engine.Engine`.
648
649 """
650 return self.select().execute()
651
652 def _bind_param(self, operator, obj, type_=None, **kw):
653 return BindParameter(
654 None,
655 obj,
656 _compared_to_operator=operator,
657 _compared_to_type=self.type,
658 unique=True,
659 type_=type_,
660 **kw
661 )
662
663 def self_group(self, against=None):
664 # for the moment, we are parenthesizing all array-returning
665 # expressions against getitem. This may need to be made
666 # more portable if in the future we support other DBs
667 # besides postgresql.
668 if against is operators.getitem and isinstance(
669 self.type, sqltypes.ARRAY
670 ):
671 return Grouping(self)
672 else:
673 return super(FunctionElement, self).self_group(against=against)
674
675 @property
676 def entity_namespace(self):
677 """overrides FromClause.entity_namespace as functions are generally
678 column expressions and not FromClauses.
679
680 """
681 # ideally functions would not be fromclauses but we failed to make
682 # this adjustment in 1.4
683 return _entity_namespace(self.clause_expr)
684
685
686class FunctionAsBinary(BinaryExpression):
687 _traverse_internals = [
688 ("sql_function", InternalTraversal.dp_clauseelement),
689 ("left_index", InternalTraversal.dp_plain_obj),
690 ("right_index", InternalTraversal.dp_plain_obj),
691 ("modifiers", InternalTraversal.dp_plain_dict),
692 ]
693
694 def _gen_cache_key(self, anon_map, bindparams):
695 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
696
697 def __init__(self, fn, left_index, right_index):
698 self.sql_function = fn
699 self.left_index = left_index
700 self.right_index = right_index
701
702 self.operator = operators.function_as_comparison_op
703 self.type = sqltypes.BOOLEANTYPE
704 self.negate = None
705 self._is_implicitly_boolean = True
706 self.modifiers = {}
707
708 @property
709 def left(self):
710 return self.sql_function.clauses.clauses[self.left_index - 1]
711
712 @left.setter
713 def left(self, value):
714 self.sql_function.clauses.clauses[self.left_index - 1] = value
715
716 @property
717 def right(self):
718 return self.sql_function.clauses.clauses[self.right_index - 1]
719
720 @right.setter
721 def right(self, value):
722 self.sql_function.clauses.clauses[self.right_index - 1] = value
723
724
725class ScalarFunctionColumn(NamedColumn):
726 __visit_name__ = "scalar_function_column"
727
728 _traverse_internals = [
729 ("name", InternalTraversal.dp_anon_name),
730 ("type", InternalTraversal.dp_type),
731 ("fn", InternalTraversal.dp_clauseelement),
732 ]
733
734 is_literal = False
735 table = None
736
737 def __init__(self, fn, name, type_=None):
738 self.fn = fn
739 self.name = name
740 self.type = sqltypes.to_instance(type_)
741
742
743class _FunctionGenerator(object):
744 """Generate SQL function expressions.
745
746 :data:`.func` is a special object instance which generates SQL
747 functions based on name-based attributes, e.g.::
748
749 >>> print(func.count(1))
750 count(:param_1)
751
752 The returned object is an instance of :class:`.Function`, and is a
753 column-oriented SQL element like any other, and is used in that way::
754
755 >>> print(select(func.count(table.c.id)))
756 SELECT count(sometable.id) FROM sometable
757
758 Any name can be given to :data:`.func`. If the function name is unknown to
759 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
760 which SQLAlchemy is aware of, the name may be interpreted as a *generic
761 function* which will be compiled appropriately to the target database::
762
763 >>> print(func.current_timestamp())
764 CURRENT_TIMESTAMP
765
766 To call functions which are present in dot-separated packages,
767 specify them in the same manner::
768
769 >>> print(func.stats.yield_curve(5, 10))
770 stats.yield_curve(:yield_curve_1, :yield_curve_2)
771
772 SQLAlchemy can be made aware of the return type of functions to enable
773 type-specific lexical and result-based behavior. For example, to ensure
774 that a string-based function returns a Unicode value and is similarly
775 treated as a string in expressions, specify
776 :class:`~sqlalchemy.types.Unicode` as the type:
777
778 >>> print(func.my_string(u'hi', type_=Unicode) + ' ' +
779 ... func.my_string(u'there', type_=Unicode))
780 my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
781
782 The object returned by a :data:`.func` call is usually an instance of
783 :class:`.Function`.
784 This object meets the "column" interface, including comparison and labeling
785 functions. The object can also be passed the :meth:`~.Connectable.execute`
786 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
787 where it will be
788 wrapped inside of a SELECT statement first::
789
790 print(connection.execute(func.current_timestamp()).scalar())
791
792 In a few exception cases, the :data:`.func` accessor
793 will redirect a name to a built-in expression such as :func:`.cast`
794 or :func:`.extract`, as these names have well-known meaning
795 but are not exactly the same as "functions" from a SQLAlchemy
796 perspective.
797
798 Functions which are interpreted as "generic" functions know how to
799 calculate their return type automatically. For a listing of known generic
800 functions, see :ref:`generic_functions`.
801
802 .. note::
803
804 The :data:`.func` construct has only limited support for calling
805 standalone "stored procedures", especially those with special
806 parameterization concerns.
807
808 See the section :ref:`stored_procedures` for details on how to use
809 the DBAPI-level ``callproc()`` method for fully traditional stored
810 procedures.
811
812 .. seealso::
813
814 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
815
816 :class:`.Function`
817
818 """
819
820 def __init__(self, **opts):
821 self.__names = []
822 self.opts = opts
823
824 def __getattr__(self, name):
825 # passthru __ attributes; fixes pydoc
826 if name.startswith("__"):
827 try:
828 return self.__dict__[name]
829 except KeyError:
830 raise AttributeError(name)
831
832 elif name.endswith("_"):
833 name = name[0:-1]
834 f = _FunctionGenerator(**self.opts)
835 f.__names = list(self.__names) + [name]
836 return f
837
838 def __call__(self, *c, **kwargs):
839 o = self.opts.copy()
840 o.update(kwargs)
841
842 tokens = len(self.__names)
843
844 if tokens == 2:
845 package, fname = self.__names
846 elif tokens == 1:
847 package, fname = "_default", self.__names[0]
848 else:
849 package = None
850
851 if package is not None:
852 func = _registry[package].get(fname.lower())
853 if func is not None:
854 return func(*c, **o)
855
856 return Function(
857 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
858 )
859
860
861func = _FunctionGenerator()
862func.__doc__ = _FunctionGenerator.__doc__
863
864modifier = _FunctionGenerator(group=False)
865
866
867class Function(FunctionElement):
868 r"""Describe a named SQL function.
869
870 The :class:`.Function` object is typically generated from the
871 :data:`.func` generation object.
872
873
874 :param \*clauses: list of column expressions that form the arguments
875 of the SQL function call.
876
877 :param type\_: optional :class:`.TypeEngine` datatype object that will be
878 used as the return value of the column expression generated by this
879 function call.
880
881 :param packagenames: a string which indicates package prefix names
882 to be prepended to the function name when the SQL is generated.
883 The :data:`.func` generator creates these when it is called using
884 dotted format, e.g.::
885
886 func.mypackage.some_function(col1, col2)
887
888 .. seealso::
889
890 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
891
892 :data:`.func` - namespace which produces registered or ad-hoc
893 :class:`.Function` instances.
894
895 :class:`.GenericFunction` - allows creation of registered function
896 types.
897
898 """
899
900 __visit_name__ = "function"
901
902 _traverse_internals = FunctionElement._traverse_internals + [
903 ("packagenames", InternalTraversal.dp_plain_obj),
904 ("name", InternalTraversal.dp_string),
905 ("type", InternalTraversal.dp_type),
906 ]
907
908 type = sqltypes.NULLTYPE
909 """A :class:`_types.TypeEngine` object which refers to the SQL return
910 type represented by this SQL function.
911
912 This datatype may be configured when generating a
913 :class:`_functions.Function` object by passing the
914 :paramref:`_functions.Function.type_` parameter, e.g.::
915
916 >>> select(func.lower("some VALUE", type_=String))
917
918 The small number of built-in classes of :class:`_functions.Function` come
919 with a built-in datatype that's appropriate to the class of function and
920 its arguments. For functions that aren't known, the type defaults to the
921 "null type".
922
923 """
924
925 @util.deprecated_params(
926 bind=(
927 "2.0",
928 "The :paramref:`_sql.text.bind` argument is deprecated and "
929 "will be removed in SQLAlchemy 2.0.",
930 ),
931 )
932 def __init__(self, name, *clauses, **kw):
933 """Construct a :class:`.Function`.
934
935 The :data:`.func` construct is normally used to construct
936 new :class:`.Function` instances.
937
938 """
939 self.packagenames = kw.pop("packagenames", None) or ()
940 self.name = name
941
942 self._bind = self._get_bind(kw)
943 self.type = sqltypes.to_instance(kw.get("type_", None))
944
945 FunctionElement.__init__(self, *clauses, **kw)
946
947 def _get_bind(self, kw):
948 if "bind" in kw:
949 util.warn_deprecated_20(
950 "The Function.bind argument is deprecated and "
951 "will be removed in SQLAlchemy 2.0.",
952 )
953 return kw["bind"]
954
955 def _bind_param(self, operator, obj, type_=None, **kw):
956 return BindParameter(
957 self.name,
958 obj,
959 _compared_to_operator=operator,
960 _compared_to_type=self.type,
961 type_=type_,
962 unique=True,
963 **kw
964 )
965
966
967class _GenericMeta(TraversibleType):
968 def __init__(cls, clsname, bases, clsdict):
969 if annotation.Annotated not in cls.__mro__:
970 cls.name = name = clsdict.get("name", clsname)
971 cls.identifier = identifier = clsdict.get("identifier", name)
972 package = clsdict.pop("package", "_default")
973 # legacy
974 if "__return_type__" in clsdict:
975 cls.type = clsdict["__return_type__"]
976
977 # Check _register attribute status
978 cls._register = getattr(cls, "_register", True)
979
980 # Register the function if required
981 if cls._register:
982 register_function(identifier, cls, package)
983 else:
984 # Set _register to True to register child classes by default
985 cls._register = True
986
987 super(_GenericMeta, cls).__init__(clsname, bases, clsdict)
988
989
990class GenericFunction(util.with_metaclass(_GenericMeta, Function)):
991 """Define a 'generic' function.
992
993 A generic function is a pre-established :class:`.Function`
994 class that is instantiated automatically when called
995 by name from the :data:`.func` attribute. Note that
996 calling any name from :data:`.func` has the effect that
997 a new :class:`.Function` instance is created automatically,
998 given that name. The primary use case for defining
999 a :class:`.GenericFunction` class is so that a function
1000 of a particular name may be given a fixed return type.
1001 It can also include custom argument parsing schemes as well
1002 as additional methods.
1003
1004 Subclasses of :class:`.GenericFunction` are automatically
1005 registered under the name of the class. For
1006 example, a user-defined function ``as_utc()`` would
1007 be available immediately::
1008
1009 from sqlalchemy.sql.functions import GenericFunction
1010 from sqlalchemy.types import DateTime
1011
1012 class as_utc(GenericFunction):
1013 type = DateTime
1014 inherit_cache = True
1015
1016 print(select(func.as_utc()))
1017
1018 User-defined generic functions can be organized into
1019 packages by specifying the "package" attribute when defining
1020 :class:`.GenericFunction`. Third party libraries
1021 containing many functions may want to use this in order
1022 to avoid name conflicts with other systems. For example,
1023 if our ``as_utc()`` function were part of a package
1024 "time"::
1025
1026 class as_utc(GenericFunction):
1027 type = DateTime
1028 package = "time"
1029 inherit_cache = True
1030
1031 The above function would be available from :data:`.func`
1032 using the package name ``time``::
1033
1034 print(select(func.time.as_utc()))
1035
1036 A final option is to allow the function to be accessed
1037 from one name in :data:`.func` but to render as a different name.
1038 The ``identifier`` attribute will override the name used to
1039 access the function as loaded from :data:`.func`, but will retain
1040 the usage of ``name`` as the rendered name::
1041
1042 class GeoBuffer(GenericFunction):
1043 type = Geometry
1044 package = "geo"
1045 name = "ST_Buffer"
1046 identifier = "buffer"
1047 inherit_cache = True
1048
1049 The above function will render as follows::
1050
1051 >>> print(func.geo.buffer())
1052 ST_Buffer()
1053
1054 The name will be rendered as is, however without quoting unless the name
1055 contains special characters that require quoting. To force quoting
1056 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
1057 construct::
1058
1059 from sqlalchemy.sql import quoted_name
1060
1061 class GeoBuffer(GenericFunction):
1062 type = Geometry
1063 package = "geo"
1064 name = quoted_name("ST_Buffer", True)
1065 identifier = "buffer"
1066 inherit_cache = True
1067
1068 The above function will render as::
1069
1070 >>> print(func.geo.buffer())
1071 "ST_Buffer"()
1072
1073 .. versionadded:: 1.3.13 The :class:`.quoted_name` construct is now
1074 recognized for quoting when used with the "name" attribute of the
1075 object, so that quoting can be forced on or off for the function
1076 name.
1077
1078
1079 """
1080
1081 coerce_arguments = True
1082 _register = False
1083 inherit_cache = True
1084
1085 def __init__(self, *args, **kwargs):
1086 parsed_args = kwargs.pop("_parsed_args", None)
1087 if parsed_args is None:
1088 parsed_args = [
1089 coercions.expect(
1090 roles.ExpressionElementRole,
1091 c,
1092 name=self.name,
1093 apply_propagate_attrs=self,
1094 )
1095 for c in args
1096 ]
1097 self._has_args = self._has_args or bool(parsed_args)
1098 self.packagenames = ()
1099 self._bind = self._get_bind(kwargs)
1100 self.clause_expr = ClauseList(
1101 operator=operators.comma_op, group_contents=True, *parsed_args
1102 ).self_group()
1103 self.type = sqltypes.to_instance(
1104 kwargs.pop("type_", None) or getattr(self, "type", None)
1105 )
1106
1107
1108register_function("cast", Cast)
1109register_function("extract", Extract)
1110
1111
1112class next_value(GenericFunction):
1113 """Represent the 'next value', given a :class:`.Sequence`
1114 as its single argument.
1115
1116 Compiles into the appropriate function on each backend,
1117 or will raise NotImplementedError if used on a backend
1118 that does not provide support for sequences.
1119
1120 """
1121
1122 type = sqltypes.Integer()
1123 name = "next_value"
1124
1125 _traverse_internals = [
1126 ("sequence", InternalTraversal.dp_named_ddl_element)
1127 ]
1128
1129 def __init__(self, seq, **kw):
1130 assert isinstance(
1131 seq, schema.Sequence
1132 ), "next_value() accepts a Sequence object as input."
1133 self._bind = self._get_bind(kw)
1134 self.sequence = seq
1135 self.type = sqltypes.to_instance(
1136 seq.data_type or getattr(self, "type", None)
1137 )
1138
1139 def compare(self, other, **kw):
1140 return (
1141 isinstance(other, next_value)
1142 and self.sequence.name == other.sequence.name
1143 )
1144
1145 @property
1146 def _from_objects(self):
1147 return []
1148
1149
1150class AnsiFunction(GenericFunction):
1151 """Define a function in "ansi" format, which doesn't render parenthesis."""
1152
1153 inherit_cache = True
1154
1155 def __init__(self, *args, **kwargs):
1156 GenericFunction.__init__(self, *args, **kwargs)
1157
1158
1159class ReturnTypeFromArgs(GenericFunction):
1160 """Define a function whose return type is the same as its arguments."""
1161
1162 inherit_cache = True
1163
1164 def __init__(self, *args, **kwargs):
1165 args = [
1166 coercions.expect(
1167 roles.ExpressionElementRole,
1168 c,
1169 name=self.name,
1170 apply_propagate_attrs=self,
1171 )
1172 for c in args
1173 ]
1174 kwargs.setdefault("type_", _type_from_args(args))
1175 kwargs["_parsed_args"] = args
1176 super(ReturnTypeFromArgs, self).__init__(*args, **kwargs)
1177
1178
1179class coalesce(ReturnTypeFromArgs):
1180 _has_args = True
1181 inherit_cache = True
1182
1183
1184class max(ReturnTypeFromArgs): # noqa: A001
1185 """The SQL MAX() aggregate function."""
1186
1187 inherit_cache = True
1188
1189
1190class min(ReturnTypeFromArgs): # noqa: A001
1191 """The SQL MIN() aggregate function."""
1192
1193 inherit_cache = True
1194
1195
1196class sum(ReturnTypeFromArgs): # noqa: A001
1197 """The SQL SUM() aggregate function."""
1198
1199 inherit_cache = True
1200
1201
1202class now(GenericFunction):
1203 """The SQL now() datetime function.
1204
1205 SQLAlchemy dialects will usually render this particular function
1206 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1207
1208 """
1209
1210 type = sqltypes.DateTime
1211 inherit_cache = True
1212
1213
1214class concat(GenericFunction):
1215 """The SQL CONCAT() function, which concatenates strings.
1216
1217 E.g.::
1218
1219 >>> print(select(func.concat('a', 'b')))
1220 SELECT concat(:concat_2, :concat_3) AS concat_1
1221
1222 String concatenation in SQLAlchemy is more commonly available using the
1223 Python ``+`` operator with string datatypes, which will render a
1224 backend-specific concatenation operator, such as ::
1225
1226 >>> print(select(literal("a") + "b"))
1227 SELECT :param_1 || :param_2 AS anon_1
1228
1229
1230 """
1231
1232 type = sqltypes.String
1233 inherit_cache = True
1234
1235
1236class char_length(GenericFunction):
1237 """The CHAR_LENGTH() SQL function."""
1238
1239 type = sqltypes.Integer
1240 inherit_cache = True
1241
1242 def __init__(self, arg, **kwargs):
1243 GenericFunction.__init__(self, arg, **kwargs)
1244
1245
1246class random(GenericFunction):
1247 """The RANDOM() SQL function."""
1248
1249 _has_args = True
1250 inherit_cache = True
1251
1252
1253class count(GenericFunction):
1254 r"""The ANSI COUNT aggregate function. With no arguments,
1255 emits COUNT \*.
1256
1257 E.g.::
1258
1259 from sqlalchemy import func
1260 from sqlalchemy import select
1261 from sqlalchemy import table, column
1262
1263 my_table = table('some_table', column('id'))
1264
1265 stmt = select(func.count()).select_from(my_table)
1266
1267 Executing ``stmt`` would emit::
1268
1269 SELECT count(*) AS count_1
1270 FROM some_table
1271
1272
1273 """
1274 type = sqltypes.Integer
1275 inherit_cache = True
1276
1277 def __init__(self, expression=None, **kwargs):
1278 if expression is None:
1279 expression = literal_column("*")
1280 super(count, self).__init__(expression, **kwargs)
1281
1282
1283class current_date(AnsiFunction):
1284 """The CURRENT_DATE() SQL function."""
1285
1286 type = sqltypes.Date
1287 inherit_cache = True
1288
1289
1290class current_time(AnsiFunction):
1291 """The CURRENT_TIME() SQL function."""
1292
1293 type = sqltypes.Time
1294 inherit_cache = True
1295
1296
1297class current_timestamp(AnsiFunction):
1298 """The CURRENT_TIMESTAMP() SQL function."""
1299
1300 type = sqltypes.DateTime
1301 inherit_cache = True
1302
1303
1304class current_user(AnsiFunction):
1305 """The CURRENT_USER() SQL function."""
1306
1307 type = sqltypes.String
1308 inherit_cache = True
1309
1310
1311class localtime(AnsiFunction):
1312 """The localtime() SQL function."""
1313
1314 type = sqltypes.DateTime
1315 inherit_cache = True
1316
1317
1318class localtimestamp(AnsiFunction):
1319 """The localtimestamp() SQL function."""
1320
1321 type = sqltypes.DateTime
1322 inherit_cache = True
1323
1324
1325class session_user(AnsiFunction):
1326 """The SESSION_USER() SQL function."""
1327
1328 type = sqltypes.String
1329 inherit_cache = True
1330
1331
1332class sysdate(AnsiFunction):
1333 """The SYSDATE() SQL function."""
1334
1335 type = sqltypes.DateTime
1336 inherit_cache = True
1337
1338
1339class user(AnsiFunction):
1340 """The USER() SQL function."""
1341
1342 type = sqltypes.String
1343 inherit_cache = True
1344
1345
1346class array_agg(GenericFunction):
1347 """Support for the ARRAY_AGG function.
1348
1349 The ``func.array_agg(expr)`` construct returns an expression of
1350 type :class:`_types.ARRAY`.
1351
1352 e.g.::
1353
1354 stmt = select(func.array_agg(table.c.values)[2:5])
1355
1356 .. versionadded:: 1.1
1357
1358 .. seealso::
1359
1360 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1361 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1362 added.
1363
1364 """
1365
1366 type = sqltypes.ARRAY
1367 inherit_cache = True
1368
1369 def __init__(self, *args, **kwargs):
1370 args = [
1371 coercions.expect(
1372 roles.ExpressionElementRole, c, apply_propagate_attrs=self
1373 )
1374 for c in args
1375 ]
1376
1377 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1378 if "type_" not in kwargs:
1379
1380 type_from_args = _type_from_args(args)
1381 if isinstance(type_from_args, sqltypes.ARRAY):
1382 kwargs["type_"] = type_from_args
1383 else:
1384 kwargs["type_"] = default_array_type(type_from_args)
1385 kwargs["_parsed_args"] = args
1386 super(array_agg, self).__init__(*args, **kwargs)
1387
1388
1389class OrderedSetAgg(GenericFunction):
1390 """Define a function where the return type is based on the sort
1391 expression type as defined by the expression passed to the
1392 :meth:`.FunctionElement.within_group` method."""
1393
1394 array_for_multi_clause = False
1395 inherit_cache = True
1396
1397 def within_group_type(self, within_group):
1398 func_clauses = self.clause_expr.element
1399 order_by = sqlutil.unwrap_order_by(within_group.order_by)
1400 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
1401 return sqltypes.ARRAY(order_by[0].type)
1402 else:
1403 return order_by[0].type
1404
1405
1406class mode(OrderedSetAgg):
1407 """Implement the ``mode`` ordered-set aggregate function.
1408
1409 This function must be used with the :meth:`.FunctionElement.within_group`
1410 modifier to supply a sort expression to operate upon.
1411
1412 The return type of this function is the same as the sort expression.
1413
1414 .. versionadded:: 1.1
1415
1416 """
1417
1418 inherit_cache = True
1419
1420
1421class percentile_cont(OrderedSetAgg):
1422 """Implement the ``percentile_cont`` ordered-set aggregate function.
1423
1424 This function must be used with the :meth:`.FunctionElement.within_group`
1425 modifier to supply a sort expression to operate upon.
1426
1427 The return type of this function is the same as the sort expression,
1428 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1429 expression's type.
1430
1431 .. versionadded:: 1.1
1432
1433 """
1434
1435 array_for_multi_clause = True
1436 inherit_cache = True
1437
1438
1439class percentile_disc(OrderedSetAgg):
1440 """Implement the ``percentile_disc`` ordered-set aggregate function.
1441
1442 This function must be used with the :meth:`.FunctionElement.within_group`
1443 modifier to supply a sort expression to operate upon.
1444
1445 The return type of this function is the same as the sort expression,
1446 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1447 expression's type.
1448
1449 .. versionadded:: 1.1
1450
1451 """
1452
1453 array_for_multi_clause = True
1454 inherit_cache = True
1455
1456
1457class rank(GenericFunction):
1458 """Implement the ``rank`` hypothetical-set aggregate function.
1459
1460 This function must be used with the :meth:`.FunctionElement.within_group`
1461 modifier to supply a sort expression to operate upon.
1462
1463 The return type of this function is :class:`.Integer`.
1464
1465 .. versionadded:: 1.1
1466
1467 """
1468
1469 type = sqltypes.Integer()
1470 inherit_cache = True
1471
1472
1473class dense_rank(GenericFunction):
1474 """Implement the ``dense_rank`` hypothetical-set aggregate function.
1475
1476 This function must be used with the :meth:`.FunctionElement.within_group`
1477 modifier to supply a sort expression to operate upon.
1478
1479 The return type of this function is :class:`.Integer`.
1480
1481 .. versionadded:: 1.1
1482
1483 """
1484
1485 type = sqltypes.Integer()
1486 inherit_cache = True
1487
1488
1489class percent_rank(GenericFunction):
1490 """Implement the ``percent_rank`` hypothetical-set aggregate function.
1491
1492 This function must be used with the :meth:`.FunctionElement.within_group`
1493 modifier to supply a sort expression to operate upon.
1494
1495 The return type of this function is :class:`.Numeric`.
1496
1497 .. versionadded:: 1.1
1498
1499 """
1500
1501 type = sqltypes.Numeric()
1502 inherit_cache = True
1503
1504
1505class cume_dist(GenericFunction):
1506 """Implement the ``cume_dist`` hypothetical-set aggregate function.
1507
1508 This function must be used with the :meth:`.FunctionElement.within_group`
1509 modifier to supply a sort expression to operate upon.
1510
1511 The return type of this function is :class:`.Numeric`.
1512
1513 .. versionadded:: 1.1
1514
1515 """
1516
1517 type = sqltypes.Numeric()
1518 inherit_cache = True
1519
1520
1521class cube(GenericFunction):
1522 r"""Implement the ``CUBE`` grouping operation.
1523
1524 This function is used as part of the GROUP BY of a statement,
1525 e.g. :meth:`_expression.Select.group_by`::
1526
1527 stmt = select(
1528 func.sum(table.c.value), table.c.col_1, table.c.col_2
1529 ).group_by(func.cube(table.c.col_1, table.c.col_2))
1530
1531 .. versionadded:: 1.2
1532
1533 """
1534 _has_args = True
1535 inherit_cache = True
1536
1537
1538class rollup(GenericFunction):
1539 r"""Implement the ``ROLLUP`` grouping operation.
1540
1541 This function is used as part of the GROUP BY of a statement,
1542 e.g. :meth:`_expression.Select.group_by`::
1543
1544 stmt = select(
1545 func.sum(table.c.value), table.c.col_1, table.c.col_2
1546 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
1547
1548 .. versionadded:: 1.2
1549
1550 """
1551 _has_args = True
1552 inherit_cache = True
1553
1554
1555class grouping_sets(GenericFunction):
1556 r"""Implement the ``GROUPING SETS`` grouping operation.
1557
1558 This function is used as part of the GROUP BY of a statement,
1559 e.g. :meth:`_expression.Select.group_by`::
1560
1561 stmt = select(
1562 func.sum(table.c.value), table.c.col_1, table.c.col_2
1563 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
1564
1565 In order to group by multiple sets, use the :func:`.tuple_` construct::
1566
1567 from sqlalchemy import tuple_
1568
1569 stmt = select(
1570 func.sum(table.c.value),
1571 table.c.col_1, table.c.col_2,
1572 table.c.col_3
1573 ).group_by(
1574 func.grouping_sets(
1575 tuple_(table.c.col_1, table.c.col_2),
1576 tuple_(table.c.value, table.c.col_3),
1577 )
1578 )
1579
1580
1581 .. versionadded:: 1.2
1582
1583 """
1584 _has_args = True
1585 inherit_cache = True