9

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
ZeldaElf
  • 333
  • 1
  • 3
  • 9
  • Possible duplicate of [Is there a way to define a named constant in a PostgreSQL query?](http://stackoverflow.com/questions/13316773/is-there-a-way-to-define-a-named-constant-in-a-postgresql-query) – jtbandes Feb 06 '17 at 18:17

1 Answers1

19

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:

Many ways to set it:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank for the info! Instead of 'BLABLABLA' i would only use f_my_constant? – ZeldaElf Mar 26 '14 at 14:21
  • @ZeldaElf `f_my_constant()`, including the parens. – Erwin Brandstetter Mar 26 '14 at 14:22
  • 1
    I just wrote a microbenchmark to test the difference in performance between an immutable function and a single-row/single-column table -- I was very surprised that the table outperformed the function in multiple runs. I hope to write it up as a question here at SO but I would caution against the accepted wisdom. With functions you also have to change the value with DDL rather than DML, which has a couple of negative implications. – Doctor Eval Dec 05 '17 at 22:57
  • All true and to be expected. A function has more overhead than reading the value from a singleton table. The difference is tiny, though, especially if the function can be inlined. Since Postgres 9.2 a customized option would be another good alternative. I added pointers above. – Erwin Brandstetter Dec 06 '17 at 01:00