Working with extensions

We have so far initialized the database cluster. However, we made quite a few extensions available using the world option. What about them? We can list the installed extensions using the dx (describe extension) command at the psql prompt:

postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

To get a list of available extensions, we can query the pg_available_extensions view, as shown here:

postgres=# SELECT name,comment  FROM pg_available_extensions limit 5;
   name   |                           comment                         
----------+--------------------------------------------------------------
dblink   | connect to other PostgreSQL databases from within a database
isn      | data types for international product numbering standards
file_fdw | foreign-data wrapper for flat file access
tsearch2 | compatibility package for pre-8.3 text search functions
unaccent | text search dictionary that removes accents
(5 rows)

Let's try installing one extension and then see the list of installed extensions again:

postgres=# CREATE EXTENSION dblink ;
CREATE EXTENSION
postgres=# \dx
                         List of installed extensions
  Name   | Version |   Schema   |                         Description
---------+---------+------------+--------------------------------------------------------------
dblink  | 1.1     | public     | connect to other PostgreSQL databases from within a database
plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

The dblink extension has been added to the list of installed extensions. To remove it, just drop it:

postgres=# DROP EXTENSION dblink ;
DROP EXTENSION
postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

Tip

Downloading the example code

You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.