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

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