Обсуждение: syntax error in plperl wrapper for Perl regex replace.

Hi,

I am trying to create a simple wrapper to use Perl regexp replace capabilites, which
supports \L (aka "lowercase from here on") in a replacement-strings, not available in
postgre native regexp functions.

Based on this article:
https://www.anchor.com.au/blog/2013/09/extending-postgresql-high-level-languages-cats/
I copy-pasted the function code with a little adjustment, leading to:

----------
create or replace function perl_regexp_replace (text, text, text, text)
    returns text as $funcbody$

$_=$_[0];
s/$_[1]/$_[2]/$_[3];
return $_;

$funcbody$ language plperl immutable;
----------

When running the above code, I get a syntax error in the line "s/..".

When I remove this line, the function compiles, but (of course) does not do anything
usefull.

So, what is wrong with line:
    s/$_[1]/$_[2]/$_[3];

Thank in advance for reply.

R.Golis, Prague.

William,

Thank you for your reply.

I am familiar with "s" command of sed and with regular expressions in general. However, I
am not familiar with Perl.

My attempt to use the "s" command in my function is exactly the way you describe, i.e:

  s/regex_to_match/replacement-string-referencing-captured-groups/optional-options

I just tried to use function parameters $_[1], $_[2], $_[3] instead fixed strings.
However, it is rejected by postgre as syntax error.

Why? Must be some trivial, novice error.

R.Golis.

####################################
On Thu, 23 Aug 2018 17:05:51 -0400, William Jackson <wtjack3@gmail.com> wrote:

> The correct syntax for Perl regex substitutions is
> 
> s/pattern_to_match/string_to_replace_matches_with/
> 
> After the third slash, you may add a single letter to indicate special
> options, like g for global (matches however many times the pattern occurs
> in the string) or i for case insensitivity. However, you may at most have
> three of those slashes in your expression, with one substitution at a time.
> There are ways to do multiple substitutions by using the default variables
> for pattern matches, but it might be beyond the scope of your
> time/usefulness. You may still want to have a look at
> https://perldoc.perl.org/perlretut.html for details.
> 
> -WTJ

"io.sys" <io.sys@post.cz> writes:
> I just tried to use function parameters $_[1], $_[2], $_[3] instead fixed strings.
> However, it is rejected by postgre as syntax error.

That's not a Postgres problem, it's a Perl problem.  I'm no Perl
expert, but I think if you study the Perl docs carefully you will
find there are restrictions on what sorts of variable references
can be interpolated into a regexp ... and a subscripted reference
probably doesn't qualify.

            regards, tom lane

Tom,

You are right, it is a Perl's fault, not Postgre's.

You are right also with assumption, that the "s" command of Perl does not accept variable
in every of it's part. I have narrowed down the problem (btw, I could not find anything
about it is Perl docs) to the options part, ie. after last slash. Perl only accepts literals
there, not a variable of any kind. So these options must be hardcoded, not a dynamic value
constructed at runtime.

This compiles:
    s/$_[1]/$_[2]/ig;

But even then, the command does not work as expected, as it does not interpret the
backreferences in a replacement string passed to it. It uses it literally. For example,
one would expect that the following call will return 'Praha':

select perl_regexp_replace ('PRAHA', '^(.)(.*)$', '$1\L$2');

But it returns:
    $1\L$2

Well, Perl is praised for it's capabilities at the field of text processing, but this
experience disappointed me a little bit. Never mind, I will solve the things different way.

Thank you all for your inputs.

Regards, Roman Golis.

####################################
On Thu, 23 Aug 2018 22:17:10 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "io.sys" <io.sys@post.cz> writes:
> > I just tried to use function parameters $_[1], $_[2], $_[3] instead fixed strings.
> > However, it is rejected by postgre as syntax error.
> 
> That's not a Postgres problem, it's a Perl problem.  I'm no Perl
> expert, but I think if you study the Perl docs carefully you will
> find there are restrictions on what sorts of variable references
> can be interpolated into a regexp ... and a subscripted reference
> probably doesn't qualify.
> 
>             regards, tom lane

Hi, Cédric.

Works! Thank you very much for the hint. Apparently, you are much more
experienced with Perl, than me.

Just for reference, my working function wrapper for Perl regexp replace
now looks like this:

----------
create or replace function perl_regexp_replace (text, text, text, text)
    returns text as $funcbody$

$_=$_[0];
eval "s/$_[1]/$_[2]/$_[3]";
return $_;

$funcbody$ language plperl immutable strict;
----------

Regards, Roman.


####################################
On Thu, 30 Aug 2018 11:20:49 +0200, Cédric Godin <cedric.godin@skynet.be>
wrote:

> Not being a perl expert either I think the problem lies in the regex
> line. You should eval it to have the expected result (and you can use
> your 4th parameter again then).
>
> eval "s/$_[1]/$_[2]/$_[3]";
>
> Regards, Cédric