Thread: Problem with replace function in postgres

Problem with replace function in postgres

From

akp geek

Date:

23 November 2010, 19:05:19

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

Re: Problem with replace function in postgres

From

Pavel Stehule

Date:

23 November 2010, 19:08:38

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

Re: Problem with replace function in postgres

From

akp geek

Date:

23 November 2010, 19:20:42

CREATE OR REPLACE FUNCTION test_repl(x character varying)

RETURNS character varying AS

$BODY$

DECLARE

ret_var varchar(4000);

a record;

begin

ret_var := x;

for a in select * from lookup

loop

ret_var := replace(ret_var,a.code,a.codeword);

end loop;

return ret_var;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION test_repl(character varying) OWNER TO postgres;

On Tue, Nov 23, 2010 at 3:07 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

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

Re: Problem with replace function in postgres

From

Pavel Stehule

Date:

23 November 2010, 19:33:20

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

Re: Problem with replace function in postgres

From

akp geek

Date:

23 November 2010, 20:11:45

I used pg_catalog.replace, it worked. Thanks for the help

On Tue, Nov 23, 2010 at 3:32 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

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