PostgreSQL PL/pgSQL代码报错排查：统计空值行数插入表失败

核心错误原因 #

报错ERROR: syntax error at or near "null"源于以下几个语法和逻辑问题：

• SQL拼接缺少空格：直接拼接字符串时，tab.table_name后未加空格就接where，tab.column_name后未加空格接is null，生成的SQL会出现类似mytablewhere mycolumnis null的非法语法。
• 标识符与字符串未转义：直接拼接表名/列名时未做转义，若名称含特殊字符或关键字会报错；插入时表名/列名被当作标识符而非字符串值，缺少单引号包裹。
• PostgreSQL函数误用：PostgreSQL中没有SYSDATE函数，需用CURRENT_TIMESTAMP或CURRENT_DATE替代。
• 变量未复用：定义的l_schema未实际使用，原查询硬编码table_schema='public'，易引发不一致。

修正后的代码 #

DO $$
DECLARE
  tab RECORD;
  l_schema VARCHAR := 'public'; -- 改为实际要查询的schema，如需要可替换为'test'
  l_sql text;
  l1_sql text;
  RSE_ROW_COUNT int;
BEGIN
  FOR tab IN (
    SELECT table_name, column_name 
    FROM INFORMATION_SCHEMA.columns 
    WHERE table_schema = l_schema 
      AND is_nullable = 'NO' 
      AND data_type NOT IN ('integer')
      AND column_default IS NULL 
    ORDER BY table_name, column_name
  ) LOOP
    -- 用format的%I转义标识符，自动处理空格和特殊字符
    l_sql := format('SELECT COUNT(1) FROM %I WHERE %I IS NULL', tab.table_name, tab.column_name);
    RAISE NOTICE '%', l_sql;
    EXECUTE l_sql INTO RSE_ROW_COUNT;
    
    -- 用%L生成带单引号的字符串值，CURRENT_TIMESTAMP替换SYSDATE
    l1_sql := format(
      'INSERT INTO RSE_TABLE_COUNT (TABLE_NAME, Table_column_name, ROW_COUNT, DATE_LAST_UPDATED) 
       VALUES (%L, %L, %s, CURRENT_TIMESTAMP)',
      tab.table_name, tab.column_name, RSE_ROW_COUNT
    );
    RAISE NOTICE '%', l1_sql;
    EXECUTE l1_sql;
  END LOOP;
END
$$;

关键修正说明 #

1. 使用format函数规范SQL拼接：
   • %I：自动为表名/列名添加双引号，处理特殊字符和关键字，同时保证语法空格正确。
   • %L：自动为字符串值添加单引号，避免插入时的语法错误。
2. 替换日期函数：用PostgreSQL原生的CURRENT_TIMESTAMP替代Oracle风格的SYSDATE。
3. 复用l_schema变量：统一管理查询的schema，减少硬编码，提升代码可维护性。

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