From: | "Anoop G" <anoopmadavoor(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Problem in dynamic query execution in plpgsql |
Date: | 2008-07-10 12:55:38 |
Message-ID: | 1a027d210807100555r2e25da6ekb53c09157840a648@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marcin Krawczyk | 2008-07-10 13:21:26 | record type |
Previous Message | PostgreSQL Admin | 2008-07-10 12:50:23 | Design and Question |