Create constant string for entire database

Question

I'm still new to SQL, so I'm having some little issues to solve. I'm running a Postgres database in Acqua Data Studio, with some queries that get follow the same model.
Some variables into these queries are the same, but may change in the future...

Thinking of an optimized database, it would be faster to change the value of a constant than to enter on 20+ queries and change the same aspect in all of them.

Example:

SELECT *
FROM Table AS Default_Configs      
    LEFT JOIN Table AS Test_Configs
        ON Default_Configs.Column1 = 'BLABLABLA'

Imagining 'BLABLABLA' could be 'XXX', how could I make 'BLABLABLA' a constant to every View that is created following this pattern?

Answer 1

Create a tiny function that serves as "global constant":

CREATE OR REPLACE FUNCTION f_my_constant()
  RETURNS text AS
$$SELECT text 'XXX'$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; -- see below

And use that function instead of 'BLABLABLA' in your queries.

Be sure to declare the data type correctly and make the function IMMUTABLE (because it is) for better performance with big queries.

In Postgres 9.6 or later add PARALLEL SAFE, so it won't block parallel query plans. The setting isn't valid in older versions.

To change the constant, replace the function by running an updated CREATE OR REPLACE FUNCTION statement. Invalidates query plans using it automatically, so queries are re-planned. Should be safe for concurrent use. Transactions starting after the change use the new function. But indexes involving the function have to be rebuilt manually.

---

Alternatively (especially in pg 9.2 or later), you could set a Customized Option as "global constant" for the whole cluster, a given DB, a given role etc, and retrieve the value with:

current_setting('constant.blabla')

One limitation: the value is always text and may have to be cast to a target type.

Related:

• User defined variables in PostgreSQL

Many ways to set it:

• How does the search_path influence identifier resolution and the "current schema"