1，添加单位部门
-- PROCEDURE: public.pr_department_info_add(integer, integer, integer, integer, integer, character varying, bigint, character varying, character varying, character varying, character varying, integer, integer, integer)-- DROP PROCEDURE public.pr_department_info_add(integer, integer, integer, integer, integer, character varying, bigint, character varying, character varying, character varying, character varying, integer, integer, integer);CREATE OR REPLACE PROCEDURE public.pr_department_info_add(workunittype integer,provinceid integer,cityid integer,districtid integer,streetid integer,departmentname character varying,addtime bigint,provincename character varying,cityname character varying,districtname character varying,streetname character varying,isshow integer,orderweight integer,INOUT returnvalue integer)
LANGUAGE 'plpgsql'
AS $BODY$
declare stack text;
BEGIN-- 添加单位部门 insert into t_department_info(work_unit_type,province_id,city_id,district_id,street_id,department_name)values(workunittype,provinceid,cityid,districtid,streetid,departmentname);insert into t_notice_info(notice_title,notice_content,province_id,city_id,district_id,street_id,province_name,city_name,district_name,street_name,order_weight,is_show,add_time)values('','',provinceid,cityid,districtid,streetid,coalesce(provincename,'character varying'),coalesce(cityname,'character varying'),coalesce(districtname,'character varying'),coalesce(streetname,'character varying'),coalesce(orderweight,0),coalesce(isshow,0),floor(EXTRACT(epoch FROM (now()::timestamp with time zone))*1000));returnvalue:=1;exceptionWHEN RAISE_EXCEPTION THENbeginGET STACKED DIAGNOSTICS stack = MESSAGE_TEXT;RAISE NOTICE E'--- Call Stack ---\n%', stack ;returnvalue = 0;end;
END;
$BODY$;2，修改单位部门
-- PROCEDURE: public.pr_department_info_edit(integer, integer, integer, integer, integer, integer, character varying, bigint, character varying, character varying, character varying, character varying, integer, integer, integer)-- DROP PROCEDURE public.pr_department_info_edit(integer, integer, integer, integer, integer, integer, character varying, bigint, character varying, character varying, character varying, character varying, integer, integer, integer);CREATE OR REPLACE PROCEDURE public.pr_department_info_edit(departmentid integer,workunittype integer,provinceid integer,cityid integer,districtid integer,streetid integer,departmentname character varying,addtime bigint,provincename character varying,cityname character varying,districtname character varying,streetname character varying,isshow integer,orderweight integer,INOUT returnvalue integer)
LANGUAGE 'plpgsql'
AS $BODY$
declare stack text;
BEGIN-- 修改工作单位update t_department_infoset work_unit_type=workunittype,province_id=provinceid,city_id=cityid,district_id=districtid,street_id=streetid,department_name=departmentnamewhere province_id=provinceid and department_id=departmentid;update t_notice_infoset province_name=provincename,city_name=cityname,district_name=districtname,street_name=streetname,is_show=isshow, order_weight=orderweight,add_time=floor(EXTRACT(epoch FROM (now()::timestamp with time zone))*1000)where province_id=provinceid;returnvalue:=1;exceptionWHEN RAISE_EXCEPTION THENbeginGET STACKED DIAGNOSTICS stack = MESSAGE_TEXT;RAISE NOTICE E'--- Call Stack ---\n%', stack ;returnvalue = 0;end;
END
$BODY$;