是否可以在postgresql中定义全局变量?

Question

我使用的是postgresql 9.4，在编写函数时，我想使用自定义的error_codes (int)。但是我可能需要稍后更改确切的数值。
例如
-1表示USER_NOT_FOUND.
-2表示USER_DOES_NOT_HAVE_PERMISSION.

我可以在表codes_table(code_name::text, code_value::integer)中定义它们，并在函数中使用它们，如下所示

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

有别的方法吗?也许是全局变量?

Answer 1

Postgres没有全局变量。但是，您可以定义自定义配置参数。为了保持清晰，使用给定的前缀定义您自己的参数，例如glb。

这个简单的函数将使在查询中放置参数变得更容易:

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');

用户自定义参数在会话中是本地的，因此应该在每个会话开始时定义参数。

Answer 2

基于@klin的回答，有几种方法可以在当前会话之后持久化配置参数。注意，这些需要超级用户权限。

为指定数据库的所有连接设置一个值:

ALTER DATABASE db SET abc.xyz = 1;

您还可以使用9.4中添加的ALTER SYSTEM命令设置服务器范围的值。它似乎只适用于用户定义的参数，如果他们已经在您当前的会话SET。还要注意，这需要重新加载配置才能生效。

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

在9.4之前，您可以通过将参数添加到服务器的postgresql.conf文件来完成相同的事情。在9.1和更早的版本中，还需要注册一个自定义变量类。

Answer 3

你可以使用

CREATE OR REPLACE FUNCTION globals.maxCities()
  RETURNS integer AS
  $$SELECT 100 $$ LANGUAGE sql IMMUTABLE;

. .并直接在代码中使用global . maxcities ()

Answer 4

您可以使用一个技巧，将变量声明为1行CTE，然后将其交叉连接到其余部分。看到的例子:

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;

Answer 5

Postgresql不支持DB级别的全局变量。为什么不加呢:

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');

如果值可能是不同的类型，则考虑将JSON作为value的类型，但是每个类型都需要反序列化代码。

Answer 6

请注意，如果您使用数据库级别的设置，如@nick-barnes的答案，当用户连接到该数据库时，这些设置将被设置为会话默认值，但是可以为该会话(或事务)重写。

如果您希望强制执行数据库设置，您可以通过直接转到pg_db_role_setting系统目录来实现，就像我的pg_safer_settings扩展中的这个函数一样(在我的明确许可下复制到这里😉):

create function pg_db_setting(pg_setting_name$ text, pg_role$ regrole = 0)
    returns text
    stable
--    security definer
    return (
        select
            regexp_replace(expanded_settings.raw_setting, E'^[^=]+=', '')
        from
            pg_catalog.pg_db_role_setting
        inner join
            pg_catalog.pg_database
            on pg_database.oid = pg_db_role_setting.setdatabase
        cross join lateral
            unnest(pg_db_role_setting.setconfig) as expanded_settings(raw_setting)
        where
            pg_database.datname = current_database()
            and pg_db_role_setting.setrole = coalesce(
                pg_role$,
                0  -- 0 means “not role-specific”
            )
            and expanded_settings.raw_setting like pg_setting_name$ || '=%'
        limit 1
    );

这里有一个例子来说明pg_catalog.current_setting()函数和我的pg_db_role_setting()函数在语义上的区别:

CREATE DATABASE mydb;
CONNECT TO mydb
CREATE ROLE myrole;
ALTER DATABASE mydb
    SET app.settings.bla = 1::text;
ALTER ROLE myrole
    IN DATABASE mydb
    SET app.settings.bla = 2::text;
SET ROLE myrole;
SET app.settings.bla TO 3::text;
SELECT current_setting('app.settings.bla', true);  -- '3'
SELECT pg_db_role_setting('app.settings.bla');  -- '1'
SELECT pg_db_role_setting('app.settings.bla', current_user);  -- '2'

另一种常见的模式是将设置存储为IMMUTABLE函数或存储在表中。如果您将它们存储在表中，请帮我一个忙，将它们存储为列，而不是行。我的pg_safer_settings扩展有一个pg_safer_settings_table，它结合了这两个范例，允许您将设置存储为列，并自动为每个列维护current_<col_name>()函数。(current_前缀可配置)