This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: pgsql-novice@postgresql.org
Date: Sat, 04 Oct 2003 01:29:00 +0200
From: Nabil Sayegh <postgresql@e-trolley.de>
Subject: [NOVICE] perlsub
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
===
Subject: Re: [NOVICE] perlsub
From: Oliver Elphick <olly@lfix.co.uk>
To: Nabil Sayegh <postgresql@e-trolley.de>
Cc: pgsql-novice@postgresql.org
Date: Sat, 04 Oct 2003 07:20:10 +0100
Nabil Sayegh wrote:
> 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)
===
Date: Sat, 04 Oct 2003 20:03:08 +0200
From: Nabil Sayegh <nas@e-trolley.de>
To: Oliver Elphick <olly@lfix.co.uk>
Cc: Nabil Sayegh <postgresql@e-trolley.de>, pgsql-novice@postgresql.org
Subject: Re: [NOVICE] perlsub
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 :(
===
Date: Sun, 5 Oct 2003 15:03:17 +0200
From: Martin_vi_Lange@t-online.de (Martin Lange)
To: Nabil Sayegh <postgresql@e-trolley.de>
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] perlsub
Nabil Sayegh wrote:
> 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"
===
Date: Sun, 05 Oct 2003 18:53:04 +0200
From: Nabil Sayegh <postgresql@e-trolley.de>
To: Martin Lange <martin_vi_lange@t-online.de>
Cc: Nabil Sayegh <postgresql@e-trolley.de>, pgsql-novice@postgresql.org
Subject: Re: [NOVICE] perlsub
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);
Thanks for your answer.
The snippet was just an example.
What I really want(ed) to do was arbitrary regex search/replace.
===
Date: Sun, 05 Oct 2003 12:23:22 -0500
From: Daniel Staal <DStaal@usa.net>
To: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Subject: Re: [NOVICE] perlsub
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.
===
Date: Mon, 6 Oct 2003 07:17:01 -0700 (PDT)
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: [NOVICE] perlsub
To: Nabil Sayegh <postgresql@e-trolley.de>
Cc: pgsql-novice@postgresql.org
Nabil Sayegh <postgresql@e-trolley.de> wrote:
> 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);
> The snippet was just an example.
> What I really want(ed) to do was arbitrary regex
> search/replace.
(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.
===
Date: Mon, 06 Oct 2003 16:46:19 +0200
From: Nabil Sayegh <postgresql@e-trolley.de>
To: Jeff Eckermann <jeff_eckermann@yahoo.com>
Cc: Nabil Sayegh <postgresql@e-trolley.de>, pgsql-novice@postgresql.org
Subject: Re: [NOVICE] perlsub
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
===
Date: Tue, 7 Oct 2003 12:46:59 -0700 (PDT)
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: [NOVICE] perlsub
To: Nabil Sayegh <postgresql@e-trolley.de>
Cc: pgsql-novice@postgresql.org
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
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.
===
Subject: Re: [NOVICE] perlsub
From: Oliver Elphick <olly@lfix.co.uk>
To: Jeff Eckermann <jeff_eckermann@yahoo.com>
Cc: Nabil Sayegh <postgresql@e-trolley.de>, pgsql-novice@postgresql.org
Date: Tue, 07 Oct 2003 22:18:01 +0100
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.
===
Date: Tue, 7 Oct 2003 14:49:11 -0700 (PDT)
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: [NOVICE] perlsub
To: Oliver Elphick <olly@lfix.co.uk>
Cc: Nabil Sayegh <postgresql@e-trolley.de>, pgsql-novice@postgresql.org
Oliver Elphick <olly@lfix.co.uk> wrote:
> 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
===
Date: Tue, 7 Oct 2003 15:16:44 -0700 (PDT)
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: [NOVICE] perlsub
To: Nabil Sayegh <postgresql@e-trolley.de>, Oliver Elphick <olly@lfix.co.uk>
Cc: pgsql-novice@postgresql.org
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 ;-)
====
To: Jeff Eckermann <jeff_eckermann@yahoo.com>
Cc: Oliver Elphick <olly@lfix.co.uk>, Nabil Sayegh <postgresql@e-trolley.de>, pgsql-novice@postgresql.org
Subject: Re: [NOVICE] perlsub
Date: Tue, 07 Oct 2003 23:26:09 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
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.
===
Date: Wed, 8 Oct 2003 07:09:52 -0700 (PDT)
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: [NOVICE] perlsub
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-novice@postgresql.org
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 ...
>
> * 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 ;-)
===
From: Josh Berkus <josh@agliodbs.com>
To: Systems Administrator <sysadmin@sunet.com.au>
Subject: Re: [NOVICE] SQL from PLPerl
Date: Mon, 13 Oct 2003 19:27:28 -0700
Cc: pgsql-novice@postgresql.org
> Hi all. Another question -- I'm using plperl, and I was wondering
> what the best way to do a SELECT in it is. Am I best off using DBI and
> having the plperl connect to the database again? Or is there some other
> way to do it?
There is currently no good way to do this in PL/perl.
PL/pgSQL, C, and I believe PL/Tcl can interact with the database. No other PL
at this time can. Hopefully this will change in the future.
===
Date: Mon, 13 Oct 2003 20:21:01 -0700
From: Joe Conway <mail@joeconway.com>
To: josh@agliodbs.com
Cc: Systems Administrator <sysadmin@sunet.com.au>, pgsql-novice@postgresql.org
Subject: Re: [NOVICE] SQL from PLPerl
Josh Berkus wrote:
> PL/pgSQL, C, and I believe PL/Tcl can interact with the
> database. No other PL at this time can. Hopefully this
> will change in the future.
PL/R can:
http://www.joeconway.com/plr/doc/plr-spi-rsupport-funcs.html
So can PL/Python:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpython-database.html
===
Subject: [GENERAL] PLPERL function error - utf-8 to iso8859-1
To: pgsql-general@postgresql.org
From: "Patrick Hatcher" <PHatcher@macys.com>
Date: Tue, 14 Oct 2003 16:17:53 -0700
Trying to create a plperl function to strip non-friendly mainframe
characters from a string. However, when I try to add the Trademark symbol
(