Problem with replace function in postgres

Dear all -
                I am having trouble with replace function inside a function.
 If I do the same replace function outside it works fine, Following example
clearly explains my problem. Can you please help?

create table str_table
( mystr varchar(1000));
insert into str_table values ( 'This road is INDEFLY CLSD');
insert into str_table values('That is INDEFLY open');
insert into str_table values('That is a CLSD road');

select * from str_table;
create table lookup
( code varchar(100),
  codeword varchar(100));

insert into lookup values(  'INDEFLY','indefinitely');
insert into lookup values(  'CLSD','closed');
select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD
INDEFLY') replaced_str
from str_table;
RESULT SET "  This road is INDEFLY CLSD;This road is indefinitely closed
INDEFLY  "
Regards

Hello

please, can you send a source code of your function?

Regards

Pavel Stehule

2010/11/23 akp geek <akpgeek@gmail.com>:

Dear all -
I am having trouble with replace function inside a

function.

If I do the same replace function outside it works fine, Following

example

clearly explains my problem. Can you please help?

create table str_table
( mystr varchar(1000));
insert into str_table values ( 'This road is INDEFLY CLSD');
insert into str_table values('That is INDEFLY open');
insert into str_table values('That is a CLSD road');

select * from str_table;
create table lookup
( code varchar(100),
codeword varchar(100));

insert into lookup values( 'INDEFLY','indefinitely');
insert into lookup values( 'CLSD','closed');
select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD
INDEFLY') replaced_str
from str_table;
RESULT SET " This road is INDEFLY CLSD;This road is indefinitely closed
INDEFLY "
Regards

Dear all -
                I am having trouble with replace function inside a function.
 If I do the same replace function outside it works fine, Following example
clearly explains my problem. Can you please help?

create table str_table
( mystr varchar(1000));
insert into str_table values ( 'This road is INDEFLY CLSD');
insert into str_table values('That is INDEFLY open');
insert into str_table values('That is a CLSD road');

select * from str_table;
create table lookup
( code varchar(100),
  codeword varchar(100));

insert into lookup values(  'INDEFLY','indefinitely');
insert into lookup values(  'CLSD','closed');
select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD
INDEFLY') replaced_str
from str_table;
RESULT SET "  This road is INDEFLY CLSD;This road is indefinitely closed
INDEFLY  "
Regards

Hello

it working on my comp

postgres=# select mystr, test_repl(mystr) replaced_strfrom str_table;
mystr | replaced_str
---------------------------+----------------------------------
This road is INDEFLY CLSD | This road is indefinitely closed
That is INDEFLY open | That is indefinitely open
That is a CLSD road | That is a closed road
(3 rows)

I don't see a problem in your code, I am thinking so it's ok.

try to debug code with RAISE NOTICE statement

for a in select * from lookup
loop
RAISE NOTICE 'lookup rec: %', a;
RAISE NOTICE 'ret_var before replace: %', ret_var;
ret_var := replace(ret_var,a.code,a.codeword);
RAISE NOTICE 'ret_var after replace: %', ret_var;
end loop;
return ret_var;

you can ensure using just buildin "replace" function - use

ret_var := pg_catalog.replace(...)

Regards

Pavel Stehule
maybe it can help to identify a problem

2010/11/23 akp geek <akpgeek@gmail.com>:

Dear all -
I am having trouble with replace function inside a

function.

If I do the same replace function outside it works fine, Following

example

clearly explains my problem. Can you please help?

create table str_table
( mystr varchar(1000));
insert into str_table values ( 'This road is INDEFLY CLSD');
insert into str_table values('That is INDEFLY open');
insert into str_table values('That is a CLSD road');

select * from str_table;
create table lookup
( code varchar(100),
codeword varchar(100));

insert into lookup values( 'INDEFLY','indefinitely');
insert into lookup values( 'CLSD','closed');
select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD
INDEFLY') replaced_str
from str_table;
RESULT SET " This road is INDEFLY CLSD;This road is indefinitely closed
INDEFLY "
Regards