sql - 公共(public)架构未在安全定义器函数的搜索路径中定义，但仍可访问

Question

当我定义这样一个函数时:

create or replace function test_func_a()
returns table(search_path name[], public_func_result text) as $$
  select current_schemas('true'), trim(' does it work ');
$$ language sql stable security definer set search_path = pg_temp;
select test_func_a();
                test_func_a                
-------------------------------------------
 ("{pg_catalog,pg_temp_2}","does it work")

为什么我仍然可以在公共(public)架构中使用函数，例如 trim 或 current_schemas ？它不应该只在我明确使用 public.trim() 时才有效吗？

我创建的模式中的函数不会以相同的方式工作。在这个例子中，我尝试在 util 模式中使用一个函数，而不在 search_path 中设置它:

create or replace function test_func_b()
returns table(search_path name[], public_func_result text) as $$
  select current_schemas('true'), trim_whitespace(' does it work ');
$$ language sql stable security definer set search_path = pg_temp;
ERROR:  function trim_whitespace(unknown) does not exist
LINE 3:   select current_schemas('true'), trim_whitespace(' does it ...
                                          ^
HINT:  No function matches the given name and argument types. You might need 
to add explicit type casts.

在函数之外，我的 search_path 设置如下:

select current_schemas('true');
                      current_schemas                       
 ------------------------------------------------------------
 {pg_temp_2,pg_catalog,public,util}

Answer 1

查看您的 public 架构。你看到TRIM了吗？当前模式？不，他们不在那里。阅读此 https://www.postgresql.org/docs/current/static/ddl-schemas.html

关于公共(public)模式:

By default [...] tables (and other objects) are automatically put into a schema named "public". Every new database contains such a schema. [...] There is nothing special about the public schema except that it exists by default. It can be dropped, too.

那么它是如何解析 current_schemas 的？

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly place pg_catalog at the end of your search path if you prefer to have user-defined names override built-in names.

查看 pg_catalog(在 PGAdmin 中的 Catalogs 下)，current_schemas 在函数列表中。

而内置函数TRIM是PG识别的关键字，见https://www.postgresql.org/docs/current/static/sql-keywords-appendix.html