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-2021 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: http://www.opensource.org/licenses/mit-license.php
8"""SQL function API, factories, and built-in functions.
10"""
11from . import annotation
12from . import operators
13from . import schema
14from . import sqltypes
15from . import util as sqlutil
16from .base import ColumnCollection
17from .base import Executable
18from .elements import _clone
19from .elements import _literal_as_binds
20from .elements import _type_from_args
21from .elements import BinaryExpression
22from .elements import BindParameter
23from .elements import Cast
24from .elements import ClauseList
25from .elements import ColumnElement
26from .elements import Extract
27from .elements import FunctionFilter
28from .elements import Grouping
29from .elements import literal_column
30from .elements import Over
31from .elements import WithinGroup
32from .selectable import Alias
33from .selectable import FromClause
34from .selectable import Select
35from .visitors import VisitableType
36from .. import util
39_registry = util.defaultdict(dict)
40_case_sensitive_registry = util.defaultdict(lambda: util.defaultdict(dict))
41_CASE_SENSITIVE = util.symbol(
42 name="case_sensitive_function",
43 doc="Symbol to mark the functions that are switched into case-sensitive "
44 "mode.",
45)
48def register_function(identifier, fn, package="_default"):
49 """Associate a callable with a particular func. name.
51 This is normally called by _GenericMeta, but is also
52 available by itself so that a non-Function construct
53 can be associated with the :data:`.func` accessor (i.e.
54 CAST, EXTRACT).
56 """
57 reg = _registry[package]
58 case_sensitive_reg = _case_sensitive_registry[package]
59 raw_identifier = identifier
60 identifier = util.text_type(identifier).lower()
62 # Check if a function with the same lowercase identifier is registered.
63 if identifier in reg and reg[identifier] is not _CASE_SENSITIVE:
64 if raw_identifier in case_sensitive_reg[identifier]:
65 util.warn(
66 "The GenericFunction '{}' is already registered and "
67 "is going to be overridden.".format(identifier)
68 )
69 reg[identifier] = fn
70 else:
71 # If a function with the same lowercase identifier is registered,
72 # then these 2 functions are considered as case-sensitive.
73 # Note: This case should raise an error in a later release.
74 util.warn_deprecated(
75 "GenericFunction '{}' is already registered with "
76 "different letter case, so the previously registered function "
77 "'{}' is switched into case-sensitive mode. "
78 "GenericFunction objects will be fully case-insensitive in a "
79 "future release.".format(
80 raw_identifier,
81 list(case_sensitive_reg[identifier].keys())[0],
82 )
83 )
84 reg[identifier] = _CASE_SENSITIVE
86 # Check if a function with different letter case identifier is registered.
87 elif identifier in case_sensitive_reg:
88 # Note: This case will be removed in a later release.
89 if raw_identifier not in case_sensitive_reg[identifier]:
90 util.warn_deprecated(
91 "GenericFunction(s) '{}' are already registered with "
92 "different letter cases and might interact with '{}'. "
93 "GenericFunction objects will be fully case-insensitive in a "
94 "future release.".format(
95 sorted(case_sensitive_reg[identifier].keys()),
96 raw_identifier,
97 )
98 )
100 else:
101 util.warn(
102 "The GenericFunction '{}' is already registered and "
103 "is going to be overriden.".format(raw_identifier)
104 )
106 # Register by default
107 else:
108 reg[identifier] = fn
110 # Always register in case-sensitive registry
111 case_sensitive_reg[identifier][raw_identifier] = fn
114class FunctionElement(Executable, ColumnElement, FromClause):
115 """Base for SQL function-oriented constructs.
117 .. seealso::
119 :ref:`coretutorial_functions` - in the Core tutorial
121 :class:`.Function` - named SQL function.
123 :data:`.func` - namespace which produces registered or ad-hoc
124 :class:`.Function` instances.
126 :class:`.GenericFunction` - allows creation of registered function
127 types.
129 """
131 packagenames = ()
133 _has_args = False
135 def __init__(self, *clauses, **kwargs):
136 r"""Construct a :class:`.FunctionElement`.
138 :param \*clauses: list of column expressions that form the arguments
139 of the SQL function call.
141 :param \**kwargs: additional kwargs are typically consumed by
142 subclasses.
144 .. seealso::
146 :data:`.func`
148 :class:`.Function`
150 """
151 args = [_literal_as_binds(c, self.name) for c in clauses]
152 self._has_args = self._has_args or bool(args)
153 self.clause_expr = ClauseList(
154 operator=operators.comma_op, group_contents=True, *args
155 ).self_group()
157 def _execute_on_connection(self, connection, multiparams, params):
158 return connection._execute_function(self, multiparams, params)
160 @property
161 def columns(self):
162 r"""The set of columns exported by this :class:`.FunctionElement`.
164 Function objects currently have no result column names built in;
165 this method returns a single-element column collection with
166 an anonymously named column.
168 An interim approach to providing named columns for a function
169 as a FROM clause is to build a :func:`_expression.select` with the
170 desired columns::
172 from sqlalchemy.sql import column
174 stmt = select([column('x'), column('y')]).\
175 select_from(func.myfunction())
178 """
179 return ColumnCollection(self.label(None))
181 @util.memoized_property
182 def clauses(self):
183 """Return the underlying :class:`.ClauseList` which contains
184 the arguments for this :class:`.FunctionElement`.
186 """
187 return self.clause_expr.element
189 def over(self, partition_by=None, order_by=None, rows=None, range_=None):
190 """Produce an OVER clause against this function.
192 Used against aggregate or so-called "window" functions,
193 for database backends that support window functions.
195 The expression::
197 func.row_number().over(order_by='x')
199 is shorthand for::
201 from sqlalchemy import over
202 over(func.row_number(), order_by='x')
204 See :func:`_expression.over` for a full description.
206 """
207 return Over(
208 self,
209 partition_by=partition_by,
210 order_by=order_by,
211 rows=rows,
212 range_=range_,
213 )
215 def within_group(self, *order_by):
216 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
218 Used against so-called "ordered set aggregate" and "hypothetical
219 set aggregate" functions, including :class:`.percentile_cont`,
220 :class:`.rank`, :class:`.dense_rank`, etc.
222 See :func:`_expression.within_group` for a full description.
224 .. versionadded:: 1.1
227 """
228 return WithinGroup(self, *order_by)
230 def filter(self, *criterion):
231 """Produce a FILTER clause against this function.
233 Used against aggregate and window functions,
234 for database backends that support the "FILTER" clause.
236 The expression::
238 func.count(1).filter(True)
240 is shorthand for::
242 from sqlalchemy import funcfilter
243 funcfilter(func.count(1), True)
245 .. versionadded:: 1.0.0
247 .. seealso::
249 :class:`.FunctionFilter`
251 :func:`.funcfilter`
254 """
255 if not criterion:
256 return self
257 return FunctionFilter(self, *criterion)
259 def as_comparison(self, left_index, right_index):
260 """Interpret this expression as a boolean comparison between two values.
262 A hypothetical SQL function "is_equal()" which compares to values
263 for equality would be written in the Core expression language as::
265 expr = func.is_equal("a", "b")
267 If "is_equal()" above is comparing "a" and "b" for equality, the
268 :meth:`.FunctionElement.as_comparison` method would be invoked as::
270 expr = func.is_equal("a", "b").as_comparison(1, 2)
272 Where above, the integer value "1" refers to the first argument of the
273 "is_equal()" function and the integer value "2" refers to the second.
275 This would create a :class:`.BinaryExpression` that is equivalent to::
277 BinaryExpression("a", "b", operator=op.eq)
279 However, at the SQL level it would still render as
280 "is_equal('a', 'b')".
282 The ORM, when it loads a related object or collection, needs to be able
283 to manipulate the "left" and "right" sides of the ON clause of a JOIN
284 expression. The purpose of this method is to provide a SQL function
285 construct that can also supply this information to the ORM, when used
286 with the :paramref:`_orm.relationship.primaryjoin` parameter.
287 The return
288 value is a containment object called :class:`.FunctionAsBinary`.
290 An ORM example is as follows::
292 class Venue(Base):
293 __tablename__ = 'venue'
294 id = Column(Integer, primary_key=True)
295 name = Column(String)
297 descendants = relationship(
298 "Venue",
299 primaryjoin=func.instr(
300 remote(foreign(name)), name + "/"
301 ).as_comparison(1, 2) == 1,
302 viewonly=True,
303 order_by=name
304 )
306 Above, the "Venue" class can load descendant "Venue" objects by
307 determining if the name of the parent Venue is contained within the
308 start of the hypothetical descendant value's name, e.g. "parent1" would
309 match up to "parent1/child1", but not to "parent2/child1".
311 Possible use cases include the "materialized path" example given above,
312 as well as making use of special SQL functions such as geometric
313 functions to create join conditions.
315 :param left_index: the integer 1-based index of the function argument
316 that serves as the "left" side of the expression.
317 :param right_index: the integer 1-based index of the function argument
318 that serves as the "right" side of the expression.
320 .. versionadded:: 1.3
322 """
323 return FunctionAsBinary(self, left_index, right_index)
325 @property
326 def _from_objects(self):
327 return self.clauses._from_objects
329 def get_children(self, **kwargs):
330 return (self.clause_expr,)
332 def _copy_internals(self, clone=_clone, **kw):
333 self.clause_expr = clone(self.clause_expr, **kw)
334 self._reset_exported()
335 FunctionElement.clauses._reset(self)
337 def within_group_type(self, within_group):
338 """For types that define their return type as based on the criteria
339 within a WITHIN GROUP (ORDER BY) expression, called by the
340 :class:`.WithinGroup` construct.
342 Returns None by default, in which case the function's normal ``.type``
343 is used.
345 """
347 return None
349 def alias(self, name=None, flat=False):
350 r"""Produce a :class:`_expression.Alias` construct against this
351 :class:`.FunctionElement`.
353 This construct wraps the function in a named alias which
354 is suitable for the FROM clause, in the style accepted for example
355 by PostgreSQL.
357 e.g.::
359 from sqlalchemy.sql import column
361 stmt = select([column('data_view')]).\
362 select_from(SomeTable).\
363 select_from(func.unnest(SomeTable.data).alias('data_view')
364 )
366 Would produce:
368 .. sourcecode:: sql
370 SELECT data_view
371 FROM sometable, unnest(sometable.data) AS data_view
373 .. versionadded:: 0.9.8 The :meth:`.FunctionElement.alias` method
374 is now supported. Previously, this method's behavior was
375 undefined and did not behave consistently across versions.
377 """
379 return Alias._construct(self, name)
381 def select(self):
382 """Produce a :func:`_expression.select` construct
383 against this :class:`.FunctionElement`.
385 This is shorthand for::
387 s = select([function_element])
389 """
390 s = Select([self])
391 if self._execution_options:
392 s = s.execution_options(**self._execution_options)
393 return s
395 def scalar(self):
396 """Execute this :class:`.FunctionElement` against an embedded
397 'bind' and return a scalar value.
399 This first calls :meth:`~.FunctionElement.select` to
400 produce a SELECT construct.
402 Note that :class:`.FunctionElement` can be passed to
403 the :meth:`.Connectable.scalar` method of :class:`_engine.Connection`
404 or :class:`_engine.Engine`.
406 """
407 return self.select().execute().scalar()
409 def execute(self):
410 """Execute this :class:`.FunctionElement` against an embedded
411 'bind'.
413 This first calls :meth:`~.FunctionElement.select` to
414 produce a SELECT construct.
416 Note that :class:`.FunctionElement` can be passed to
417 the :meth:`.Connectable.execute` method of :class:`_engine.Connection`
418 or :class:`_engine.Engine`.
420 """
421 return self.select().execute()
423 def _bind_param(self, operator, obj, type_=None):
424 return BindParameter(
425 None,
426 obj,
427 _compared_to_operator=operator,
428 _compared_to_type=self.type,
429 unique=True,
430 type_=type_,
431 )
433 def self_group(self, against=None):
434 # for the moment, we are parenthesizing all array-returning
435 # expressions against getitem. This may need to be made
436 # more portable if in the future we support other DBs
437 # besides postgresql.
438 if against is operators.getitem and isinstance(
439 self.type, sqltypes.ARRAY
440 ):
441 return Grouping(self)
442 else:
443 return super(FunctionElement, self).self_group(against=against)
446class FunctionAsBinary(BinaryExpression):
447 def __init__(self, fn, left_index, right_index):
448 left = fn.clauses.clauses[left_index - 1]
449 right = fn.clauses.clauses[right_index - 1]
451 self.sql_function = fn
452 self.left_index = left_index
453 self.right_index = right_index
455 super(FunctionAsBinary, self).__init__(
456 left,
457 right,
458 operators.function_as_comparison_op,
459 type_=sqltypes.BOOLEANTYPE,
460 )
462 @property
463 def left(self):
464 return self.sql_function.clauses.clauses[self.left_index - 1]
466 @left.setter
467 def left(self, value):
468 self.sql_function.clauses.clauses[self.left_index - 1] = value
470 @property
471 def right(self):
472 return self.sql_function.clauses.clauses[self.right_index - 1]
474 @right.setter
475 def right(self, value):
476 self.sql_function.clauses.clauses[self.right_index - 1] = value
478 def _copy_internals(self, **kw):
479 clone = kw.pop("clone")
480 self.sql_function = clone(self.sql_function, **kw)
481 super(FunctionAsBinary, self)._copy_internals(**kw)
484class _FunctionGenerator(object):
485 """Generate SQL function expressions.
487 :data:`.func` is a special object instance which generates SQL
488 functions based on name-based attributes, e.g.::
490 >>> print(func.count(1))
491 count(:param_1)
493 The returned object is an instance of :class:`.Function`, and is a
494 column-oriented SQL element like any other, and is used in that way::
496 >>> print(select([func.count(table.c.id)]))
497 SELECT count(sometable.id) FROM sometable
499 Any name can be given to :data:`.func`. If the function name is unknown to
500 SQLAlchemy, it will be rendered exactly as is. For common SQL functions
501 which SQLAlchemy is aware of, the name may be interpreted as a *generic
502 function* which will be compiled appropriately to the target database::
504 >>> print(func.current_timestamp())
505 CURRENT_TIMESTAMP
507 To call functions which are present in dot-separated packages,
508 specify them in the same manner::
510 >>> print(func.stats.yield_curve(5, 10))
511 stats.yield_curve(:yield_curve_1, :yield_curve_2)
513 SQLAlchemy can be made aware of the return type of functions to enable
514 type-specific lexical and result-based behavior. For example, to ensure
515 that a string-based function returns a Unicode value and is similarly
516 treated as a string in expressions, specify
517 :class:`~sqlalchemy.types.Unicode` as the type:
519 >>> print(func.my_string(u'hi', type_=Unicode) + ' ' +
520 ... func.my_string(u'there', type_=Unicode))
521 my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
523 The object returned by a :data:`.func` call is usually an instance of
524 :class:`.Function`.
525 This object meets the "column" interface, including comparison and labeling
526 functions. The object can also be passed the :meth:`~.Connectable.execute`
527 method of a :class:`_engine.Connection` or :class:`_engine.Engine`,
528 where it will be
529 wrapped inside of a SELECT statement first::
531 print(connection.execute(func.current_timestamp()).scalar())
533 In a few exception cases, the :data:`.func` accessor
534 will redirect a name to a built-in expression such as :func:`.cast`
535 or :func:`.extract`, as these names have well-known meaning
536 but are not exactly the same as "functions" from a SQLAlchemy
537 perspective.
539 Functions which are interpreted as "generic" functions know how to
540 calculate their return type automatically. For a listing of known generic
541 functions, see :ref:`generic_functions`.
543 .. note::
545 The :data:`.func` construct has only limited support for calling
546 standalone "stored procedures", especially those with special
547 parameterization concerns.
549 See the section :ref:`stored_procedures` for details on how to use
550 the DBAPI-level ``callproc()`` method for fully traditional stored
551 procedures.
553 .. seealso::
555 :ref:`coretutorial_functions` - in the Core Tutorial
557 :class:`.Function`
559 """
561 def __init__(self, **opts):
562 self.__names = []
563 self.opts = opts
565 def __getattr__(self, name):
566 # passthru __ attributes; fixes pydoc
567 if name.startswith("__"):
568 try:
569 return self.__dict__[name]
570 except KeyError:
571 raise AttributeError(name)
573 elif name.endswith("_"):
574 name = name[0:-1]
575 f = _FunctionGenerator(**self.opts)
576 f.__names = list(self.__names) + [name]
577 return f
579 def __call__(self, *c, **kwargs):
580 o = self.opts.copy()
581 o.update(kwargs)
583 tokens = len(self.__names)
585 if tokens == 2:
586 package, fname = self.__names
587 elif tokens == 1:
588 package, fname = "_default", self.__names[0]
589 else:
590 package = None
592 if package is not None:
593 func = _registry[package].get(fname.lower())
594 if func is _CASE_SENSITIVE:
595 case_sensitive_reg = _case_sensitive_registry[package]
596 func = case_sensitive_reg.get(fname.lower()).get(fname)
598 if func is not None:
599 return func(*c, **o)
601 return Function(
602 self.__names[-1], packagenames=self.__names[0:-1], *c, **o
603 )
606func = _FunctionGenerator()
607func.__doc__ = _FunctionGenerator.__doc__
609modifier = _FunctionGenerator(group=False)
612class Function(FunctionElement):
613 r"""Describe a named SQL function.
615 The :class:`.Function` object is typically generated from the
616 :data:`.func` generation object.
619 :param \*clauses: list of column expressions that form the arguments
620 of the SQL function call.
622 :param type\_: optional :class:`.TypeEngine` datatype object that will be
623 used as the return value of the column expression generated by this
624 function call.
626 :param packagenames: a string which indicates package prefix names
627 to be prepended to the function name when the SQL is generated.
628 The :data:`.func` generator creates these when it is called using
629 dotted format, e.g.::
631 func.mypackage.some_function(col1, col2)
634 .. seealso::
636 :ref:`coretutorial_functions`
638 :data:`.func` - namespace which produces registered or ad-hoc
639 :class:`.Function` instances.
641 :class:`.GenericFunction` - allows creation of registered function
642 types.
644 """
646 __visit_name__ = "function"
648 def __init__(self, name, *clauses, **kw):
649 """Construct a :class:`.Function`.
651 The :data:`.func` construct is normally used to construct
652 new :class:`.Function` instances.
654 """
655 self.packagenames = kw.pop("packagenames", None) or []
656 self.name = name
657 self._bind = kw.get("bind", None)
658 self.type = sqltypes.to_instance(kw.get("type_", None))
660 FunctionElement.__init__(self, *clauses, **kw)
662 def _bind_param(self, operator, obj, type_=None):
663 return BindParameter(
664 self.name,
665 obj,
666 _compared_to_operator=operator,
667 _compared_to_type=self.type,
668 type_=type_,
669 unique=True,
670 )
673class _GenericMeta(VisitableType):
674 def __init__(cls, clsname, bases, clsdict):
675 if annotation.Annotated not in cls.__mro__:
676 cls.name = name = clsdict.get("name", clsname)
677 cls.identifier = identifier = clsdict.get("identifier", name)
678 package = clsdict.pop("package", "_default")
679 # legacy
680 if "__return_type__" in clsdict:
681 cls.type = clsdict["__return_type__"]
683 # Check _register attribute status
684 cls._register = getattr(cls, "_register", True)
686 # Register the function if required
687 if cls._register:
688 register_function(identifier, cls, package)
689 else:
690 # Set _register to True to register child classes by default
691 cls._register = True
693 super(_GenericMeta, cls).__init__(clsname, bases, clsdict)
696class GenericFunction(util.with_metaclass(_GenericMeta, Function)):
697 """Define a 'generic' function.
699 A generic function is a pre-established :class:`.Function`
700 class that is instantiated automatically when called
701 by name from the :data:`.func` attribute. Note that
702 calling any name from :data:`.func` has the effect that
703 a new :class:`.Function` instance is created automatically,
704 given that name. The primary use case for defining
705 a :class:`.GenericFunction` class is so that a function
706 of a particular name may be given a fixed return type.
707 It can also include custom argument parsing schemes as well
708 as additional methods.
710 Subclasses of :class:`.GenericFunction` are automatically
711 registered under the name of the class. For
712 example, a user-defined function ``as_utc()`` would
713 be available immediately::
715 from sqlalchemy.sql.functions import GenericFunction
716 from sqlalchemy.types import DateTime
718 class as_utc(GenericFunction):
719 type = DateTime
721 print(select([func.as_utc()]))
723 User-defined generic functions can be organized into
724 packages by specifying the "package" attribute when defining
725 :class:`.GenericFunction`. Third party libraries
726 containing many functions may want to use this in order
727 to avoid name conflicts with other systems. For example,
728 if our ``as_utc()`` function were part of a package
729 "time"::
731 class as_utc(GenericFunction):
732 type = DateTime
733 package = "time"
735 The above function would be available from :data:`.func`
736 using the package name ``time``::
738 print(select([func.time.as_utc()]))
740 A final option is to allow the function to be accessed
741 from one name in :data:`.func` but to render as a different name.
742 The ``identifier`` attribute will override the name used to
743 access the function as loaded from :data:`.func`, but will retain
744 the usage of ``name`` as the rendered name::
746 class GeoBuffer(GenericFunction):
747 type = Geometry
748 package = "geo"
749 name = "ST_Buffer"
750 identifier = "buffer"
752 The above function will render as follows::
754 >>> print(func.geo.buffer())
755 ST_Buffer()
757 The name will be rendered as is, however without quoting unless the name
758 contains special characters that require quoting. To force quoting
759 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name`
760 construct::
762 from sqlalchemy.sql import quoted_name
764 class GeoBuffer(GenericFunction):
765 type = Geometry
766 package = "geo"
767 name = quoted_name("ST_Buffer", True)
768 identifier = "buffer"
770 The above function will render as::
772 >>> print(func.geo.buffer())
773 "ST_Buffer"()
775 .. versionadded:: 1.3.13 The :class:`.quoted_name` construct is now
776 recognized for quoting when used with the "name" attribute of the
777 object, so that quoting can be forced on or off for the function
778 name.
781 """
783 coerce_arguments = True
784 _register = False
786 def __init__(self, *args, **kwargs):
787 parsed_args = kwargs.pop("_parsed_args", None)
788 if parsed_args is None:
789 parsed_args = [_literal_as_binds(c, self.name) for c in args]
790 self._has_args = self._has_args or bool(parsed_args)
791 self.packagenames = []
792 self._bind = kwargs.get("bind", None)
793 self.clause_expr = ClauseList(
794 operator=operators.comma_op, group_contents=True, *parsed_args
795 ).self_group()
796 self.type = sqltypes.to_instance(
797 kwargs.pop("type_", None) or getattr(self, "type", None)
798 )
801register_function("cast", Cast)
802register_function("extract", Extract)
805class next_value(GenericFunction):
806 """Represent the 'next value', given a :class:`.Sequence`
807 as its single argument.
809 Compiles into the appropriate function on each backend,
810 or will raise NotImplementedError if used on a backend
811 that does not provide support for sequences.
813 """
815 type = sqltypes.Integer()
816 name = "next_value"
818 def __init__(self, seq, **kw):
819 assert isinstance(
820 seq, schema.Sequence
821 ), "next_value() accepts a Sequence object as input."
822 self._bind = kw.get("bind", None)
823 self.sequence = seq
825 @property
826 def _from_objects(self):
827 return []
830class AnsiFunction(GenericFunction):
831 def __init__(self, *args, **kwargs):
832 GenericFunction.__init__(self, *args, **kwargs)
835class ReturnTypeFromArgs(GenericFunction):
836 """Define a function whose return type is the same as its arguments."""
838 def __init__(self, *args, **kwargs):
839 args = [_literal_as_binds(c, self.name) for c in args]
840 kwargs.setdefault("type_", _type_from_args(args))
841 kwargs["_parsed_args"] = args
842 super(ReturnTypeFromArgs, self).__init__(*args, **kwargs)
845class coalesce(ReturnTypeFromArgs):
846 _has_args = True
849class max(ReturnTypeFromArgs): # noqa
850 pass
853class min(ReturnTypeFromArgs): # noqa
854 pass
857class sum(ReturnTypeFromArgs): # noqa
858 pass
861class now(GenericFunction): # noqa
862 type = sqltypes.DateTime
865class concat(GenericFunction):
866 type = sqltypes.String
869class char_length(GenericFunction):
870 type = sqltypes.Integer
872 def __init__(self, arg, **kwargs):
873 GenericFunction.__init__(self, arg, **kwargs)
876class random(GenericFunction):
877 _has_args = True
880class count(GenericFunction):
881 r"""The ANSI COUNT aggregate function. With no arguments,
882 emits COUNT \*.
884 E.g.::
886 from sqlalchemy import func
887 from sqlalchemy import select
888 from sqlalchemy import table, column
890 my_table = table('some_table', column('id'))
892 stmt = select([func.count()]).select_from(my_table)
894 Executing ``stmt`` would emit::
896 SELECT count(*) AS count_1
897 FROM some_table
900 """
901 type = sqltypes.Integer
903 def __init__(self, expression=None, **kwargs):
904 if expression is None:
905 expression = literal_column("*")
906 super(count, self).__init__(expression, **kwargs)
909class current_date(AnsiFunction):
910 type = sqltypes.Date
913class current_time(AnsiFunction):
914 type = sqltypes.Time
917class current_timestamp(AnsiFunction):
918 type = sqltypes.DateTime
921class current_user(AnsiFunction):
922 type = sqltypes.String
925class localtime(AnsiFunction):
926 type = sqltypes.DateTime
929class localtimestamp(AnsiFunction):
930 type = sqltypes.DateTime
933class session_user(AnsiFunction):
934 type = sqltypes.String
937class sysdate(AnsiFunction):
938 type = sqltypes.DateTime
941class user(AnsiFunction):
942 type = sqltypes.String
945class array_agg(GenericFunction):
946 """Support for the ARRAY_AGG function.
948 The ``func.array_agg(expr)`` construct returns an expression of
949 type :class:`_types.ARRAY`.
951 e.g.::
953 stmt = select([func.array_agg(table.c.values)[2:5]])
955 .. versionadded:: 1.1
957 .. seealso::
959 :func:`_postgresql.array_agg` - PostgreSQL-specific version that
960 returns :class:`_postgresql.ARRAY`, which has PG-specific operators
961 added.
963 """
965 type = sqltypes.ARRAY
967 def __init__(self, *args, **kwargs):
968 args = [_literal_as_binds(c) for c in args]
970 default_array_type = kwargs.pop("_default_array_type", sqltypes.ARRAY)
971 if "type_" not in kwargs:
973 type_from_args = _type_from_args(args)
974 if isinstance(type_from_args, sqltypes.ARRAY):
975 kwargs["type_"] = type_from_args
976 else:
977 kwargs["type_"] = default_array_type(type_from_args)
978 kwargs["_parsed_args"] = args
979 super(array_agg, self).__init__(*args, **kwargs)
982class OrderedSetAgg(GenericFunction):
983 """Define a function where the return type is based on the sort
984 expression type as defined by the expression passed to the
985 :meth:`.FunctionElement.within_group` method."""
987 array_for_multi_clause = False
989 def within_group_type(self, within_group):
990 func_clauses = self.clause_expr.element
991 order_by = sqlutil.unwrap_order_by(within_group.order_by)
992 if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
993 return sqltypes.ARRAY(order_by[0].type)
994 else:
995 return order_by[0].type
998class mode(OrderedSetAgg):
999 """Implement the ``mode`` ordered-set aggregate function.
1001 This function must be used with the :meth:`.FunctionElement.within_group`
1002 modifier to supply a sort expression to operate upon.
1004 The return type of this function is the same as the sort expression.
1006 .. versionadded:: 1.1
1008 """
1011class percentile_cont(OrderedSetAgg):
1012 """Implement the ``percentile_cont`` ordered-set aggregate function.
1014 This function must be used with the :meth:`.FunctionElement.within_group`
1015 modifier to supply a sort expression to operate upon.
1017 The return type of this function is the same as the sort expression,
1018 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1019 expression's type.
1021 .. versionadded:: 1.1
1023 """
1025 array_for_multi_clause = True
1028class percentile_disc(OrderedSetAgg):
1029 """Implement the ``percentile_disc`` ordered-set aggregate function.
1031 This function must be used with the :meth:`.FunctionElement.within_group`
1032 modifier to supply a sort expression to operate upon.
1034 The return type of this function is the same as the sort expression,
1035 or if the arguments are an array, an :class:`_types.ARRAY` of the sort
1036 expression's type.
1038 .. versionadded:: 1.1
1040 """
1042 array_for_multi_clause = True
1045class rank(GenericFunction):
1046 """Implement the ``rank`` hypothetical-set aggregate function.
1048 This function must be used with the :meth:`.FunctionElement.within_group`
1049 modifier to supply a sort expression to operate upon.
1051 The return type of this function is :class:`.Integer`.
1053 .. versionadded:: 1.1
1055 """
1057 type = sqltypes.Integer()
1060class dense_rank(GenericFunction):
1061 """Implement the ``dense_rank`` hypothetical-set aggregate function.
1063 This function must be used with the :meth:`.FunctionElement.within_group`
1064 modifier to supply a sort expression to operate upon.
1066 The return type of this function is :class:`.Integer`.
1068 .. versionadded:: 1.1
1070 """
1072 type = sqltypes.Integer()
1075class percent_rank(GenericFunction):
1076 """Implement the ``percent_rank`` hypothetical-set aggregate function.
1078 This function must be used with the :meth:`.FunctionElement.within_group`
1079 modifier to supply a sort expression to operate upon.
1081 The return type of this function is :class:`.Numeric`.
1083 .. versionadded:: 1.1
1085 """
1087 type = sqltypes.Numeric()
1090class cume_dist(GenericFunction):
1091 """Implement the ``cume_dist`` hypothetical-set aggregate function.
1093 This function must be used with the :meth:`.FunctionElement.within_group`
1094 modifier to supply a sort expression to operate upon.
1096 The return type of this function is :class:`.Numeric`.
1098 .. versionadded:: 1.1
1100 """
1102 type = sqltypes.Numeric()
1105class cube(GenericFunction):
1106 r"""Implement the ``CUBE`` grouping operation.
1108 This function is used as part of the GROUP BY of a statement,
1109 e.g. :meth:`_expression.Select.group_by`::
1111 stmt = select(
1112 [func.sum(table.c.value), table.c.col_1, table.c.col_2]
1113 ).group_by(func.cube(table.c.col_1, table.c.col_2))
1115 .. versionadded:: 1.2
1117 """
1118 _has_args = True
1121class rollup(GenericFunction):
1122 r"""Implement the ``ROLLUP`` grouping operation.
1124 This function is used as part of the GROUP BY of a statement,
1125 e.g. :meth:`_expression.Select.group_by`::
1127 stmt = select(
1128 [func.sum(table.c.value), table.c.col_1, table.c.col_2]
1129 ).group_by(func.rollup(table.c.col_1, table.c.col_2))
1131 .. versionadded:: 1.2
1133 """
1134 _has_args = True
1137class grouping_sets(GenericFunction):
1138 r"""Implement the ``GROUPING SETS`` grouping operation.
1140 This function is used as part of the GROUP BY of a statement,
1141 e.g. :meth:`_expression.Select.group_by`::
1143 stmt = select(
1144 [func.sum(table.c.value), table.c.col_1, table.c.col_2]
1145 ).group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
1147 In order to group by multiple sets, use the :func:`.tuple_` construct::
1149 from sqlalchemy import tuple_
1151 stmt = select(
1152 [
1153 func.sum(table.c.value),
1154 table.c.col_1, table.c.col_2,
1155 table.c.col_3]
1156 ).group_by(
1157 func.grouping_sets(
1158 tuple_(table.c.col_1, table.c.col_2),
1159 tuple_(table.c.value, table.c.col_3),
1160 )
1161 )
1164 .. versionadded:: 1.2
1166 """
1167 _has_args = True