函数
CREATE OR REPLACE FUNCTION public.reset_sequences_for_tables()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
table_name_text TEXT;
seq_name TEXT;
max_id INT;
default_value TEXT;
pk_column_name TEXT; -- 用于存储自增主键列名
BEGIN
-- 遍历所有表
FOR table_name_text IN
SELECT
t.table_name
FROM
information_schema.tables AS t
WHERE
t.table_schema = 'public'
AND EXISTS (
SELECT 1
FROM information_schema.columns AS c
WHERE
c.table_name = t.table_name
AND c.column_default ILIKE 'nextval%'
)
LOOP
-- 获取表的自增主键列名称
SELECT column_name
INTO pk_column_name
FROM information_schema.columns AS c
WHERE
c.table_name = table_name_text
AND c.column_default ILIKE 'nextval%';
-- 获取表的自增主键默认值
SELECT column_default
INTO default_value
FROM information_schema.columns AS c
WHERE
c.table_name = table_name_text
AND c.column_name = pk_column_name;
-- 从默认值中提取序列名称
seq_name := substring(default_value from E'\'(\\w+)\'::regclass');
-- 检查序列是否存在
IF EXISTS (SELECT 1 FROM pg_class WHERE relname = seq_name) THEN
-- 获取当前最大ID值
EXECUTE 'SELECT MAX(' || pk_column_name || ') FROM ' || table_name_text INTO max_id;
-- 打印日志信息
RAISE NOTICE 'Table: %, PK Column: %, Sequence: %, Max ID: %', table_name_text, pk_column_name, seq_name, max_id;
-- 如果 max_id 为 NULL，设置序列的当前值为1，但不增加当前值
IF max_id IS NULL THEN
EXECUTE 'SELECT setval(' || quote_literal(seq_name) || ', 1, false)';
RAISE NOTICE 'Set sequence % to 1 (no increment).', seq_name;
ELSE
-- 否则，设置序列的当前值为当前最大ID值
EXECUTE 'SELECT setval(' || COALESCE(quote_literal(seq_name), 'null') || ', ' || max_id || ')';
RAISE NOTICE 'Set sequence % to %.', seq_name, max_id;
END IF;
ELSE
-- 打印未找到序列的日志信息
RAISE NOTICE 'Sequence not found for table: %', table_name_text;
END IF;
END LOOP;
END;
$function$
;使用
select reset_sequences_for_tables()