Return to Revisions

4 of 7

typo

edited May 28, 2016 at 15:49

182.2k
28
457
620

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:

http://marxsoftware.blogspot.co.at/2015/08/postgresql-psql-variable-from-query.html

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:

How to create temporary function in Postgres

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 wit 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.