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.

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