是否可以在 postgresql 中定义全局变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31316053/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to define global variables in postgresql
提问by Nuri Tasdemir
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.
我使用的是 postgresql 9.4，在编写函数时我想使用自定义的 error_codes (int)。但是，我可能想稍后更改确切的数值。
例如
-1 表示 USER_NOT_FOUND。
-2 表示 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?
有没有其他方法可以解决这个问题。也许是全局变量？
回答by Nick Barnes
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.
基于@klin 的回答，有几种方法可以在当前会话之外保留配置参数。请注意，这些需要超级用户权限。
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 SYSTEMcommand, added in 9.4. It only seems to work for user-defined parameters if they have already been SETin your current session. Note also that this requires a configuration reload to take effect.
您还可以使用ALTER SYSTEM9.4 中添加的命令设置服务器范围的值。如果用户定义的参数已经SET在您的当前会话中，它似乎只适用于用户定义的参数。另请注意，这需要重新加载配置才能生效。
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.conffile. In 9.1 and earlier, you also need to register a custom variable class.
在 9.4 之前，您可以通过将参数添加到服务器postgresql.conf文件来完成相同的操作。在 9.1 及更早版本中，您还需要注册一个自定义变量 class。
回答by klin
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.
Postgres 没有全局变量。但是，您可以定义自定义配置参数。为了清楚起见，请使用给定的前缀定义您自己的参数，例如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.
用户定义的参数在会话中是本地的，因此应该在每个会话开始时定义参数。
回答by Srinivas Rao
You can use this
你可以用这个
CREATE OR REPLACE FUNCTION globals.maxCities()
RETURNS integer AS
$$SELECT 100 $$ LANGUAGE sql IMMUTABLE;
.. and directly use globals.maxCities() in the code.
..并直接在代码中使用 globals.maxCities() 。
回答by Joe Samanek
You can use a trick and declare your variables as a 1-row CTE, which you then CROSS JOIN to the rest. See example:
您可以使用一个技巧并将您的变量声明为 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;
回答by AlexGordon
Postgresql does not support global variables on the DB level. Why not add it:
Postgresql 不支持数据库级别的全局变量。为什么不添加它：
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.
如果不同类型可能是值，则将 JSON 视为 的类型value，但每种类型都需要反序列化代码。
