53

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!

Clash
  • 4,896
  • 11
  • 47
  • 67

3 Answers3

90

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

Denis Abakumov
  • 355
  • 3
  • 11
  • 1
    You are right. However, I personally do not use the PUBLIC schema, as I find it bad karma. If there's anything in the PUBLIC schema, it shouldn't be there, and there's a bug in my scripts. – cc young May 13 '17 at 12:23
  • 1
    @ccyoung: I didn't say anything about wether the public schema is "good" or "bad" I just included it, to be compatible with the default configuration. But the public schema is in absolutely no way special and there is no reason whatsoever to avoid it. If you think it's "bad karma" you have not understood how schemas work in Postgres. On the other hand, messing around with `pg_catalog` is a real no-go and a really bad advice –  May 13 '17 at 12:38
  • I think you're making assumptions. In large projects I use different schemas for different arms of the project. This helps teams from stepping on each others' toes and facilitates name space, eg, there might be two tables called parts: one in the inventory arm and one in the BOM arm. In development I have found it good practice to always specify the schema. In this practice the public schema is like a honeypot. Extensions tend use function names without the schema. – cc young May 15 '17 at 07:13
  • Why do you need to do the `grant … on all` for functions, but not on types - when is setting the default privileges enough? Also, should it be `all routines` (including procedures) instead of `all functions`? – Bergi Aug 29 '19 at 15:43
  • 1
    "The accepted answer is a bad advice. Do not install extensions into the pg_catalog schema." could you explain why?... – Alexandre Jan 21 '21 at 07:38
23

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.

cc young
  • 18,939
  • 31
  • 90
  • 148
  • 9
    I would strongly recommend creating a new `unaccent` schema and creating it in that, then adding that schema to your `search_path`. Cleaner to avoid messing with the system catalogs, and putting it in a new schema doesn't stop you having it on the search path. – Craig Ringer Apr 21 '13 at 09:25
  • @CraigRinger - I can appreciate what you're saying. Right now I usually use different schema for different clients using the same app or brother apps using same server. What you're suggesting is creating a schema solely for naming purposes, similar to modules in node.js. If this were done consistently, eg, `schema.fn()`, throughout the code, as in node.js, then there would be no need to add to the search path. I would be highly in favor of this approach. But since this is not done in the extensions themselves, it's just not happening. – cc young Apr 21 '13 at 13:14
  • 9
    This is a bad advice. –  May 12 '17 at 11:53
15

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.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • 4
    Important to note l: `pg_catalog` is added as ***first*** item of the `search_path` before any other schema (can be changed by explicitly mentioning `pg_catalog` in the `search_path`). Any other approach would be unwise: http://stackoverflow.com/questions/25068040/when-how-are-default-value-expression-functions-bound-with-regard-to-search-pa/25068681#25068681 The manual: http://www.postgresql.org/docs/current/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH – Erwin Brandstetter May 30 '15 at 02:00
  • For reader that is using this good estrategy to qualify as `.`... Remember that the syntax for operators is `operator(.)`, eg. the operator `@>` in *myschema* must be replaced by `operator(myschema.@>)`. – Peter Krauss Feb 19 '18 at 22:56
  • I do both. I have all my extensions in an `extensions` schema as @a_horse_with_no_name shows AND I call them with `extensions.(...)` – Christiaan Westerbeek Apr 28 '18 at 20:43