如何在PostgreSQL的execute format()中参数化带参数的列类型？

问题场景 #

现有如下PL/pgSQL代码：

DECLARE
    v_schema_name pg_catalog.pg_namespace.nspname%type := 'my_schema';
    v_table_name pg_catalog.pg_tables.tablename%type := 'my_table';
    v_column_name pg_catalog.pg_attribute.attname%type := 'my_column';
    v_new_type TEXT := 'DECIMAL(16, 12)';
BEGIN
    -- Omitted other code using v_new_type
    EXECUTE format(
        'ALTER TABLE %I.%I ALTER COLUMN %I TYPE %I',
        v_schema_name,
        v_table_name,
        v_column_name,
        v_new_type
    );
END;

执行后报错：

ERROR: type "DECIMAL(16, 12)" does not exist

尝试将格式符改为%L后，又出现新错误：

ERROR: syntax error at or near "'DECIMAL(16, 12)'"

疑问：该如何参数化这个查询？是否需要拆分成三部分处理？

补充说明：

• decimal与numeric类型等价。
• 可通过select * from pg_catalog.pg_type where typname = 'numeric';找到numeric类型，因此v_new_type pg_catalog.pg_type.typname%type := 'decimal';应该可用。

解决方法 #

问题根源 #

• 使用%I时，PostgreSQL会把DECIMAL(16, 12)整个当作一个标识符加双引号，但PostgreSQL里不存在名为"DECIMAL(16, 12)"的类型，正确的带精度的类型语法是DECIMAL(16,12)，类型名和精度是分离的。
• 使用%L会给字符串加上单引号，而类型名不需要单引号，因此导致语法错误。

方案1：拆分类型名、精度和刻度参数（推荐，更安全） #

将完整的类型字符串拆分为类型名、精度、刻度三个独立参数，分别处理：

DECLARE
    v_schema_name pg_catalog.pg_namespace.nspname%type := 'my_schema';
    v_table_name pg_catalog.pg_tables.tablename%type := 'my_table';
    v_column_name pg_catalog.pg_attribute.attname%type := 'my_column';
    v_type_name pg_catalog.pg_type.typname%type := 'decimal'; -- 或用'numeric'，两者等价
    v_precision INT := 16;
    v_scale INT := 12;
BEGIN
    EXECUTE format(
        'ALTER TABLE %I.%I ALTER COLUMN %I TYPE %I(%s, %s)',
        v_schema_name,
        v_table_name,
        v_column_name,
        v_type_name,
        v_precision,
        v_scale
    );
END;

• v_type_name用%I确保类型名作为标识符被正确转义。
• 精度和刻度是数值，直接用%s拼接即可，无需额外转义。

方案2：直接拼接完整类型字符串（适合固定类型场景） #

如果v_new_type是可信的固定值（非用户输入，无SQL注入风险），可以直接用%s代替%I或%L：

DECLARE
    v_schema_name pg_catalog.pg_namespace.nspname%type := 'my_schema';
    v_table_name pg_catalog.pg_tables.tablename%type := 'my_table';
    v_column_name pg_catalog.pg_attribute.attname%type := 'my_column';
    v_new_type TEXT := 'DECIMAL(16, 12)';
BEGIN
    EXECUTE format(
        'ALTER TABLE %I.%I ALTER COLUMN %I TYPE %s',
        v_schema_name,
        v_table_name,
        v_column_name,
        v_new_type
    );
END;

注意：如果v_new_type来自不可信输入，此方法存在SQL注入风险，优先选择方案1。

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