I used pg_catalog.replace, it worked. Thanks for the help
On Tue, Nov 23, 2010 at 3:32 PM, Pavel Stehule <pavel.steh...@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 <akpg...@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
>
