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