Thread: Converting a plperlu function to a plpgsql function
Hello,
I am trying to convert a database function that is written in perl to a
PL/pgSQL function. However, there a a couple of lines that I don't
think can be converted.
First line:
my @active_tables=split(/,/,$tables);
Is there anyway to split a variable like the perl split above?
Second line:
if ($r=~/^-([0-9]?)([A-z_]+)/){ my $locid = $1; my $table = $2;
Is there any way to to regular expressions similar to above. I need to
be able to save the matches for later use. I know you can do regular
expressions in sql but I can't find any equivalent for the above.
Now you may be saying that why don't I just leave it in perl? Well, I
would like to but my boss wants me to convert this to PL/pgSQL....long
story. Anyway, if it can't be done it can't be done. But I was just
wondering if anyone has any advice.
Thanks for the help.
Later
--
Devin Whalen
Programmer
Synaptic Vision Inc
Phone-(416) 539-0801
Fax- (416) 539-8280
1179A King St. West
Toronto, Ontario
Suite 309 M6K 3C5
Home-(416) 653-3982
Devin Whalen wrote:
> First line:
> my @active_tables=split(/,/,$tables);
>
> Is there anyway to split a variable like the perl split above?
I'm no perl guru, but in 7.4 I believe this does what you're looking for:
regression=# select string_to_array('1,2,3',','); string_to_array
----------------- {1,2,3}
(1 row)
> Second line:
>
> if ($r=~/^-([0-9]?)([A-z_]+)/)
> {
> my $locid = $1;
> my $table = $2;
Not sure about this one. Hopefully someone else can chime in. Maybe a
little less efficient, but it seems like it would be easy enough to
parse when true.
HTH,
Joe
--- Joe Conway <mail@joeconway.com> wrote:
> Devin Whalen wrote:
> > First line:
> > my @active_tables=split(/,/,$tables);
> >
> > Is there anyway to split a variable like the perl
> split above?
>
> I'm no perl guru, but in 7.4 I believe this does
> what you're looking for:
>
> regression=# select string_to_array('1,2,3',',');
> string_to_array
> -----------------
> {1,2,3}
> (1 row)
>
> > Second line:
> >
> > if ($r=~/^-([0-9]?)([A-z_]+)/)
> > {
> > my $locid = $1;
> > my $table = $2;
PostgreSQL doesn't offer capturing parentheses. The
regex library does offer that, but no-one has yet done
the coding to bring that functionality into
PostgreSQL.
You could do it in two steps:
1. Test using a regular expression
2. locid := substr(r,1); table := substr(r,2,1-len(r))
(better check the syntax on those).
>
> Not sure about this one. Hopefully someone else can
> chime in. Maybe a
> little less efficient, but it seems like it would be
> easy enough to
> parse when true.
>
> HTH,
>
> Joe
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
On Thu, 2004-07-22 at 15:09, Jeff Eckermann wrote:
> --- Joe Conway <mail@joeconway.com> wrote:
> > Devin Whalen wrote:
> > > First line:
> > > my @active_tables=split(/,/,$tables);
> > >
> > > Is there anyway to split a variable like the perl
> > split above?
> >
> > I'm no perl guru, but in 7.4 I believe this does
> > what you're looking for:
> >
> > regression=# select string_to_array('1,2,3',',');
> > string_to_array
> > -----------------
> > {1,2,3}
> > (1 row)
> >
> > > Second line:
> > >
> > > if ($r=~/^-([0-9]?)([A-z_]+)/)
> > > {
> > > my $locid = $1;
> > > my $table = $2;
>
> PostgreSQL doesn't offer capturing parentheses. The
> regex library does offer that, but no-one has yet done
> the coding to bring that functionality into
> PostgreSQL.
>
> You could do it in two steps:
> 1. Test using a regular expression
> 2. locid := substr(r,1); table := substr(r,2,1-len(r))
> (better check the syntax on those).
>
> >
> > Not sure about this one. Hopefully someone else can
> > chime in. Maybe a
> > little less efficient, but it seems like it would be
> > easy enough to
> > parse when true.
> >
> > HTH,
> >
> > Joe
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> > majordomo@postgresql.org
> >
>
>
>
>
Hey,
Thanks for the advice guys. The main reason that we wanted to change
from a perl function was because we were having problems getting the
perl library installed for postgres. We think we have solved that
problem so it looks like I won't have to convert the function. However,
your responses have made me realize that I can convert it. So I think
I will convert it because it saves having to connect up to the database
from inside the perl function. This might be a little faster??
Thanks for the help.
Later
--
Devin Whalen
Programmer
Synaptic Vision Inc
Phone-(416) 539-0801
Fax- (416) 539-8280
1179A King St. West
Toronto, Ontario
Suite 309 M6K 3C5
Home-(416) 653-3982
Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
>> Second line:
>>
>> if ($r=~/^-([0-9]?)([A-z_]+)/)
>> {
>> my $locid = $1;
>> my $table = $2;
> PostgreSQL doesn't offer capturing parentheses.
Hm? See the substring function:
regression=# select substring('foobar' from 'o(.)a');substring
-----------b
(1 row)
You'd have to use it twice to collect two separate substrings, which is
mildly annoying, but it's hard to see how to do better without bizarre
behind-the-scenes stuff (like Perl's magic $n variables).
regards, tom lane