Re: workaround for lack of REPLACE() function

(crossposted to -hackers, should follow up on that list)

> Well, OVERLAY is defined as:
> overlay(string placing string from integer [for integer])
> and replace() is defined (by me at least) as:
> replace(inputstring, old-substr, new-substr)

OK.

> OVERLAY requires that I know the "from" position and possibly the "for"
> in advance. Other functions (such as strpos() and substr()) can be used
> to help...

Right. So you can do your example pretty easily:

thomas=# select overlay(f1 placing '' from position('/local' in f1)
thomas-# for length('/local')) from strtest;
overlay
--------------------
/usr/pgsql/data
/m1/usr/pgsql/data

And if you don't like that much typing you can do:

thomas=# create function replace(text, text, text) returns text as '
thomas'# select overlay($1 placing $3 from position($2 in $1) for
length($2));
thomas'# ' language 'sql';
CREATE FUNCTION
thomas=# select replace(f1, '/local', '') from strtest;
replace
--------------------
/usr/pgsql/data
/m1/usr/pgsql/data

> But now what happens if you wanted to replace all of the '/' characters
> with '\'?...
> You can't do this at all with overlay(), unless you want to write a
> PL/pgSQL function and loop through each string. I started out with
> exactly this, using strpos() and substr(), but I thought a C function
> was cleaner, and it is certainly faster.

OK, this is in the "can't do it what we have" category. Should we have
it accept a regular expression rather than a simple string? In either
case it should probably go into the main distro. Except that I see
"REPLACE" is mentioned as a reserved word in SQL99. But has no other
mention in my copy of the draft standard. Anyone else have an idea what
it might be used for in the standard?

The other functions look useful too, unless to_char() and varbit can be
evolved to support this functionality.

- Thomas