1

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"
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
eliangius
  • 145
  • 1
  • 6

3 Answers3

2

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:

Related:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • Thanks @ErwinBrandstetter I think your answer is complete but I must not be seeing something obvious..

    In my postgis scenario I am trying to set the spatial-reference identifier as a constant integer for all Geometry(Point, :SRID) columns in my schema. Defining as \set SRID 3347 for psql interpolation should have worked, but gives syntax errors when the constant is referenced with & without quotes as :"SRID". Any ideas?

     – eliangius Dec 23 '22 at 21:23
  • @eliangius: The interpolation should work while you stay in the same psql session. (Without quotes for an integer literal.) But Geometry(Point, :SRID) does not look like a valid PostGis function call. – Erwin Brandstetter Dec 23 '22 at 22:06
  • SQL interpolation does not work inside strings (incl. dollar-quoted strings). You are aware of that? See: https://stackoverflow.com/a/37716568/939860 – Erwin Brandstetter Dec 23 '22 at 22:13
  • 1
    Calling the constant without quotes to get its value is the correct syntax. Turns out the problem was in same line comments I had when defining the constant like \set MY_CONSTANT 123 ; -- some comment does NOT work as intended because the whole line is treated as its value(s). – eliangius Dec 23 '22 at 22:35
  • @eliangius: Turns out to be related to the original problem: confusing different scopes. Terminating a command with semicolon and the appended comment work in SQL (or PL/pgSQL), but not for psql meta-commands. – Erwin Brandstetter Dec 23 '22 at 22:43
  • Fair enough. If there's a PL/pgSQL clean way to reference a custom constant without having to do current_setting('custom.MY_CONSTANT')::INTEGER love to know of it as that complexity didn't seem to work for type modifiers like postgis' Geometry() wanting simple constants. – eliangius Dec 23 '22 at 23:00
  • You can declare any number of variables in a PL/pgSQL code block. Local to the same code block of course. You could fetch the value in the DECLARE section from a "customized option" into such a variable once ... Ask a new question if you need more. Comments are not the place ... – Erwin Brandstetter Dec 23 '22 at 23:07
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:

Sahap Asci
  • 2,889
  • 1
  • 14
  • 26
1

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.

Kamal
  • 140
  • 4