postgresql search_path 如何影响标识符解析和“当前模式”

How does the search_path influence identifier resolution and the "current schema"

提问by thyandrecardoso

Is it possible to define in which schema new tables get created by default? (Referred by "unqualified table names".)

是否可以定义默认情况下在哪个模式中创建新表？（由“非限定表名”引用。）

I've seen some details about using the "search path" in Postgres, but I think it only works while retrieving data, not creating.

我已经看到了一些关于在 Postgres 中使用“搜索路径”的细节，但我认为它只在检索数据时有效，而不是在创建时有效。

I have a bunch of SQL scripts, which create many tables. Instead of modifying the scripts, I want to set the database create tables in a specific schema by default - when they have unqualified names.

我有一堆 SQL 脚本，它们创建了许多表。我不想修改脚本，而是希望在默认情况下在特定模式中设置数据库创建表 - 当它们具有不合格的名称时。

Is this possible?

这可能吗？

采纳答案by Alex Howansky

Search path is indeed what you want:

搜索路径确实是您想要的：

% create schema blarg;
% set search_path to blarg;
% create table foo (id int);
% \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 blarg  | foo  | table | pgsql

回答by Erwin Brandstetter

What is the schema search path search_path?

什么是模式搜索路径search_path？

The manual:

手册：

[...] tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in.

[...] 表通常由非限定名称引用，该名称仅由表名称组成。系统通过遵循搜索路径来确定哪个表是指要查找的模式列表。

Bold emphasis mine. This explains identifier resolution.

大胆强调我的。这解释了标识符解析。

The “current schema”(or “default schema”) is, per documentation:

根据文档，“当前架构”（或“默认架构”）是：

The first schema named in the search pathis called the current schema. Aside from being the first schema searched, it is also the schema in which new tables will be created if the CREATE TABLEcommand does not specify a schema name.

搜索路径中命名的第一个架构称为当前架构。除了作为搜索的第一个模式之外，如果CREATE TABLE命令未指定模式名称，它也是将在其中创建新表的模式。

Bold emphasis mine. The system schemas pg_temp(schema for temporary objects of the current session) and pg_catalogare automatically part of the search path and searched first, in this order. The manual:

大胆强调我的。系统模式pg_temp（当前会话的临时对象的模式）和pg_catalog自动成为搜索路径的一部分，并按此顺序首先搜索。手册：

pg_catalogis always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched beforesearching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly place pg_catalogat the end of your search path if you prefer to have user-defined names override built-in names.

pg_catalog始终是搜索路径的有效组成部分。如果它没有在路径中显式命名，那么在搜索路径的模式之前会隐式搜索它。这可确保始终可以找到内置名称。但是，pg_catalog如果您希望用户定义的名称覆盖内置名称，则可以明确地将其放置在搜索路径的末尾。

Bold emphasis as per original. And pg_tempcomes before that, unless it's put into a different position.

按照原文大胆强调。并且pg_temp在此之前，除非它被置于不同的位置。

How to set it?

如何设置？

There are various ways to set the runtime variable search_path.

有多种方法可以设置运行时变量search_path。

1. Set a cluster-wide default for all roles in all databases in postgresql.conf(and reload). Careful with that!

   search_path = 'blarg,public'
   

   The shipped default for this settingis:

   search_path = "$user",public
   

   The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored.

2. Set it as default for one database:

   ALTER DATABASE test SET search_path = blarg,public;
   

3. Set it as default for the roleyou connect with (effective cluster-wide):

   ALTER ROLE foo SET search_path = blarg,public;
   

4. Or even (often best!) as default for a role in a database:

   ALTER ROLE foo IN DATABASE test SET search_path = blarg,public;
   

5. Write the command at the top of your script. Or execute it in your DB session:

   SET search_path = blarg,public;
   

6. Set a specific search_pathfor the scope of a function(to be safe from malicious users with sufficient privileges). Read about Writing SECURITY DEFINERFunctions Safelyin the manual.

1. 为所有数据库中的所有角色设置集群范围的默认值postgresql.conf（并重新加载）。小心点！

   search_path = 'blarg,public'
   

   此设置的出厂默认值为：

   search_path = "$user",public
   

   第一个元素指定要搜索与当前用户同名的模式。如果不存在这样的模式，则忽略该条目。

2. 将其设置为一个数据库的默认值：

   ALTER DATABASE test SET search_path = blarg,public;
   

3. 将其设置为您连接的角色的默认值（在集群范围内有效）：

   ALTER ROLE foo SET search_path = blarg,public;
   

4. 甚至（通常是最好的！）作为数据库中角色的默认值：

   ALTER ROLE foo IN DATABASE test SET search_path = blarg,public;
   

5. 在脚本顶部写入命令。或者在您的数据库会话中执行它：

   SET search_path = blarg,public;
   

6. search_path为函数的范围设置一个特定的值（以防止具有足够权限的恶意用户）。阅读手册中的安全编写SECURITY DEFINER函数。

CREATE FUNCTION foo() RETURNS void AS
$func$
BEGIN
   -- do stuff
END
$func$ LANGUAGE plpgsql SECURITY DEFINER
       SET search_path=blarg,public,pg_temp;

Higher number in my list trumps lower number.
The manual has even more ways, like setting environment variables or using command-line options.

我列表中的数字越大，数字越小。
该手册有更多的方式，比如设置环境变量或使用命令行选项。

To see the current setting:

查看当前设置：

SHOW search_path;

To resetit:

要重置它：

RESET search_path;

The manual:

手册：

The default value is defined as the value that the parameter would have had, if no SEThad ever been issued for it in the current session.

默认值被定义为参数将具有的值，如果SET在当前会话中没有为它发出。