Обсуждение: perlsub
Hello again,
now that perlsub works i need to know how backreferences work with
plperl. $1 - $9 doesn't work. Any idea ?
perlsub:
-----------------------------------------------
CREATE FUNCTION perlsub(text, text, text) RETURNS text AS '
my ($data, $pat, $repl) = @_;
$data =~ s/$pat/$repl/;
return $data
' LANGUAGE 'plperl';
-----------------------------------------------
SELECT perlsub('a=b','([^=]+)=(.+)','key:$1;val:$2');
Any idea ? Just a matter of quoting ?
Also this example doesn't work with * instead of + :(
TIA
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
On Sat, 2003-10-04 at 00:29, Nabil Sayegh wrote:
> Hello again,
>
> now that perlsub works i need to know how backreferences work with
> plperl. $1 - $9 doesn't work. Any idea ?
>
> perlsub:
> -----------------------------------------------
> CREATE FUNCTION perlsub(text, text, text) RETURNS text AS '
> my ($data, $pat, $repl) = @_;
> $data =~ s/$pat/$repl/;
> return $data
> ' LANGUAGE 'plperl';
> -----------------------------------------------
>
> SELECT perlsub('a=b','([^=]+)=(.+)','key:$1;val:$2');
>
> Any idea ? Just a matter of quoting ?
It's a problem with Perl itself rather than with PL/Perl. I tried that
in a Perl script and it produces the same result. I couldn't find any
way of including $ in the replacement string as a metacharacter.
You need to consult a Perl guru.
When you do get an example that works in a script, remember to double
any single quotes and backslashes when you create the function.
> Also this example doesn't work with * instead of + :(
I don't see any difference:
junk=# SELECT perlsub('a=b','([^=]*)=(.*)','key:$1;val:$2');
perlsub
---------------
key:$1;val:$2
(1 row)
junk=# SELECT perlsub('a=b','([^=]+)=(.+)','key:$1;val:$2');
perlsub
---------------
key:$1;val:$2
(1 row)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For the word of God is quick, and powerful, and
sharper than any twoedged sword, piercing even to the
dividing asunder of soul and spirit, and of the joints
and marrow, and is a discerner of the thoughts and
intents of the heart." Hebrews 4:12
Hello Nabil,
you worte at 2003-10-04T01:29:00:
> perlsub:
> -----------------------------------------------
> CREATE FUNCTION perlsub(text, text, text) RETURNS text AS '
> my ($data, $pat, $repl) = @_;
> $data =~ s/$pat/$repl/;
> return $data
> ' LANGUAGE 'plperl';
> -----------------------------------------------
> SELECT perlsub('a=b','([^=]+)=(.+)','key:$1;val:$2');
> Any idea ? Just a matter of quoting ?
What I understand: You want to split some data into a pair of key
and value.
So, just do that:
($key, $val) = split(/=/, $data);
HTH.
cya :-)
Martin "vi"
Thanks for your answer. The snippet was just an example. What I really want(ed) to do was arbitrary regex search/replace. Martin Lange wrote: > What I understand: You want to split some data into a pair of key > and value. > > So, just do that: > > ($key, $val) = split(/=/, $data); > > HTH. -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
--On Sunday, October 5, 2003 18:53 +0200 Nabil Sayegh <postgresql@e-trolley.de> wrote: > Thanks for your answer. > > The snippet was just an example. > What I really want(ed) to do was arbitrary regex search/replace. I think your problem is that Perl only does one level of interpolation: variables in variables just don't get looked at. You might be able to do this in a two-level search/replace: the first finds the values in the data string, and the second replaces placeholders with the correct values, but I'm not exactly sure if it would work. I'd take this to a Perl forum. You need an expert. Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
(sound of dusting off of perl) Capturing using $1 etc definitely does work in pl/perl functions: I have used that plenty. Strictly speaking, backreferences in search-and-replace are signified by \1, \2 etc (not $1, $2). In the body of a PostgreSQL function those backslashes would need to be escaped: my preference is to double them (\\1, \\2). This does not seem to relate to your example though. Arbitrary search-and-replace works fine for me, in a simple case: create function s(text, text, text) returns text as ' $_[0] =~ s/$_[1]/$_[2]/; return $_[0]; ' language 'plperl'; Perhaps if you could post a "real" example, you might get more useful help. --- Nabil Sayegh <postgresql@e-trolley.de> wrote: > Thanks for your answer. > > The snippet was just an example. > What I really want(ed) to do was arbitrary regex > search/replace. > > Martin Lange wrote: > > > What I understand: You want to split some data > into a pair of key > > and value. > > > > So, just do that: > > > > ($key, $val) = split(/=/, $data); > > > > HTH. > > -- > e-Trolley Sayegh & John, Nabil Sayegh > Tel.: 0700 etrolley /// 0700 38765539 > Fax.: +49 69 8299381-8 > PGP : http://www.e-trolley.de > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose > an index scan if your > joining column's datatypes do not match __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
Jeff Eckermann wrote:
> Perhaps if you could post a "real" example, you might
> get more useful help.
Here we go:
SELECT s('abc 123','([^ ]*) (.*)','$2 $1');
This regex should swaps 2 columns seperated by 1 space.
For the moment I harcoded my regex in a special function, but I would be
delighted if it would be possible (without the use of eval) to have
arbitrary regular expression search-replace.
I don't think we're getting off-topic as too many ppl have asked this
question already and always the answer has been: Use PL/Perl
cu
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
Oliver Elphick wrote:
> It's a problem with Perl itself rather than with PL/Perl. I tried that
> in a Perl script and it produces the same result. I couldn't find any
> way of including $ in the replacement string as a metacharacter.
Ok, here's what I got from #perl
I need to 'eval' the string to make it an executable expression.
-------------------------8<-------------------------------
#!/usr/bin/perl
print perlsub('abc','b','123')."\n";
print perlsub('a=b','([^=]+)=(.+)','key:$1;val:$2;')."\n";
sub perlsub
{
my ($data, $pat, $repl) = @_;
eval "\$data =~ s/$pat/$repl/gi";
return $data;
}
-------------------------8<-------------------------------
As a Pl/Perl Function this still doesnt work.
Seems like it's forbidden, as it would allow to execute
arbitrary code :(
-------------------------8<-------------------------------
CREATE FUNCTION perlsub(text, text, text) RETURNS text AS '
my ($data, $pat, $repl) = @_;
eval "\$data =~ s/$pat/$repl/gi";
return $data
' LANGUAGE 'plperl';
-------------------------8<--------------------------------
plasma=# SELECT perlsub('a=b','([^=]+)=(.+)','key:$1;val:$2');
ERROR: creation of function failed: 'eval "string"' trapped by
operation mask at (eval 2) line 3.
Seems like I have to hardcode the regular expression in the function :(
TFYH
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
jeff=# create or replace function s(text, text, text)
returns text as '$_[0] =~ s/$_[1]/$_[2]/ee; return
$_[0]' language 'plperl' with (isstrict);
CREATE FUNCTION
jeff=# SELECT s('abc 123','([^ ]*) (.*)','$2 $1');
ERROR: creation of function failed: eval "string"
trapped by operation mask at (eval 4) line 1.
Looks like you just can't do it with pl/perl, because
"eval" is blocked for safety reasons. If using an
untrusted language is not an issue for you, you should
be able to do what you want using pl/perlu (untrusted
perl).
I believe you can install pl/perlu in just the same
way as installing pl/perl. You will probably need to
drop pl/perl first though.
--- Nabil Sayegh <postgresql@e-trolley.de> wrote:
> Jeff Eckermann wrote:
> > Perhaps if you could post a "real" example, you
> might
> > get more useful help.
>
> Here we go:
>
> SELECT s('abc 123','([^ ]*) (.*)','$2 $1');
>
> This regex should swaps 2 columns seperated by 1
> space.
>
> For the moment I harcoded my regex in a special
> function, but I would be
> delighted if it would be possible (without the use
> of eval) to have
> arbitrary regular expression search-replace.
>
> I don't think we're getting off-topic as too many
> ppl have asked this
> question already and always the answer has been: Use
> PL/Perl
>
> cu
> --
> e-Trolley Sayegh & John, Nabil Sayegh
> Tel.: 0700 etrolley /// 0700 38765539
> Fax.: +49 69 8299381-8
> PGP : http://www.e-trolley.de
>
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
On Tue, 2003-10-07 at 20:46, Jeff Eckermann wrote: > I believe you can install pl/perlu in just the same > way as installing pl/perl. You will probably need to > drop pl/perl first though. No. plperl and plperlu can be installed simultaneously. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Blessed is the man that endureth temptation; for when he is tried, he shall receive the crown of life, which the Lord hath promised to them that love him." James 1:12
--- Oliver Elphick <olly@lfix.co.uk> wrote: > On Tue, 2003-10-07 at 20:46, Jeff Eckermann wrote: > > > I believe you can install pl/perlu in just the > same > > way as installing pl/perl. You will probably need > to > > drop pl/perl first though. > > No. plperl and plperlu can be installed > simultaneously. Hmm. I had thought the same, but I get this: jeff@Tetsuo=> createlang -e plperlu jeff SELECT oid FROM pg_language WHERE lanname = 'plperlu'; Password: SELECT oid FROM pg_proc WHERE proname = 'plperl_call_handler' AND prorettype = 0 AND pronargs = 0; Password: SET autocommit TO 'on';CREATE FUNCTION "plperl_call_handler" () RETURNS LANGUAGE_HANDLER AS '$libdir/plperl' LANGUAGE C; Password: ERROR: function plperl_call_handler already exists with same argument types createlang: language installation failed jeff@Tetsuo=> psql -V psql (PostgreSQL) 7.3.1 __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
Ok, using plperlu:
jeck=# select s('fred','(fr)(ed)', '$1');
s
----
fr
(1 row)
jeck=# select s('fred','(fr)(ed)', '$2');
s
----
ed
(1 row)
jeck=# select s('fred','(fr)(ed)', '$2 $1');
ERROR: plperl: error from function: syntax error at
(eval 7) line 2, near "$2 $1
"
jeck=# select s('fred','(fr)(ed)', '$2." ".$1');
s
-------
ed fr
(1 row)
So, the string to be evaluated must meet normal perl
syntactic rules for an expression.
The example you gave worked fine for me from the
command line because I was typing the "$2 $1" directly
into the regex, so one interpolation did the job. In
the function the first interpolation placed the
argument string into the regex, not the value of the
variable, so the eval (s/.../.../ee) is needed to get
the value.
Hmm, maybe this was off-topic after all ;-)
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
> --- Oliver Elphick <olly@lfix.co.uk> wrote:
>> No. plperl and plperlu can be installed
>> simultaneously.
> Hmm. I had thought the same, but [ it doesn't work ]
> psql (PostgreSQL) 7.3.1
There was a silly bug in 7.3-7.3.2 ...
2003-04-26 11:19 tgl
* src/bin/scripts/createlang.sh (REL7_3_STABLE): Correct oversight
in createlang: test for pre-existing handler function was broken by
opaque->language_handler change. I see this is already fixed in
CVS tip, but must back-patch for 7.3.3.
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jeff Eckermann <jeff_eckermann@yahoo.com> writes: > > --- Oliver Elphick <olly@lfix.co.uk> wrote: > >> No. plperl and plperlu can be installed > >> simultaneously. > > > Hmm. I had thought the same, but [ it doesn't > work ] > > psql (PostgreSQL) 7.3.1 > > There was a silly bug in 7.3-7.3.2 ... > > 2003-04-26 11:19 tgl > > * src/bin/scripts/createlang.sh (REL7_3_STABLE): > Correct oversight > in createlang: test for pre-existing handler > function was broken by > opaque->language_handler change. I see this is > already fixed in > CVS tip, but must back-patch for 7.3.3. > Thanks. I'm not the administrator of this box, but I think I can hassle him into upgrading ;-) __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com