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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Converting a plperlu function to a plpgsql function

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Converting a plperlu function to a plpgsql function

--- Joe Conway 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Converting a plperlu function to a plpgsql function

On Thu, 2004-07-22 at 15:09, Jeff Eckermann wrote:
> --- Joe Conway 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Converting a plperlu function to a plpgsql function

Jeff Eckermann 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org