

Installing and Using PostgreSQL Modules - zhiping
http://www.linuxforu.com/2012/03/installing-using-postgresql-modules-chkpass-fuzzystrmatch-isn-hstore/

======
jeltz
In PostgreSQL 9.1 the contrib modules were all converted to the new concept of
extensions. So instead of having to run a SQL script you can simply run.

CREATE EXTENSION hstore;

The advantages of this new command is that extensions can easily be
uninstalled, installed in other schemas, and most of the problems with modules
relating to database upgrades are gone.

~~~
mgkimsal
pgnoob here: wouldn't "install extension" have been more accurate? It's
probably doing something 'create'-ive under the hood, but to install something
with 'create' doesn't seem at all intuitive. If this was something legacy from
30 years ago, I'd understand changing at this point doesn't make much sense,
but this seems recent.

~~~
jeltz
I believe it was for symmetry with already existing CREATE commands.
Especially CREATE LANGUAGE comes to mind. CREATE LANGUAGE is very similar in
that what is does is installing a stored procedure programming language into
the current database.

Another advantage of using CREATE is that it is an already existing convention
in PostgreSQL which means it is obvious that there may be an ALTER EXTENSION
and a DROP EXTENSION (both exist). If they had called it INSTALL then it could
be either UNINSTALL EXTENSION or REMOVE EXTENSION.

In summary: It is meant to be intuitive to people who are used to SQL
databases.

EDIT: And yes, it does "'create'-ive" under the hood. I.e. the various CREATE
commands generally insert new rows in the catalog tables (the tables that
store the schema definitions and users). pg_extension is the catalog table
containing the list of installed extension.

~~~
mgkimsal
Thanks for the explanation.

------
fdr
The state of the art of this has advanced significantly with the advent of
"extension" support, as seen in CREATE EXTENSION in 9.1+:

    
    
      http://www.postgresql.org/docs/9.1/static/sql-createextension.html
    

The new way is much better than the old way.

------
ibotty
using postgres 8.4? seriously? as has been said by fdr, the new extension
support is far superior!

(there are many other reasons to target newer postgres as well)

------
jacques_chester
What modules demonstrate well is PostgreSQL's largely unsung degree of
programmability. Most databases tie you to the data types that came out of the
box, which leads more complex type modelling to be pushed out into the
application code.

But PostgreSQL lets you go in the other direction, pushing type logic closer
to the actual data store. This makes data storage smarter and can with a bit
of judicious footwork simplify your application code.

~~~
deafbybeheading
Indeed. User-defined aggregates[1] are another great tool, and complement
user-defined types very nicely.

[1]: <http://www.postgresql.org/docs/9.1/static/xaggr.html>

