将 PostgreSQL 扩展安装到所有模式

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/12986368/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-10 23:47:35  来源:igfitidea点击:

Installing PostgreSQL Extension to all schemas

postgresql

提问by Clash

I'm on PostgresQL 9.1.1 trying to have the extension unaccentavailable on all schemas.

我在 PostgresQL 9.1.1 上试图在所有模式上都使用unaccent扩展。

So I ran the command CREATE EXTENSION unaccent;. Which works, but only for the current schema set on search_path. So this means if I change the search_path, I no longer can call unaccent. How do I make this extension available to all schemas in a particular database?

所以我运行了命令CREATE EXTENSION unaccent;。哪个有效,但仅适用于search_path. 所以这意味着如果我更改 search_path,我将无法再调用unaccent. 如何使此扩展可用于特定数据库中的所有模式?

Thanks in advance!

提前致谢!

回答by a_horse_with_no_name

The accepted answer is a badadvice. Do notinstall extensions into the pg_catalogschema.

接受的答案是一个糟糕的建议。不要安装扩展到pg_catalog架构。

CREATE EXTENSION unaccent;installs the extension into the public schema. To make it usably, simply include that when changing the search_path:

CREATE EXTENSION unaccent;将扩展安装到公共架构中。为了使它可用,只需在更改 search_path 时包含它:

set search_path = my_schema, public;

Or better create a schema to contain all extensions, then always append that schema to the search_path.

或者更好地创建一个包含所有扩展的架构,然后始终将该架构附加到 search_path。

create schema extensions;

-- make sure everybody can use everything in the extensions schema
grant usage on schema extensions to public;
grant execute on all functions in schema extensions to public;

-- include future extensions
alter default privileges in schema extensions
   grant execute on functions to public;

alter default privileges in schema extensions
   grant usage on types to public;

Now install the extension:

现在安装扩展:

create extension unaccent schema extensions;

Then use include that schema in the search_path

然后在 search_path 中使用包含该架构

set search_path = my_schema, extensions;


If you don't want to repeat the above for every new database you create, run the above steps while being connected to the template1database. You can even include the extensions schema in the default search_path by either editing postgresql.confor using alter system

如果您不想为您创建的每个新数据库重复上述操作,请在连接到template1数据库时运行上述步骤。您甚至可以通过编辑postgresql.conf或使用在默认 search_path 中包含扩展架构alter system

回答by cc young

Had same question, but @Richard Huxton answer led to correct solution:

有同样的问题,但@Richard Huxton 的回答导致了正确的解决方案:

create extension unaccent schema pg_catalog;

This works!!

这有效!!

As Richard said, pg_catalogis automatically added (silently) to each search_path. Extensions added there will be found.

正如理查德所说,pg_catalog会自动(悄悄地)添加到每个search_path. 将找到在那里添加的扩展。

imho this is muchbetter than schema.func()ifthe extension is global.

恕我直言,这是很多优于schema.func()如果扩展是全球性的。

For example, I use a lot of schemae. I use the schema PUBLICfor debugging - everything should be in its own schema. If something is in PUBLIC, it's wrong.

例如,我使用了很多模式。我使用模式PUBLIC进行调试 - 一切都应该在它自己的模式中。如果某些东西在 PUBLIC 中,那就是错误的。

Creating the extension in pg_catalogkeeps all the schema clean, and lets the schema itself work as if it were part of the core postgres.

在 in 中创建扩展pg_catalog保持所有模式清洁,并让模式本身工作,就好像它是核心 postgres 的一部分一样。

回答by Richard Huxton

You don't. You can always call it fully qualified if you want to.

你没有。如果您愿意,您可以随时称其为完全合格的。

SELECT <schema>.<function>(...)

In fact, I believe the only reason the built-in functions are always available is that PG adds pg_catalog to the end of your search_path no matter what you do.

事实上,我认为内置函数始终可用的唯一原因是,无论您做什么,PG 都会在您的 search_path 末尾添加 pg_catalog。