如何转换PostgreSQL中函数返回的数据

Question

CREATE OR REPLACE FUNCTION dcr_report_demo(
finid integer,
prdid integer,
comp_cd character varying,
divid integer,
fsid integer)
RETURNS refcursor
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
STR REFCURSOR;
BEGIN
OPEN STR FOR SELECT HD.REPORT_NO,HD.DCR_DATE,COALESCE( PR2.PARA_DESCR ,' 
') WORK_TYPE,
COALESCE( PR1.PARA_DESCR,' ') HQ_TYPE,COALESCE( RM.ROUTE_NAME,' ') 
ROUTE_NAME,
COALESCE(HD.DOCTOR_VISITS,0) DOCTOR_VISITS,COALESCE(HD.STOCKIST_VISITS,0) 
STOCKIST_VISITS,
COALESCE(HD.RETAILER_VISITS,0) 
RETAILER_VISITS,HD.DCR_ID,FM.FS_NAME,HD.FSTAFF_ID,
CASE HD.STATUS
WHEN 'A' THEN 'APPROVED'
WHEN 'D' THEN 'DISCARDED'
WHEN 'F' THEN 'FORWARDED'
WHEN 'E' THEN 'DRAFT' END STATUS ,ZSM.FS_NAME report1,
RSM.FS_NAME report2,FM.GEOG_LVL1_HQ,FM.LEVEL_CODE ,
COALESCE(PM.PARA_DESCR,'SELF') JOINT_WORK 
,FM.FS_CODE,FM.EMP_CODE,COALESCE(HD.DOC_OTHER,0) DOC_OTHER
FROM   DCRHD HD LEFT OUTER JOIN PARAMETERS PM ON HD.JFW = PM.PARA_CODE
AND PM.PARA_TYPE = 'JFW'
LEFT OUTER JOIN ROUTE_MASTER RM ON RM.FSTAFF_ID=HD.FSTAFF_ID
AND RM.ROUTE_ID =HD.ROUTE_ID AND RM.COMPANY_CD = COMP_CD
LEFT OUTER JOIN parameters PR1 ON PR1.PARA_CODE  = HD.HQ_EXHQ
AND PR1.PARA_TYPE ='HQ_' ,parameters PR2,FIELD_MASTER FM,FIELD_MASTER 
ZSM,FIELD_MASTER RSM
WHERE HD.PERIOD_ID= PRDID AND HD.FIN_YEAR_ID= FINID
AND HD.FSTAFF_ID=FM.FS_ID
AND FM.MGR_LEVEL4=ZSM.FS_ID
AND FM.MGR_LEVEL3= RSM.FS_ID
AND FM.FS_ID=HD.FSTAFF_ID AND FM.LEVEL_CODE ='005'
AND PR2.PARA_CODE = HD.WORK_TYPE AND PR2.PARA_TYPE ='WTP' AND HD.COMPANY= 
COMP_CD
AND FM.COMPANY_CD = COMP_CD
ORDER BY FM.FS_NAME,DCR_DATE ;
RAISE NOTICE '%', STR ;
RETURN STR;
END;
$BODY$;

我是PostgreSQL的新手。我最近在这里创建了一个函数：

FINID
PRDID
COMP_CD
DIVID

FSID是我的参数，由用户传递给函数。这个函数编译成功，但当我查询select * from dcr_report_demo(12,1,'VET',327,14);时，它显示<unnamed portal 1>。

可能是什么问题？如何在PostgreSQL中转换此函数返回的数据？

Answer 1

不要使用refcursor作为返回类型，也不要在函数内部使用游标。

您的函数所做的只是返回SQL查询的结果。最好使用声明为returns table (...)的language sql函数

CREATE OR REPLACE FUNCTION dcr_report_demo(finid integer,
prdid integer,
comp_cd character varying,
divid integer,
fsid integer)
RETURNS table(report_no integer, 
dcr_date date, 
work_type text, 
hq_type text, 
rout_name text, 
.... rest of the columns ...) 
LANGUAGE sql
AS $BODY$
SELECT hd.report_no,
hd.dcr_date,
... 
FROM ...
JOIN ...
WHERE hd.period_id = prdid
AND hd.fin_year_id = finid
AND hd.company = comp_cd
AND fm.company_cd = comp_cd
.... 
ORDER BY fm.fs_name,
dcr_date;
$BODY$;

---

不相关：在WHERE子句中混合显式JOIN运算符和隐式联接是一种糟糕的编码风格。