Creating Extensions in Schemas

Postgres extensions are a powerful way to add functionality to Postgres and dozens of extensions are available. However, it can be difficult to manage them. By default, extensions are loaded into the first existing schema in search_path (usually the public schema) e.g.:

CREATE EXTENSION pgcrypto;
\df
                                        List of functions
 Schema |         Name          | Result data type |        Argument data types         |  Type
--------+-----------------------+------------------+------------------------------------+--------
 public | armor                 | text             | bytea                              | normal
 public | armor                 | text             | bytea, text[], text[]              | normal
…

 

This loads 36 functions into the public schema. This can be difficult to manage, particularly if multiple extensions are loaded into the same database.

One solution is to place each extension into its own schema. This can be accomplished by setting search_path before loading the extension, or using the with schema clause in the create extension command:

CREATE SCHEMA pgcrypto;
 
CREATE EXTENSION pgcrypto WITH SCHEMA pgcrypto;
 
\df pgcrypto.*
                                         List of functions
  Schema  |         Name          | Result data type |        Argument data types         |  Type
----------+-----------------------+------------------+------------------------------------+--------
 pgcrypto | armor                 | text             | bytea                              | normal
 pgcrypto | armor                 | text             | bytea, text[], text[]              | normal
…

Users can then set their search_path whenever they want to use the extension, or prefix the function call with the schema name, e.g. select pgcrypto.armor('abc').