PostgreSQL自定义create_and_insert函数执行后未生成预期表排查

• 核心错误1：建表语句误用PERFORM而非EXECUTE
PERFORM关键字在plpgsql中仅用于执行语句并丢弃返回结果，不会实际执行动态SQL完成建表操作，你注释掉的EXECUTE才是正确用法。
• 错误2：表名生成逻辑错误
  你用了regexp_split_to_table（字符串拆分为多行的函数）处理单个分类输入，会导致表名生成异常，且特殊字符替换逻辑过于冗余，不符合需求要求的特殊字符转下划线规则。
• 错误3：动态SQL占位符误用
  查询条件中的Category是字符串值，你用了标识符占位符%I，应该改为文本值占位符%L，否则会把分类值当做列名处理导致语法错误，无法正确筛选数据。
• 错误4：索引创建语句直接拼接字符串存在SQL注入风险，也没有做标识符转义，遇到带特殊字符的表名时会执行失败。

1. 函数实现 #

DROP FUNCTION IF EXISTS public.create_and_insert(text);
CREATE OR REPLACE FUNCTION public.create_and_insert(
   cat_name text)
    RETURNS void
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    cat_table_name text;
BEGIN
    -- 特殊字符替换：所有非字母、数字、中文的字符替换为下划线，符合需求
    -- 示例中"Tall Tree"会自动转换为"Tall_Tree"，生成符合要求的表名
    SELECT CONCAT('Category_', regexp_replace(trim(cat_name), '[^a-zA-Z0-9\u4e00-\u9fa5]+', '_', 'g'), '_Table')
    INTO cat_table_name;

    -- 执行建表并插入数据，注意占位符的正确使用
    EXECUTE format(
        'CREATE TABLE %I AS
        SELECT * FROM Living_Things
        WHERE Category = %L', cat_table_name, cat_name
    );

    -- 最后创建索引，用format处理标识符避免注入风险
    EXECUTE format(
        'CREATE INDEX %I ON %I USING spgist (name)',
        cat_table_name || '_idx',
        cat_table_name
    );
END;
$BODY$;

2. 匿名块调用 #

DO
$$
DECLARE cat_name text;
BEGIN
FOR cat_name IN (SELECT DISTINCT Category FROM Living_Things) LOOP
    PERFORM public.create_and_insert(cat_name);
END LOOP;
END;
$$;

因为你的数据量是数百万行，可以在执行前调整maintenance_work_mem参数到合适大小（比如设置为1GB），加快索引创建速度；如果分类数量很多，也可以考虑并行执行不同分类的建表操作，进一步降低整体耗时。

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