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

cld4siwp 于 2023-04-05 发布在 PostgreSQL

关注(0)|答案(2)|浏览(167)

我有一些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;

这将导致以下错误：
错误：类型“DECIMAL（16，12）”不存在
我尝试将格式字符串的最后一部分改为%L，但导致以下错误：
错误：语法错误位于或接近“'DECIMAL（16，12）'”
我如何参数化这个查询？我需要把它分成三个部分吗？
更新：

"The types decimal and numeric are equivalent."
numeric类型可以在select * from pg_catalog.pg_type where typname = 'numeric';中找到，因此v_new_type pg_catalog.pg_type.typname%type := 'decimal';应该是可用的。

postgresql

来源：https://stackoverflow.com/questions/75893986/how-to-parameterize-parametric-column-type-in-postgresql-execute-format

2条答案

按热度按时间

3gtaxfhh1#

Postgres中的类型规范可能会非常复杂，因为ANSI SQL类型像DOUBLE PRECISION或TIMESTAMP WITH TIME ZONE。Postgres的解析器应该支持这些类型的解析，然后这种语法只允许用于内置类型。对于更常见的类型，类型规范有两个部分：标识符和可选的类型修饰符。所以你的代码应该看起来像：

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'; -- Postgres uses lowercase notation
    v_new_type_mod TEXT := '(16, 12)'
BEGIN
    -- Omitted other code using v_new_type
    EXECUTE format(
        'ALTER TABLE %I.%I ALTER COLUMN %I TYPE %I%s',
        v_schema_name,
        v_table_name,
        v_column_name,
        v_new_type,
        v_new_type_mod
    );
END;

注意-你可以像使用proposes @klin一样使用%s，并且你可以通过转换为regtype来清理它。但是它需要正确输入类型名称。

DECLARE
  ...
  v_new_type TEXT := 'decimal(16, 12)';
BEGIN
  -- sanitize data type
  PERFORM v_new_type::regtype;
  EXECUTE format(... TYPE %s',
     ...
     v_new_type);
END;

赞(0）回复(0）举报 2023-04-05

yzckvree2#

类型声明既不是标识符也不是文字。使用%s。

赞(0）回复(0）举报 2023-04-05