PGSQL实现拉链表的存储过程
1、表必须有唯一主键，否则拉链不成功
2、函数传入两个参数，第一个参数为表名，第二个参数为主键名
3、过程中会产生两个中间表，不可删除或修改
4、编码不易，记得点赞收藏，如有疑问可以联系本人
CREATE OR REPLACE FUNCTION "public"."incre_data"("original_table_name" varchar, "main_key" varchar)
RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
marge_data_records_cursor refcursor;
marge_data_record record;
target_table_records_has_data BOOLEAN; --目标表记录
all_column_cursor cursor for SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = original_table_name;
all_column_name varchar; -- 所有列字段名，逗号分隔
all_column_name_array varchar[];
now_column_name VARCHAR; -- 游标操作变量
main_table_all_column_name VARCHAR;
main_table_all_column_array VARCHAR[];
origin_table_all_column_name VARCHAR;
origin_table_all_column_array VARCHAR[];
compare_table_all_column_name VARCHAR;
compare_table_all_column_array VARCHAR[];
all_column_count int; --原表数据项
MAX_TIME_DATE timestamp := '9999-12-31 00:00:00'; --拉链表默认最大时间
NOW_TIME_DATE timestamp := now(); --当前时间
BEGIN
execute format(
'SELECT count(*) FROM information_schema.columns WHERE table_schema = ''public'' AND table_name = ''%I''',
original_table_name) into all_column_count;
OPEN all_column_cursor;
loop
FETCH all_column_cursor INTO now_column_name;
-- 对每条记录进行处理
all_column_name_array := array_append(all_column_name_array, concat('"', now_column_name, '"'));
main_table_all_column_array := array_append(main_table_all_column_array, concat('AA."', now_column_name, '"'));
origin_table_all_column_array :=
array_append(origin_table_all_column_array, concat('BB."', now_column_name, '"'));
compare_table_all_column_array := array_append(compare_table_all_column_array,
concat('AA."', now_column_name, '"!=BB."', now_column_name, '"'));
all_column_count = all_column_count - 1;
if all_column_count = 0 then exit; end if;
end loop;
CLOSE all_column_cursor;
--所有列名变量
all_column_name = array_to_string(all_column_name_array, ',');
main_table_all_column_name = array_to_string(main_table_all_column_array, ',');
origin_table_all_column_name = array_to_string(origin_table_all_column_array, ',');
compare_table_all_column_name = array_to_string(compare_table_all_column_array, ' or ');
if (select count(*) from information_schema.tables where table_name = concat(original_table_name, '_incre')) =
0 then
--拉链表不存在，创建新拉链表,复制原来表结构
EXECUTE format('CREATE TABLE %I_incre AS TABLE %I WITH NO DATA', original_table_name, original_table_name);
--增加时间起止字段
execute format(
'ALTER TABLE "public"."%I_incre" ADD COLUMN "start_time" timestamp,ADD COLUMN "end_time" timestamp',
original_table_name);
EXECUTE format('CREATE TABLE %I_incre_marge_temp AS TABLE %I_incre WITH NO DATA', original_table_name,
original_table_name);
EXECUTE format('CREATE TABLE %I_temp AS TABLE %I_incre WITH NO DATA', original_table_name,
original_table_name);
execute format(
'insert into %I_incre(%s,start_time,end_time) select %s,now() as start_time,to_timestamp(''9999-12-31 00:00:00'',''YYYY-MM-DD HH24:MI:SS'') as end_time from %I',
original_table_name, all_column_name, all_column_name, original_table_name);
else
--表存在，执行后续拉链操作
EXECUTE format('SELECT EXISTS (SELECT 1 FROM %I_incre LIMIT 1)',
original_table_name) into target_table_records_has_data;
IF target_table_records_has_data THEN
--执行具体拉链操作
--1、删除数据,插入临时表
EXECUTE format('insert into %I_temp SELECT %s,start_time,''%s'' as end_time ' ||
'from (SELECT %s,start_time,end_time from %I_incre ' ||
'WHERE end_time=''%s'') AA ' ||
'WHERE not exists ( SELECT 1 FROM %I BB WHERE AA.%s = BB.%s)',
original_table_name, all_column_name, NOW_TIME_DATE, all_column_name, original_table_name,
MAX_TIME_DATE, original_table_name, main_key, main_key);
-- 2、新增数据，插入临时表
EXECUTE format('insert into %I_temp(%s,start_time,end_time) ' ||
'(SELECT %s,''%s'' as start_time,''%s'' as end_time ' ||
'from (SELECT %s from %I) AA ' ||
'WHERE not exists ( SELECT 1 FROM (SELECT %s from ' ||
'%I_incre WHERE end_time=''%s'') BB WHERE AA.%s = BB.%s))',
original_table_name, all_column_name, all_column_name, NOW_TIME_DATE, MAX_TIME_DATE,
all_column_name, original_table_name, all_column_name, original_table_name, MAX_TIME_DATE,
main_key, main_key);
--3、修改数据，插入临时表
EXECUTE format('insert into %I_temp(%s,start_time,end_time) ' ||
'SELECT %s,''%s'' as start_time,''%s'' as end_time ' ||
'from (SELECT %s,start_time,end_time from %I_incre WHERE end_time=''%s'') AA ' ||
'left join %I BB on AA.%s = BB.%s WHERE %s',
original_table_name, all_column_name, origin_table_all_column_name, NOW_TIME_DATE,
MAX_TIME_DATE, all_column_name, original_table_name, MAX_TIME_DATE, original_table_name,
main_key, main_key, compare_table_all_column_name);
--4、临时表和拉链表合并
EXECUTE format(
'insert into %I_incre_marge_temp SELECT %s,AA.start_time,case when BB.%s is not null and AA.end_time=''%s'' then now() else AA.end_time end ' ||
'from %I_incre AA left join %I_temp BB on AA.%s=BB.%s union all ' ||
'SELECT * from %I_temp WHERE end_time=''%s''',
original_table_name, main_table_all_column_name, main_key, MAX_TIME_DATE,
original_table_name,
original_table_name, main_key, main_key,
original_table_name, MAX_TIME_DATE);
--5、清空拉链表
EXECUTE format('truncate TABLE %I_incre', original_table_name);
--6、清空临时表
EXECUTE format('truncate TABLE %I_temp', original_table_name);
--7、插入合并数据
EXECUTE format('insert into %I_incre SELECT * from %I_incre_marge_temp', original_table_name,
original_table_name);
EXECUTE format('truncate TABLE %I_incre_marge_temp', original_table_name);
ELSE
--查询拉链表结果为空，则执行全量插入数据到拉链表并且设置数据起止时间
EXECUTE 'INSERT into ' || original_table_name ||
'_incre (SELECT *,now() as start_time,to_timestamp(''9999-12-31 00:00:00'',''YYYY-MM-DD HH24:MI:SS'') as end_time from ' ||
original_table_name || ')';
END IF;
END IF;
RETURN;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
1694
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
