Thread: Problem in dynamic query execution in plpgsql

Problem in dynamic query execution in plpgsql

From
"Anoop G"
Date:
Hai all,<br />      I Have a problem,I have a plpgsql function <br />==================================<br /><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">CREATE OR REPLACE FUNCTION
function_to_get_ticket_wise_sales(VARCHAR,VARCHAR)RETURNS SETOF RECORD AS '</font></font><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">DECLARE</font></font><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">dat_from_date ALIAS FOR $1;</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">dat_to_date ALIAS FOR
$2;</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">dat_from
DATE;</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">dat_to
DATE;</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">vchr_query
VARCHAR(1000);</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">r
RECORD;</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">BEGIN</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">dat_from:= to_date(dat_from_date, ''DD MM YYYY'');</font></font><p style="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">dat_to := to_date(dat_to_date,''DD MM YYYY'');</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">vchr_query := ''SELECT
vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector,</font></font><pstyle="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">dbl_market_fare_inv,
dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo,</font></font><pstyle="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">dbl_market_fare_inv AS
flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type,
vchr_airline_numeric_code,vchr_account_code_inv , vchr_account_name_inv FROM tbl_ticket WHERE dat_ticket_issue BETWEEN
''||dat_from || '' AND '' || dat_to || '' ;</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif
Condensed,serif"><font size="3">RAISE NOTICE ''Query : % '',vchr_query;</font></font><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">vchr_query := vchr_query || '' AND (vchr_our_lpo = ''
''OR vchr_our_lpo = "VS") '';</font></font><p style="margin-bottom: 0.2in;"><br /><br /><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">FOR r in EXECUTE vchr_query LOOP</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">RETURN NEXT r;</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">END LOOP;</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">RETURN;</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">END</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">' language
'plpgsql';</font></font><pstyle="margin-bottom: 0.2in;">==================================<br /><br /><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">my problems are:</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3"><span style="color: rgb(255, 0,
0);">1problem : </span>in RAISE NOTICE query string is print like this,</font></font><p style="margin-bottom:
0.2in;"><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">SELECT
vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector,</font></font><pstyle="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">dbl_market_fare_inv,
dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo,</font></font><pstyle="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">dbl_market_fare_inv AS
flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type,
vchr_airline_numeric_code,vchr_account_code_inv , vchr_account_name_inv FROM tbl_ticket WHERE dat_ticket_issue BETWEEN
2008-04-01AND 2008-07-10 </font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,
serif"><fontsize="3">when $1 = '2008-04-01' and $2 = '2008-04-10' , but i dont get the required result.</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">I think that i will get the
resultif my query string will be like this(ie dates in single quote),</font></font><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">:</font></font><p style="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">SELECT
vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector,</font></font><pstyle="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">dbl_market_fare_inv,
dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo,</font></font><pstyle="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">dbl_market_fare_inv AS
flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type,
vchr_airline_numeric_code,vchr_account_code_inv , vchr_account_name_inv FROM tbl_ticket WHERE dat_ticket_issue BETWEEN
'2008-04-01'AND '2008-07-10 '</font></font><p style="margin-bottom: 0.2in;"><br /><br /><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">How i can put the dates in single quote in a dynamic
querystring?</font></font><p style="margin-bottom: 0.2in;"><br /><br /><p style="margin-bottom: 0.2in; color: rgb(204,
0,0);"><font face="DejaVu Serif Condensed, serif"><font size="3">2 problem:</font></font><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">next problem is i have a varchar variable vchr_our_lpo
howI can check is it containn an empty string or characters in a dynamic query string</font></font><p
style="margin-bottom:0.2in;"><br /><br /><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,
serif"><fontsize="3">I tried different methods like,</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu
SerifCondensed, serif"><font size="3">vchr_query :='' (vchr_our_lpo = '' '' OR vchr_our_lpo = "VS") '';</font></font><p
style="margin-bottom:0.2in;"><br /><br /><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,
serif"><fontsize="3">str_temp2:= ''VS'';</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif
Condensed,serif"><font size="3">vchr_query := '' (vchr_our_lpo = '' '' OR vchr_our_lpo = %)
'',str_temp2;</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">butall failed</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">HowI can solve these problem in a dynamic query string?.pls help me with a suitable example</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">thanks in
advance:</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">Anoop
G<br/><br /></font></font><p style="margin-bottom: 0.2in;"><br /><br /><br /><br /><br /> 

Re: Problem in dynamic query execution in plpgsql

From
"Pavel Stehule"
Date:
Hello

why you do use dynamic query?

your function is little bit ugly

a)
create or replace function ......(date_from date, date_to date)
returns setof record as $$
declare r record;
begin for r in select .. from tbl_ticket where dat_ticket_issue between
date_from and date_to loop

...

b) for single quoting use function quote_literal

postgres=# select '>>>'||quote_literal(current_date)||'<<<';     ?column?
-------------------->>>'2008-07-10'<<<
(1 row)

Regards
Pavel Stehule



2008/7/10 Anoop G <anoopmadavoor@gmail.com>:
> Hai all,
>       I Have a problem,I have a plpgsql function
> ==================================
>
> CREATE OR REPLACE FUNCTION
> function_to_get_ticket_wise_sales(VARCHAR,VARCHAR) RETURNS SETOF RECORD AS '
>
> DECLARE
>
> dat_from_date ALIAS FOR $1;
>
> dat_to_date ALIAS FOR $2;
>
> dat_from DATE;
>
> dat_to DATE;
>
> vchr_query VARCHAR(1000);
>
> r RECORD;
>
> BEGIN
>
> dat_from := to_date(dat_from_date, ''DD MM YYYY'');
>
> dat_to := to_date(dat_to_date,''DD MM YYYY'');
>
> vchr_query := ''SELECT
> vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector,
>
> dbl_market_fare_inv,
> dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo,
>
> dbl_market_fare_inv AS
>
flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type,
> vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv
> FROM tbl_ticket WHERE dat_ticket_issue BETWEEN ''|| dat_from || '' AND '' ||
> dat_to || '' ;
>
> RAISE NOTICE ''Query : % '',vchr_query;
>
> vchr_query := vchr_query || '' AND (vchr_our_lpo = '' '' OR vchr_our_lpo =
> "VS") '';
>
> FOR r in EXECUTE vchr_query LOOP
>
> RETURN NEXT r;
>
> END LOOP;
>
> RETURN;
>
> END
>
> ' language 'plpgsql';
>
> ==================================
>
> my problems are:
>
> 1 problem : in RAISE NOTICE query string is print like this,
>
> SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector,
>
> dbl_market_fare_inv,
> dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo,
>
> dbl_market_fare_inv AS
>
flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type,
> vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv
> FROM tbl_ticket WHERE dat_ticket_issue BETWEEN 2008-04-01 AND 2008-07-10
>
> when $1 = '2008-04-01' and $2 = '2008-04-10' , but i dont get the required
> result.
>
> I think that i will get the result if my query string will be like this(ie
> dates in single quote),
>
> :
>
> SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector,
>
> dbl_market_fare_inv,
> dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo,
>
> dbl_market_fare_inv AS
>
flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type,
> vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv
> FROM tbl_ticket WHERE dat_ticket_issue BETWEEN '2008-04-01' AND '2008-07-10
> '
>
> How i can put the dates in single quote in a dynamic query string?
>
> 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
>
> I tried different methods like,
>
> vchr_query :='' (vchr_our_lpo = '' '' OR vchr_our_lpo = "VS") '';
>
> str_temp2:= ''VS'';
>
> vchr_query := '' (vchr_our_lpo = '' '' OR vchr_our_lpo = %) '',str_temp2;
>
> but all failed
>
> How I can solve these problem in a dynamic query string?.pls help me with a
> suitable example
>
> thanks in advance:
>
> Anoop G
>
>
>
>
>
>
>


Re: Problem in dynamic query execution in plpgsql

From
"A. Kretschmer"
Date:
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


Re: Problem in dynamic query execution in plpgsql

From
"Anoop G"
Date:
Hai <span>Kretschmer</span> ,<br />   Thanks for your reply, it help to solve my problem. I have few for doubts
regardingdynamic query<br /><br />I have a  table  structure:<br /><br /> Column |       Type       | Modifiers<br
/>--------+------------------+-----------<br/>  mf     | double precision |<br /> sf     | double precision |<br
/> comm  | integer          |<br /><br />I create a  the following funtion <br /><br /><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">create or replace function test_perc() returns setof
recordas $body$</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">declare</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">vchr_queryVARCHAR(100);</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,
serif"><fontsize="3">r record;</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,
serif"><fontsize="3">begin</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,
serif"><fontsize="3">--vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm) as flt_claim from calc';</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">vchr_query:= 'SELECT mf,sf,(mf –
mf* comm /100) – (sf – sf * comm/100) as flt_claim';</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu
SerifCondensed, serif"><font size="3">FOR r in EXECUTE vchr_query LOOP</font></font><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">RETURN NEXT r;</font></font><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">END LOOP;</font></font><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">RETURN;</font></font><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">end$body$<br />language 'plpgsql'</font></font><p
style="margin-bottom:0.2in;"><br /><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">functioncreated</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">butwhen I am traing to run this function I got the following error</font></font><p style="margin-bottom:
0.2in;">ERROR: syntax error at or near "–" at character 18<br /> QUERY:  SELECT mf,sf,(mf – mf * comm /100) – (sf – sf
*comm/100) as flt_claim<br />CONTEXT:  PL/pgSQL function "test_perc" line 7 at for over execute statement<br />LINE 1:
SELECTmf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as...<br /><br /><p style="margin-bottom: 0.2in;">How i can
solvethis ? <br /><p style="margin-bottom: 0.2in;"><br /><p style="margin-bottom: 0.2in;">I   tried  another   method  
tosolve  this  <br /><p style="margin-bottom: 0.2in;"> I create a function to calculate the value<p
style="margin-bottom:0.2in;"><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">createor replace function calc_perse(double precision,double precision,double precision) returns double
precisionas $body$</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">declare</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font
size="3">resdouble precision ;</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,
serif"><fontsize="3">begin</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,
serif"><fontsize="3">res :=( $1 - $1* $3/100 )- ($2 - $2*$3/100); </font></font><p style="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">return res;</font></font><p style="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">end$body$</font></font><p style="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">language 'plpgsql'</font></font><br /><p style="margin-bottom:
0.2in;">Theni try to call it from another function<p style="margin-bottom: 0.2in;"><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm)
asflt_claim from calc';</font></font><p style="margin-bottom: 0.2in;"><p style="margin-bottom: 0.2in;"><font
face="DejaVuSerif Condensed, serif"><font size="3">FOR r in EXECUTE vchr_query LOOP</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">RETURN NEXT r;</font></font><p
style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">END LOOP;</font></font><p><p><p
style="margin-bottom:0.2in;">It also faild, How I can call a function like this from other function?<p
style="margin-bottom:0.2in;">thanks in advance<p style="margin-bottom: 0.2in;">Anoop G<br /><p style="margin-bottom:
0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3"><br /></font></font><p><p style="margin-bottom:
0.2in;"><br/><p style="margin-bottom: 0.2in;"><br /><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif
Condensed,serif"><font size="3"><br /></font></font><br /><br /> <br /> 

Re: Problem in dynamic query execution in plpgsql

From
Ragnar
Date:
On lau, 2008-07-12 at 14:45 +0530, Anoop G wrote:
> Hai Kretschmer ,
>    Thanks for your reply, it help to solve my problem. I have few for
> doubts regarding dynamic query
...
> vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf *
> comm/100) as flt_claim';
the '–' characters here probably are not what you think.
try with '-'

...
> but when I am traing to run this function I got the following error
> 
> ERROR:  syntax error at or near "–" at character 18
> QUERY:  SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as
> flt_claim

gnari