Thread: function replace doesnt exist
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
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
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
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 treatas 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.