Clever Geek Handbook

Is it possible to define global variables in postgresql

I am using postgresql 9.4 and I want to use self-defined error_codes (int) when writing functions. However, I can change the exact numeric values ​​later .
For example,
-1 means USER_NOT_FOUND.
-2 means USER_DOES_NOT_HAVE_PERMISSION.

I can define them in codes_table (code_name :: text, code_value :: integer) and use them in the following functions

(SELECT codes_table.code_value FROM codes_table WHERE codes_table.code_name = 'USER_NOT_FOUND')

Is there any other way for this. Maybe global variables?

+6


source to share


4 answers


Postgres has no globals. However, you can define configurable configuration options. To make things clear, define your own parameters with a given prefix, say glb

.

This simple function will make it easier to place a parameter in queries:



create or replace function glb(code text)
returns integer language sql as $$
    select current_setting('glb.' || code)::integer;
$$;

set glb.user_not_found to -1;
set glb.user_does_not_have_permission to -2;

select glb('user_not_found'), glb('user_does_not_have_permission');

Custom parameters are local to the session, so parameters must be defined at the beginning of each session.

+4


source


Building on @klin's answer , there are several ways to keep a config setting outside of the current session. Note that this requires superuser privileges.

To set a value for all connections to a specific database:

ALTER DATABASE db SET abc.xyz = 1;


You can also set the value server-wide with a command ALTER SYSTEM

added in 9.4. It only seems to work for custom parameters if they were already SET

in your current session. Also note that this requires a configuration reload.

SET abc.xyz = 1;
ALTER SYSTEM SET abc.xyz = 1;
SELECT pg_reload_conf();

Pre-9.4, you can do the same by adding a parameter to your server postgresql.conf

. In 9.1 and earlier, you also need to register a custom variable class .

+4


source


You can use the trick and declare your variables as a 1-row CTE, which you then cross with the rest. Example:

WITH
variables AS (
    SELECT 'value1'::TEXT AS var1, 10::INT AS var2
)
SELECT t.*, v.*
FROM
    my_table AS t
    CROSS JOIN variables AS v
WHERE t.random_int_column = var2;
0


source


Postgresql does not support database-level global variables. Why not add it:

CREATE TABLE global_variables (
  key text not null PRIMARY KEY
  value text
);

INSERT INTO global_variables (key, value) VALUES ('error_code_for_spaceship_engine', '404');

If different types can be values, consider JSON as a type value

, but each type requires deserialization code.

0


source






More articles:


All Articles