Database Reference
In-Depth Information
Creating extensions
When creating an extension that is shipped to the PostgreSQL installer, users uses
the
CREATE EXTENSION
command as follows:
CREATE EXTENSION [IF NOT EXISTS] extension_name
[WITH] [SCHEMA schema_name]
[Version]
[FROM old_version]
When the user calls the
CREATE EXTENSION
command, it will load a speciied
extension into the current database. There must not be an extension of the same
name already loaded. If an extension with the same name is already loaded, the user
will get an error. In the backend, when the user calls the
LOAD EXTENSION
command,
the extension script ile will be executed. The script will create initial SQL objects that
are mainly functions, data types, operators, and indexes. The
CREATE EXTENSION
command, in addition, notes down the identities of all the created objects so that
they can be dropped again later, if the user executes the
DROP EXTENSION
command.
Only superusers or users who have database owner privileges can create or drop an
extension. Moreover, the user who runs the command of creating an extension will
own the extension. Let's go through the details of each parameter that we mentioned
in the preceding command:
•
IF NOT EXISTS
: The
IF NOT EXISTS
statement, when used, tells PostgreSQL
to not throw an error if an extension with the same name already exists. If it
exists, only a notice is issued.
•
extension_name
: The
extension_name
parameter is the name of the
extension to be installed. The PostgreSQL will create the extension using
details from the
control
ile.
Control
iles are normally present at the
sharedir/extension/extension_name.control
location.
•
schema_name
: The
schema_name
parameter represents the name of the
schema in which users wishes to install the extension's objects, given that the
extension allows its contents to be relocated. When specifying the schema
name, it must be present in the same database. If the user does not give a
schema name, then the default object creation schema is used.
You can view a list of schemas in the current path by using this command:
# show search_path;