From: 100.179370@germanynet.de
To: pgsql-general@postgresql.org
Subject: Re: String REPLACE function
Date: Wed, 28 Mar 2001 21:16:54 +0200 (MEST)
Message-ID: <m14iLR8-000QZtC@Schnecke.Windsbach.de> (raw)
In-Reply-To: <Pine.LNX.4.21.0103220708370.28322-100000@olympus.scw.org>
Hi Joel,
Joel Burton schrieb:
>
> Is there a function for substring replacement?
>
> There's translate(s, a, b), but that replaces all characters in a with
> their corresponding character in b, eg.
>
> replace ('this is a cat', 'cat', 'dog') => ghis is o dog
>
> I'm looking for a function that matches the whole string and replaces it:
>
> replace ('this is a cat', 'cat', 'dog') => this is a dog
>
> I know I could write it in PL/PGSQL, but it seems that it would be very
> inefficient. We're not using PL/perl or PL/tcl in this project, so I'd
> rather not do it this way if it coulod be avoided.
As long as you rebuild this functionality with native SQL and/or
PGSQL functions performance should not be a problem at all. Have
a look at this code example which uses plpgsql as language.
CREATE FUNCTION stuff (text, text, text)
RETURNS text
AS '
DECLARE
source ALIAS FOR $1;
search ALIAS FOR $2;
newstr ALIAS FOR $3;
prefix text;
postfix text;
pos integer;
len_of_search integer;
BEGIN
pos := position(search in source);
IF pos <= 0 THEN
RETURN source;
END IF;
len_of_search := char_length(search);
prefix := substring(source from 1 for pos - 1);
postfix := substring(source from pos + len_of_search);
RETURN textcat(textcat(prefix, newstr), postfix);
END;
' LANGUAGE 'plpgsql'
I have made a very simple test to give you an idea of the
performance. I ran
update dt set d = stuff(d, 'dog', 'cat') where d notnull;
on a table with 4096 records of 'this is a dog'. With replacement
output of
time psql test -c "update dt set d = stuff(d, 'dog', 'cat') where d notnull; "
realtime was about 5.8s, without replacement about 1.9s. This on
my now ancient Pentium clone (200MHz IDT Winchip, 72MB RAM). I
think this is not so bad for interpreted PL.
>
> Am I missing anything? Has anyone already solved this?
>
> Thanks!
Hope this helps.
> ...
Martin
--
Dipl-Ing. Martin Jacobs * Windsbach * Germany
Registered Linux User #87175, http://counter.li.org/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: 100.179370@germanynet.de, pgsql-general@postgresql.org
Subject: Re: String REPLACE function
In-Reply-To: <m14iLR8-000QZtC@Schnecke.Windsbach.de>
References: <Pine.LNX.4.21.0103220708370.28322-100000@olympus.scw.org> <m14iLR8-000QZtC@Schnecke.Windsbach.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox