Set custom constant in Postgres script

Question

In Postgres 15, how can we define constants that pertain to business logic rather than db configuration settings? I'd like to define them within SQL scripts rather than passed through the psql -v MY_CONSTANT=123 command & I don't care if they persist only for the session.

In prior versions I quietly got away with the following syntax:

SET MY_CONSTANT TO 123;     -- declared
CALL my_func(:MY_CONSTANT); -- used

Which from the documentation seems to be intended for modifying known db config settings & explains why I am encountering issues like this:

my_script.sql:2: ERROR:  unrecognized configuration parameter "my_constant"

Answer 1

There are three distinct contexts that might pertain to your question:

• Plain SQL in a Postgres session
• PL/pgSQL inside a code block of a function, procedure or DO command
• The default interactive terminal psql

The answer for each scope is different. Seems like you are mixing at least two of those.

SET MY_CONSTANT TO 123; -- declared

SET is an SQL command. You can use it to set "customized options". Must contain a dot - as opposed to Postgres settings (GUCs). The manual:

Custom options have two-part names: an extension name, then a dot, then the parameter name proper, much like qualified names in SQL. An example is plpgsql.variable_conflict.

And you retrieve it with SHOW or current_setting():

SELECT current_setting('custom.my_constant');

Only operates on type text though. You may need to cast.

CALL my_func(:MY_CONSTANT); -- used

That's the syntax of SQL interpolation in psql, which works for psql variables. See:

• How to pass parameters to sql script via psql \i command
• How can I quote a named argument passed in to psql?

Related:

• Difference between set, \set and \pset in psql
• Set a temporary global variable during a Postgres query
• Declaring variable for a whole script

Answer 2

If you want a "sql only" way then you can use prepared statements in SQL. Such;

PREPARE any_name (integer) AS SELECT my_func($1);
EXECUTE any_name (1); -- any constant

References:

• PREPARE
• EXECUTE

Answer 3

Try:

postgres=# \set my_constant 123
postgres=# SELECT my_func(:my_constant);
 my_func
---------
     123

Please note, this syntax only works if you run your scripts in psql. If you don't, try this workaround:

postgres=# CREATE TEMP TABLE my_vars AS SELECT 123 AS my_constant, 456 AS myother_constant;
SELECT 1
postgres=# SELECT * FROM my_vars;
 my_constant | myother_constant
-------------+------------------
         123 |              456
postgres=# SELECT my_func((SELECT my_constant FROM my_vars));
 my_func

 123

The temp table only exists for the current session, it'll be gone after logout.