在不同的模式中创建过程，不需要模式名，也不需要修改搜索路径

Question

我有一个过程列表，其中25%的过程需要在特定的模式中编译，其余的过程也需要在特定的模式中编译，总共有5个模式。

任何过程都不能存在于一个以上的模式中。

在创建/编译时，我不能在过程名之前使用模式前缀。过程可以具有来自不同模式的数据库对象。到目前为止，每次编译过程(基于它需要驻留的模式)时，我都需要更改search_path。

是否有更简单的替代方法?

Answer 1

使函数依赖于当前会话中的search_path听起来很脆弱。

如何使用动态SQL?

CREATE PROCEDURE do_something(p_schema text)
LANGUAGE plpgsql AS
$$DECLARE
old_search_path text;
BEGIN
old_search_path := current_setting('search_path');
EXECUTE format('SET LOCAL search_path = %I', p_schema);
/* so something */
EXECUTE format('SET LOCAL search_path = %s', old_search_path);
END;$$;