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 withselect * from pg_catalog.pg_type where typname = 'numeric';
, sov_new_type pg_catalog.pg_type.typname%type := 'decimal';
should be usable.
2
Answers
A type declaration is neither an identifier nor a literal. Use
%s
.type specification in Postgres can be very complex due ANSI SQL types like
DOUBLE PRECISION
orTIMESTAMP 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:Note – you can use %s like proposes @klin, and you can sanitize it by casting to regtype. But it requires correctly entered type name.