pgsql存储过程

小城南

已于 2023-12-09 11:07:43 修改

阅读量761

点赞数 7

分类专栏：数据库文章标签： pgsql 存储过程函数

于 2023-12-09 11:01:59 首次发布

本文链接：https://blog.youkuaiyun.com/cainiaobulan/article/details/134891556

版权

数据库专栏收录该内容

32 篇文章

订阅专栏

文章描述了企业从AWS迁移至腾讯云后，因定时任务失误导致的数据问题。为避免在白天上线，作者提供了创建的存储过程示例，用于在指定时间段内，通过函数重新计算并插入数据到sys_statlog_month表中，以确保统计数据的准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

由于部分企业数据库从aws迁移到腾讯云，导致有一个定时任务（从详情表汇总数据到统计表中）错过了触发，所以这部分企业的数据需要触发重新刷一下，但是又有规定白天不允许上线，只能把定时任务的逻辑用存储过程（函数）实现一遍，通过这种方式把数据刷正确。下面是完整的存储过程示例：

 --删除储存过程
 --DROP FUNCTION f_sys_statlog_month(text,text,text) 
 --新建储存过程
CREATE OR REPLACE FUNCTION "public"."f_sys_statlog_month"(beginTime TEXT,endTime TEXT,monthTime TEXT)
  RETURNS "pg_catalog"."void" AS $BODY$ 
DECLARE
     c_record record;
     BEGIN
      delete from sys_statlog_month where statistics_time = monthTime;
      for c_record in
       with used_set as (
                            SELECT
                            b.user_id,
                            sum(case when b.client_type='1' THEN 1 else 0 end) as client_1, 
                            sum(case when b.client_type='2' THEN 1 else 0 end) as client_2, 
                            sum(case when b.client_type='4' THEN 1 else 0 end) as client_4,
                            sum(case when b.client_type='5' THEN 1 else 0 end) as client_5
                            FROM sys_statlog b
                            WHERE 1 = 1
                            and b.occur_time >= to_date(beginTime,'yyyy-MM-dd')
                            AND b.occur_time < to_date(endTime,'yyyy-MM-dd')
                            and b.operate_type IN ('1', '2')
                            and b.user_id != 999
                            GROUP BY b.user_id
                        )
                         select id,
                            COALESCE(client_1, 0) as client_1,
                            COALESCE(client_2, 0) as client_2,
                            COALESCE(client_4, 0) as client_4,
                            COALESCE(client_5, 0) as client_5 from sys_employee a1
                            left join used_set a2 on a1.id=a2.user_id
                            where a1.status in ('1','2')
                         union ALL
                            select id,
                                 COALESCE(client_1, 0) as client_1,
                                 COALESCE(client_2, 0) as client_2,
                                 COALESCE(client_4, 0) as client_4,
                                 COALESCE(client_5, 0) as client_5 
                                from sys_employee a1
                                join used_set a2 on a1.id=a2.user_id
                                where a1.status= '0'
                        loop
                         insert into sys_statlog_month
                         (
                                 id, 
                                 user_id,
                                 creator_id,
                                 create_time,
                                 client_type_1,
                                 client_type_2,
                                 client_type_4,
                                 client_type_5,
                                 statistics_time
                         )
                         values 
                         (
                                cast(RANDOM() * 10000000000000 as int8), 
                                c_record.id,
                                -10000,
                                now(), 
                                c_record.client_1,
                                c_record.client_2, 
                                c_record.client_4,
                                c_record.client_5,
                                monthTime
                        );
                end loop;
                END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

---下面sql演示刷2023年11月份的数据
select f_sys_statlog_month('2023-11-01','2023-12-01','202311');