Toad World Blog

Creating Extensions in Schemas

Feb 28, 2017 12:36:25 PM by Bruce Momjian

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').

Tags: PostgreSQL Postgres

Bruce Momjian

Written by Bruce Momjian

Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader.

He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value of Postgres advances and new technology enhancements.

He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.