RE: search/replace in update - Mailing list pgsql-sql

Gary,
To answer your questions in the order given:
*    Parameters are as you guess.  You could juggle them any way you
like, but I think easier to remember if they correspond to the language
being used.
*    You call the function by including it in your SQL, like:
UPDATE table
SET mobile_number = search_replace (mobile_number, '07889', '0589')
WHERE mobile_number LIKE '0589%';
Or, if you just want to check that it works, you could do:
*    SELECT mobile_number, search_replace (mobile_number, '07889',
'0589') AS new_number
FROM table
WHERE mobile_number LIKE '0589%';
*    Procedural languages are not enabled by default: you need to do
that.  Easiest way is to run "createlang" from the OS prompt (see "man
createlang" for details, or PostgreSQL documentation).  For plperl, there
are extra steps required, which you can find in the documentation under
"Procedural Languages".  The result of those extra steps will be the
creation of a file "plperl.so", which you should then copy to your
/usr/local/lib directory.  You can then run "createlang plperl <database
name>", and you will be good to go.
PostgreSQL has lots of functions built in, but if you need something that is
missing, you can probably find it in one of the supported procedural
languages, and "import" it by wrapping in a custom function in the manner
shown below.
HTH
Jeff

> -----Original Message-----
> From:    Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
> Sent:    Friday, June 15, 2001 6:31 AM
> To:    Jeff Eckermann; 'Josh Berkus'
> Cc:    pgsql-sql@postgresql.org
> Subject:    Re: search/replace in update
> 
> Hi Jeff,
> 
> Thanks for the response.  Although I've never used functions before, I
> kinda 
> follow what you're doing.  Presumably the parameters are field, old
> string, 
> new string.
> 
> However, I have two problems.
> 
> 1) I don't know how I would call that function
> 2) when I tried to create the function it failed saying unrecognised 
> landuage. The message I got was: 
> 
> ERROR:  Unrecognized language specified in a CREATE FUNCTION: 'plperl'.  
> Recognized languages are sql, C, internal and the created procedural 
> languages.
> 
> I'm running postgresql 7.0.2-2 installed from rpms onto RH6.1.
> 
> Gary
> 
> On Thursday 14 June 2001  4:42 pm, Jeff Eckermann wrote:
> > Which function would you be suggesting?  I hope not "translate".  That
> > works on characters, not strings.  Easy to confuse if you don't read
> > closely enough, as I found to my cost.
> > Easiest way using these functions would be:
> > UPDATE table
> > SET mobile_number = '07889' || substr (mobile_number, 5)
> > WHERE mobile_number LIKE '0589%';
> > Alternatively, you could define a generic search/replace function like:
> > CREATE FUNCTION search_replace (text, text, text)
> > RETURNS text AS '
> > RETURN @_[0] =~ s/@_[1]/@_[2]/
> > ' LANGUAGE 'plperl';
> > Which would be available anytime for similar purposes (credit to Tom
> Lane
> > for the idea).
> >
> > > -----Original Message-----
> > > From:    Josh Berkus [SMTP:josh@agliodbs.com]
> > > Sent:    Thursday, June 14, 2001 9:52 AM
> > > To:    Gary Stainburn
> > > Cc:    pgsql-sql@postgresql.org
> > > Subject:    Re: search/replace in update
> > >
> > > Gary,
> > >
> > > > This means that e.g. all mobile numbers that used to start 0589 now
> > > > start 07889.
> > > >
> > > > Is there a way in SQL to update the phone number in-place?
> > >
> > > Postgresql has a nifty string substitution function.  For this and
> other
> > > character manipulation functions, see:
> > >
> > > http://www.postgresql.org/idocs/index.php?functions-string.html
> > >
> > > You should be able to fairly easily swap out "07889" and replace it
> with
> > > "0589".
> > >
> > > -Josh
> > >
> > >
> > >
> > > ______AGLIO DATABASE SOLUTIONS___________________________
> > >                                        Josh Berkus
> > >   Complete information technology      josh@agliodbs.com
> > >    and data management solutions       (415) 565-7293
> > >   for law firms, small businesses        fax 621-2533
> > >     and non-profit organizations.      San Francisco
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html