如何在PL/pgsql动态ALTER TABLE语句中处理双引号问题

单表重命名的具体语法 #

你要将myschema."mytable"重命名为不需要引号的myschema.mytable，用EXECUTE的标准写法如下：

EXECUTE format(
    'ALTER TABLE %I.%I RENAME TO %I',
    'myschema',
    'mytable',
    'mytable'
);

语法点解释（对应你困惑的几个概念） #

• %I是format()函数专用的标识符占位符，会自动对传入的标识符加必要的双引号，完全替代手动写quote_ident()的操作，既安全又避免拼接字符串出错。上面的语句执行时会自动拼接为ALTER TABLE "myschema"."mytable" RENAME TO "mytable"，刚好匹配你当前带双引号的表名，重命名后的表因为名称全小写，后续访问直接写myschema.mytable就不需要加引号了。
• $$是PostgreSQL的美元符引用标记，用来包裹函数体这类包含大量单引号的字符串，避免内部的单引号需要重复转义，和字符串本身的逻辑没有关系，只是简化书写的语法糖。
• 不需要手动给原表名加双引号，%I会自动处理，完全不用自己拼串处理引号逻辑。

批量处理全schema表和列的函数 #

你可以直接用下面的函数批量处理指定schema下所有的表名和列名：

CREATE OR REPLACE FUNCTION remove_quote_from_identifiers(target_schema text)
RETURNS void AS $$
DECLARE
    rec record;
BEGIN
    -- 批量处理表名
    FOR rec IN 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = target_schema
        AND table_type = 'BASE TABLE'
    LOOP
        EXECUTE format(
            'ALTER TABLE %I.%I RENAME TO %I',
            target_schema,
            rec.table_name,
            lower(rec.table_name)
        );
    END LOOP;

    -- 批量处理列名
    FOR rec IN
        SELECT 
            t.table_name,
            c.column_name
        FROM information_schema.columns c
        JOIN information_schema.tables t 
            ON c.table_schema = t.table_schema 
            AND c.table_name = t.table_name
        WHERE c.table_schema = target_schema
        AND t.table_type = 'BASE TABLE'
    LOOP
        EXECUTE format(
            'ALTER TABLE %I.%I RENAME COLUMN %I TO %I',
            target_schema,
            rec.table_name,
            rec.column_name,
            lower(rec.column_name)
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

函数使用方法：

-- 替换为你要处理的schema名称即可
SELECT remove_quote_from_identifiers('myschema');

注意事项 #

• 操作前务必全量备份数据库，确认备份可用后再执行修改
• 执行前先在测试环境验证修改后的所有业务查询可正常运行，确认没有业务代码强制使用带双引号的标识符访问
• 如果原标识符包含大写字母、特殊字符，需要先单独评估修改影响，再决定是否调整

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