From: Benoît BOURNON <benoit.bournon@adelis.com>
To: Jodi Kanter <jkanter@virginia.edu>
Cc: Postgres SQL List <pgsql-sql@postgresql.org>
Subject: Re: search and replace
Date: Fri, 11 Jun 2004 10:16:49 +0200
Message-ID: <40C96A71.1040400@adelis.com> (raw)
In-Reply-To: <40C74940.7060401@virginia.edu>
References: <40C74940.7060401@virginia.edu>
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 <mailto:jkanter@virginia.edu>/
>
>
>
> / /
>
> / /
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: benoit.bournon@adelis.com, jkanter@virginia.edu
Subject: Re: search and replace
In-Reply-To: <40C96A71.1040400@adelis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox