SQL 永久设置 Postgresql 架构路径
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2875610/
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
Permanently Set Postgresql Schema Path
提问by Nakh
I need to set schema path in Postgres so that I don't every time specify schema dot table e.g. schema2.table.
Set schema path:
我需要在 Postgres 中设置模式路径，这样我就不会每次都指定模式点表，例如schema2.table. 设置架构路径：
SET SCHEMA PATH a,b,c
only seems to work for one query session on mac, after I close query window the path variable sets itself back to default.
似乎只适用于 mac 上的一个查询会话，在我关闭查询窗口后，路径变量将自身设置回默认值。
How can I make it permanent?
我怎样才能让它永久？
回答by Milen A. Radev
(And if you have no admin access to the server)
（如果您没有对服务器的管理员访问权限）
ALTER ROLE <your_login_role> SET search_path TO a,b,c;
Two important things to know about:
需要了解的两个重要事项：
- When a schema name is not simple, it needs to be wrapped in double quotes.
- The order in which you set default schemas
a, b, cmatters, as it is also the order in which the schemas will be looked up for tables. So if you have the same table name in more than one schema among the defaults, there will be no ambiguity, the server will always use the table from the first schema you specified for yoursearch_path.
- 当模式名不简单时，需要用双引号括起来。
- 设置默认模式
a, b, c的顺序很重要，因为这也是查找表的模式的顺序。因此，如果您在默认值中的多个架构中具有相同的表名，则不会有歧义，服务器将始终使用您为search_path.
回答by joshperry
You can set the default search_pathat the database level:
您可以search_path在数据库级别设置默认值：
ALTER DATABASE <database_name> SET search_path TO schema1,schema2;
Or at the user or role level:
或者在用户或角色级别：
ALTER ROLE <role_name> SET search_path TO schema1,schema2;
Or if you have a common default schema in all your databases you could set the system-wide default in the config file with the search_pathoption.
或者，如果您在所有数据库中都有一个通用的默认架构，您可以使用search_path选项在配置文件中设置系统范围的默认值。
When a database is created it is created by default from a hidden "template" database named template1, you could alter that database to specify a new default search path for all databases created in the future. You could also create another template database and use CREATE DATABASE <database_name> TEMPLATE <template_name>to create your databases.
创建数据库时，默认情况下，它是从名为template1的隐藏“模板”数据库创建的，您可以更改该数据库以为将来创建的所有数据库指定新的默认搜索路径。您还可以创建另一个模板数据库并用于CREATE DATABASE <database_name> TEMPLATE <template_name>创建您的数据库。
回答by Chris Johnson
Josh is correct but he left out one variation:
乔希是对的，但他遗漏了一个变体：
ALTER ROLE <role_name> IN DATABASE <db_name> SET search_path TO schema1,schema2;
Set the search path for the user, in one particular database.
在一个特定的数据库中为用户设置搜索路径。
