regular expression substittion function?

Started by Bruno Wolff IIIalmost 26 years ago13 messagesgeneral
Jump to latest
#1Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bruno Wolff III (#2)
Re: regular expression substittion function?

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

#2Bruno Wolff III
bruno@wolff.to

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.

#3Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Bruno Wolff III (#2)
Re: regular expression substittion function?

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
#4Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Bruno Wolff III (#2)
Re: regular expression substittion function?

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
#5David E. Wheeler
david@wheeler.net
In reply to: Bruce Momjian (#3)
Re: regular expression substittion function?

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

#6Bruce Momjian
pgman@candle.pha.pa.us
In reply to: David E. Wheeler (#5)
Re: regular expression substittion function?

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
#7David E. Wheeler
david@wheeler.net
In reply to: Bruce Momjian (#6)
Re: regular expression substittion function?

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

#8Bruno Wolff III
bruno@wolff.to
In reply to: Tom Lane (#1)
Re: regular expression substittion function?

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.

#9Bruce Momjian
pgman@candle.pha.pa.us
In reply to: David E. Wheeler (#7)
Re: regular expression substittion function?

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
#10Bruno Wolff III
bruno@wolff.to
In reply to: Bruce Momjian (#3)
Re: regular expression substittion function?

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.

#11Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bruno Wolff III (#8)
Re: regular expression substittion function?

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

#12Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bruce Momjian (#9)
Re: regular expression substittion function?

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

#13David E. Wheeler
david@wheeler.net
In reply to: Tom Lane (#12)
Re: regular expression substittion function?

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