pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction replace doesnt exist
4+ messages / 4 participants
[nested] [flat]
* function replace doesnt exist
@ 2002-12-12 16:34 Andy Morrow <andy.morrow@jinny.ie>
0 siblings, 3 replies; 4+ messages in thread
From: Andy Morrow @ 2002-12-12 16:34 UTC (permalink / raw)
To: pgsql-sql
Hi
im trying to execute an update command on a postgresql DB table using
pgAdmin II
im using the following statement
UPDATE commandlist SET command = REPLACE (command,'A','B')
commandlist is the table name
command is the column
and i want to change the value A to B
but it's giving me the following error message
an error has occured in pgAdmin II:frmSQLInput.cmdExecute_Click:
Number: -2147467259
Description: Error while executing the query;
ERROR: Function'replace(varchar, unknown, unknown)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function replace doesnt exist
@ 2002-12-12 16:48 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Andy Morrow <andy.morrow@jinny.ie>
2 siblings, 0 replies; 4+ messages in thread
From: Jean-Luc Lachance @ 2002-12-12 16:48 UTC (permalink / raw)
To: Andy Morrow <andy.morrow@jinny.ie>; +Cc: pgsql-sql
If you want character translation like the tr command under unix,
use TRANSLATE.
Andy Morrow wrote:
>
> Hi
>
> im trying to execute an update command on a postgresql DB table using
> pgAdmin II
>
> im using the following statement
>
> UPDATE commandlist SET command = REPLACE (command,'A','B')
>
> commandlist is the table name
> command is the column
> and i want to change the value A to B
>
> but it's giving me the following error message
>
> an error has occured in pgAdmin II:frmSQLInput.cmdExecute_Click:
>
> Number: -2147467259
> Description: Error while executing the query;
> ERROR: Function'replace(varchar, unknown, unknown)' does not exist
> Unable to identify a function that satisfies the given argument types
> You may need to add explicit typecasts
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function replace doesnt exist
@ 2002-12-12 20:24 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: Andy Morrow <andy.morrow@jinny.ie>
2 siblings, 0 replies; 4+ messages in thread
From: Jeff Eckermann @ 2002-12-12 20:24 UTC (permalink / raw)
To: Andy Morrow <andy.morrow@jinny.ie>; pgsql-sql
What version are you using? I think "replace" is new
for version 7.3.
As another poster has pointed out, "translate" works
for individual characters. Or you could code up your
own replace using the native functionality of pl/perl
or pl/tcl or such. Or you could just upgrade to 7.3.
--- Andy Morrow <andy.morrow@jinny.ie> wrote:
> Hi
>
> im trying to execute an update command on a
> postgresql DB table using
> pgAdmin II
>
> im using the following statement
>
> UPDATE commandlist SET command = REPLACE
> (command,'A','B')
>
>
> commandlist is the table name
> command is the column
> and i want to change the value A to B
>
>
> but it's giving me the following error message
>
>
> an error has occured in pgAdmin
> II:frmSQLInput.cmdExecute_Click:
>
> Number: -2147467259
> Description: Error while executing the query;
> ERROR: Function'replace(varchar, unknown, unknown)'
> does not exist
> Unable to identify a function that satisfies the
> given argument types
> You may need to add explicit typecasts
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function replace doesnt exist
@ 2002-12-13 10:56 Rajesh Kumar Mallah. <mallah@trade-india.com>
parent: Andy Morrow <andy.morrow@jinny.ie>
2 siblings, 0 replies; 4+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-12-13 10:56 UTC (permalink / raw)
To: Andy Morrow <andy.morrow@jinny.ie>; pgsql-sql
Are you looking for this ?
available on http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23
regds
mallah.
View One Recipe
Home -> Postgres -> CookBook Home -> View One Recipe Submitted on: 03-16-2001
Description:
mimic oracle's replace function. versions in pltcl and plpgsql.
Code:
-- by Jonathan Ellis (jbellis@hotmail.com)
-- licensed under the GPL
-- emailing me improvements is appreciated but not required
-- args: string substring replacement_substring
create function replace (varchar, varchar, varchar) returns varchar as '
-- escape out characters that regsub would treat as special
regsub -all {&} "$3" {\\\&} 3
regsub -all {\\[0-9]} "$3" {\\\0} 3
eval "regsub -all \{$2\} \{$1\} \{$3\} rval"
return $rval
' language 'pltcl';
-- plpgsql version so we don't have to jump through hoops to call it from other functions
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';
On Thursday 12 December 2002 10:04 pm, Andy Morrow wrote:
> Hi
>
> im trying to execute an update command on a postgresql DB table using
> pgAdmin II
>
> im using the following statement
>
> UPDATE commandlist SET command = REPLACE (command,'A','B')
>
>
> commandlist is the table name
> command is the column
> and i want to change the value A to B
>
>
> but it's giving me the following error message
>
>
> an error has occured in pgAdmin II:frmSQLInput.cmdExecute_Click:
>
> Number: -2147467259
> Description: Error while executing the query;
> ERROR: Function'replace(varchar, unknown, unknown)' does not exist
> Unable to identify a function that satisfies the given argument types
> You may need to add explicit typecasts
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-12-13 10:56 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-12 16:34 function replace doesnt exist Andy Morrow <andy.morrow@jinny.ie>
2002-12-12 16:48 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-12 20:24 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-12-13 10:56 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
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