Structure of a Postgres Extension
A Postgres trusted language extension (TLE) consists of the following files:
- Script files. These files contain the SQL commands to create the extension's objects.
- Control files. These files contain basic properties of the extension itself.
For an extension to be valid, one file of each type must be present in an extension. For example, if you want to create an extension named
my-extension, create the following folder structure:
In the above example, the
my-extension folder contains the extension files. Names of the files are important. The control file should be named
<extension_name>.control and the script file should be named
<extension_name>--<extension_version>.sql. The readme file should be named
README.md (case sensitive).
A control file contains metadata about the extension in key-value pairs. The most common keys that you should consider setting are the following:
- default_version (string). The version to use if the user doesn't provide one in the
- comment (string). A comment added to the extension object created in the database.
- requires (string). A comma separated list of extensions that this extension depends on.
- relocatable (boolean). Set to true if the extension's objects can be moved to a different schema after they are created.
- superuser (boolean). Set to true if only superusers should be able to create this extension.
For example, the pgjwt extension's control file looks like this:
1 2 3 4 5 6
For a complete list of keys available in a control file, refer to Postgres documentation.
A control file contains key-value pairs. Each pair should be on a separate line. Empty lines are ignored. Text after a
# is a comment and is also ignored. Keys should start with a letter and contain only letters or digits. The
= sign following a key is optional, but there must be at least one whitespace after a key if
= is omitted. Values can be either a boolean or a string. Valid values for a boolean are
false. Strings are anything between a pair of single quotes. If you want to include a single quote in the middle of a string, use
'' to escape it. A complete list of escape sequences is:
\r- carriage return
''- single quote
Strings which do not contain whitespace or escape sequences can be written without the surrounding single quotes.
Some examples of valid control file syntax follow:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
Script files contain the SQL commands to create or modify database objects. These database objects can be, but are not limited to, tables, views, functions, types, operators etc. For example, the pgjwt's
pgjwt--0.1.1.sql file contains definitions for functions which the extension adds to the database. One exception to the SQL commands which can exist in a script file are transaction control commands like
You might have noticed a strange line at the beginning of the
pgjwt-0.1.1.sql file starting with
\echo. This line prevents the script file from being run accidentally in
psql. Lines starting with
\echo are run only in
psql but are ignored when the script file is executed by the
CREATE EXTENSION command. It is recommended that you include such a line at the beginning of your script file.
Update scripts are used to update an installed extension. An update script should be named
<extension_name>--<old_version>--<new_version>.sql. For example if version
my-extension was published already and you want to publish a new version
1.1 you need to create
my-extension--1.0--1.1.sql. Update scripts can create new database object or modify/delete existing objects created by the previous version of the extension.
README.md file contains documentation for the extension in the markdown format. The documentation typically contains installation and usage instructions about the extension.