pgsql 函数、存储过程用法

函数

--获取年月日  2022-03-01
select date(to_timestamp(round(1646103144959/1000)) + interval '1 hour' * 8 ) 

--获取当前年份
thisyear:=(select to_char(to_timestamp(floor(EXTRACT(epoch FROM (now()::timestamp with time zone)))),'yyyy')::integer);



--备注说明【获取工人今年总工作日（天）】--用户【CYL】--时间【2022-3-3】
CREATE OR REPLACE FUNCTION public.fx_get_worker_this_year_data
(
    workerid integer    --工人ID
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
declare returnvalue integer;
declare thisyear integer;
begin
    thisyear:=(select to_char(to_timestamp(floor(EXTRACT(epoch FROM (now()::timestamp with time zone)))),'yyyy')::integer);
    select count(distinct(date(to_timestamp(round(t_reimbursement_worker_info.add_time/1000)) + interval '1 hour' * 8 ))) into returnvalue
    from t_reimbursement_worker_info
    left join t_reimbursement_info on t_reimbursement_worker_info.reimbursement_id=t_reimbursement_info.reimbursement_id
    where examine_state=2 and t_reimbursement_worker_info.worker_id=workerid and fx_get_year_by_timestamp(t_reimbursement_worker_info.add_time)=thisyear;
    
    return returnvalue;
end;
$BODY$;

--备注说明【获取工人今年总住宿时间（天）】--用户【CYL】--时间【2022-3-3】
CREATE OR REPLACE FUNCTION public.fx_get_worker_accommodation_this_year_data
(
    workerid integer    --工人ID
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
declare returnvalue integer;
declare thisyear integer;
begin
    thisyear:=(select to_char(to_timestamp(floor(EXTRACT(epoch FROM (now()::timestamp with time zone)))),'yyyy')::integer);
    select sum(accommodation_num) into returnvalue from t_worker_accommodation_subsidy_record_info where fx_get_year_by_timestamp(add_time)=thisyear and worker_id=workerid;
    return returnvalue;
end;
$BODY$;

--备注说明【获取工人今年住宿总补助额】--用户【CYL】--时间【2022-3-3】
CREATE OR REPLACE FUNCTION public.fx_get_worker_accommodation_money_this_year_data
(
    workerid integer    --工人ID
)
RETURNS numeric(18,2)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
declare returnvalue integer;
declare thisyear integer;
begin
    thisyear:=(select to_char(to_timestamp(floor(EXTRACT(epoch FROM (now()::timestamp with time zone)))),'yyyy')::integer);
    select sum(accommodation_num * subsidy_amount) into returnvalue from t_worker_accommodation_subsidy_record_info where fx_get_year_by_timestamp(add_time)=thisyear and worker_id=workerid;
    return returnvalue;
end;
$BODY$;

存储过程

--备注说明【工人列表】--用户【CYL】--时间【2022-3-3】
CREATE OR REPLACE PROCEDURE public.pr_worker_info_get_list_by_page(
    startindex integer,
    pagesize integer,
    strwhere character varying,
    strorder character varying,
    INOUT workerinfocursor refcursor,
    INOUT workerinfocountcursor refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
declare exec_sql character varying;
declare exec_str_order character varying;
declare exec_recordcount_sql character varying;
begin
    exec_str_order := ' t_worker_info.worker_id desc ';
    if length(strorder)>0 then
        exec_str_order := strorder;
    end if;
    exec_sql := ' select worker_id,head_img,nick_name,login_name,wage_amount,fx_get_worker_this_year_data(worker_id) as total_week_days,
    fx_get_worker_accommodation_this_year_data(worker_id) as total_accommodation_days,
    fx_get_worker_accommodation_money_this_year_data(worker_id) as total_accommodation_money_days
    from t_worker_info '; 
    exec_recordcount_sql := ' select count(t_worker_info.worker_id) as record_count from t_worker_info ';
    if length(strwhere)>0 then 
        exec_sql := format('%s where %s',exec_sql,strwhere);
        exec_recordcount_sql := format(' %s where %s ',exec_recordcount_sql,strwhere);
    end if;
    exec_sql := format('%s order by %s limit %s offset %s ',exec_sql,exec_str_order,pagesize,startindex);
    open workerinfocursor for execute exec_sql;
    open workerinfocountcursor for execute exec_recordcount_sql;
end;
$BODY$;

--备注说明【新增工人信息】--用户【CYL】--时间【2022-3-3】
CREATE OR REPLACE PROCEDURE public.pr_worker_info_add(
    loginname character varying,
    headimg character varying,
    nickname character varying,
    wageamount numeric(18,2),
    INOUT returnvalue integer)
LANGUAGE 'plpgsql'
AS $BODY$
declare stack text;
declare currenttime bigint;
begin
    currenttime:=floor(EXTRACT(epoch FROM (now()::timestamp with time zone))*1000);
    insert into t_worker_info(login_name,head_img,nick_name,add_time,wage_amount,is_show,is_del)
    values(loginname,headimg,nickname,currenttime,wageamount,1,0);
    returnvalue:=1;
    exception
    WHEN RAISE_EXCEPTION THEN
    begin
        GET STACKED DIAGNOSTICS stack = MESSAGE_TEXT;
        RAISE NOTICE E'--- Call Stack ---\n%', stack ;
        returnvalue = 0;
    end;
end
$BODY$;

--备注说明【获取工人信息】--用户【CYL】--时间【2022-3-3】
CREATE OR REPLACE PROCEDURE public.pr_worker_info_get_model
(
    workerid integer,
    INOUT workerinfocursor refcursor
)
LANGUAGE 'plpgsql'
AS $BODY$
begin
    open workerinfocursor for
    select worker_id, login_name, head_img, nick_name, add_time, wage_amount, is_show, is_del
    from t_worker_info where worker_id=workerid;
end
$BODY$;


--备注说明【编辑工人信息】--用户【CYL】--时间【2022-3-3】
CREATE OR REPLACE PROCEDURE public.pr_worker_info_edit(
    workerid integer,
    loginname character varying,
    headimg character varying,
    nickname character varying,
    wageamount numeric,
    isshow integer,
    INOUT returnvalue integer)
LANGUAGE 'plpgsql'
AS $BODY$
declare stack text;
begin
    update t_worker_info
    set login_name=loginname,head_img=headimg,nick_name=nickname,wage_amount=wageamount,is_show=isshow
    where worker_id=workerid;
    returnvalue:=1;
    exception
    WHEN RAISE_EXCEPTION THEN
    begin
        GET STACKED DIAGNOSTICS stack = MESSAGE_TEXT;
        RAISE NOTICE E'--- Call Stack ---\n%', stack ;
        returnvalue = 0;
    end;
end
$BODY$;


--备注说明【删除工人信息】--用户【CYL】--时间【2022-3-3】
CREATE OR REPLACE PROCEDURE public.pr_worker_info_del
(
    workerid integer,
    INOUT returnvalue integer
)
LANGUAGE 'plpgsql'
AS $BODY$
declare stack text;
begin
    update t_worker_info set is_del=1 where worker_id=workerid;
    returnvalue:=1;
    exception
    WHEN RAISE_EXCEPTION THEN
    begin
        GET STACKED DIAGNOSTICS stack = MESSAGE_TEXT;
        RAISE NOTICE E'--- Call Stack ---\n%', stack ;
        returnvalue = 0;
    end;
end
$BODY$;