Re: [SQL] search and replace

[Benoît BOURNON]

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
  [EMAIL PROTECTED]


   
   
   
  
  

Re: [SQL] sum ( time) problem

[Willem de Jong]

I'm afraid it is the only solution...
Thanks for reply


[EMAIL PROTECTED] (Bruno Wolff III) wrote in news:20040601140334.GA24188
@wolff.to:

> On Thu, May 27, 2004 at 11:14:58 +,
>   Willem de Jong <[EMAIL PROTECTED]> wrote:
>> >>
>> >> If i do a sum(time) the result is like this '1 day 18:00:00'. But i'd
>> >> like to get a result like this '42:00:00'.
>> >>
>> >> How can i realise is by a query?
> 
> You can do something like the following: (not completely tested)
> select extract(epoch from sum(time))/3600 || ':' ||
>   extract(minutes from sum(time)) || ':' ||
>   extract(seconds from sum(time));
> 
> I am not sure if the above will work right for negative intervals.
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
> 
> 


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org