Problem in dynamic query execution in plpgsql - Mailing list pgsql-sql

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 />