How to refer to a value in a PostgreSQL script?

Question

I'd like to use the max value to feed the creation of a sequence. For instance, I am getting the max id value and I'd like to use this value:

DO $$
DECLARE   
    min_value Integer;
BEGIN
    SELECT MAX(tmp.id)+1 into min_value FROM tmp;
    -- raise notice 'Value: %', min_value;
    
    CREATE SEQUENCE id_seq_tmp
    INCREMENT 1
    START :tmp.min_value --Getting error: syntax error at or near ":"
    MINVALUE :tmp.min_value;
END $$;

How do I refer to the max value and pass to the sequence creation? I am using psql (PostgreSQL) 13.3.

Answer 1

The fundamental roadblock is that DDL commands ("utility statements") do not allow parameter substitution at all. You need to build the query string and execute it.

Generic SQL

I suggest format() for the task:

DO
$do$
BEGIN
   EXECUTE format('
   CREATE SEQUENCE id_seq_tmp
   INCREMENT 1
   START %1$s
   MINVALUE %1$s'
 , (SELECT max(tmp.id)+1 FROM tmp));
END
$do$;

The manual:

Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.

See:

• “ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql
• Creating user with password from variables in anonymous block

Operating from psql

While working from psql you can use \gexec to execute a the dynamically built DDL statement directly:

SELECT format('CREATE SEQUENCE id_seq_tmp INCREMENT 1 START %1$s MINVALUE %1$s', max(big_id)+1) FROM b2\gexec

See:

• How to pass variable to PL/pgSQL code from the command line?
• Filter column names from existing table for SQL DDL statement

(Either way, if the table tmp can be empty, you need to do more, as the SELECT query comes up empty.)