Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/functions.py: 72%

1# sql/functions.py 

2# Copyright (C) 2005-2023 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