为整个数据库创建常量字符串 [英] Create constant string for entire database
问题描述
我还是SQL的新手，所以我要解决一些小问题。
我正在Acqua Data Studio中运行Postgres数据库，有些查询遵循相同的模型。
这些查询中的某些变量相同，但将来可能会更改。
I'm still new to SQL, so I'm having some little issues to solve.
I'm running a Postgres database in Acqua Data Studio, with some queries that get follow the same model.
Some variables into these queries are the same, but may change in the future...
考虑优化的数据库，更改常量的值比输入20多个查询并更改所有方面的相同方面要快。
Thinking of an optimized database, it would be faster to change the value of a constant than to enter on 20+ queries and change the same aspect in all of them.
示例：
SELECT *
FROM Table AS Default_Configs
LEFT JOIN Table AS Test_Configs
ON Default_Configs.Column1 = 'BLABLABLA'
想象 BLABLABLA可能是 XXX，我如何才能使 BLABLABLA对于按照该模式创建的每个视图都为常数？
Imagining 'BLABLABLA' could be 'XXX', how could I make 'BLABLABLA' a constant to every View that is created following this pattern?
推荐答案
创建一个充当全局常量的微型函数：
Create a tiny function that serves as "global constant":
CREATE OR REPLACE FUNCTION f_my_constant()
RETURNS text AS
$$SELECT text 'XXX'$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; -- see below
并使用该函数代替'BLABLABLA'。
请确保正确声明数据类型，并使用函数 IMMUTABLE （因为它是），以便在大型查询中获得更好的性能。
Be sure to declare the data type correctly and make the function IMMUTABLE (because it is) for better performance with big queries.
在Postgres 9.6或更高版本中，添加 PARALLEL SAFE ，因此它不会阻止并行查询计划。该设置在旧版本中无效。
In Postgres 9.6 or later add PARALLEL SAFE, so it won't block parallel query plans. The setting isn't valid in older versions.
要更改常量，请通过运行更新的 CREATE OR REPLACE FUNCTION 语句。使用它自动使查询计划无效，因此重新计划查询。应安全使用。更改后开始的事务使用新功能。但是涉及该函数的索引必须手动重建。
To change the constant, replace the function by running an updated CREATE OR REPLACE FUNCTION statement. Invalidates query plans using it automatically, so queries are re-planned. Should be safe for concurrent use. Transactions starting after the change use the new function. But indexes involving the function have to be rebuilt manually.
或者（尤其是在pg 9.2或更高版本中），您可以设置自定义选项作为整个集群的全局常量 ，给定的数据库，给定的角色等，并使用以下方式检索值：
Alternatively (especially in pg 9.2 or later), you could set a Customized Option as "global constant" for the whole cluster, a given DB, a given role etc, and retrieve the value with:
current_setting('constant.blabla')
一个限制：该值始终为 text 并可能
One limitation: the value is always text and may have to be cast to a target type.
相关：
- User defined variables in PostgreSQL
许多设置方式：
- How does the search_path influence identifier resolution and the "current schema"
这篇关于为整个数据库创建常量字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
