--SELECT * FROM db_scld.scld_7jdcsaqscyhpczzqktjb(2018,'全国各地') t(mkmc VARCHAR,total NUMERIC )
--隐患排查统计表
CREATE OR REPLACE FUNCTION scld_7jdcsaqscyhpczzqktjb(nf int , sjmc varchar )
RETURNS SETOF RECORD as
$$
DECLARE
v_rec RECORD;
ksrq DATE;
jsrq DATE;
BEGIN
ksrq=to_Date(nf||'-01-01', 'yy-mm-dd');
jsrq=to_Date(nf||'-12-31', 'yy-mm-dd');
--传入地区
if (sjmc ='全国各地') then
sjmc:='';
end if;
for v_rec in (
select cast('排查数' as varchar),COALESCE(sum(num),0)
from (
SELECT c_szs,code.c_name,count(jdcsaqscyhpczzxx.*) num
from t_scld_jdcsaqscyhpczzxx jdcsaqscyhpczzxx, t_aty_code code
where jdcsaqscyhpczzxx.c_szs=code.c_code
and code.c_pid='070004'
and code.c_name like '%'||sjmc||'%'
and jdcsaqscyhpczzxx.D_JCRQ between ksrq and jsrq
and deleted='0'
group by c_szs,code.c_name
) t
union all
select cast('隐患数' as varchar),COALESCE(sum(num),0)
from (
SELECT c_szs,code.c_name,sum(N_YHGS) num
from t_scld_jdcsaqscyhpczzxx jdcsaqscyhpczzxx, t_aty_code code
where jdcsaqscyhpczzxx.c_szs=code.c_code
and code.c_pid='070004'
and code.c_name like '%'||sjmc||'%'
and jdcsaqscyhpczzxx.D_JCRQ between ksrq and jsrq
and deleted='0'
group by c_szs,code.c_name
) t
)loop
RETURN NEXT v_rec ;
end loop;
return;
END;
$$
LANGUAGE 'plpgsql';