PostgreSQL调用ID_resync存储过程遇NULL值引发语法错误求助

我需要遍历public schema下所有包含id列的表，调用ID_resync存储过程重置id列的序列。但执行时发现l_current_max取值为NULL，导致生成的ALTER语句缺少数值，触发语法错误（错误码42601）。即使添加了NULL判断逻辑，仍需彻底解决该问题。

原存储过程与执行脚本 #

create or replace procedure ID_resync(table_name_in text, id_column_in text )
 language plpgsql
as $$
declare 
    k_select_max_id constant text = 'select max(%I) from %I'; 
       
    k_alter_key_seq constant text = 'alter table %I alter column %I restart with %s'; 
       
    l_current_max integer;   
    l_statement   text; 
   
begin 
    l_statement = format(k_select_max_id, id_column_in, table_name_in); 
    raise notice '%',E'GET CURRENT MAX:\n ' || l_statement; 
    execute l_statement into l_current_max; 
   
    l_current_max = l_current_max + 1;  -- set for new sequence
    l_statement = format(k_alter_key_seq, table_name_in, id_column_in, l_current_max);
    raise notice '%',E'SET NEW SEQUENCE:\n ' || l_statement; 
    execute l_statement;
   
    commit; 
     
end;
$$;


DO
$$
    DECLARE
        rec record;
    BEGIN
        FOR rec IN 
            SELECT table_name, column_name
              FROM information_schema.columns
             WHERE table_schema='public'
                AND column_name='id'
        LOOP
            raise notice 'name: %', rec.table_name;
            CALL ID_resync( rec.table_name ,'id');
 
        END LOOP;
    END;
$$
LANGUAGE plpgsql;

错误信息 #

SQL Error [42601]: ERROR: syntax error at end of input
  Where: PL/pgSQL function id_resync(text,text) line 20 at EXECUTE
SQL statement "CALL ID_resync( rec.table_name ,'id')"
PL/pgSQL function inline_code_block line 10 at CALL

执行日志 #

name: ad
GET CURRENT MAX:
 select max(id) from ad
VALUE OF l_current_max: <NULL>
SET NEW SEQUENCE:
 alter table ad alter column id restart with 

尝试的修改（未彻底解决） #

if l_current_max is not null 
        then
        raise notice 'VALUE OF l_current_max: %', l_current_max; 
        l_current_max = l_current_max + 1;
        l_statement = format(k_alter_key_seq, table_name_in, id_column_in, l_current_max);
        raise notice '%',E'SET NEW SEQUENCE:\n ' || l_statement; 
        execute l_statement;
        commit; 

    END IF;

问题根源与修复方案 #

问题原因 #

当表中没有数据时，max(id)会返回NULL，直接执行l_current_max = l_current_max + 1会导致NULL值传递，最终生成的ALTER语句缺少restart with后面的数值，触发语法错误。

修复后的存储过程 #

我们需要处理NULL的情况：当表为空时，将序列重置为1（默认起始值），否则用max(id)+1。修改后的代码如下：

create or replace procedure ID_resync(table_name_in text, id_column_in text )
 language plpgsql
as $$
declare 
    k_select_max_id constant text = 'select max(%I) from %I'; 
    k_alter_key_seq constant text = 'alter table %I alter column %I restart with %s'; 
    l_current_max integer;   
    l_statement   text; 
begin 
    l_statement = format(k_select_max_id, id_column_in, table_name_in); 
    raise notice '%',E'GET CURRENT MAX:\n ' || l_statement; 
    execute l_statement into l_current_max; 

    -- 处理NULL情况：空表时从1开始，否则用max(id)+1
    l_current_max = coalesce(l_current_max + 1, 1);
    
    l_statement = format(k_alter_key_seq, table_name_in, id_column_in, l_current_max);
    raise notice '%',E'SET NEW SEQUENCE:\n ' || l_statement; 
    execute l_statement;

    commit; 
end;
$$;

补充说明 #

• 使用coalesce(l_current_max + 1, 1)可以同时处理两种情况：如果l_current_max不为NULL，就执行+1；如果是NULL（表为空），则直接使用1作为起始值。
• 不需要额外的IF判断，coalesce函数可以简洁地解决NULL值问题。
• 确保所有包含id列的表（无论是否有数据）都能正确重置序列，避免语法错误。

内容的提问来源于stack exchange，提问作者Sam