Search code examples
postgresqlglobal-variablespostgresql-9.4

Is it possible to define global variables in postgresql

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?

Solution

  • 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.