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.

"Postgresql script" is unclear. Do you mean the DO command (which is an SQL command) or a script for psql? — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 7, 2021 at 1:34

Erwin Brandstetter · Accepted Answer · 2021-08-07 01:31:57Z

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:

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:

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

Collectives™ on Stack Overflow

How to refer to a value in a PostgreSQL script?

1 Answer 1

Generic SQL

Operating from psql

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Generic SQL

Operating from psql

Comments

Linked

Related