pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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/



view thread (3+ messages)

Message-ID: <m14iLR8-000QZtC@Schnecke.Windsbach.de>
Permalink:  ../m14iLR8-000QZtC@Schnecke.Windsbach.de/
Also on:    postgresql.org/message-id/m14iLR8-000QZtC@Schnecke.Windsbach.de

 · 

reply

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