From: Rajesh Kumar Mallah <rmallah@trade-india.com>
To: rute solipa <rutes@eselx.ipl.pt>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: replace function
Date: Mon, 28 Apr 2003 21:15:06 +0530 (IST)
Message-ID: <Pine.LNX.4.33.0304282107320.868-100000@localhost.localdomain> (raw)
In-Reply-To: <3EAD44CA.2090303@eselx.ipl.pt>
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
>
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: rmallah@trade-india.com, rutes@eselx.ipl.pt
Subject: Re: replace function
In-Reply-To: <Pine.LNX.4.33.0304282107320.868-100000@localhost.localdomain>
References: <3EAD44CA.2090303@eselx.ipl.pt> <Pine.LNX.4.33.0304282107320.868-100000@localhost.localdomain>
* 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