Re: search and replace - Mailing list pgsql-sql

I do not try,<br /> may be it exists different languages to make this in postgresql :<br /><br /> I make this in
pl/pgsql,I do not have time to make this in C, <br /><br /> CREATE OR REPLACE FUNCTION "_replaceblock" (text, text,
text)RETURNS text AS'<br /> DECLARE<br /><br />  text_begin ALIAS FOR $1 ;<br />  key_name ALIAS FOR $2 ;<br />
 key_valALIAS FOR $3 ;<br /><br />  key_begin VARCHAR(10) := '{' ;<br />  key_end VARCHAR(10) := '}' ;<br /><br />
 return_textTEXT ;<br />  return_text_length INTEGER;<br /><br />  find_key_start INTEGER ;<br />  find_key_end INTEGER
;<br/><br />  temp_text TEXT ;<br />  key_search TEXT ;<br />  key_value TEXT ;<br /> BEGIN<br />  IF (key_val IS NULL)
THEN<br/>      key_value :='' ;<br />   ELSE<br />      key_value := key_val ;<br />   END IF ;<br />   <br />
 return_text:= text_begin ;<br />  key_search := key_begin || key_name || key_end ;<br /><br />  find_key_start :=
position(key_search IN return_text) ;<br /><br />  WHILE (find_key_start >0) LOOP<br />   IF (find_key_start > 0)
THEN<br/>      find_key_end := find_key_start + length (key_search) ;<br /><br />      find_key_start := find_key_start
-1 ;<br /><br />      return_text_length := length (return_text) ;<br /><br />      temp_text := substring (return_text
FROM1 FOR find_key_start) ;<br />      temp_text := temp_text || key_value ;<br />      temp_text := temp_text ||
substring(return_text FROM find_key_end) ;<br /><br /><br />      return_text := temp_text ;<br />   END IF ;<br />  
find_key_start:= position (key_search IN return_text) ;<br />  END LOOP ;<br /><br />  return return_text ;<br /><br />
END;<br /> 'LANGUAGE 'plpgsql';<br /><br /><br /> I do not know it exists a contrib to use perl in procedure language
???... but you can use TCL if you knows this language.<br /><br /><br /><br /> Jodi Kanter wrote: <blockquote
cite="mid40C74940.7060401@virginia.edu"type="cite"></blockquote> I have a field in one of my tables that has a path to
afile listed. I need to move those files and now have to update all those paths. Is there a simply search and replace
typeoption in sql? or do I need to do an update using the entire new path name?<br /> If so, I think the easier
solutionwill be to dump the table, use a macro to update it and then restore.<br /> Jodi<br /><div
class="moz-signature">--<br /></div><div class="Section1"><p class="MsoNormal"><i><span style="font-size: 9pt;
font-family:Arial;">_______________________________<br /></span></i><i><span style="font-size: 10pt;">Jodi L Kanter<br
/>BioInformatics Database Administrator<br /> University of Virginia<br /> (434) 924-2846<br /><a
href="mailto:jkanter@virginia.edu">jkanter@virginia.edu</a></span></i><spanstyle="font-size: 11pt; font-family:
Arial;"><brstyle="" /><br style="" /></span><p class="MsoNormal"><span style="font-size: 11pt; font-family:
Arial;"> </span><pclass="MsoNormal"><i><span style="font-size: 9pt; font-family: Arial;"> </span></i><p
class="MsoNormal"><i><spanstyle="font-size: 9pt; font-family: Arial;"> </span></i></div>