Re: replace function

you have to install the function then.

In 7.2.2 
if not installed plpgsql already install
it by issuing.

you could use pltcl too.

for plpgsql below is the procedure:

$ createlang plpgsql <dbname>  

$ save the text below

taken from 
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23


--  BEGINS HERE ---
create function replace (varchar, varchar, varchar) returns varchar as '
declare   string alias for $1;   sub alias for $2;   replacement alias for $3;   -- xxxxxxxxxxx[MATCH]xxxxxxxxxxxx   --
         | end_before   --                   | start_after   match integer;   end_before integer;   start_after
integer;  string_replaced varchar;   string_remainder varchar;
 
begin   string_remainder := string;   string_replaced := '''';   match := position(sub in string_remainder);
   while match > 0 loop       end_before := match - 1;       start_after := match + length(sub);       string_replaced
:=string_replaced || substr(string_remainder, 1, 
 
end_b
efore) || replacement;       string_remainder := substr(string_remainder, start_after);       match := position(sub in
string_remainder);  end loop;   string_replaced := string_replaced || string_remainder;
 
   return string_replaced;
end;
' LANGUAGE 'plpgsql';

-- ENDS HERE --------

in a file named replace.sql

install it in your database by

$ psql -Uusername dbname -f replace.sql

then use the normal syntax from psql



BTW You shud upgrade to latest 7.3.2 or 
7.2.4 if u have any compelling reasons
to stay in 7.2.x versions.

Regds
mallah.



On Mon, 28 Apr 2003, rute solipa wrote:

> Rajesh Kumar Mallah wrote:
> 
> >On Mon, 28 Apr 2003, rute solipa wrote:
> >
> >  
> >
> >>hi everyone,
> >>
> >>i've got an oracle query that goes like this:
> >>select  replace (v.ds_conf, 'ns', 'p/fazer') as conf.
> >>from tb_visitas
> >>
> >>the result of this query is everytime is find string 'ns' it will be 
> >>replaced by 'p/fazer'.
> >>    
> >>
> >
> >
> >
> >It works exactly the same way here :)
> >in postgresql 7.3.x it is inbuilt , in older
> >version it can be installed from the postgresql
> >cookbook website ,
> >
> >
> >Regds
> >mallah.
> >
> >
> >  
> >
> >>does any one can tell me how do i do this in postgresql.
> >>
> >>kind regards,
> >>
> >>rute
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 4: Don't 'kill -9' the postmaster
> >>
> >>    
> >>
> >
> >
> >  
> >
> but i have the 7.2.2 version
>