From b1b97ed1fcac777c4f42fdf84e05f8d59f63b679 Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Thu, 16 Jul 2020 21:32:52 +0200 Subject: Add support for regular expression on supported backend. Two operations have been defined: * :meth:`~.ColumnOperators.regexp_match` implementing a regular expression match like function. * :meth:`~.ColumnOperators.regexp_replace` implementing a regular expression string replace function. Fixes: #1390 Change-Id: I44556846e4668ccf329023613bd26861d5c674e6 --- lib/sqlalchemy/dialects/postgresql/base.py | 47 ++++++++++++++++++++++++++++++ 1 file changed, 47 insertions(+) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 07405e6d1..8eb116111 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1796,6 +1796,53 @@ class PGCompiler(compiler.SQLCompiler): else "" ) + def _regexp_match(self, base_op, binary, operator, kw): + flags = binary.modifiers["flags"] + if flags is None: + return self._generate_generic_binary( + binary, " %s " % base_op, **kw + ) + if isinstance(flags, elements.BindParameter) and flags.value == "i": + return self._generate_generic_binary( + binary, " %s* " % base_op, **kw + ) + flags = self.process(flags, **kw) + string = self.process(binary.left, **kw) + pattern = self.process(binary.right, **kw) + return "%s %s CONCAT('(?', %s, ')', %s)" % ( + string, + base_op, + flags, + pattern, + ) + + def visit_regexp_match_op_binary(self, binary, operator, **kw): + return self._regexp_match("~", binary, operator, kw) + + def visit_not_regexp_match_op_binary(self, binary, operator, **kw): + return self._regexp_match("!~", binary, operator, kw) + + def visit_regexp_replace_op_binary(self, binary, operator, **kw): + string = self.process(binary.left, **kw) + pattern = self.process(binary.right, **kw) + flags = binary.modifiers["flags"] + if flags is not None: + flags = self.process(flags, **kw) + replacement = self.process(binary.modifiers["replacement"], **kw) + if flags is None: + return "REGEXP_REPLACE(%s, %s, %s)" % ( + string, + pattern, + replacement, + ) + else: + return "REGEXP_REPLACE(%s, %s, %s, %s)" % ( + string, + pattern, + replacement, + flags, + ) + def visit_empty_set_expr(self, element_types): # cast the empty set to the type we are comparing against. if # we are comparing against the null type, pick an arbitrary -- cgit v1.2.1