The following bug has been logged online:
Bug reference: 6156
Logged by: Tejesh
Email address: tejesh_10spo...@yahoo.co.in
PostgreSQL version: 1.6.2
Operating system: Win Xp
Description: Function returns refcursor which not returns values it
gives <unnamed portal>
Details:
Thanks in advance...
i am writing functions that returns refcursor.
i have written many functions like that..
but someof my functions that returns <unnamed portal>
so i dont get results..
plz help me...
sample code as:
CREATE OR REPLACE FUNCTION get_payment_details_by_receipt(p_mann_bill_no
text, p_corp_id integer)
RETURNS SETOF refcursor AS
$BODY$declare ref1 refcursor;
begin
open ref1 for
select 'Dehuroad Cantonment Board' as
corp_name,dprop_payment_recipt_detail.property_code,dprop_payment_recipt_det
ail.payment_uid,tax_payment_flag,
loc_name,owner_name1,
COALESCE(sum(
CASE
WHEN dprop_payment_recipt_detail.tax_type_id = 1 THEN
dprop_payment_recipt_detail.tax_amount_paid
ELSE NULL::double precision END), 0::double precision) AS dpt,
COALESCE(sum(
CASE
WHEN dprop_payment_recipt_detail.tax_type_id = 2 THEN
dprop_payment_recipt_detail.tax_amount_paid
ELSE NULL::double precision
END), 0::double precision) AS dsc, COALESCE(sum(
CASE
WHEN dprop_payment_recipt_detail.tax_type_id = 3 THEN
dprop_payment_recipt_detail.tax_amount_paid
ELSE NULL::double precision
END), 0::double precision) AS dwt, COALESCE(sum(
CASE
when dprop_payment_recipt_detail.tax_type_id = 1000 THEN
dprop_payment_recipt_detail.tax_amount_paid
ELSE NULL::double precision
END), 0::double precision) AS dto, COALESCE(sum(
CASE
when dprop_payment_recipt_detail.tax_type_id = 101 THEN
dprop_payment_recipt_detail.tax_amount_paid
ELSE NULL::double precision
END), 0::double precision) AS t101, COALESCE(sum(
CASE
when dprop_payment_recipt_detail.tax_type_id = 102 THEN
dprop_payment_recipt_detail.tax_amount_paid
ELSE NULL::double precision
END), 0::double precision) AS t102, COALESCE(sum(
CASE
when dprop_payment_recipt_detail.tax_type_id = 103 THEN
dprop_payment_recipt_detail.tax_amount_paid
ELSE NULL::double precision
END), 0::double precision) AS t103,property_master_entry.revise_no,
case when dprop_payment_recipt_master.man_bill_no='0' then
dprop_payment_recipt_master.recipt_no ::character varying else
dprop_payment_recipt_master.man_bill_no ::character varying end as
man_bill_no,
case when dprop_payment_recipt_master.man_payment_date is null then
dprop_payment_recipt_master.payment_date::timestamp without time zone else
dprop_payment_recipt_master.man_payment_date::timestamp without time zone
end as display_date,
dprop_payment_recipt_master.man_payment_date
from dprop_payment_recipt_detail
,location_master,property_master_owner,dprop_payment_recipt_master,property_
master_entry
where
dprop_payment_recipt_master.man_bill_no ~ (p_mann_bill_no) and
dprop_payment_recipt_detail.corp_councile_code=p_corp_id
and
dprop_payment_recipt_detail.property_code=property_master_owner.property_mas
ter_id
and dprop_payment_recipt_detail.location_code=location_master.loc_id
and
dprop_payment_recipt_detail.property_code=dprop_payment_recipt_master.proper
ty_code
and
dprop_payment_recipt_detail.payment_uid=dprop_payment_recipt_master.payment_
uid
and
(property_master_entry.prop_master_id=dprop_payment_recipt_detail.property_c
ode)
group by
dprop_payment_recipt_detail.property_code,tax_payment_flag,dprop_payment_rec
ipt_detail.payment_uid,loc_name,owner_name1,dprop_payment_recipt_master.man_
bill_no,
dprop_payment_recipt_master.man_payment_date,dprop_payment_recipt_master.rec
ipt_no,dprop_payment_recipt_master.payment_date,property_master_entry.revise
_no
order by
dprop_payment_recipt_detail.property_code,dprop_payment_recipt_detail.paymen
t_uid;
return next ref1;
return;
end$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION get_payment_details_by_receipt(p_mann_bill_no text, p_corp_id
integer) OWNER TO postgres;
Thank you.
Regards
tejesh
email id: tejesh_10spo...@yahoo.co.in
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
