Thread: regular expression substittion function?

regular expression substittion function?

From
Bruno Wolff III
Date:
Are there any plans to provide a regular expression substitution function,
similar to translate but allowing more complex substitutions?

This is an example of what I want to use it for. I am using a query that
builds web urls from a couple of different types of data and then union
together. Some of the data is raw urls, and some is title information
that gets turn into search urls. The title information needs to be
massaged before being used in a url and it needs to be done in the query
if I want to do the union. The translate command could be used in my
particular case, but it will be very long, as I need to delete most
nonletter nondigit characters and change spaces to plusses. A regular
expression describing what I want would be much more compact.

Re: regular expression substittion function?

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
> Are there any plans to provide a regular expression substitution function,
> similar to translate but allowing more complex substitutions?

Not in Postgres itself.  Either pltcl or plperl offers you far better
text-mashing facilities than we could provide as SQL functions, so I
don't really see the point in partially duplicating their functionality.

            regards, tom lane

Re: regular expression substittion function?

From
Bruce Momjian
Date:
I thought someone suggested that, but I can't remember what happened to
it.  We have a Perl internal language that works in 7.1beta.  You
basically want a sed-type ability.  I can see real value in that.

> Are there any plans to provide a regular expression substitution function,
> similar to translate but allowing more complex substitutions?
>
> This is an example of what I want to use it for. I am using a query that
> builds web urls from a couple of different types of data and then union
> together. Some of the data is raw urls, and some is title information
> that gets turn into search urls. The title information needs to be
> massaged before being used in a url and it needs to be done in the query
> if I want to do the union. The translate command could be used in my
> particular case, but it will be very long, as I need to delete most
> nonletter nondigit characters and change spaces to plusses. A regular
> expression describing what I want would be much more compact.
>


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: regular expression substittion function?

From
Bruce Momjian
Date:
Added to TODO:

    * Add sed-like regular expression search/replace capability

> Are there any plans to provide a regular expression substitution function,
> similar to translate but allowing more complex substitutions?
>
> This is an example of what I want to use it for. I am using a query that
> builds web urls from a couple of different types of data and then union
> together. Some of the data is raw urls, and some is title information
> that gets turn into search urls. The title information needs to be
> massaged before being used in a url and it needs to be done in the query
> if I want to do the union. The translate command could be used in my
> particular case, but it will be very long, as I need to delete most
> nonletter nondigit characters and change spaces to plusses. A regular
> expression describing what I want would be much more compact.
>


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: regular expression substittion function?

From
David Wheeler
Date:
On Thu, 15 Feb 2001, Bruce Momjian wrote:

> I thought someone suggested that, but I can't remember what happened to
> it.  We have a Perl internal language that works in 7.1beta.  You
> basically want a sed-type ability.  I can see real value in that.

A "Perl internal language" in 7.1? Can you tell me what that means? Is it
like Oracle's feature allowing one to write SPs in Java? I can tell you
right now that the ability to write SPs in Perl would *totally* rock, from
my point of view.

Thanks!

David



Re: regular expression substittion function?

From
Bruno Wolff III
Date:
On Thu, Feb 15, 2001 at 10:59:11AM -0500,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > Are there any plans to provide a regular expression substitution function,
> > similar to translate but allowing more complex substitutions?
>
> Not in Postgres itself.  Either pltcl or plperl offers you far better
> text-mashing facilities than we could provide as SQL functions, so I
> don't really see the point in partially duplicating their functionality.

I tried to explain that in the message. Because I was doing a union
where some of the data needed to be changed, deferring the substitution
until after the rows were returned wasn't simple.

There are three ways I can handle things correctly. One is to split the
queries and combine the results in perl. I didn't do this originally
because there was no guarenty that either part of the union would return
any rows and I wanted to do something different if there weren't any
returned rows. That isn't an issue currently.

The second way that I could do things would be to mark what parts of the
returned url still needed to be quoted. Splitting the returned url into
3 columns with the middle one getting quoted would work in my situation.

The third was that since I really wanted to drop characters that would
be encoded as multiple characters, I could use a complicated
(and typo prone) translate call to handle the quoting problem.

It just seemed that things would be simpler to write and understand if
I could have used a perl like substitution function. I thought if other
people could use something like this that maybe it was something that
should get added to postgresql.

Re: regular expression substittion function?

From
Bruce Momjian
Date:
> On Thu, 15 Feb 2001, Bruce Momjian wrote:
>
> > I thought someone suggested that, but I can't remember what happened to
> > it.  We have a Perl internal language that works in 7.1beta.  You
> > basically want a sed-type ability.  I can see real value in that.
>
> A "Perl internal language" in 7.1? Can you tell me what that means? Is it
> like Oracle's feature allowing one to write SPs in Java? I can tell you
> right now that the ability to write SPs in Perl would *totally* rock, from
> my point of view.

Yes, SP's in Perl, Tcl, or PL/SQL-type languages.  Perl only works well in
7.1beta.

What do people think of this TODO item:

    * Add sed-like regular expression search/replace capability

Seems it would be nice for quick and dirty stuff rather than having to
define a function.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: regular expression substittion function?

From
Bruno Wolff III
Date:
On Thu, Feb 15, 2001 at 11:01:18AM -0500,
  Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> I thought someone suggested that, but I can't remember what happened to
> it.  We have a Perl internal language that works in 7.1beta.  You
> basically want a sed-type ability.  I can see real value in that.

I am running 7.1beta3. I will take a look at that and see what I can
figure out. I am still pretty much a beginner. I am doing OK with SQL
but have only briefly looked at using the language stuff.

Re: regular expression substittion function?

From
David Wheeler
Date:
On Thu, 15 Feb 2001, Bruce Momjian wrote:

> Yes, SP's in Perl, Tcl, or PL/SQL-type languages.  Perl only works well in
> 7.1beta.

Very cool! We have to compile --with-perl, yes? Where may I find
documentation on how to use it?

> What do people think of this TODO item:
>
>     * Add sed-like regular expression search/replace capability
>
> Seems it would be nice for quick and dirty stuff rather than having to
> define a function.

Yeah, I think that's a nice idea.

Thanks,

David


Re: regular expression substittion function?

From
Bruce Momjian
Date:
> On Thu, 15 Feb 2001, Bruce Momjian wrote:
>
> > Yes, SP's in Perl, Tcl, or PL/SQL-type languages.  Perl only works well in
> > 7.1beta.
>
> Very cool! We have to compile --with-perl, yes? Where may I find
> documentation on how to use it?

I think so.  You have to install the perl language in your database with
createlang.  Not sure about the steps or docs.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: regular expression substittion function?

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
> It just seemed that things would be simpler to write and understand if
> I could have used a perl like substitution function.

Why do you want a "perl-like" substitution function when you have real
Perl available?

CREATE FUNCTION perlsub(text, text, text) RETURNS text AS '
    my ($data, $pat, $repl) = $_;
    $data =~ s/$pat/$repl/;
    return $data
' LANGUAGE 'plperl';

(I'm not much of a Perl expert, but I think this is right.)

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO:
>    * Add sed-like regular expression search/replace capability

I fail to understand this enthusiam for building our own wheel, when
we already have a Formula I racer at hand.  Two of them, in fact.

            regards, tom lane

Re: regular expression substittion function?

From
Tom Lane
Date:
>> Very cool! We have to compile --with-perl, yes? Where may I find
>> documentation on how to use it?

> I think so.  You have to install the perl language in your database with
> createlang.  Not sure about the steps or docs.

http://www.postgresql.org/devel-corner/docs/postgres/plperl.htm

            regards, tom lane

Re: regular expression substittion function?

From
David Wheeler
Date:
On Thu, 15 Feb 2001, Tom Lane wrote:

> >> Very cool! We have to compile --with-perl, yes? Where may I find
> >> documentation on how to use it?
>
> > I think so.  You have to install the perl language in your database with
> > createlang.  Not sure about the steps or docs.
>
> http://www.postgresql.org/devel-corner/docs/postgres/plperl.htm

Way bitchen'! Thanks, Tom!

Best,

David