PostgreSQL存储过程(四)：追加指定日期区间数据

需求介绍：

 

        本次的目标是实现追加指定区间内的历史数据，且仅保存指定日期区间内月份月末一天的数据的函数。在文章三中介绍了方便一次性使用的匿名代码块，但匿名代码块对脚本的封装能力有限，本次使用普通的函数进行实现。【注：函数设计应该尽量解耦合，因此本文实现的代码更优的方式是拆分为插入一个月数据的函数及一个负责调用的循环函数。】

通用程序框架：

        通用的程序脚本如下，在类似的场景下可以直接在函数体中填充需要的业务逻辑代码。

start_dt：起始日期
end_dt：结束日期
p_result：返回结果变量
-------------------
CREATE OR REPLACE FUNCTION mydb.mysc.generate_data(IN start_dt DATE,IN end_dt DATE,OUT p_result VARCHAR(1000))
    AS 
	$BODY$ 
			DECLARE 
			dura_dt DATE[];
			counter int = 1;
			-- 初始化将传入的起始日期取月末日期
			step_dt DATE:=(date_trunc('MONTH', start_dt) + INTERVAL '1 MONTH - 1 day')::date;
			-- 初始化将传入的结束日期取月末日期
			last_dt DATE:=(date_trunc('MONTH', end_dt) + INTERVAL '1 MONTH - 1 day')::date;
			-- 定义两个临时变量
			tmp_p VARCHAR(1000)=''; -- 用于拼接查询结束的返回信息
			tmp_dt DATE; -- 用于FOREACH循环
		BEGIN
			LOOP
			if step_dt <= last_dt then
				dura_dt[counter] := step_dt;
				step_dt:=(date_trunc('MONTH', step_dt) + INTERVAL '2 MONTH - 1 day')::date;
				counter:=counter+1;			
			end if;
			EXIT WHEN step_dt > last_dt;
			END LOOP;	
			FOREACH tmp_dt IN ARRAY dura_dt LOOP

                /* 插入数据的逻辑代码开始 */
                ----------------------------------------
                -- 编写业务逻辑代码，日期使用变量tmp_dt
                ----------------------------------------
                /* 插入数据的逻辑代码结束 */
				tmp_p := tmp_p||'已追加'||tmp_dt||'数据；'||E'\n';
				raise notice ' %', tmp_p;
			END LOOP;
			p_result:=tmp_p;
		END;
	$BODY$ 
    LANGUAGE 'plpgsql'			-- 指定函数的程序语言
    VOLATILE					-- 优化器不进行优化
   RETURNS NULL ON NULL INPUT; -- 当传入参数含有null时返回null

使用函数追加指定日期区间数据的函数实现：

        示例脚本使用的数据表依旧是文章三中创建的learn01表。

start_dt：起始日期
end_dt：结束日期
p_result：返回结果变量
-------------------
CREATE OR REPLACE FUNCTION mydb.mysc.generate_data(IN start_dt DATE,IN end_dt DATE,OUT p_result VARCHAR(1000))
    AS 
	$BODY$ 
			DECLARE 
			dura_dt DATE[];
			counter int = 1;
			-- 初始化将传入的起始日期取月末日期
			step_dt DATE:=(date_trunc('MONTH', start_dt) + INTERVAL '1 MONTH - 1 day')::date;
			-- 初始化将传入的结束日期取月末日期
			last_dt DATE:=(date_trunc('MONTH', end_dt) + INTERVAL '1 MONTH - 1 day')::date;
			-- 定义两个临时变量
			tmp_p VARCHAR(1000)=''; -- 用于拼接查询结束的返回信息
			tmp_dt DATE; -- 用于FOREACH循环
		BEGIN
			LOOP
			if step_dt <= last_dt then
				dura_dt[counter] := step_dt;
				step_dt:=(date_trunc('MONTH', step_dt) + INTERVAL '2 MONTH - 1 day')::date;
				counter:=counter+1;			
			end if;
			EXIT WHEN step_dt > last_dt;
			END LOOP;	
			FOREACH tmp_dt IN ARRAY dura_dt LOOP
                /* 插入数据的逻辑代码开始 */
                ----------------------------------------
				-- 确保该日期无数据
				DELETE FROM mydb.mysc.learn01 WHERE statistics_dt = tmp_dt;
				-- 每个数据日期下插入1000条数据
				FOR i IN 1..1000 LOOP
					INSERT INTO mydb.mysc.learn01
						(statistics_dt 
						,cust_id 		
						,cust_name 		
						,aum_avg 		
						,age 			
						,gender )
					VALUES(
						tmp_dt
						,RIGHT('000'||i,4)
						,'编号'||RIGHT('000'||i,4)
						,CAST(RANDOM() * 10000  AS decimal(16,2))
						,CAST(RANDOM() * 90  AS INT)
						,CAST(CAST(RANDOM() * 90  AS INT)%2 AS CHAR));
					i:=i+1;
				END LOOP;
                /* 插入数据的逻辑代码结束 */
                ----------------------------------------
				tmp_p := tmp_p||'已追加'||tmp_dt||'数据；'||E'\n';
				raise notice ' %', tmp_p;
			END LOOP;
			p_result:=tmp_p;
		END;
	$BODY$ 
    LANGUAGE 'plpgsql'			-- 指定函数的程序语言
    VOLATILE					-- 优化器不进行优化
   RETURNS NULL ON NULL INPUT; -- 当传入参数含有null时返回null

调用函数追加2020年12个月月末数据：

验证数据：