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
>