1、创建函数
CREATE OR REPLACE FUNCTION "public"."update_sequence_values"()
RETURNS TABLE("var_table_schema" text, "var_table_name" text, "old_max_id" int4, "new_max_id" int4) AS $$
DECLARE
table_rec RECORD;
max_id INTEGER;
old_max_id_val INTEGER;
BEGIN
DROP TABLE IF EXISTS temp_sequence_updates;
CREATE TEMP TABLE temp_sequence_updates (
var_table_schema TEXT,
var_table_name TEXT,
old_max_id INTEGER,
new_max_id INTEGER
);
FOR table_rec IN (SELECT DISTINCT(table_schema), table_name, is_identity, column_name FROM information_schema.columns WHERE is_identity= 'YES' AND table_schema = 'public') LOOP
EXECUTE format('SELECT MAX(%I) FROM %I.%I;', table_rec.column_name, table_rec.table_schema, table_rec.table_name) INTO max_id;
IF max_id IS NOT NULL THEN
EXECUTE format('SELECT setval(pg_get_serial_sequence(''%I'', ''%s''), %s, false);', table_rec.table_name, replace(table_rec.column_name, '"','') , max_id + 1);
INSERT INTO temp_sequence_updates (var_table_schema, var_table_name, old_max_id, new_max_id) VALUES (table_rec.table_schema, table_rec.table_name , max_id, max_id + 1);
END IF;
END LOOP;
RETURN QUERY SELECT * FROM temp_sequence_updates;
END;
$$ LANGUAGE plpgsql;
2、执行自动扫表,全量更新序列
SELECT * FROM update_sequence_values();