Re: search and replace

I do not try,
may be it exists different languages to make this in postgresql :

I make this in pl/pgsql, I do not have time to make this in C,

CREATE OR REPLACE FUNCTION "_replaceblock" (text, text, text) RETURNS
text AS'
DECLARE

text_begin ALIAS FOR $1 ;
key_name ALIAS FOR $2 ;
key_val ALIAS FOR $3 ;

key_begin VARCHAR(10) := '{' ;
key_end VARCHAR(10) := '}' ;

return_text TEXT ;
return_text_length INTEGER;

find_key_start INTEGER ;
find_key_end INTEGER ;

temp_text TEXT ;
key_search TEXT ;
key_value TEXT ;
BEGIN
IF (key_val IS NULL) THEN
key_value :='' ;
ELSE
key_value := key_val ;
END IF ;

return_text := text_begin ;
key_search := key_begin || key_name || key_end ;

find_key_start := position (key_search IN return_text) ;

WHILE (find_key_start >0) LOOP
IF (find_key_start > 0) THEN
find_key_end := find_key_start + length (key_search) ;

find_key_start := find_key_start - 1 ;

return_text_length := length (return_text) ;

temp_text := substring (return_text FROM 1 FOR find_key_start) ;
temp_text := temp_text || key_value ;
temp_text := temp_text || substring (return_text FROM find_key_end) ;

return_text := temp_text ;
END IF ;
find_key_start := position (key_search IN return_text) ;
END LOOP ;

return return_text ;

END ;
'LANGUAGE 'plpgsql';

I do not know it exists a contrib to use perl in procedure language ???
... but you can use TCL if you knows this language.

Jodi Kanter wrote:

> I have a field in one of my tables that has a path to a file listed. I
> need to move those files and now have to update all those paths. Is
> there a simply search and replace type option in sql? or do I need to
> do an update using the entire new path name?
> If so, I think the easier solution will be to dump the table, use a
> macro to update it and then restore.
> Jodi
> --
>
> /_______________________________
> //Jodi L Kanter
> BioInformatics Database Administrator
> University of Virginia
> (434) 924-2846
> jkanter(at)virginia(dot)edu <mailto:jkanter(at)virginia(dot)edu>/
>
>
>
> / /
>
> / /
>