X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 45CBAD1B191 for ; Fri, 11 Jun 2004 05:12:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71639-09 for ; Fri, 11 Jun 2004 08:12:56 +0000 (GMT) Received: from neptune.adelis.net (neptune.adelis.net [195.115.171.102]) by svr1.postgresql.org (Postfix) with ESMTP id 4361BD1B1CB for ; Fri, 11 Jun 2004 05:12:52 -0300 (ADT) Received: from [195.115.171.16] (ben.adelis.net [195.115.171.16]) by neptune.adelis.net (Postfix) with ESMTP id B9B21794AA; Fri, 11 Jun 2004 10:00:42 +0200 (CEST) Message-ID: <40C96A71.1040400@adelis.com> Date: Fri, 11 Jun 2004 10:16:49 +0200 From: =?ISO-8859-1?Q?Beno=EEt_BOURNON?= User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: fr, en MIME-Version: 1.0 To: Jodi Kanter Cc: Postgres SQL List Subject: Re: search and replace References: <40C74940.7060401@virginia.edu> In-Reply-To: <40C74940.7060401@virginia.edu> Content-Type: multipart/alternative; boundary="------------070102050003070109080009" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: * X-Archive-Number: 200406/101 X-Sequence-Number: 18046 This is a multi-part message in MIME format. --------------070102050003070109080009 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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@virginia.edu / > > > > / / > > / / > --------------070102050003070109080009 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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@virginia.edu

--------------070102050003070109080009--