给PG数据库已有表，已存在列添加序列并设置序列当前值为自增列的最大值

CREATE OR REPLACE FUNCTION "public"."add_sequence_to_table"("p_table_name" text, "p_column_name" text)
  RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
  max_value INTEGER;
  sequence_name text;
BEGIN
  -- 获取表的最大值
  EXECUTE format('SELECT COALESCE(MAX(%I), 0) FROM %I', p_column_name, p_table_name) INTO max_value;
  
  -- 检查是否已经存在同名的序列
  SELECT relname INTO sequence_name 
    FROM pg_class WHERE relname = p_table_name || '_' || p_column_name || '_seq';

  IF sequence_name IS NULL THEN
    -- 创建序列
    EXECUTE format('CREATE SEQUENCE %I', p_table_name || '_' || p_column_name || '_seq');
    
    -- 将序列的起始值设置为当前表的最大值
    EXECUTE format('ALTER SEQUENCE %I RESTART WITH %s', p_table_name || '_' || p_column_name || '_seq', (max_value + 1));
    
    -- 修改列的默认值为序列的下一个值
    EXECUTE format('ALTER TABLE %I ALTER COLUMN %I SET DEFAULT nextval(%L)', p_table_name, p_column_name, p_table_name || '_' || p_column_name || '_seq');
    
    RAISE NOTICE '成功将自增序列添加到表 % 的列 %，当前值已设置为 %', p_table_name, p_column_name, (max_value + 1);
  ELSE
    RAISE NOTICE '表 % 的列 % 已存在同名的序列', p_table_name, p_column_name;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100