Re: workaround for lack of REPLACE() function

Thomas Lockhart wrote:
>>I think Thomas did just recently commit the SQL99 OVERLAY function, but
>>similar to Tom's comment, I don't like the way SQL99 defines it. I've
>>written a replace() C function (along with a couple of other string
>>manipulation functions) for my own use. If you'd like a copy let me know
>>and I'll gladly send it to you.
>
> OK, what don't you like about it? If you can define some functionality
> that we *should* have, then it is likely to go into the main distro.
> Either as an extension to existing functions or as a separate function.
>
> "Style" counts for not-much, but "can't do it with what we have" counts
> for a lot.

Hmmm, making justify my comment ;-)

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)

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, but consider the following:

test=# create table strtest(f1 text);
CREATE TABLE
test=# insert into strtest values('/usr/local/pgsql/data');
INSERT 124955 1
test=# select replace(f1,'/local','') from strtest;
replace
-----------------
/usr/pgsql/data
(1 row)

Now, how can I do this with overlay()? If I happen to know in advance
that my only input string is '/usr/local/pgsql/data', then I can do:

test=# select overlay(f1 placing '' from 5 for 6) from strtest;
overlay
-----------------
/usr/pgsql/data
(1 row)

But what if now I do:
test=# insert into strtest values('/m1/usr/local/pgsql/data');
INSERT 124957 1

Now

test=# select replace(f1,'/local','') from strtest;
replace
--------------------
/usr/pgsql/data
/m1/usr/pgsql/data
(2 rows)

works fine, but

test=# select overlay(f1 placing '' from 5 for 6) from strtest;
overlay
--------------------
/usr/pgsql/data
/m1/cal/pgsql/data
(2 rows)

doesn't give the desired result. Of course you can work around this, but
it starts to get ugly:

test=# select overlay(f1 placing '' from strpos(f1,'/local') for 6) from
strtest;
overlay
--------------------
/usr/pgsql/data
/m1/usr/pgsql/data
(2 rows)

But now what happens if you wanted to replace all of the '/' characters
with '\'?

test=# select replace(f1,'/','\\') from strtest;
replace
--------------------------
\usr\local\pgsql\data
\m1\usr\local\pgsql\data
(2 rows)

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.

BTW, the other functions already in the string manipulation module are:

to_hex -- Accepts bigint and returns it as equivilent hex string
to_hex(bigint inputnum) RETURNS text

test=# select to_hex(123456789::bigint);
to_hex
---------
75bcd15
(1 row)

and

extract_tok -- Extracts and returns individual token from delimited
text
extract_tok(text inputstring, text delimiter, int posn) RETURNS text

test=# select extract_tok(extract_tok('f=1&g=3&h=4','&',2),'=',2);
extract_tok
-------------
3
(1 row)

extract_tok() is actually already in dblink (dblink_strtok), because it
is useful in that context, but it probably belongs in a contrib for
string manipulation instead. In fact, now that I think about it, so is
replace() (dblink_replace).

Regards,

Joe