Unable to use declared variable in DDL

Question

Please consider below simple code

DO $$
DECLARE
    _VARCHAR_SIZE int := 200;
BEGIN
    create table mytable(
        id int primary key,
        name varchar( _VARCHAR_SIZE )
    );
END $$;

On executing above code I am getting error

ERROR:  syntax error at or near "_VARCHAR_SIZE"
LINE 14:   name character varying(_VARCHAR_SIZE) NOT NULL,
                                  ^
SQL state: 42601
Character: 222

Why postgres is not recognizing declared variable in DDL

I don't want to use EXECUTE to make whole statement dynamic.

Answer 1

Internally Postgres knows two type of commands - commands (CREATE, ALTER, DROP, ..) and queries (SELECT, INSERT, UPDATE, DELETE). Every command has some special implementation. Queries are implemented together with steps: parsing, analyzing, optimization, execution. There is high probability so queries will be executed repeatedly. Commands usually are not executed more times. Then queries has special optimization for repeated execution - parametrized plan. The plan is sequence of steps of query execution. Usually it is same, but the parameters (variables) are different - You can search Pavel or Tomas, you can insert Pavel or Tomas. There is nothing similar for commands.

You can use plpgsql variables everywhere where is possible to use plan parameters. PlpgSQL runtime join it. And you cannot to use variables else where. DDL statements (commands has not plans), and then you cannot to use variables there. Only one alternative is dynamic sql - EXECUTE command in PLpgSQL.