Extensions are one of the nice features in PostgreSQL. These were introduced in PostgreSQL 9.1. An extension is simply a bunch of SQL together in a script file. So, creating an extension loads the objects into the database. For example, a new datatype will require new functions, new operators, and probably new index operator classes. It is helpful to collect all these objects in a single package to simplify database management. PostgreSQL calls such a package an extension.
The following is the syntax to create an extension:
CREATE EXTENSION [ IF NOT EXISTS ] extension_name [ WITH ] [ SCHEMA schema_name ] [ VERSION version ] [ FROM old_version ]
Let's take an example of creating a simple extension, pg_stat_statements
, and the objects loaded by creating it:
postgres=# CREATE EXTENSION pg_stat_statements ; CREATE EXTENSION
The functions created are as follows:
postgres=# \df pg_stat_statement* List of functions -[ RECORD 1 ]--------+------ Schema ...