Is it possible to define global variables in postg

问题:

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

I can define these in a table codes_table(code_name::text, code_value::integer) and use them in functions as follows

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

Is there another way for this. Maybe global variables?

回答1:

Postgres does not have global variables. However you can define custom configuration parameters. To keep things clear define your own parameters with a given prefix, say glb.

This simple function will make it easier to place the 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');

User-defined parameters are local in the session, therefore the parameters should be defined at the beginning of each session.

回答2:

Building on @klin's answer, there are a couple of ways to persist a configuration parameter beyond the current session. Note that these require superuser privieges.

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

ALTER DATABASE db SET abc.xyz = 1;

You can also set a server-wide value using the ALTER SYSTEM command, added in 9.4. It only seems to work for user-defined parameters if they have already been SET in your current session. Note also that this requires a configuration reload to take effect.

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

Pre-9.4, you can accomplish the same thing by adding the parameter to your server's postgresql.conf file. In 9.1 and earlier, you also need to register a custom variable class.

回答3:

You can use a trick and declare your variables as a 1-row CTE, which you then CROSS JOIN to the rest. See 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;

回答4:

Postgresql does not support global variables on the DB level. 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 may be the values, consider JSON to be the type for value, but then deserialization code is required for each type.