pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
> 




view thread (10+ messages)  latest in thread

Message-ID: <Pine.LNX.4.33.0304282107320.868-100000@localhost.localdomain>
Permalink:  ../Pine.LNX.4.33.0304282107320.868-100000@localhost.localdomain/
Also on:    postgresql.org/message-id/Pine.LNX.4.33.0304282107320.868-100000@localhost.localdomain

 · 

reply

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