Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/functions.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8"""SQL function API, factories, and built-in functions.
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
44_registry = util.defaultdict(dict)
47def register_function(identifier, fn, package="_default"):
48 """Associate a callable with a particular func. name.
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).
55 """
56 reg = _registry[package]
58 identifier = util.text_type(identifier).lower()
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
69class FunctionElement(Executable, ColumnElement, FromClause, Generative):
70 """Base for SQL function-oriented constructs.
72 .. seealso::
74 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
76 :class:`.Function` - named SQL function.
78 :data:`.func` - namespace which produces registered or ad-hoc
79 :class:`.Function` instances.
81 :class:`.GenericFunction` - allows creation of registered function
82 types.
84 """
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 ]
92 packagenames = ()
94 _has_args = False
95 _with_ordinality = False
96 _table_value_type = None
98 def __init__(self, *clauses, **kwargs):
99 r"""Construct a :class:`.FunctionElement`.
101 :param \*clauses: list of column expressions that form the arguments
102 of the SQL function call.
104 :param \**kwargs: additional kwargs are typically consumed by
105 subclasses.
107 .. seealso::
109 :data:`.func`
111 :class:`.Function`
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()
128 _non_anon_label = None
130 @property
131 def _proxy_key(self):
132 return super(FunctionElement, self)._proxy_key or getattr(
133 self, "name", None
134 )
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 )
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.
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.
153 E.g.::
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
160 .. versionadded:: 1.4.0b2
162 .. seealso::
164 :meth:`_functions.FunctionElement.table_valued`
166 :meth:`_functions.FunctionElement.alias`
168 :meth:`_functions.FunctionElement.column_valued`
170 """ # noqa: E501
172 return ScalarFunctionColumn(self, name, type_)
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.
178 e.g.::
180 >>> fn = (
181 ... func.generate_series(1, 5).
182 ... table_valued("value", "start", "stop", "step")
183 ... )
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
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
194 A WITH ORDINALITY expression may be generated by passing the keyword
195 argument "with_ordinality"::
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
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.
207 :param name: optional name to assign to the alias name that's generated.
208 If omitted, a unique anonymizing name is used.
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`.
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()``.
220 .. versionadded:: 1.4.33
222 .. versionadded:: 1.4.0b2
225 .. seealso::
227 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
229 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation
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
235 :meth:`_functions.FunctionElement.column_valued`
237 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias
238 using a derived column clause, e.g. ``AS name(col1, col2, ...)``
240 """ # noqa: 501
242 new_func = self._generate()
244 with_ordinality = kw.pop("with_ordinality", None)
245 joins_implicitly = kw.pop("joins_implicitly", None)
246 name = kw.pop("name", None)
248 if with_ordinality:
249 expr += (with_ordinality,)
250 new_func._with_ordinality = True
252 new_func.type = new_func._table_value_type = sqltypes.TableValueType(
253 *expr
254 )
256 return new_func.alias(name=name, joins_implicitly=joins_implicitly)
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.
262 E.g.::
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
270 This is shorthand for::
272 gs = func.generate_series(1, 5, -1).alias().column
274 :param name: optional name to assign to the alias name that's generated.
275 If omitted, a unique anonymizing name is used.
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()``.
283 .. versionadded:: 1.4.46
285 .. seealso::
287 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
289 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation
291 :meth:`_functions.FunctionElement.table_valued`
293 """ # noqa: 501
295 return self.alias(name=name, joins_implicitly=joins_implicitly).column
297 @property
298 def columns(self):
299 r"""The set of columns exported by this :class:`.FunctionElement`.
301 This is a placeholder collection that allows the function to be
302 placed in the FROM clause of a statement::
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()
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.
313 .. seealso::
315 :meth:`_functions.FunctionElement.table_valued` - generates table-valued
316 SQL function expressions.
318 """ # noqa: E501
320 return ColumnCollection(
321 columns=[(col.key, col) for col in self._all_selected_columns]
322 )
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)]
331 return cols
333 @property
334 def exported_columns(self):
335 return self.columns
337 @HasMemoized.memoized_attribute
338 def clauses(self):
339 """Return the underlying :class:`.ClauseList` which contains
340 the arguments for this :class:`.FunctionElement`.
342 """
343 return self.clause_expr.element
345 def over(self, partition_by=None, order_by=None, rows=None, range_=None):
346 """Produce an OVER clause against this function.
348 Used against aggregate or so-called "window" functions,
349 for database backends that support window functions.
351 The expression::
353 func.row_number().over(order_by='x')
355 is shorthand for::
357 from sqlalchemy import over
358 over(func.row_number(), order_by='x')
360 See :func:`_expression.over` for a full description.
362 .. seealso::
364 :func:`_expression.over`
366 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
368 """
369 return Over(
370 self,
371 partition_by=partition_by,
372 order_by=order_by,
373 rows=rows,
374 range_=range_,
375 )
377 def within_group(self, *order_by):
378 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
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.
384 See :func:`_expression.within_group` for a full description.
386 .. versionadded:: 1.1
389 .. seealso::
391 :ref:`tutorial_functions_within_group` -
392 in the :ref:`unified_tutorial`
395 """
396 return WithinGroup(self, *order_by)
398 def filter(self, *criterion):
399 """Produce a FILTER clause against this function.
401 Used against aggregate and window functions,
402 for database backends that support the "FILTER" clause.
404 The expression::
406 func.count(1).filter(True)
408 is shorthand for::
410 from sqlalchemy import funcfilter
411 funcfilter(func.count(1), True)
413 .. versionadded:: 1.0.0
415 .. seealso::
417 :ref:`tutorial_functions_within_group` -
418 in the :ref:`unified_tutorial`
420 :class:`.FunctionFilter`
422 :func:`.funcfilter`
425 """
426 if not criterion:
427 return self
428 return FunctionFilter(self, *criterion)
430 def as_comparison(self, left_index, right_index):
431 """Interpret this expression as a boolean comparison between two
432 values.
434 This method is used for an ORM use case described at
435 :ref:`relationship_custom_operator_sql_function`.
437 A hypothetical SQL function "is_equal()" which compares to values
438 for equality would be written in the Core expression language as::
440 expr = func.is_equal("a", "b")
442 If "is_equal()" above is comparing "a" and "b" for equality, the
443 :meth:`.FunctionElement.as_comparison` method would be invoked as::
445 expr = func.is_equal("a", "b").as_comparison(1, 2)
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.
450 This would create a :class:`.BinaryExpression` that is equivalent to::
452 BinaryExpression("a", "b", operator=op.eq)
454 However, at the SQL level it would still render as
455 "is_equal('a', 'b')".
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`.
464 An ORM example is as follows::
466 class Venue(Base):
467 __tablename__ = 'venue'
468 id = Column(Integer, primary_key=True)
469 name = Column(String)
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 )
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".
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.
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.
494 .. versionadded:: 1.3
496 .. seealso::
498 :ref:`relationship_custom_operator_sql_function` -
499 example use within the ORM
501 """
502 return FunctionAsBinary(self, left_index, right_index)
504 @property
505 def _from_objects(self):
506 return self.clauses._from_objects
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.
513 Returns None by default, in which case the function's normal ``.type``
514 is used.
516 """
518 return None
520 def alias(self, name=None, joins_implicitly=False):
521 r"""Produce a :class:`_expression.Alias` construct against this
522 :class:`.FunctionElement`.
524 .. tip::
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`.
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.
540 For a full table-valued expression, use the
541 :meth:`_functions.FunctionElement.table_valued` method first to
542 establish named columns.
544 e.g.::
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
552 The :meth:`_functions.FunctionElement.column_valued` method provides
553 a shortcut for the above pattern::
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
560 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor
562 :param name: alias name, will be rendered as ``AS <name>`` in the
563 FROM clause
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()``.
571 .. versionadded:: 1.4.33
573 .. seealso::
575 :ref:`tutorial_functions_table_valued` -
576 in the :ref:`unified_tutorial`
578 :meth:`_functions.FunctionElement.table_valued`
580 :meth:`_functions.FunctionElement.scalar_table_valued`
582 :meth:`_functions.FunctionElement.column_valued`
585 """
587 return TableValuedAlias._construct(
588 self,
589 name,
590 table_value_type=self.type,
591 joins_implicitly=joins_implicitly,
592 )
594 def select(self):
595 """Produce a :func:`_expression.select` construct
596 against this :class:`.FunctionElement`.
598 This is shorthand for::
600 s = select(function_element)
602 """
603 s = Select._create_select(self)
604 if self._execution_options:
605 s = s.execution_options(**self._execution_options)
606 return s
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.
620 This first calls :meth:`~.FunctionElement.select` to
621 produce a SELECT construct.
623 Note that :class:`.FunctionElement` can be passed to
624 the :meth:`.Connectable.scalar` method of :class:`_engine.Connection`
625 or :class:`_engine.Engine`.
627 """
628 return self.select().execute().scalar()
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'.
642 This first calls :meth:`~.FunctionElement.select` to
643 produce a SELECT construct.
645 Note that :class:`.FunctionElement` can be passed to
646 the :meth:`.Connectable.execute` method of :class:`_engine.Connection`
647 or :class:`_engine.Engine`.
649 """
650 return self.select().execute()
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 )
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)
675 @property
676 def entity_namespace(self):
677 """overrides FromClause.entity_namespace as functions are generally
678 column expressions and not FromClauses.
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)
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 ]
694 def _gen_cache_key(self, anon_map, bindparams):
695 return ColumnElement._gen_cache_key(self, anon_map, bindparams)
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
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 = {}
708 @property
709 def left(self):
710 return self.sql_function.clauses.clauses[self.left_index - 1]
712 @left.setter
713 def left(self, value):
714 self.sql_function.clauses.clauses[self.left_index - 1] = value
716 @property
717 def right(self):
718 return self.sql_function.clauses.clauses[self.right_index - 1]
720 @right.setter
721 def right(self, value):
722 self.sql_function.clauses.clauses[self.right_index - 1] = value
725class ScalarFunctionColumn(NamedColumn):
726 __visit_name__ = "scalar_function_column"
728 _traverse_internals = [
729 ("name", InternalTraversal.dp_anon_name),
730 ("type", InternalTraversal.dp_type),
731 ("fn", InternalTraversal.dp_clauseelement),
732 ]
734 is_literal = False
735 table = None
737 def __init__(self, fn, name, type_=None):
738 self.fn = fn
739 self.name = name
740 self.type = sqltypes.to_instance(type_)
743class _FunctionGenerator(object):
744 """Generate SQL function expressions.
746 :data:`.func` is a special object instance which generates SQL
747 functions based on name-based attributes, e.g.::
749 >>> print(func.count(1))
750 count(:param_1)
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::
755 >>> print(select(func.count(table.c.id)))
756 SELECT count(sometable.id) FROM sometable
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::
763 >>> print(func.current_timestamp())
764 CURRENT_TIMESTAMP
766 To call functions which are present in dot-separated packages,
767 specify them in the same manner::
769 >>> print(func.stats.yield_curve(5, 10))
770 stats.yield_curve(:yield_curve_1, :yield_curve_2)
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:
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)
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::
790 print(connection.execute(func.current_timestamp()).scalar())
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.
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`.
802 .. note::
804 The :data:`.func` construct has only limited support for calling
805 standalone "stored procedures", especially those with special
806 parameterization concerns.
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.
812 .. seealso::
814 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
816 :class:`.Function`
818 """
820 def __init__(self, **opts):
821 self.__names = []
822 self.opts = opts
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)
832 elif name.endswith("_"):
833 name = name[0:-1]
834 f = _FunctionGenerator(**self.opts)
835 f.__names = list(self.__names) + [name]
836 return f
838 def __call__(self, *c, **kwargs):
839 o = self.opts.copy()
840 o.update(kwargs)
842 tokens = len(self.__names)
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
851 if package is not None:
852 func = _registry[package].get(fname.lower())
853 if func is not None:
854 return func(*c, **o)
856 return Function(
857 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o
858 )
861func = _FunctionGenerator()
862func.__doc__ = _FunctionGenerator.__doc__
864modifier = _FunctionGenerator(group=False)
867class Function(FunctionElement):
868 r"""Describe a named SQL function.
870 The :class:`.Function` object is typically generated from the
871 :data:`.func` generation object.
874 :param \*clauses: list of column expressions that form the arguments
875 of the SQL function call.
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.
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.::
886 func.mypackage.some_function(col1, col2)
888 .. seealso::
890 :ref:`tutorial_functions` - in the :ref:`unified_tutorial`
892 :data:`.func` - namespace which produces registered or ad-hoc
893 :class:`.Function` instances.
895 :class:`.GenericFunction` - allows creation of registered function
896 types.
898 """
900 __visit_name__ = "function"
902 _traverse_internals = FunctionElement._traverse_internals + [
903 ("packagenames", InternalTraversal.dp_plain_obj),
904 ("name", InternalTraversal.dp_string),
905 ("type", InternalTraversal.dp_type),
906 ]
908 type = sqltypes.NULLTYPE
909 """A :class:`_types.TypeEngine` object which refers to the SQL return
910 type represented by this SQL function.
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.::
916 >>> select(func.lower("some VALUE", type_=String))
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".
923 """
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`.
935 The :data:`.func` construct is normally used to construct
936 new :class:`.Function` instances.
938 """
939 self.packagenames = kw.pop("packagenames", None) or ()
940 self.name = name
942 self._bind = self._get_bind(kw)
943 self.type = sqltypes.to_instance(kw.get("type_", None))
945 FunctionElement.__init__(self, *clauses, **kw)
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"]
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 )
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__"]
977 # Check _register attribute status
978 cls._register = getattr(cls, "_register", True)
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
987 super(_GenericMeta, cls).__init__(clsname, bases, clsdict)
990class GenericFunction(util.with_metaclass(_GenericMeta, Function)):
991 """Define a 'generic' function.
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.
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::
1009 from sqlalchemy.sql.functions import GenericFunction
1010 from sqlalchemy.types import DateTime
1012 class as_utc(GenericFunction):
1013 type = DateTime
1014 inherit_cache = True
1016 print(select(func.as_utc()))
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"::
1026 class as_utc(GenericFunction):
1027 type = DateTime
1028 package = "time"
1029 inherit_cache = True
1031 The above function would be available from :data:`.func`
1032 using the package name ``time``::
1034 print(select(func.time.as_utc()))
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::
1042 class GeoBuffer(GenericFunction):
1043 type = Geometry
1044 package = "geo"
1045 name = "ST_Buffer"
1046 identifier = "buffer"
1047 inherit_cache = True
1049 The above function will render as follows::
1051 >>> print(func.geo.buffer())
1052 ST_Buffer()
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::
1059 from sqlalchemy.sql import quoted_name
1061 class GeoBuffer(GenericFunction):
1062 type = Geometry
1063 package = "geo"
1064 name = quoted_name("ST_Buffer", True)
1065 identifier = "buffer"
1066 inherit_cache = True
1068 The above function will render as::
1070 >>> print(func.geo.buffer())
1071 "ST_Buffer"()
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.
1079 """
1081 coerce_arguments = True
1082 _register = False
1083 inherit_cache = True
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 )
1108register_function("cast", Cast)
1109register_function("extract", Extract)
1112class next_value(GenericFunction):
1113 """Represent the 'next value', given a :class:`.Sequence`
1114 as its single argument.
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.
1120 """
1122 type = sqltypes.Integer()
1123 name = "next_value"
1125 _traverse_internals = [
1126 ("sequence", InternalTraversal.dp_named_ddl_element)
1127 ]
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 )
1139 def compare(self, other, **kw):
1140 return (
1141 isinstance(other, next_value)
1142 and self.sequence.name == other.sequence.name
1143 )
1145 @property
1146 def _from_objects(self):
1147 return []
1150class AnsiFunction(GenericFunction):
1151 """Define a function in "ansi" format, which doesn't render parenthesis."""
1153 inherit_cache = True
1155 def __init__(self, *args, **kwargs):
1156 GenericFunction.__init__(self, *args, **kwargs)
1159class ReturnTypeFromArgs(GenericFunction):
1160 """Define a function whose return type is the same as its arguments."""
1162 inherit_cache = True
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)
1179class coalesce(ReturnTypeFromArgs):
1180 _has_args = True
1181 inherit_cache = True
1184class max(ReturnTypeFromArgs): # noqa: A001
1185 """The SQL MAX() aggregate function."""
1187 inherit_cache = True
1190class min(ReturnTypeFromArgs): # noqa: A001
1191 """The SQL MIN() aggregate function."""
1193 inherit_cache = True
1196class sum(ReturnTypeFromArgs): # noqa: A001
1197 """The SQL SUM() aggregate function."""
1199 inherit_cache = True
1202class now(GenericFunction):
1203 """The SQL now() datetime function.
1205 SQLAlchemy dialects will usually render this particular function
1206 in a backend-specific way, such as rendering it as ``CURRENT_TIMESTAMP``.
1208 """
1210 type = sqltypes.DateTime
1211 inherit_cache = True
1214class concat(GenericFunction):
1215 """The SQL CONCAT() function, which concatenates strings.
1217 E.g.::
1219 >>> print(select(func.concat('a', 'b')))
1220 SELECT concat(:concat_2, :concat_3) AS concat_1
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 ::
1226 >>> print(select(literal("a") + "b"))
1227 SELECT :param_1 || :param_2 AS anon_1
1230 """
1232 type = sqltypes.String
1233 inherit_cache = True
1236class char_length(GenericFunction):
1237 """The CHAR_LENGTH() SQL function."""
1239 type = sqltypes.Integer
1240 inherit_cache = True
1242 def __init__(self, arg, **kwargs):
1243 GenericFunction.__init__(self, arg, **kwargs)
1246class random(GenericFunction):
1247 """The RANDOM() SQL function."""
1249 _has_args = True
1250 inherit_cache = True
1253class count(GenericFunction):
1254 r"""The ANSI COUNT aggregate function. With no arguments,
1255 emits COUNT \*.
1257 E.g.::
1259 from sqlalchemy import func
1260 from sqlalchemy import select
1261 from sqlalchemy import table, column
1263 my_table = table('some_table', column('id'))
1265 stmt = select(func.count()).select_from(my_table)
1267 Executing ``stmt`` would emit::
1269 SELECT count(*) AS count_1
1270 FROM some_table
1273 """
1274 type = sqltypes.Integer
1275 inherit_cache = True
1277 def __init__(self, expression=None, **kwargs):
1278 if expression is None:
1279 expression = literal_column("*")
1280 super(count, self).__init__(expression, **kwargs)
1283class current_date(AnsiFunction):
1284 """The CURRENT_DATE() SQL function."""
1286 type = sqltypes.Date
1287 inherit_cache = True
1290class current_time(AnsiFunction):
1291 """The CURRENT_TIME() SQL function."""
1293 type = sqltypes.Time
1294 inherit_cache = True
1297class current_timestamp(AnsiFunction):
1298 """The CURRENT_TIMESTAMP() SQL function."""
1300 type = sqltypes.DateTime
1301 inherit_cache = True
1304class current_user(AnsiFunction):
1305 """The CURRENT_USER() SQL function."""
1307 type = sqltypes.String
1308 inherit_cache = True
1311class localtime(AnsiFunction):
1312 """The localtime() SQL function."""
1314 type = sqltypes.DateTime
1315 inherit_cache = True
1318class localtimestamp(AnsiFunction):
1319 """The localtimestamp() SQL function."""
1321 type = sqltypes.DateTime
1322 inherit_cache = True
1325class session_user(AnsiFunction):
1326 """The SESSION_USER() SQL function."""
1328 type = sqltypes.String
1329 inherit_cache = True
1332class sysdate(AnsiFunction):
1333 """The SYSDATE() SQL function."""
1335 type = sqltypes.DateTime
1336 inherit_cache = True
1339class user(AnsiFunction):
1340 """The USER() SQL function."""
1342 type = sqltypes.String
1343 inherit_cache = True
1346class array_agg(GenericFunction):
1347 """Support for the ARRAY_AGG function.
1349 The ``func.array_agg(expr)`` construct returns an expression of
1350 type :class:`_types.ARRAY`.
1352 e.g.::
1354 stmt = select(func.array_agg(table.c.values)[2:5])
1356 .. versionadded:: 1.1
1358 .. seealso::
1360 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
1361 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
1362 added.
1364 """
1366 type = sqltypes.ARRAY
1367 inherit_cache = True
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 ]
1377 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
1378 if "type_" not in kwargs:
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)
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."""
1394 array_for_multi_clause = False
1395 inherit_cache = True
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
1406class mode(OrderedSetAgg):
1407 """Implement the ``mode`` ordered-set aggregate function.
1409 This function must be used with the :meth:`.FunctionElement.within_group`
1410 modifier to supply a sort expression to operate upon.
1412 The return type of this function is the same as the sort expression.
1414 .. versionadded:: 1.1
1416 """
1418 inherit_cache = True
1421class percentile_cont(OrderedSetAgg):
1422 """Implement the ``percentile_cont`` ordered-set aggregate function.
1424 This function must be used with the :meth:`.FunctionElement.within_group`
1425 modifier to supply a sort expression to operate upon.
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.
1431 .. versionadded:: 1.1
1433 """
1435 array_for_multi_clause = True
1436 inherit_cache = True
1439class percentile_disc(OrderedSetAgg):
1440 """Implement the ``percentile_disc`` ordered-set aggregate function.
1442 This function must be used with the :meth:`.FunctionElement.within_group`
1443 modifier to supply a sort expression to operate upon.
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.
1449 .. versionadded:: 1.1
1451 """
1453 array_for_multi_clause = True
1454 inherit_cache = True
1457class rank(GenericFunction):
1458 """Implement the ``rank`` hypothetical-set aggregate function.
1460 This function must be used with the :meth:`.FunctionElement.within_group`
1461 modifier to supply a sort expression to operate upon.
1463 The return type of this function is :class:`.Integer`.
1465 .. versionadded:: 1.1
1467 """
1469 type = sqltypes.Integer()
1470 inherit_cache = True
1473class dense_rank(GenericFunction):
1474 """Implement the ``dense_rank`` hypothetical-set aggregate function.
1476 This function must be used with the :meth:`.FunctionElement.within_group`
1477 modifier to supply a sort expression to operate upon.
1479 The return type of this function is :class:`.Integer`.
1481 .. versionadded:: 1.1
1483 """
1485 type = sqltypes.Integer()
1486 inherit_cache = True
1489class percent_rank(GenericFunction):
1490 """Implement the ``percent_rank`` hypothetical-set aggregate function.
1492 This function must be used with the :meth:`.FunctionElement.within_group`
1493 modifier to supply a sort expression to operate upon.
1495 The return type of this function is :class:`.Numeric`.
1497 .. versionadded:: 1.1
1499 """
1501 type = sqltypes.Numeric()
1502 inherit_cache = True
1505class cume_dist(GenericFunction):
1506 """Implement the ``cume_dist`` hypothetical-set aggregate function.
1508 This function must be used with the :meth:`.FunctionElement.within_group`
1509 modifier to supply a sort expression to operate upon.
1511 The return type of this function is :class:`.Numeric`.
1513 .. versionadded:: 1.1
1515 """
1517 type = sqltypes.Numeric()
1518 inherit_cache = True
1521class cube(GenericFunction):
1522 r"""Implement the ``CUBE`` grouping operation.
1524 This function is used as part of the GROUP BY of a statement,
1525 e.g. :meth:`_expression.Select.group_by`::
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))
1531 .. versionadded:: 1.2
1533 """
1534 _has_args = True
1535 inherit_cache = True
1538class rollup(GenericFunction):
1539 r"""Implement the ``ROLLUP`` grouping operation.
1541 This function is used as part of the GROUP BY of a statement,
1542 e.g. :meth:`_expression.Select.group_by`::
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))
1548 .. versionadded:: 1.2
1550 """
1551 _has_args = True
1552 inherit_cache = True
1555class grouping_sets(GenericFunction):
1556 r"""Implement the ``GROUPING SETS`` grouping operation.
1558 This function is used as part of the GROUP BY of a statement,
1559 e.g. :meth:`_expression.Select.group_by`::
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))
1565 In order to group by multiple sets, use the :func:`.tuple_` construct::
1567 from sqlalchemy import tuple_
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 )
1581 .. versionadded:: 1.2
1583 """
1584 _has_args = True
1585 inherit_cache = True