CODEWITHSUNDEEP
postgresql
Clash
Clash

Reputation: 5025

Installing PostgreSQL Extension to all schemas

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

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?

Thanks in advance!

Upvotes: 58

Views: 60060

Answers (3)

user330315
user330315

Reputation:

CREATE EXTENSION unaccent; installs the extension into the public schema. To make it usable, simply include this to change the 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.

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

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 template1 database. You can even include the extensions schema in the default search_path by either editing postgresql.conf or using alter system

Upvotes: 103

cc young
cc young

Reputation: 20203

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

create extension unaccent schema pg_catalog;

This works!!

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

imho this is much better than schema.func() if the extension is global.

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

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

Upvotes: 25

Richard Huxton
Richard Huxton

Reputation: 22893

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.

Upvotes: 15

Related Questions