Re: Problem in dynamic query execution in plpgsql

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