From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Problem in dynamic query execution in plpgsql |
Date: | 2008-07-10 13:30:54 |
Message-ID: | 20080710133054.GC13926@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Lennin Caro | 2008-07-10 13:33:46 | Re: i can't connect after some periode |
Previous Message | Pavel Stehule | 2008-07-10 13:30:23 | Re: Problem in dynamic query execution in plpgsql |