am Thu, dem 10.07.2008, um 18:25:38 +0530 mailte Anoop G folgendes:
> my problems are:
>
> 1 problem : in RAISE NOTICE query string is print like this,
>
> How i can put the dates in single quote in a dynamic query string?
Use more quotes *g*:
Example:
test=*# create or replace function my_foo(text) returns int as '
declare s text;
begin s:=''select '''''' || $1 || '''''' as ...''; raise notice ''%'',s; return 1; end'
language 'plpgsql';
CREATE FUNCTION
test=*# select * from my_foo('2008-01-01');
NOTICE: select '2008-01-01' as ...my_foo
-------- 1
(1 row)
Better solution: use $-Quoting, example:
test=*# create or replace function my_foo(text) returns int as $$
declare s text;
begin s:='select ''' || $1 || ''' as ...';
raise notice '%',s;
return 1;
end$$
language 'plpgsql';
CREATE FUNCTION
test=*# select * from my_foo('2008-01-01');
NOTICE: select '2008-01-01' as ...my_foo
-------- 1
(1 row)
As you can see, same result but easier to read.
>
>
>
> 2 problem:
>
> next problem is i have a varchar variable vchr_our_lpo how I can check is it
> containn an empty string or characters in a dynamic query string
Use coalesce(), example:
test=*# select 'foo' || NULL || 'bar';?column?
----------
(1 row)
test=*# select 'foo' || coalesce(NULL,' empty string ') || 'bar'; ?column?
----------------------foo empty string bar
(1 row)
Hope that helps, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
