背景：自己在pgsql上面写了一个自定义函数，现在想将自定义函数的结果表插入到一张新表中，但结果报错
报错信息
ERROR: function cannot execute on a QE slice because it issues a non-SELECT statement (entry db xx.xx.x.xx:5432 pid=21588)
CONTEXT: SQL statement "create temporary table "t_4" (date_actual date,equipmentno varchar(50) ,areano varchar(50), mono_finish int) DISTRIBUTED REPLICATED"
PL/pgSQL function mes_work_order_closing_rate(date,date) lin
CREATE OR REPLACE FUNCTION "ads"."mes_work_order_closing_rate"("beginDate" date, "endDate" date)
RETURNS TABLE("day" date, "equipmentno" varchar, "areano" varchar, "closedon_sameday" int4, "closedon_threedays" int4, "closedon_sevendays" int4, "all_no" int4, "mono_finish_threedays" int4, "mono_finish_sevendays" int4, "mono_finish_sameday" int4, "mono_finish_fs" int4, "workshop" text) AS $BODY$
declare
i integer ; --初始值
j integer := ("endDate" - "beginDate")::INTEGER ; --结束日期参数-开始日期参数
statistical_date date; --统计日期
BEGIN
statistical_date :="endDate";
create temporary table "t_4" (date_actual date,equipmentno varchar(50) ,areano varchar(50), mono_finish int) ;
create temporary table "t_5" (date_actual date,equipmentno varchar(50) ,areano varchar(50), mono_finish int) ;
create temporary table "t_6" (date_actual date,equipmentno varchar(50) ,areano varchar(50), mono_finish int) ;
create temporary table "t_7" (date_actual date,equipmentno varchar(50) ,areano varchar(50), mono_finish int) ;
create temporary table "t_9" (date_actual date,equipmentno varchar(50) ,areano varchar(50), mono_finish int) ;
create temporary table "t_10" (date_actual date,equipmentno varchar(50) ,areano varchar(50), mono_finish int) ;
create temporary table "t_11" (date_actual date,equipmentno varchar(50) ,areano varchar(50), mono_finish int) ;
create temporary table "t_12" (date_actual date,equipmentno varchar(50) ,areano varchar(50), mono_finish int) ;
FOR i IN 0..j LOOP
insert into "t_4"(date_actual,equipmentno,areano,mono_finish)
select statistical_date,t1.equipmentno,t1.areano,count(t1.mono) mono_finish
from
(
SELECT t1.mono,t1.mostate,t1.actualstartdate,t1.moclosedate,t2.equipmentno,t2.areano
FROM dwd.dwd_mes_tbloemobasis_detail t1
left join
(select a.mono,a.equipmentno,a.areano from dwd.dwd_mes_tblwipdispatch_detail a where opno = 'ZSGX03' group by a.mono,a.equipmentno,a.areano ) t2
on t1.mono = t2.mono
WHERE t1.order_type = '注塑生产订单'
and actualstartdate::date BETWEEN "beginDate" and statistical_date
and statistical_date-actualstartdate::date <'3'
and t2.equipmentno is not null
-- and ( moclosedate::date >statistical_date + interval '3 day' or t1.mostate != '99')
) t1
group by t1.equipmentno,t1.areano;
statistical_date = statistical_date - interval '1 day';
if(statistical_date<"beginDate") then
statistical_date :="endDate";
end if;
END LOOP;
。。。t_5
。。。t_6
。。。一直往下
return query SELECT * FROM (
SELECT t1.date_actual
,t1.equipmentno,t1.areano,t1.closedon_sameday,t1.closedon_threedays,t1.closedon_sevendays,t1.all_no,t2.mono_finish mono_finish_threedays
,t3.mono_finish mono_finish_sevendays,t4.mono_finish mono_finish_sameday,t5.mono_finish mono_finish_fs
, cast('注塑车间' as text) workshop
FROM
(
SELECT aa.date_actual
, bb.equipmentno
, bb.areano
, bb.closedon_sameday
, cc.closedon_threedays
, dd.closedon_sevendays
, ee.all_no
FROM dim.dim_date aa
LEFT JOIN
(
SELECT A.date_actual
,A.equipmentno,A.areano
, cast(SUM ( A.closedon_sameday ) as int) AS closedon_sameday
FROM
(
SELECT t2.date_actual
,t1.equipmentno,t1.areano
, ( CASE WHEN t1.closedon_sameday IS NULL THEN 0 ELSE t1.closedon_sameday END ) closedon_sameday
FROM dim.dim_date t2
LEFT JOIN
(
SELECT bb.date_actual
, cc.equipmentno ,cc.areano
, COUNT ( * ) closedon_sameday
FROM dim.dim_date bb
LEFT JOIN dwd.dwd_mes_tbloemobasis_detail aa ON to_date( to_char( aa.moclosedate , 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) = bb.date_actual
left join (select a.mono,a.equipmentno,a.areano from dwd.dwd_mes_tblwipdispatch_detail a where opno = 'ZSGX03' group by a.mono,a.equipmentno,a.areano ) cc on aa.mono = cc.mono
WHERE to_date( to_char( aa.actualstartdate, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) >= "beginDate"
AND to_date( to_char( aa.actualstartdate, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) <= "endDate"
AND aa.close_days <= '1' AND aa.order_type = '注塑生产订单'
and cc.equipmentno is not null
GROUP BY bb.date_actual,cc.areano,cc.equipmentno
ORDER BY bb.date_actual
) t1 ON t1.date_actual = t2.date_actual ORDER BY t2.date_actual
) A
WHERE A.date_actual BETWEEN "beginDate" AND "endDate" GROUP BY A.date_actual,A.equipmentno,A.areano
) bb ON aa.date_actual = bb. date_actual
LEFT JOIN
(
SELECT A .date_actual
,A.equipmentno,a.areano
,cast(SUM ( A.closedon_threedays )as int) AS closedon_threedays
FROM
(
SELECT t2.date_actual
,t1.equipmentno,t1.areano
, ( CASE WHEN t1.closedon_threedays IS NULL THEN 0 ELSE t1.closedon_threedays END ) closedon_threedays
FROM dim.dim_date t2
LEFT JOIN
(
SELECT bb.date_actual
, cc.equipmentno ,cc.areano
, COUNT ( * ) closedon_threedays
FROM dim.dim_date bb
LEFT JOIN dwd.dwd_mes_tbloemobasis_detail aa ON to_date( to_char( aa.moclosedate , 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) = bb.date_actual
left join (select a.mono,a.equipmentno,a.areano from dwd.dwd_mes_tblwipdispatch_detail a where opno = 'ZSGX03' group by a.mono,a.equipmentno,a.areano ) cc on aa.mono = cc.mono
WHERE to_date( to_char( aa.actualstartdate, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) >= "beginDate"
AND to_date( to_char( aa.actualstartdate, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) <= "endDate"
AND aa.close_days <= '3' AND aa.order_type = '注塑生产订单'
and cc.equipmentno is not null
GROUP BY bb.date_actual,cc.areano,cc.equipmentno
ORDER BY bb.date_actual
) t1 ON t1.date_actual = t2.date_actual ORDER BY t2.date_actual
) A
WHERE A.date_actual BETWEEN "beginDate" AND "endDate"
GROUP BY A.date_actual ,A.equipmentno,a.areano
) cc ON cc.date_actual = aa.date_actual and bb.equipmentno = cc.equipmentno
LEFT JOIN
(
SELECT A .date_actual
,a.equipmentno,a.areano
,cast(SUM ( A.closedon_sevendays ) as int) AS closedon_sevendays
FROM
(
SELECT t2.date_actual
,t1.equipmentno,t1.areano
, ( CASE WHEN t1.closedon_sevendays IS NULL THEN 0 ELSE t1.closedon_sevendays END ) closedon_sevendays
FROM dim.dim_date t2
LEFT JOIN
(
SELECT bb.date_actual
, cc.equipmentno ,cc.areano
, COUNT ( * ) closedon_sevendays
FROM dim.dim_date bb
LEFT JOIN dwd.dwd_mes_tbloemobasis_detail aa ON to_date( to_char( aa.moclosedate , 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) = bb.date_actual
left join (select a.mono,a.equipmentno,a.areano from dwd.dwd_mes_tblwipdispatch_detail a where opno = 'ZSGX03' group by a.mono,a.equipmentno,a.areano) cc on aa.mono = cc.mono
WHERE to_date( to_char( aa.actualstartdate, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) >= "beginDate"
AND to_date( to_char( aa.actualstartdate, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) <= "endDate"
AND aa.close_days <= '7' AND aa.order_type = '注塑生产订单'
and cc.equipmentno is not null
GROUP BY bb.date_actual,cc.areano,cc.equipmentno
ORDER BY bb.date_actual
) t1 ON t1.date_actual = t2.date_actual ORDER BY t2.date_actual
) A
WHERE A.date_actual BETWEEN "beginDate" AND "endDate"
GROUP BY A.date_actual ,a.equipmentno,a.areano
) dd ON dd.date_actual = aa.date_actual and dd.equipmentno = bb.equipmentno
LEFT JOIN
(
SELECT A .date_actual
,a.equipmentno,a.areano
,cast(SUM ( mono_qty ) as int) AS all_no
FROM
(
SELECT aa.date_actual,bb.equipmentno,bb.areano, ( CASE WHEN bb.mono_qty IS NULL THEN 0 ELSE bb.mono_qty END ) mono_qty
FROM dim.dim_date aa
LEFT JOIN
(
SELECT bb.date_actual,aa.equipmentno,aa.areano, COUNT ( mono ) mono_qty
FROM
(
SELECT to_char( aa.actualstartdate, 'yyyy-mm-dd' ) first_close_date,aa.mono,cc.equipmentno,cc.areano
FROM dwd.dwd_mes_tbloemobasis_detail aa
left join (select a.mono,a.equipmentno,a.areano from dwd.dwd_mes_tblwipdispatch_detail a where opno = 'ZSGX03' group by a.mono,a.equipmentno,a.areano) cc on aa.mono = cc.mono
WHERE aa.order_type = '注塑生产订单'
) aa
LEFT JOIN dim.dim_date bb ON bb.date_actual = aa.first_close_date :: DATE
GROUP BY first_close_date, bb.date_actual ,aa.equipmentno,aa.areano
ORDER BY first_close_date
) bb ON bb.date_actual = aa.date_actual ORDER BY aa.date_actual
) A
WHERE A.date_actual BETWEEN "beginDate" AND "endDate"
GROUP BY A.date_actual ,a.equipmentno,a.areano
) ee ON ee.date_actual = aa.date_actual and ee.equipmentno = bb.equipmentno ORDER BY aa.date_actual
) t1
LEFT JOIN t_4 t2 ON t2.date_actual = t1.date_actual and t2.equipmentno = t1.equipmentno
LEFT JOIN t_5 t3 ON t3.date_actual = t1.date_actual and t3.equipmentno = t1.equipmentno
LEFT JOIN t_9 t4 ON t4.date_actual = t1.date_actual and t4.equipmentno = t1.equipmentno
left join t_11 t5 on t5.date_actual = t1.date_actual and t5.equipmentno = t1.equipmentno
WHERE t1.date_actual BETWEEN "beginDate" AND "endDate"
)A where a.date_actual BETWEEN "beginDate" AND "endDate" ORDER BY a.date_actual;
DROP TABLE IF EXISTS t_4;
DROP TABLE IF EXISTS t_5;
DROP TABLE IF EXISTS t_6;
DROP TABLE IF EXISTS t_7;
DROP TABLE IF EXISTS t_9;
DROP TABLE IF EXISTS t_10;
DROP TABLE IF EXISTS t_11;
DROP TABLE IF EXISTS t_12;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
在网上搜了相关解答，则是将使用的表进行一个复制表操作，但是我的函数中有临时表也有明细表，不知道应该复制哪个表好
这么多表连接和子查询，效率真的高吗？