SQL 有没有办法在 PostgreSQL 查询中定义命名常量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13316773/
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 there a way to define a named constant in a PostgreSQL query?
提问by Ajedi32
Is there a way to define a named constant in a PostgreSQL query? For example:
有没有办法在 PostgreSQL 查询中定义命名常量？例如：
MY_ID = 5;
SELECT * FROM users WHERE id = MY_ID;
采纳答案by Gordon Linoff
This question has been asked before (How do you use script variables in PostgreSQL?). However, there is a trick that I use for queries sometimes:
之前已经问过这个问题（如何在 PostgreSQL 中使用脚本变量？）。但是，我有时会使用一个技巧来进行查询：
with const as (
select 1 as val
)
select . . .
from const cross join
<more tables>
That is, I define a CTE called const that has the constants defined there. I can then cross join this into my query, any number of times at any level. I have found this particularly useful when I'm dealing with dates, and need to handle date constants across many subqueries.
也就是说，我定义了一个名为 const 的 CTE，其中定义了常量。然后我可以在任何级别将其交叉加入我的查询中，任意次数。我发现这在我处理日期时特别有用，并且需要跨多个子查询处理日期常量。
回答by Erwin Brandstetter
PostgreSQL has no built-in way to define (global) variables like MySQL or Oracle. (There is a limited workaround using "customized options"). Depending on what you want exactly there are other ways:
PostgreSQL 没有像 MySQL 或 Oracle 那样定义（全局）变量的内置方法。（使用“自定义选项”的解决方法有限）。根据您的需求，还有其他方法：
For onequery
对于一个查询
You can provide values at the top of a query in a CTElike @Gordon already provided.
您可以在CTE中的查询顶部提供值，例如@Gordon already provided。
Global, persistent constant:
全局的、持久的常量：
You could create a simple IMMUTABLEfunction for that:
您可以IMMUTABLE为此创建一个简单的函数：
CREATE FUNCTION public.f_myid()
RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT 5';
(Parallel safetysettings only apply to Postgres 9.6 or later.)
（并行安全设置仅适用于 Postgres 9.6 或更高版本。）
It has to live in a schema that is visibleto the current user, i.e. is in the respective search_path. Like the schema public, by default. If security is an issue, make sure it's the first schema in the search_pathor schema-qualify it in your call:
它必须存在于当前用户可见的模式中，即位于相应的search_path. 像 schema 一样public，默认情况下。如果安全性是一个问题，请确保它是search_path调用中的第一个架构或架构限定它：
SELECT public.f_myid();
Visible for all users in the database (that are allowed to access schema public).
对数据库中的所有用户可见（允许访问 schema public）。
Multiple values for current session:
当前会话的多个值：
CREATE TEMP TABLE val (val_id int PRIMARY KEY, val text);
INSERT INTO val(val_id, val) VALUES
( 1, 'foo')
, ( 2, 'bar')
, (317, 'baz');
CREATE FUNCTION f_val(_id int)
RETURNS text LANGUAGE sql STABLE PARALLEL RESTRICTED AS
'SELECT val FROM val WHERE val_id = ';
SELECT f_val(2); -- returns 'baz'
Since plpgsql checks the existence of a table on creation, you need to create a (temporary) table valbefore you can create the function - even if a temp table is dropped at the end of the session while the function persists. The function will raise an exception if the underlying table is not found at call time.
由于 plpgsql 在创建时检查表的存在，因此您需要先创建一个（临时）表，val然后才能创建函数 - 即使在会话结束时删除了临时表，而函数仍然存在。如果在调用时未找到基础表，该函数将引发异常。
The current schema for temporary objects comes before the rest of your search_pathper default - if not instructed otherwise explicitly. You cannot excludethe temporary schema from the search_path, but you can put other schemas first.
Evil creatures of the night (with the necessary privileges) might tinker with the search_pathand put another object of the same name in front:
临时对象的当前模式出现在search_path默认情况下的其余模式之前- 如果没有明确指示。您不能从 中排除临时架构search_path，但您可以将其他架构放在首位。
夜晚的邪恶生物（具有必要的特权）可能会修改search_path并将另一个同名对象放在前面：
CREATE TABLE myschema.val (val_id int PRIMARY KEY, val text);
INSERT INTO val(val_id, val) VALUES (2, 'wrong');
SET search_path = myschema, pg_temp;
SELECT f_val(2); -- returns 'wrong'
It's not much of a threat, since only privileged users can alter global settings. Other users can only do it for their own session. Consider the related chapter of manual on creating functions with SECURITY DEFINER.
这不是什么大威胁，因为只有特权用户才能更改全局设置。其他用户只能为自己的会话执行此操作。考虑使用SECURITY DEFINER.
A hard-wired schema is typically simpler and faster:
硬接线模式通常更简单、更快：
CREATE FUNCTION f_val(_id int)
RETURNS text LANGUAGE sql STABLE PARALLEL RESTRICTED AS
'SELECT val FROM pg_temp.val WHERE val_id = ';Related answers with more options:
具有更多选项的相关答案：
回答by Craig Ringer
In addition to the sensible options Gordon and Erwin already mentioned (temp tables, constant-returning functions, CTEs, etc), you can also (ab)use the PostgreSQL GUC mechanism to create global-, session- and transaction-level variables.
除了 Gordon 和 Erwin 已经提到的明智选项（临时表、常量返回函数、CTE 等），您还可以（ab）使用 PostgreSQL GUC 机制来创建全局、会话和事务级变量。
See this prior postwhich shows the approach in detail.
请参阅此之前的帖子，其中详细显示了该方法。
I don't recommend this for general use, but it could be useful in narrow cases like the one mentioned in the linked question, where the poster wanted a way to provide the application-level username to triggers and functions.
我不建议将其用于一般用途，但在链接问题中提到的狭窄情况下可能很有用，在这种情况下，发布者想要一种为触发器和函数提供应用程序级用户名的方法。
回答by Brev
I've found a mixture of the available approaches to be best:
我发现混合可用的方法是最好的：
- Store your variables in a table:
- 将变量存储在表中：
CREATE TABLE vars (
id INT NOT NULL PRIMARY KEY DEFAULT 1,
zipcode INT NOT NULL DEFAULT 90210,
-- etc..
CHECK (id = 1)
);
- Create a dynamic function, which loads the contents of your table, and uses it to:
- Re/Create another separate static immutable getter function.
- 创建一个动态函数，该函数加载表的内容，并将其用于：
- 重新/创建另一个单独的静态不可变 getter 函数。
CREATE FUNCTION generate_var_getter()
RETURNS VOID AS $$
DECLARE
var_name TEXT;
var_value TEXT;
new_rows TEXT[];
new_sql TEXT;
BEGIN
FOR var_name IN (
SELECT columns.column_name
FROM information_schema.columns
WHERE columns.table_schema = 'public'
AND columns.table_name = 'vars'
ORDER BY columns.ordinal_position ASC
) LOOP
EXECUTE
FORMAT('SELECT %I FROM vars LIMIT 1', var_name)
INTO var_value;
new_rows := ARRAY_APPEND(
new_rows,
FORMAT('(''%s'', %s)', var_name, var_value)
);
END LOOP;
new_sql := FORMAT($sql$
CREATE OR REPLACE FUNCTION var_get(key_in TEXT)
RETURNS TEXT AS $config$
DECLARE
result NUMERIC;
BEGIN
result := (
SELECT value FROM (VALUES %s)
AS vars_tmp (key, value)
WHERE key = key_in
);
RETURN result;
END;
$config$ LANGUAGE plpgsql IMMUTABLE;
$sql$, ARRAY_TO_STRING(new_rows, ','));
EXECUTE new_sql;
RETURN;
END;
$$ LANGUAGE plpgsql;
- Add an update trigger to your table, so that after you change one of your variables,
generate_var_getter()is called, and the immutablevar_get()function is recreated.
- 向您的表中添加一个更新触发器，以便在您更改其中一个变量后
generate_var_getter()调用，并var_get()重新创建不可变函数。
CREATE FUNCTION vars_regenerate_update()
RETURNS TRIGGER AS $$
BEGIN
PERFORM generate_var_getter();
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_vars_regenerate_change
AFTER INSERT OR UPDATE ON vars
EXECUTE FUNCTION vars_regenerate_update();
Now you can easily keep your variables in a table, but also get blazing-fast immutable access to them. The best of both worlds:
现在，您可以轻松地将变量保存在表中，而且还可以快速访问它们。两全其美的：
INSERT INTO vars DEFAULT VALUES;
-- INSERT 0 1
SELECT var_get('zipcode')::INT;
-- 90210
UPDATE vars SET zipcode = 84111;
-- UPDATE 1
SELECT var_get('zipcode')::INT;
-- 84111
回答by Alexander Kondaurov
I've found this solution:
我找到了这个解决方案：
with vars as (
SELECT * FROM (values(5)) as t(MY_ID)
)
SELECT * FROM users WHERE id = (SELECT MY_ID FROM vars)
