Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/ext.py: 50%

1# dialects/postgresql/ext.py 

2# Copyright (C) 2005-2026 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# mypy: ignore-errors 

8from __future__ import annotations 

9 

10from typing import Any 

11from typing import Iterable 

12from typing import List 

13from typing import Optional 

14from typing import overload 

15from typing import Tuple 

16from typing import TYPE_CHECKING 

17from typing import TypeVar 

18 

19from . import types 

20from .array import ARRAY 

21from ...sql import coercions 

22from ...sql import elements 

23from ...sql import expression 

24from ...sql import functions 

25from ...sql import roles 

26from ...sql import schema 

27from ...sql.schema import ColumnCollectionConstraint 

28from ...sql.sqltypes import TEXT 

29from ...sql.visitors import InternalTraversal 

30 

31if TYPE_CHECKING: 

32 from ...sql._typing import _ColumnExpressionArgument 

33 from ...sql._typing import _DDLColumnArgument 

34 from ...sql.elements import ClauseElement 

35 from ...sql.elements import ColumnElement 

36 from ...sql.operators import OperatorType 

37 from ...sql.selectable import FromClause 

38 from ...sql.visitors import _CloneCallableType 

39 from ...sql.visitors import _TraverseInternalsType 

40 

41_T = TypeVar("_T", bound=Any) 

42 

43 

44class aggregate_order_by(expression.ColumnElement[_T]): 

45 """Represent a PostgreSQL aggregate order by expression. 

46 

47 E.g.:: 

48 

49 from sqlalchemy.dialects.postgresql import aggregate_order_by 

50 

51 expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc())) 

52 stmt = select(expr) 

53 

54 would represent the expression: 

55 

56 .. sourcecode:: sql 

57 

58 SELECT array_agg(a ORDER BY b DESC) FROM table; 

59 

60 Similarly:: 

61 

62 expr = func.string_agg( 

63 table.c.a, aggregate_order_by(literal_column("','"), table.c.a) 

64 ) 

65 stmt = select(expr) 

66 

67 Would represent: 

68 

69 .. sourcecode:: sql 

70 

71 SELECT string_agg(a, ',' ORDER BY a) FROM table; 

72 

73 .. versionchanged:: 1.2.13 - the ORDER BY argument may be multiple terms 

74 

75 .. seealso:: 

76 

77 :class:`_functions.array_agg` 

78 

79 """ 

80 

81 __visit_name__ = "aggregate_order_by" 

82 

83 stringify_dialect = "postgresql" 

84 _traverse_internals: _TraverseInternalsType = [ 

85 ("target", InternalTraversal.dp_clauseelement), 

86 ("type", InternalTraversal.dp_type), 

87 ("order_by", InternalTraversal.dp_clauseelement), 

88 ] 

89 

90 @overload 

91 def __init__( 

92 self, 

93 target: ColumnElement[_T], 

94 *order_by: _ColumnExpressionArgument[Any], 

95 ): ... 

96 

97 @overload 

98 def __init__( 

99 self, 

100 target: _ColumnExpressionArgument[_T], 

101 *order_by: _ColumnExpressionArgument[Any], 

102 ): ... 

103 

104 def __init__( 

105 self, 

106 target: _ColumnExpressionArgument[_T], 

107 *order_by: _ColumnExpressionArgument[Any], 

108 ): 

109 self.target: ClauseElement = coercions.expect( 

110 roles.ExpressionElementRole, target 

111 ) 

112 self.type = self.target.type 

113 

114 _lob = len(order_by) 

115 self.order_by: ClauseElement 

116 if _lob == 0: 

117 raise TypeError("at least one ORDER BY element is required") 

118 elif _lob == 1: 

119 self.order_by = coercions.expect( 

120 roles.ExpressionElementRole, order_by[0] 

121 ) 

122 else: 

123 self.order_by = elements.ClauseList( 

124 *order_by, _literal_as_text_role=roles.ExpressionElementRole 

125 ) 

126 

127 def self_group( 

128 self, against: Optional[OperatorType] = None 

129 ) -> ClauseElement: 

130 return self 

131 

132 def get_children(self, **kwargs: Any) -> Iterable[ClauseElement]: 

133 return self.target, self.order_by 

134 

135 def _copy_internals( 

136 self, clone: _CloneCallableType = elements._clone, **kw: Any 

137 ) -> None: 

138 self.target = clone(self.target, **kw) 

139 self.order_by = clone(self.order_by, **kw) 

140 

141 @property 

142 def _from_objects(self) -> List[FromClause]: 

143 return self.target._from_objects + self.order_by._from_objects 

144 

145 

146class ExcludeConstraint(ColumnCollectionConstraint): 

147 """A table-level EXCLUDE constraint. 

148 

149 Defines an EXCLUDE constraint as described in the `PostgreSQL 

150 documentation`__. 

151 

152 __ https://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE 

153 

154 """ # noqa 

155 

156 __visit_name__ = "exclude_constraint" 

157 

158 where = None 

159 inherit_cache = False 

160 

161 create_drop_stringify_dialect = "postgresql" 

162 

163 @elements._document_text_coercion( 

164 "where", 

165 ":class:`.ExcludeConstraint`", 

166 ":paramref:`.ExcludeConstraint.where`", 

167 ) 

168 def __init__( 

169 self, *elements: Tuple[_DDLColumnArgument, str], **kw: Any 

170 ) -> None: 

171 r""" 

172 Create an :class:`.ExcludeConstraint` object. 

173 

174 E.g.:: 

175 

176 const = ExcludeConstraint( 

177 (Column("period"), "&&"), 

178 (Column("group"), "="), 

179 where=(Column("group") != "some group"), 

180 ops={"group": "my_operator_class"}, 

181 ) 

182 

183 The constraint is normally embedded into the :class:`_schema.Table` 

184 construct 

185 directly, or added later using :meth:`.append_constraint`:: 

186 

187 some_table = Table( 

188 "some_table", 

189 metadata, 

190 Column("id", Integer, primary_key=True), 

191 Column("period", TSRANGE()), 

192 Column("group", String), 

193 ) 

194 

195 some_table.append_constraint( 

196 ExcludeConstraint( 

197 (some_table.c.period, "&&"), 

198 (some_table.c.group, "="), 

199 where=some_table.c.group != "some group", 

200 name="some_table_excl_const", 

201 ops={"group": "my_operator_class"}, 

202 ) 

203 ) 

204 

205 The exclude constraint defined in this example requires the 

206 ``btree_gist`` extension, that can be created using the 

207 command ``CREATE EXTENSION btree_gist;``. 

208 

209 :param \*elements: 

210 

211 A sequence of two tuples of the form ``(column, operator)`` where 

212 "column" is either a :class:`_schema.Column` object, or a SQL 

213 expression element (e.g. ``func.int8range(table.from, table.to)``) 

214 or the name of a column as string, and "operator" is a string 

215 containing the operator to use (e.g. `"&&"` or `"="`). 

216 

217 In order to specify a column name when a :class:`_schema.Column` 

218 object is not available, while ensuring 

219 that any necessary quoting rules take effect, an ad-hoc 

220 :class:`_schema.Column` or :func:`_expression.column` 

221 object should be used. 

222 The ``column`` may also be a string SQL expression when 

223 passed as :func:`_expression.literal_column` or 

224 :func:`_expression.text` 

225 

226 :param name: 

227 Optional, the in-database name of this constraint. 

228 

229 :param deferrable: 

230 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when 

231 issuing DDL for this constraint. 

232 

233 :param initially: 

234 Optional string. If set, emit INITIALLY <value> when issuing DDL 

235 for this constraint. 

236 

237 :param info: Optional data dictionary which will be populated into the 

238 :attr:`.SchemaItem.info` attribute of this object. 

239 

240 .. versionadded:: 2.0.50 

241 

242 :param using: 

243 Optional string. If set, emit USING <index_method> when issuing DDL 

244 for this constraint. Defaults to 'gist'. 

245 

246 :param where: 

247 Optional SQL expression construct or literal SQL string. 

248 If set, emit WHERE <predicate> when issuing DDL 

249 for this constraint. 

250 

251 :param ops: 

252 Optional dictionary. Used to define operator classes for the 

253 elements; works the same way as that of the 

254 :ref:`postgresql_ops <postgresql_operator_classes>` 

255 parameter specified to the :class:`_schema.Index` construct. 

256 

257 .. versionadded:: 1.3.21 

258 

259 .. seealso:: 

260 

261 :ref:`postgresql_operator_classes` - general description of how 

262 PostgreSQL operator classes are specified. 

263 

264 """ 

265 columns = [] 

266 render_exprs = [] 

267 self.operators = {} 

268 

269 expressions, operators = zip(*elements) 

270 

271 for (expr, column, strname, add_element), operator in zip( 

272 coercions.expect_col_expression_collection( 

273 roles.DDLConstraintColumnRole, expressions 

274 ), 

275 operators, 

276 ): 

277 if add_element is not None: 

278 columns.append(add_element) 

279 

280 name = column.name if column is not None else strname 

281 

282 if name is not None: 

283 # backwards compat 

284 self.operators[name] = operator 

285 

286 render_exprs.append((expr, name, operator)) 

287 

288 self._render_exprs = render_exprs 

289 

290 ColumnCollectionConstraint.__init__( 

291 self, 

292 *columns, 

293 name=kw.get("name"), 

294 deferrable=kw.get("deferrable"), 

295 initially=kw.get("initially"), 

296 info=kw.get("info"), 

297 ) 

298 self.using = kw.get("using", "gist") 

299 where = kw.get("where") 

300 if where is not None: 

301 self.where = coercions.expect(roles.StatementOptionRole, where) 

302 

303 self.ops = kw.get("ops", {}) 

304 

305 def _set_parent(self, table, **kw): 

306 super()._set_parent(table) 

307 

308 self._render_exprs = [ 

309 ( 

310 expr if not isinstance(expr, str) else table.c[expr], 

311 name, 

312 operator, 

313 ) 

314 for expr, name, operator in (self._render_exprs) 

315 ] 

316 

317 def _copy(self, target_table=None, **kw): 

318 elements = [ 

319 ( 

320 schema._copy_expression(expr, self.parent, target_table), 

321 operator, 

322 ) 

323 for expr, _, operator in self._render_exprs 

324 ] 

325 c = self.__class__( 

326 *elements, 

327 name=self.name, 

328 deferrable=self.deferrable, 

329 initially=self.initially, 

330 where=self.where, 

331 using=self.using, 

332 ) 

333 c.dispatch._update(self.dispatch) 

334 return c 

335 

336 

337def array_agg(*arg, **kw): 

338 """PostgreSQL-specific form of :class:`_functions.array_agg`, ensures 

339 return type is :class:`_postgresql.ARRAY` and not 

340 the plain :class:`_types.ARRAY`, unless an explicit ``type_`` 

341 is passed. 

342 

343 """ 

344 kw["_default_array_type"] = ARRAY 

345 return functions.func.array_agg(*arg, **kw) 

346 

347 

348class _regconfig_fn(functions.GenericFunction[_T]): 

349 inherit_cache = True 

350 

351 def __init__(self, *args, **kwargs): 

352 args = list(args) 

353 if len(args) > 1: 

354 initial_arg = coercions.expect( 

355 roles.ExpressionElementRole, 

356 args.pop(0), 

357 name=getattr(self, "name", None), 

358 apply_propagate_attrs=self, 

359 type_=types.REGCONFIG, 

360 ) 

361 initial_arg = [initial_arg] 

362 else: 

363 initial_arg = [] 

364 

365 addtl_args = [ 

366 coercions.expect( 

367 roles.ExpressionElementRole, 

368 c, 

369 name=getattr(self, "name", None), 

370 apply_propagate_attrs=self, 

371 ) 

372 for c in args 

373 ] 

374 super().__init__(*(initial_arg + addtl_args), **kwargs) 

375 

376 

377class to_tsvector(_regconfig_fn): 

378 """The PostgreSQL ``to_tsvector`` SQL function. 

379 

380 This function applies automatic casting of the REGCONFIG argument 

381 to use the :class:`_postgresql.REGCONFIG` datatype automatically, 

382 and applies a return type of :class:`_postgresql.TSVECTOR`. 

383 

384 Assuming the PostgreSQL dialect has been imported, either by invoking 

385 ``from sqlalchemy.dialects import postgresql``, or by creating a PostgreSQL 

386 engine using ``create_engine("postgresql...")``, 

387 :class:`_postgresql.to_tsvector` will be used automatically when invoking 

388 ``sqlalchemy.func.to_tsvector()``, ensuring the correct argument and return 

389 type handlers are used at compile and execution time. 

390 

391 .. versionadded:: 2.0.0rc1 

392 

393 """ 

394 

395 inherit_cache = True 

396 type = types.TSVECTOR 

397 

398 

399class to_tsquery(_regconfig_fn): 

400 """The PostgreSQL ``to_tsquery`` SQL function. 

401 

402 This function applies automatic casting of the REGCONFIG argument 

403 to use the :class:`_postgresql.REGCONFIG` datatype automatically, 

404 and applies a return type of :class:`_postgresql.TSQUERY`. 

405 

406 Assuming the PostgreSQL dialect has been imported, either by invoking 

407 ``from sqlalchemy.dialects import postgresql``, or by creating a PostgreSQL 

408 engine using ``create_engine("postgresql...")``, 

409 :class:`_postgresql.to_tsquery` will be used automatically when invoking 

410 ``sqlalchemy.func.to_tsquery()``, ensuring the correct argument and return 

411 type handlers are used at compile and execution time. 

412 

413 .. versionadded:: 2.0.0rc1 

414 

415 """ 

416 

417 inherit_cache = True 

418 type = types.TSQUERY 

419 

420 

421class plainto_tsquery(_regconfig_fn): 

422 """The PostgreSQL ``plainto_tsquery`` SQL function. 

423 

424 This function applies automatic casting of the REGCONFIG argument 

425 to use the :class:`_postgresql.REGCONFIG` datatype automatically, 

426 and applies a return type of :class:`_postgresql.TSQUERY`. 

427 

428 Assuming the PostgreSQL dialect has been imported, either by invoking 

429 ``from sqlalchemy.dialects import postgresql``, or by creating a PostgreSQL 

430 engine using ``create_engine("postgresql...")``, 

431 :class:`_postgresql.plainto_tsquery` will be used automatically when 

432 invoking ``sqlalchemy.func.plainto_tsquery()``, ensuring the correct 

433 argument and return type handlers are used at compile and execution time. 

434 

435 .. versionadded:: 2.0.0rc1 

436 

437 """ 

438 

439 inherit_cache = True 

440 type = types.TSQUERY 

441 

442 

443class phraseto_tsquery(_regconfig_fn): 

444 """The PostgreSQL ``phraseto_tsquery`` SQL function. 

445 

446 This function applies automatic casting of the REGCONFIG argument 

447 to use the :class:`_postgresql.REGCONFIG` datatype automatically, 

448 and applies a return type of :class:`_postgresql.TSQUERY`. 

449 

450 Assuming the PostgreSQL dialect has been imported, either by invoking 

451 ``from sqlalchemy.dialects import postgresql``, or by creating a PostgreSQL 

452 engine using ``create_engine("postgresql...")``, 

453 :class:`_postgresql.phraseto_tsquery` will be used automatically when 

454 invoking ``sqlalchemy.func.phraseto_tsquery()``, ensuring the correct 

455 argument and return type handlers are used at compile and execution time. 

456 

457 .. versionadded:: 2.0.0rc1 

458 

459 """ 

460 

461 inherit_cache = True 

462 type = types.TSQUERY 

463 

464 

465class websearch_to_tsquery(_regconfig_fn): 

466 """The PostgreSQL ``websearch_to_tsquery`` SQL function. 

467 

468 This function applies automatic casting of the REGCONFIG argument 

469 to use the :class:`_postgresql.REGCONFIG` datatype automatically, 

470 and applies a return type of :class:`_postgresql.TSQUERY`. 

471 

472 Assuming the PostgreSQL dialect has been imported, either by invoking 

473 ``from sqlalchemy.dialects import postgresql``, or by creating a PostgreSQL 

474 engine using ``create_engine("postgresql...")``, 

475 :class:`_postgresql.websearch_to_tsquery` will be used automatically when 

476 invoking ``sqlalchemy.func.websearch_to_tsquery()``, ensuring the correct 

477 argument and return type handlers are used at compile and execution time. 

478 

479 .. versionadded:: 2.0.0rc1 

480 

481 """ 

482 

483 inherit_cache = True 

484 type = types.TSQUERY 

485 

486 

487class ts_headline(_regconfig_fn): 

488 """The PostgreSQL ``ts_headline`` SQL function. 

489 

490 This function applies automatic casting of the REGCONFIG argument 

491 to use the :class:`_postgresql.REGCONFIG` datatype automatically, 

492 and applies a return type of :class:`_types.TEXT`. 

493 

494 Assuming the PostgreSQL dialect has been imported, either by invoking 

495 ``from sqlalchemy.dialects import postgresql``, or by creating a PostgreSQL 

496 engine using ``create_engine("postgresql...")``, 

497 :class:`_postgresql.ts_headline` will be used automatically when invoking 

498 ``sqlalchemy.func.ts_headline()``, ensuring the correct argument and return 

499 type handlers are used at compile and execution time. 

500 

501 .. versionadded:: 2.0.0rc1 

502 

503 """ 

504 

505 inherit_cache = True 

506 type = TEXT 

507 

508 def __init__(self, *args, **kwargs): 

509 args = list(args) 

510 

511 # parse types according to 

512 # https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-HEADLINE 

513 if len(args) < 2: 

514 # invalid args; don't do anything 

515 has_regconfig = False 

516 elif ( 

517 isinstance(args[1], elements.ColumnElement) 

518 and args[1].type._type_affinity is types.TSQUERY 

519 ): 

520 # tsquery is second argument, no regconfig argument 

521 has_regconfig = False 

522 else: 

523 has_regconfig = True 

524 

525 if has_regconfig: 

526 initial_arg = coercions.expect( 

527 roles.ExpressionElementRole, 

528 args.pop(0), 

529 apply_propagate_attrs=self, 

530 name=getattr(self, "name", None), 

531 type_=types.REGCONFIG, 

532 ) 

533 initial_arg = [initial_arg] 

534 else: 

535 initial_arg = [] 

536 

537 addtl_args = [ 

538 coercions.expect( 

539 roles.ExpressionElementRole, 

540 c, 

541 name=getattr(self, "name", None), 

542 apply_propagate_attrs=self, 

543 ) 

544 for c in args 

545 ] 

546 super().__init__(*(initial_arg + addtl_args), **kwargs)