Create Sequence Using Expressions with psql Variables in PostgreSQL

ddldynamic-sqlpostgresqlpsqlsequence

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression.
The specific goal is to automate create-scripts for disjoint ranges between servers to avoid key-conflicts in a multi-master setup.

For example, using psql:

CREATE SEQUENCE key_seq MINVALUE (:servernum * :stride)
                        MAXVALUE ((:servernum + 1) * :stride - 1);

This gives me a syntax error. Variables are interpolated verbatim, no calculations happen.

An alternative would be to set a variable based on the output of an expression:

\set minvalue (:servernum * :stride)

But the \set statement doesn't evaluate expressions.

Best Answer

You need \set (not \pset!) to set psql variables. Unlike assignment in a Unix shell the psql assignment is not capable of arithmetic operations. You could use the psql command \! to execute shell commands, but I'll suggest two different approaches:

Option 1: Let Postgres calculate and set new variables with \gset

The manual about \gset:

Sends the current query input buffer to the server and stores the query's output into psql variables

Then you can interpolate the calculated numbers as text and everything works.
psql code:

test=# \set servernum 5
test=# \set stride 300
test=# SELECT :servernum * :stride AS minvalue, (:servernum + 1) * :stride - 1 AS maxvalue \gset
test=# CREATE SEQUENCE key_seq MINVALUE :minvalue MAXVALUE :maxvalue;
CREATE SEQUENCE

A nice blog explaining \gset:

I calculated and set :minvalue and :maxvalue in a single SELECT to optimize performance. Still, the downside of this approach is that you need an extra round trip to the Postgres server.

Option 2: Function with dynamic SQL for repeated use

You could use a DO statement, but (the manual again):

Variable interpolation will not be performed within quoted SQL literals and identifiers.

This would complicate string concatenation. I suggest to create a function and use format() for clean code. If it's for the current session only, you can make it a temporary function by schema-qualifying with pg_temp. So the function is only visible to your current session and dropped at the end of the session. Temporary functions are an undocumented feature - even though suggested by Tom Lane. Details:

So:

test=# CREATE FUNCTION pg_temp.f_my_seq(_seq text, _servernum int, _stride int)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s'
                 , _seq, _servernum * _stride, (_servernum + 1) * _stride - 1);
END
$func$ LANGUAGE plpgsql;

test=# \set servernum 5
test=# \set stride 300

test=# SELECT pg_temp.f_my_seq('key_seq', :servernum, :stride);

We need dynamic SQL with EXECUTE. The manual about Variable Substitution:

Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE commands, because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the utility statement as a string and EXECUTE it.

Related answer on SO:

Related Question