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 $BODY$
DECLARE
table_rec RECORD;
max_id INTEGER;
sequence_name VARCHAR;
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 column_name= 'gid' AND table_schema = 'public' and column_default like 'nextval%') LOOP
EXECUTE format('SELECT MAX(%I) FROM %I.%I;', table_rec.column_name, table_rec.table_schema, table_rec.table_name) INTO max_id;
execute format('SELECT s.relname AS sequence_name,
n.nspname AS sequence_schema,
t.relname AS related_table,
a.attname AS related_column
FROM pg_class s, pg_depend d, pg_class t, pg_attribute a, pg_namespace n
WHERE s.relkind = ''S''
AND n.oid = s.relnamespace
AND d.objid = s.oid
AND d.refobjid = t.oid
AND (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) and t.relname = ''%I''', table_rec.table_name) INTO sequence_name;
IF max_id IS NOT NULL and sequence_name is not null THEN
EXECUTE format('alter sequence %s restart with %s', sequence_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;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
2、执行自动扫表,全量更新序列
SELECT * FROM update_sequence_values();