postgreSql 存储过程语法

CREATE OR REPLACE FUNCTION "public"."home_count"("v_processor" varchar, "v_depart_id" varchar, "v_root_depart_id" varchar, "v_depart_name" varchar, "v_root_depart_name" varchar, "v_yearmonth" varchar)
  RETURNS TABLE("dcdb" int4, "dbfw" int4, "dbsw" int4, "fwng" int4, "zhfw" int4, "zhsw" int4, "dywj" int4, "dbdbj" int4, "dbaffair" int4, "nbyj" int4, "pskh" int4, "dbrs" int4, "dwtx" int4, "ybgl" int4, "dbtp" int4) AS $BODY$
declare

				select_result record;
        dcdb     int4 default 0; -- 督察督办
        dbfw     int4 default 0; --待办发文
        dbsw     int4 default 0; --待办收文
        fwng     int4 default 0; -- 发文拟稿
        zhfw     int4 default 0; --暂缓发文
        zhsw     int4 default 0; --暂缓收文
        dywj     int4 default 0; --待阅文件
        dbdbj    int4 default 0; --待办督办件
        dbaffair int4 default 0; --待办事务
        nbyj     int4 default 0; --内部邮件
        pskh     int4 default 0; --平时考核
        dbrs     int4 default 0; --待办人事
        dwtx     int4 default 0; --党务提醒
        ybgl     int4 default 0; --月报管理
        dbtp     int4 default 0; --待办投票
begin
        --督查督办
        select count(0) into dcdb from t_oa_supervision t where fk_state = 0;
        -- 待办发文
        select
                count(0)
        into
                dbfw
        from
                (
                        select
                                b.task_id
                        from
                                t_oa_dispatch a
                        join t_wf_tasklist b
                        on
                                a.record_id = b.record_id
                        join t_wf_instance d
                        on
                                a.record_id = d.record_id
                        left join t_wf_tasklist t2
                        on
                                b.prenode_task_id = t2.task_id
                        where
                                upper(d.data_model) = 't_oa_dispatch'
                            and b.done_flag         = 0
                            and current_state       = 1
                            and b.sign_flag        in (0, 1)
                            and b.processor         = v_processor
                )
                v
        left join t_oa_suspendedsfw w
        on
                v.task_id = w.task_id
        where
                w.task_id is null;
        -- 待办收文
        select
                count(0)
        into
                dbsw
        from
                (
                        select
                                b.task_id
                        from
                                t_oa_receivefile a
                        join t_wf_instance d
                        on
                                a.record_id = d.record_id
                        left join t_wf_tasklist b
                        on
                                a.record_id = b.record_id
                        left join t_oa_suspendedsfw w
                        on
                                b.task_id = w.task_id
                        where
                                b.done_flag     = 0
                            and d.current_state = 1
                            and b.sign_flag    in (0, 1)
                            and b.processor     = v_processor
                            and w.task_id      is null
                );
        --发文拟稿
        select
                count(1)
        into
                fwng
        from
                t_oa_dispatch c
        join t_wf_instance b
        on
                c.record_id = b.record_id
        where
                current_state = 9
            and c.creator_id  = v_processor;
        -- 暂缓发文
        select
                count(1)
        into
                zhfw
        from
                (
                        select
                                b.task_id
                        from
                                t_oa_dispatch a
                        join t_wf_instance d
                        on
                                a.record_id = d.record_id
                        join t_wf_tasklist b
                        on
                                a.record_id = b.record_id
                        where
                                d.current_state = 1
                            and b.done_flag     = 0
                            and b.sign_flag    in (0, 1)
                            and b.processor     = v_processor
                )
                v
        join t_oa_suspendedsfw w
        on
                v.task_id = w.task_id
        where
                w.task_id is not null ;
        --暂缓收文
        select
                (
                (
                        select
                                count(1)
                        from
                                (
                                        select
                                                b.task_id
                                        from
                                                t_oa_receivefile a
                                        join t_wf_instance d
                                        on
                                                a.record_id = d.record_id
                                        join t_wf_tasklist b
                                        on
                                                a.record_id = b.record_id
                                        where
                                                d.current_state = 1
                                            and b.done_flag     = 0
                                            and bjlx            = 2
                                            and b.sign_flag    in (0, 1)
                                            and b.processor     = v_processor
                                )
                                v
                        join t_oa_suspendedsfw w
                        on
                                v.task_id = w.task_id
                )
                +
                (
                        select
                                count(1)
                        from
                                (
                                        select
                                                b.task_id
                                        from
                                                t_oa_receivefile a
                                        join t_wf_instance d
                                        on
                                                a.record_id = d.record_id
                                        join t_wf_tasklist b
                                        on
                                                a.record_id = b.record_id
                                        where
                                                d.current_state = 1
                                            and b.done_flag     = 0
                                            and a.bjlx         in (0, 3)
                                            and b.sign_flag    in (0, 1)
                                            and b.processor     = v_processor
                                )
                                v
                        join t_oa_suspendedsfw w
                        on
                                v.task_id = w.task_id
                )
                +
                (
                        select
                                count(1)
                        from
                                (
                                        select
                                                b.task_id
                                        from
                                                t_oa_receivefile a
                                        join t_wf_instance d
                                        on
                                                a.record_id = d.record_id
                                        join t_wf_tasklist b
                                        on
                                                a.record_id = b.record_id
                                        where
                                                d.current_state = 1
                                            and b.done_flag     = 0
                                            and b.sign_flag    in (0, 1)
                                            and a.bjlx          = 1
                                            and b.processor     = v_processor
                                )
                                v
                        join t_oa_suspendedsfw w
                        on
                                v.task_id = w.task_id
                )
                )
        into
                zhsw ;
        select
                count(1)
        into
                dywj
        from
                (
                        select
                                b.task_id
                        from
                                t_oa_receivefile a
                        join t_wf_instance d
                        on
                                a.record_id = d.record_id
                        left join t_wf_tasklist b
                        on
                                a.record_id = b.record_id
                        where
                                b.done_flag     = 0
                            and d.current_state = 1
                            and bjlx            = 1
                            and b.sign_flag    in (0, 1)
                            and b.processor     = v_processor
                )
                v
        left join t_oa_suspendedsfw w
        on
                v.task_id = w.task_id
        where
                w.task_id is null ;
        --     待办督办件
        select
                count(1)
        into
                dbdbj
        from
                t_oa_receivefile a
        join t_wf_tasklist b
        on
                a.record_id = b.record_id
            and b.processor = v_processor
        join t_wf_instance d
        on
                a.record_id = d.record_id
        left join t_oa_suspendedsfw w
        on
                b.task_id = w.task_id
        where
                w.task_id     is null
            and b.sign_flag   in(0, 1)
            and b.done_flag    = 0
            and d.current_state=1
            and a.bjlx         = 2 ;
        -- 待办事务
        select
                count(1)
        into
                dbaffair
        from
                (
                        select
                                task_id
                        from
                                t_wf_instance d
                        join t_wf_tasklist b
                        on
                                d.instance_id = b.instance_id
                        where
                                d.current_state = 1
                            and d.is_delete = 0
                            and b.node_name not like '%申请%'
                            and b.processor       = v_processor
                            and b.done_flag       = 0
                            and sign_flag        in (0, 1)
                            and d.data_model not in ('T_OA_OFFICIALBUSSINESS', 'T_OA_BUSSINESSBACK', 'T_OA_BIDENTRY', 'T_OA_APPROVALREVIEW', 'T_OFFICE_LEAVE', 'T_OFFICE_NOATTENDANCE', 'T_OFFICE_OVERTIME', 'T_OFFICE_BUSINESSLEAVE', 'T_OFFICE_OVERTIMECONFIRM', 'T_OA_THREEHOUROVERTIME', 'T_OA_LATEDEDUCTION', 'T_OA_DISPATCH', 'T_OA_RECEIVEFILE')
                        -- 需求单

                        union all

                        select
                                record_id as task_id
                        from
                                t_oa_needapply t
                        where
                                t.current_state = 1
                            and
                                (
                                        (
                                                t.czsp_id     = v_processor
                                            and t.oaadmin_id is null
                                        )
                                     or
                                        (
                                                t.oaadmin_id = v_processor
                                            and t.mrccz_id  is null
                                        )
                                     or
                                        (
                                                t.mrccz_id = v_processor
                                        )
                                )
                );
        -- 内部邮件
        select
                count(1)
        into
                nbyj
        from
                t_oa_mail
        where
                owner_id    = v_processor
            and is_readed   = 0
            and send_status = 1;
        -- 平时考核
        select
                count(1)
        into
                pskh
        from
                t_wf_tasklist t
        join t_wf_instance w
        on
                t.instance_id = w.instance_id
        where
          w.data_model in('T_OFFICE_LEAVE','T_OFFICE_BUSINESSLEAVE','T_OFFICE_NOATTENDANCE','T_OFFICE_OVERTIME','T_OFFICE_OVERTIMECONFIRM')
            and w.is_delete <>1
            and t.processor       = v_processor
            and t.node_name not like '%申请%'
            and t.sign_flag      in (1, 0)
            and t.done_flag       = 0
            and w.current_state   = 1;
        --待办人事
        select
                count(1)
        into
                dbrs
        from
                t_wf_instance d
        join t_wf_tasklist b
        on
                d.instance_id = b.instance_id
        where
                d.current_state = 1
            and d.is_delete <>1
            and b.node_name not like '%申请%'
            and b.processor   = v_processor
            and b.done_flag   = 0
            and b.sign_flag  in (0, 1)
            and d.data_model in ('T_OA_OFFICIALBUSSINESS', 'T_OA_BUSSINESSBACK', 'T_OA_BIDENTRY', 'T_OA_APPROVALREVIEW');
        -- 党务提醒
        select count(1) into dwtx from dual where 1 = 2;
        --月报管理 每月更新
        select
                nvl(sum(item_count),0)
        into
                ybgl
        from
                t_oa_monthlyreport t
        where
                t.is_fbsy  = 1
            and t.belongym = v_yearmonth;
        --投票管理 待办投票  功能未做 后续需要加上

--遍历返回数据	
	RETURN  QUERY
	select 
	dcdb as dcdb,
	dbfw as dbfw,
	dbsw as dbsw,
	fwng as fwng,
	zhfw as zhfw,
	zhsw as zhsw,
	dywj as dywj,
	dbdbj as dbdbj,
	dbaffair as dbaffair,
	nbyj as nbyj,
	pskh as pskh,
	dbrs as dbrs,
	dwtx as dwtx,
	ybgl as ybgl,
	dbtp as dbtp
	 from dual;
	end 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000