在很多时候我们需要计算两张表是否完全一致,尤其是类似发布订阅复制表的时候。这时候可以使用如下函数做全表的 hash 计算。
单表 HASH:
-- 创建函数
CREATE OR REPLACE FUNCTION table_to_hash(table_name text)
RETURNS text AS $$
DECLARE
output_hash_result text;
row_data text[];
BEGIN
-- 查询表的数据到变量
EXECUTE format('SELECT array_agg(row_to_json(t)::text) FROM %I AS t', table_name) INTO row_data;
-- 将数组中的每一行连接为一个字符串
output_hash_result := md5(array_to_string(row_data, E'\n'));
RETURN output_hash_result;
END;
$$ LANGUAGE plpgsql;
-- 调用函数
select table_to_hash('test');
全库所有表 HASH:
-- 创建函数
CREATE OR REPLACE FUNCTION get_tables_hash()
RETURNS VOID
AS $$
DECLARE
table_schema text;
table_name text;
table_data text[];
table_count bigint;
table_hash text;
BEGIN
FOR table_schema,
table_name IN
SELECT
t.table_schema,
t.table_name
FROM
information_schema.tables t
WHERE
t.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY
1,
2 LOOP
EXECUTE format('SELECT COUNT(*) FROM %I.%I', table_schema, table_name) INTO table_count;
EXECUTE format('SELECT array_agg(row_to_json(t)::text) FROM %I.%I AS t', table_schema, table_name) INTO table_data;
table_hash := md5(array_to_string(table_data, E'\n'));
RAISE INFO '% COUNT: % MD5: %', rpad(substr(table_schema || '.' || table_name, 1, 50), 50), rpad(table_count::text, 10), table_hash;
END LOOP;
END;
$$
LANGUAGE plpgsql;
-- 调用函数
SELECT get_tables_hash();