How to parameterize parametric column type in PostgreSQL `execute format()`?

Question

I've got some PL/pgSQL code like this:

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;

This results in the following error:

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

I tried changing the last part of the format string to %L instead, but that results in this error:

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

How do I parameterize this query? Do I need to break it into three parts or something?

Update:

• "The types decimal and numeric are equivalent."
• The numeric type can be found with select * from pg_catalog.pg_type where typname = 'numeric';, so v_new_type pg_catalog.pg_type.typname%type := 'decimal'; should be usable.

Answer 1

type specification in Postgres can be very complex due ANSI SQL types like DOUBLE PRECISION or TIMESTAMP WITH TIME ZONE. Parsing of these types should be supported by Postgres's parser, and then this syntax is allowed only for built-in types. For more common types the type specification has two parts: identifier and optional type modifier. So your code should to look like:

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;

Note - you can use %s like proposes @klin, and you can sanitize it by casting to regtype. But it requires correctly entered type name.

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;