Thread: regular expression substittion function?
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.
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
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
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
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
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.
> 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
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.
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
> 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
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
>> 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
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