Using Postgres 9.4, is it possible to apply a function on the captured match?
Example: upper case only the string which are surrounded by double-quotes.
SELECT regexp_replace(
'123, "name", ignored~me, "Beer & Cheese", pi=3.14',
'"(.+?)"',
upper('"\1"'),
'g'
);
--Result : '123, "name", ignored~me, "Beer & Cheese", pi=3.14'
--Expected: '123, "NAME", ignored~me, "BEER & CHEESE", pi=3.14'
It looks like the function appearing in the 3rd parameter of regexp_replace is ignored. Is there anyway to use a function and passing the \n group capture as input argument?
I once had same question, and this
I did not get the answer, but general impression that you are not supposed to use SQL functions on arguments of regular expression functions...
In SQL, functions aren't first-class. They can't be passed. There is no part of the language that's interpreted either. It's declarative, compiled (with an optimizer), and evaluated strictly.
Keep in mind, under normal circumstance an update to any column requires rewriting the whole row (not just the column).
You can still accomplish this I would just be looking to a slightly smarter tool for it..
Here is an example with plperl,
CREATE LANGUAGE plperl;
CREATE OR REPLACE FUNCTION perl_dynamic_eval_regexp_replace( IN str text, IN pattern text, IN replacementPerl text, OUT text )
STRICT
AS $BODY$
my ($input, $pattern, $replacement) = @_;
$input =~ s/$pattern/eval $replacement/ge;
return $input;
$BODY$
LANGUAGE plperl
VOLATILE;
SELECT perl_dynamic_eval_regexp_replace(
'123, "name", ignored~me, "Beer & Cheese", pi=3.14',
'"(.+?)"',
$$uc("$1")$$
);