below) while creating a trigger.

</para>

<para>

Each of the groups of functions described below is provided as a

separately-installable extension.

</para>

<sect2>

<title>refint.c &mdash; Functions for Implementing Referential Integrity</title>

<title>refint &mdash; Functions for Implementing Referential Integrity</title>

<para>

<function>check_primary_key()</> and

</sect2>

<sect2>

<title>timetravel.c &mdash; Functions for Implementing Time Travel</title>

<title>timetravel &mdash; Functions for Implementing Time Travel</title>

<para>

Long ago, <productname>PostgreSQL</> had a built-in time travel feature

</sect2>

<sect2>

<title>autoinc.c &mdash; Functions for Autoincrementing Fields</title>

<title>autoinc &mdash; Functions for Autoincrementing Fields</title>

<para>

<function>autoinc()</> is a trigger that stores the next value of

</sect2>

<sect2>

<title>insert_username.c &mdash; Functions for Tracking Who Changed a Table</title>

<title>insert_username &mdash; Functions for Tracking Who Changed a Table</title>

<para>

<function>insert_username()</> is a trigger that stores the current

</sect2>

<sect2>

<title>moddatetime.c &mdash; Functions for Tracking Last Modification Time</title>

<title>moddatetime &mdash; Functions for Tracking Last Modification Time</title>

<para>

<function>moddatetime()</> is a trigger that stores the current

<para>

Many modules supply new user-defined functions, operators, or types.

To make use of one of these modules, after you have installed the code

you need to register the new objects in the database

system by running the SQL commands in the <literal>.sql</> file

supplied by the module. For example,

you need to register the new SQL objects in the database system.

In <productname>PostgreSQL</> 9.1 and later, this is done by executing

a <xref linkend="sql-createextension"> command. In a fresh database,

you can simply do

<programlisting>

psql -d dbname -f<replaceable>SHAREDIR</>/contrib/<replaceable>module</>.sql

CREATE EXTENSION<replaceable>module_name</>;

</programlisting>

Here, <replaceable>SHAREDIR</> means the installation's <quote>share</>

directory (<literal>pg_config --sharedir</> will tell you what this is).

In most cases the script must be run by a database superuser.

</para>

<para>

You need to run the <literal>.sql</> file in each database that you want

This command must be run by a database superuser. This registers the

new SQL objects in the current database only, so you need to run this

command in each database that you want

the module's facilities to be available in. Alternatively, run it in

database <literal>template1</> so that themodule will be copied into

database <literal>template1</> so that theextension will be copied into

subsequently-created databases by default.

</para>

<para>

You can modify the first command in the <literal>.sql</> file to determine

which schema within the database the module's objects will be created in.

By default, they will be placed in <literal>public</>.

Many modules allow you to install their objects in a schema of your

choice. To do that, add <literal>SCHEMA

<replaceable>schema_name</></literal> to the <command>CREATE EXTENSION</>

command. By default, the objects will be placed in your current creation

target schema, typically <literal>public</>.

</para>

<para>

After a major-version upgrade of <productname>PostgreSQL</>, run the

installation script again, even though the module's objects might have

been brought forward from the old installation by dump and restore.

This ensures that any new functions will be available and any needed

corrections will be applied.

If your database was brought forward by dump and reload from a pre-9.1

version of <productname>PostgreSQL</>, and you had been using the pre-9.1

version of the module in it, you should instead do

<programlisting>

CREATE EXTENSION <replaceable>module_name</> FROM unpackaged;

</programlisting>

This will update the pre-9.1 objects of the module into a proper

<firstterm>extension</> object. Future updates to the module will be

managed by <xref linkend="sql-alterextension">.

For more information about extension updates, see

<xref linkend="extend-extensions">.

</para>

&adminpack;

<title>Usage</title>

<para>

Running theinstallation script creates a text search template

<literal>intdict_template</> and a dictionary <literal>intdict</>

Installing the<literal>dict_int</> extension creates a text search

template<literal>intdict_template</> and a dictionary <literal>intdict</>

based on it, with the default parameters. You can alter the

parameters, for example

<title>Usage</title>

<para>

Running theinstallation script creates a text search template

<literal>xsyn_template</> and a dictionary <literal>xsyn</>

Installing the<literal>dict_xsyn</> extension creates a text search

template<literal>xsyn_template</> and a dictionary <literal>xsyn</>

based on it, with default parameters. You can alter the

parameters, for example

<para>

The <filename>earthdistance</> module provides two different approaches to

calculating great circle distances on the surface of the Earth. The one

described first depends on the <filename>cube</>package (which

described first depends on the <filename>cube</>module (which

<emphasis>must</> be installed before <filename>earthdistance</> can be

installed). The second one is based on the built-in <type>point</> data type,

using longitude and latitude for the coordinates.

<sect2>

<title>Compatibility</title>

<para>

<emphasis>When upgrading from older versions, always load the new

version of this module into the database before restoring a dump.

Otherwise, many new features will be unavailable.</emphasis>

</para>

<para>

As of PostgreSQL 9.0, <type>hstore</> uses a different internal

representation than previous versions. This presents no obstacle for

<para>

This view, and the function <function>pg_stat_statements_reset</>,

are available only in databases they have been specifically installed into

byrunning the <filename>pg_stat_statements.sql</>install script.

byinstalling the <literal>pg_stat_statements</>extension.

However, statistics are tracked across all databases of the server

whenever the <filename>pg_stat_statements</filename> module is loaded

into the server, regardless of presence of the view.

<listitem>

<para>

Create a composite type describing the desired output columns,

similar to the examples in the installation script. Then define a

similar to the examples in

<filename>contrib/tablefunc/tablefunc--1.0.sql</>.

Then define a

unique function name accepting one <type>text</> parameter and returning

<type>setof your_type_name</>, but linking to the same underlying

<function>crosstab</> C function. For example, if your source data

<title>Usage</title>

<para>

Running theinstallation script creates a text search parser

<literal>testparser</>. It has no user-configurable parameters.

Installing the<literal>test_parser</> extension creates a text search

parser<literal>testparser</>. It has no user-configurable parameters.

</para>

<para>

<emphasis>before</> loading the dump data! If your old installation

had the <application>tsearch2</> objects in a schema other

than <literal>public</>, be sure to adjust the

<literal>tsearch2</literal> installation script so that the replacement

<command>CREATE EXTENSION</> command so that the replacement

objects are created in that same schema.

</para>

</step>

<title>Usage</title>

<para>

Running theinstallation script <filename>unaccent.sql</> creates a text

Installing the<literal>unaccent</> extension creates a text

search template <literal>unaccent</> and a dictionary <literal>unaccent</>

based on it. The <literal>unaccent</> dictionary has the default

parameter setting <literal>RULES='unaccent'</>, which makes it immediately