Re: workaround for lack of REPLACE() function
От | Joe Conway |
---|---|
Тема | Re: workaround for lack of REPLACE() function |
Дата | |
Msg-id | 3D2DCF9C.7090504@joeconway.com обсуждение исходный текст |
Ответ на | workaround for lack of REPLACE() function ("Agent155 Support" <matt@planetnet.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: