1
<!-- doc/src/sgml/extend.sgml -->
4
<title>Extending <acronym>SQL</acronym></title>
6
<indexterm zone="extend">
7
<primary>extending SQL</primary>
11
In the sections that follow, we will discuss how you
12
can extend the <productname>PostgreSQL</productname>
13
<acronym>SQL</acronym> query language by adding:
15
<itemizedlist spacing="compact" mark="bullet">
18
functions (starting in <xref linkend="xfunc">)
23
aggregates (starting in <xref linkend="xaggr">)
28
data types (starting in <xref linkend="xtypes">)
33
operators (starting in <xref linkend="xoper">)
38
operator classes for indexes (starting in <xref linkend="xindex">)
43
packages of related objects (starting in <xref linkend="extend-extensions">)
49
<sect1 id="extend-how">
50
<title>How Extensibility Works</title>
53
<productname>PostgreSQL</productname> is extensible because its operation is
54
catalog-driven. If you are familiar with standard
55
relational database systems, you know that they store information
56
about databases, tables, columns, etc., in what are
57
commonly known as system catalogs. (Some systems call
58
this the data dictionary.) The catalogs appear to the
59
user as tables like any other, but the <acronym>DBMS</acronym> stores
60
its internal bookkeeping in them. One key difference
61
between <productname>PostgreSQL</productname> and standard relational database systems is
62
that <productname>PostgreSQL</productname> stores much more information in its
63
catalogs: not only information about tables and columns,
64
but also information about data types, functions, access
65
methods, and so on. These tables can be modified by
66
the user, and since <productname>PostgreSQL</productname> bases its operation
67
on these tables, this means that <productname>PostgreSQL</productname> can be
68
extended by users. By comparison, conventional
69
database systems can only be extended by changing hardcoded
70
procedures in the source code or by loading modules
71
specially written by the <acronym>DBMS</acronym> vendor.
75
The <productname>PostgreSQL</productname> server can moreover
76
incorporate user-written code into itself through dynamic loading.
77
That is, the user can specify an object code file (e.g., a shared
78
library) that implements a new type or function, and
79
<productname>PostgreSQL</productname> will load it as required.
80
Code written in <acronym>SQL</acronym> is even more trivial to add
81
to the server. This ability to modify its operation <quote>on the
82
fly</quote> makes <productname>PostgreSQL</productname> uniquely
83
suited for rapid prototyping of new applications and storage
88
<sect1 id="extend-type-system">
89
<title>The <productname>PostgreSQL</productname> Type System</title>
91
<indexterm zone="extend-type-system">
92
<primary>base type</primary>
95
<indexterm zone="extend-type-system">
96
<primary>data type</primary>
97
<secondary>base</secondary>
100
<indexterm zone="extend-type-system">
101
<primary>composite type</primary>
104
<indexterm zone="extend-type-system">
105
<primary>data type</primary>
106
<secondary>composite</secondary>
110
<productname>PostgreSQL</productname> data types are divided into base
111
types, composite types, domains, and pseudo-types.
115
<title>Base Types</title>
118
Base types are those, like <type>int4</type>, that are
119
implemented below the level of the <acronym>SQL</> language
120
(typically in a low-level language such as C). They generally
121
correspond to what are often known as abstract data types.
122
<productname>PostgreSQL</productname> can only operate on such
123
types through functions provided by the user and only understands
124
the behavior of such types to the extent that the user describes
125
them. Base types are further subdivided into scalar and array
126
types. For each scalar type, a corresponding array type is
127
automatically created that can hold variable-size arrays of that
133
<title>Composite Types</title>
136
Composite types, or row types, are created whenever the user
137
creates a table. It is also possible to use <xref
138
linkend="sql-createtype"> to
139
define a <quote>stand-alone</> composite type with no associated
140
table. A composite type is simply a list of types with
141
associated field names. A value of a composite type is a row or
142
record of field values. The user can access the component fields
143
from <acronym>SQL</> queries. Refer to <xref linkend="rowtypes">
144
for more information on composite types.
149
<title>Domains</title>
152
A domain is based on a particular base type and for many purposes
153
is interchangeable with its base type. However, a domain can
154
have constraints that restrict its valid values to a subset of
155
what the underlying base type would allow.
159
Domains can be created using the <acronym>SQL</> command
160
<xref linkend="sql-createdomain">.
161
Their creation and use is not discussed in this chapter.
166
<title>Pseudo-Types</title>
169
There are a few <quote>pseudo-types</> for special purposes.
170
Pseudo-types cannot appear as columns of tables or attributes of
171
composite types, but they can be used to declare the argument and
172
result types of functions. This provides a mechanism within the
173
type system to identify special classes of functions. <xref
174
linkend="datatype-pseudotypes-table"> lists the existing
179
<sect2 id="extend-types-polymorphic">
180
<title>Polymorphic Types</title>
182
<indexterm zone="extend-types-polymorphic">
183
<primary>polymorphic type</primary>
186
<indexterm zone="extend-types-polymorphic">
187
<primary>polymorphic function</primary>
190
<indexterm zone="extend-types-polymorphic">
191
<primary>type</primary>
192
<secondary>polymorphic</secondary>
195
<indexterm zone="extend-types-polymorphic">
196
<primary>function</primary>
197
<secondary>polymorphic</secondary>
201
Four pseudo-types of special interest are <type>anyelement</>,
202
<type>anyarray</>, <type>anynonarray</>, and <type>anyenum</>,
203
which are collectively called <firstterm>polymorphic types</>.
204
Any function declared using these types is said to be
205
a <firstterm>polymorphic function</>. A polymorphic function can
206
operate on many different data types, with the specific data type(s)
207
being determined by the data types actually passed to it in a particular
212
Polymorphic arguments and results are tied to each other and are resolved
213
to a specific data type when a query calling a polymorphic function is
214
parsed. Each position (either argument or return value) declared as
215
<type>anyelement</type> is allowed to have any specific actual
216
data type, but in any given call they must all be the
217
<emphasis>same</emphasis> actual type. Each
218
position declared as <type>anyarray</type> can have any array data type,
219
but similarly they must all be the same type. If there are
220
positions declared <type>anyarray</type> and others declared
221
<type>anyelement</type>, the actual array type in the
222
<type>anyarray</type> positions must be an array whose elements are
223
the same type appearing in the <type>anyelement</type> positions.
224
<type>anynonarray</> is treated exactly the same as <type>anyelement</>,
225
but adds the additional constraint that the actual type must not be
227
<type>anyenum</> is treated exactly the same as <type>anyelement</>,
228
but adds the additional constraint that the actual type must
233
Thus, when more than one argument position is declared with a polymorphic
234
type, the net effect is that only certain combinations of actual argument
235
types are allowed. For example, a function declared as
236
<literal>equal(anyelement, anyelement)</> will take any two input values,
237
so long as they are of the same data type.
241
When the return value of a function is declared as a polymorphic type,
242
there must be at least one argument position that is also polymorphic,
243
and the actual data type supplied as the argument determines the actual
244
result type for that call. For example, if there were not already
245
an array subscripting mechanism, one could define a function that
246
implements subscripting as <literal>subscript(anyarray, integer)
247
returns anyelement</>. This declaration constrains the actual first
248
argument to be an array type, and allows the parser to infer the correct
249
result type from the actual first argument's type. Another example
250
is that a function declared as <literal>f(anyarray) returns anyenum</>
251
will only accept arrays of enum types.
255
Note that <type>anynonarray</> and <type>anyenum</> do not represent
256
separate type variables; they are the same type as
257
<type>anyelement</type>, just with an additional constraint. For
258
example, declaring a function as <literal>f(anyelement, anyenum)</>
259
is equivalent to declaring it as <literal>f(anyenum, anyenum)</>:
260
both actual arguments have to be the same enum type.
264
A variadic function (one taking a variable number of arguments, as in
265
<xref linkend="xfunc-sql-variadic-functions">) can be
266
polymorphic: this is accomplished by declaring its last parameter as
267
<literal>VARIADIC</> <type>anyarray</>. For purposes of argument
268
matching and determining the actual result type, such a function behaves
269
the same as if you had written the appropriate number of
270
<type>anynonarray</> parameters.
282
<sect1 id="extend-extensions">
283
<title>Packaging Related Objects into an Extension</title>
285
<indexterm zone="extend-extensions">
286
<primary>extension</primary>
290
A useful extension to <productname>PostgreSQL</> typically includes
291
multiple SQL objects; for example, a new datatype will require new
292
functions, new operators, and probably new index operator classes.
293
It is helpful to collect all these objects into a single package
294
to simplify database management. <productname>PostgreSQL</> calls
295
such a package an <firstterm>extension</>. To define an extension,
296
you need at least a <firstterm>script file</> that contains the
297
<acronym>SQL</> commands to create the extension's objects, and a
298
<firstterm>control file</> that specifies a few basic properties
299
of the extension itself. If the extension includes C code, there
300
will typically also be a shared library file into which the C code
301
has been built. Once you have these files, a simple
302
<xref linkend="sql-createextension"> command loads the objects into
307
The main advantage of using an extension, rather than just running the
308
<acronym>SQL</> script to load a bunch of <quote>loose</> objects
309
into your database, is that <productname>PostgreSQL</> will then
310
understand that the objects of the extension go together. You can
311
drop all the objects with a single <xref linkend="sql-dropextension">
312
command (no need to maintain a separate <quote>uninstall</> script).
313
Even more useful, <application>pg_dump</> knows that it should not
314
dump the individual member objects of the extension — it will
315
just include a <command>CREATE EXTENSION</> command in dumps, instead.
316
This vastly simplifies migration to a new version of the extension
317
that might contain more or different objects than the old version.
318
Note however that you must have the extension's control, script, and
319
other files available when loading such a dump into a new database.
323
<productname>PostgreSQL</> will not let you drop an individual object
324
contained in an extension, except by dropping the whole extension.
325
Also, while you can change the definition of an extension member object
326
(for example, via <command>CREATE OR REPLACE FUNCTION</command> for a
327
function), bear in mind that the modified definition will not be dumped
328
by <application>pg_dump</>. Such a change is usually only sensible if
329
you concurrently make the same change in the extension's script file.
330
(But there are special provisions for tables containing configuration
335
The extension mechanism also has provisions for packaging modification
336
scripts that adjust the definitions of the SQL objects contained in an
337
extension. For example, if version 1.1 of an extension adds one function
338
and changes the body of another function compared to 1.0, the extension
339
author can provide an <firstterm>update script</> that makes just those
340
two changes. The <command>ALTER EXTENSION UPDATE</> command can then
341
be used to apply these changes and track which version of the extension
342
is actually installed in a given database.
346
The kinds of SQL objects that can be members of an extension are shown in
347
the description of <xref linkend="sql-alterextension">. Notably, objects
348
that are database-cluster-wide, such as databases, roles, and tablespaces,
349
cannot be extension members since an extension is only known within one
350
database. (Although an extension script is not prohibited from creating
351
such objects, if it does so they will not be tracked as part of the
352
extension.) Also notice that while a table can be a member of an
353
extension, its subsidiary objects such as indexes are not directly
354
considered members of the extension.
357
<sect2 id="extension">
358
<title>Extension Files</title>
361
<primary>control file</primary>
365
The <xref linkend="sql-createextension"> command relies on a control
366
file for each extension, which must be named the same as the extension
367
with a suffix of <literal>.control</>, and must be placed in the
368
installation's <literal>SHAREDIR/extension</literal> directory. There
369
must also be at least one <acronym>SQL</> script file, which follows the
371
<literal><replaceable>extension</>--<replaceable>version</>.sql</literal>
372
(for example, <literal>foo--1.0.sql</> for version <literal>1.0</> of
373
extension <literal>foo</>). By default, the script file(s) are also
374
placed in the <literal>SHAREDIR/extension</literal> directory; but the
375
control file can specify a different directory for the script file(s).
379
The file format for an extension control file is the same as for the
380
<filename>postgresql.conf</> file, namely a list of
381
<replaceable>parameter_name</> <literal>=</> <replaceable>value</>
382
assignments, one per line. Blank lines and comments introduced by
383
<literal>#</> are allowed. Be sure to quote any value that is not
384
a single word or number.
388
A control file can set the following parameters:
393
<term><varname>directory</varname> (<type>string</type>)</term>
396
The directory containing the extension's <acronym>SQL</> script
397
file(s). Unless an absolute path is given, the name is relative to
398
the installation's <literal>SHAREDIR</literal> directory. The
399
default behavior is equivalent to specifying
400
<literal>directory = 'extension'</>.
406
<term><varname>default_version</varname> (<type>string</type>)</term>
409
The default version of the extension (the one that will be installed
410
if no version is specified in <command>CREATE EXTENSION</>). Although
411
this can be omitted, that will result in <command>CREATE EXTENSION</>
412
failing if no <literal>VERSION</> option appears, so you generally
413
don't want to do that.
419
<term><varname>comment</varname> (<type>string</type>)</term>
422
A comment (any string) about the extension. Alternatively,
423
the comment can be set by means of the <xref linkend="sql-comment">
424
command in the script file.
430
<term><varname>encoding</varname> (<type>string</type>)</term>
433
The character set encoding used by the script file(s). This should
434
be specified if the script files contain any non-ASCII characters.
435
Otherwise the files will be assumed to be in the database encoding.
441
<term><varname>module_pathname</varname> (<type>string</type>)</term>
444
The value of this parameter will be substituted for each occurrence
445
of <literal>MODULE_PATHNAME</> in the script file(s). If it is not
446
set, no substitution is made. Typically, this is set to
447
<literal>$libdir/<replaceable>shared_library_name</></literal> and
448
then <literal>MODULE_PATHNAME</> is used in <command>CREATE
449
FUNCTION</> commands for C-language functions, so that the script
450
files do not need to hard-wire the name of the shared library.
456
<term><varname>requires</varname> (<type>string</type>)</term>
459
A list of names of extensions that this extension depends on,
460
for example <literal>requires = 'foo, bar'</literal>. Those
461
extensions must be installed before this one can be installed.
467
<term><varname>superuser</varname> (<type>boolean</type>)</term>
470
If this parameter is <literal>true</> (which is the default),
471
only superusers can create the extension or update it to a new
472
version. If it is set to <literal>false</>, just the privileges
473
required to execute the commands in the installation or update script
480
<term><varname>relocatable</varname> (<type>boolean</type>)</term>
483
An extension is <firstterm>relocatable</> if it is possible to move
484
its contained objects into a different schema after initial creation
485
of the extension. The default is <literal>false</>, i.e. the
486
extension is not relocatable.
487
See below for more information.
493
<term><varname>schema</varname> (<type>string</type>)</term>
496
This parameter can only be set for non-relocatable extensions.
497
It forces the extension to be loaded into exactly the named schema
498
and not any other. See below for more information.
505
In addition to the primary control file
506
<literal><replaceable>extension</>.control</literal>,
507
an extension can have secondary control files named in the style
508
<literal><replaceable>extension</>--<replaceable>version</>.control</literal>.
509
If supplied, these must be located in the script file directory.
510
Secondary control files follow the same format as the primary control
511
file. Any parameters set in a secondary control file override the
512
primary control file when installing or updating to that version of
513
the extension. However, the parameters <varname>directory</> and
514
<varname>default_version</> cannot be set in a secondary control file.
518
An extension's <acronym>SQL</> script files can contain any SQL commands,
519
except for transaction control commands (<command>BEGIN</>,
520
<command>COMMIT</>, etc) and commands that cannot be executed inside a
521
transaction block (such as <command>VACUUM</>). This is because the
522
script files are implicitly executed within a transaction block.
526
While the script files can contain any characters allowed by the specified
527
encoding, control files should contain only plain ASCII, because there
528
is no way for <productname>PostgreSQL</> to know what encoding a
529
control file is in. In practice this is only an issue if you want to
530
use non-ASCII characters in the extension's comment. Recommended
531
practice in that case is to not use the control file <varname>comment</>
532
parameter, but instead use <command>COMMENT ON EXTENSION</>
533
within a script file to set the comment.
539
<title>Extension Relocatability</title>
542
Users often wish to load the objects contained in an extension into a
543
different schema than the extension's author had in mind. There are
544
three supported levels of relocatability:
550
A fully relocatable extension can be moved into another schema
551
at any time, even after it's been loaded into a database.
552
This is done with the <command>ALTER EXTENSION SET SCHEMA</>
553
command, which automatically renames all the member objects into
554
the new schema. Normally, this is only possible if the extension
555
contains no internal assumptions about what schema any of its
556
objects are in. Also, the extension's objects must all be in one
557
schema to begin with (ignoring objects that do not belong to any
558
schema, such as procedural languages). Mark a fully relocatable
559
extension by setting <literal>relocatable = true</> in its control
566
An extension might be relocatable during installation but not
567
afterwards. This is typically the case if the extension's script
568
file needs to reference the target schema explicitly, for example
569
in setting <literal>search_path</> properties for SQL functions.
570
For such an extension, set <literal>relocatable = false</> in its
571
control file, and use <literal>@extschema@</> to refer to the target
572
schema in the script file. All occurrences of this string will be
573
replaced by the actual target schema's name before the script is
574
executed. The user can set the target schema using the
575
<literal>SCHEMA</> option of <command>CREATE EXTENSION</>.
581
If the extension does not support relocation at all, set
582
<literal>relocatable = false</> in its control file, and also set
583
<literal>schema</> to the name of the intended target schema. This
584
will prevent use of the <literal>SCHEMA</> option of <command>CREATE
585
EXTENSION</>, unless it specifies the same schema named in the control
586
file. This choice is typically necessary if the extension contains
587
internal assumptions about schema names that can't be replaced by
588
uses of <literal>@extschema@</>. The <literal>@extschema@</>
589
substitution mechanism is available in this case too, although it is
590
of limited use since the schema name is determined by the control file.
596
In all cases, the script file will be executed with
597
<xref linkend="guc-search-path"> initially set to point to the target
598
schema; that is, <command>CREATE EXTENSION</> does the equivalent of
601
SET LOCAL search_path TO @extschema@;
603
This allows the objects created by the script file to go into the target
604
schema. The script file can change <varname>search_path</> if it wishes,
605
but that is generally undesirable. <varname>search_path</> is restored
606
to its previous setting upon completion of <command>CREATE EXTENSION</>.
610
The target schema is determined by the <varname>schema</> parameter in
611
the control file if that is given, otherwise by the <literal>SCHEMA</>
612
option of <command>CREATE EXTENSION</> if that is given, otherwise the
613
current default object creation schema (the first one in the caller's
614
<varname>search_path</>). When the control file <varname>schema</>
615
parameter is used, the target schema will be created if it doesn't
616
already exist, but in the other two cases it must already exist.
620
If any prerequisite extensions are listed in <varname>requires</varname>
621
in the control file, their target schemas are appended to the initial
622
setting of <varname>search_path</>. This allows their objects to be
623
visible to the new extension's script file.
627
Although a non-relocatable extension can contain objects spread across
628
multiple schemas, it is usually desirable to place all the objects meant
629
for external use into a single schema, which is considered the extension's
630
target schema. Such an arrangement works conveniently with the default
631
setting of <varname>search_path</> during creation of dependent
637
<title>Extension Configuration Tables</title>
640
Some extensions include configuration tables, which contain data that
641
might be added or changed by the user after installation of the
642
extension. Ordinarily, if a table is part of an extension, neither
643
the table's definition nor its content will be dumped by
644
<application>pg_dump</>. But that behavior is undesirable for a
645
configuration table; any data changes made by the user need to be
646
included in dumps, or the extension will behave differently after a dump
651
To solve this problem, an extension's script file can mark a table
652
it has created as a configuration table, which will cause
653
<application>pg_dump</> to include the table's contents (not its
654
definition) in dumps. To do that, call the function
655
<function>pg_extension_config_dump(regclass, text)</> after creating the
658
CREATE TABLE my_config (key text, value text);
660
SELECT pg_catalog.pg_extension_config_dump('my_config', '');
662
Any number of tables can be marked this way.
666
When the second argument of <function>pg_extension_config_dump</> is
667
an empty string, the entire contents of the table are dumped by
668
<application>pg_dump</>. This is usually only correct if the table
669
is initially empty as created by the extension script. If there is
670
a mixture of initial data and user-provided data in the table,
671
the second argument of <function>pg_extension_config_dump</> provides
672
a <literal>WHERE</> condition that selects the data to be dumped.
673
For example, you might do
675
CREATE TABLE my_config (key text, value text, standard_entry boolean);
677
SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');
679
and then make sure that <structfield>standard_entry</> is true only
680
in the rows created by the extension's script.
684
More complicated situations, such as initially-provided rows that might
685
be modified by users, can be handled by creating triggers on the
686
configuration table to ensure that modified rows are marked correctly.
691
<title>Extension Updates</title>
694
One advantage of the extension mechanism is that it provides convenient
695
ways to manage updates to the SQL commands that define an extension's
696
objects. This is done by associating a version name or number with
697
each released version of the extension's installation script.
698
In addition, if you want users to be able to update their databases
699
dynamically from one version to the next, you should provide
700
<firstterm>update scripts</> that make the necessary changes to go from
701
one version to the next. Update scripts have names following the pattern
702
<literal><replaceable>extension</>--<replaceable>oldversion</>--<replaceable>newversion</>.sql</literal>
703
(for example, <literal>foo--1.0--1.1.sql</> contains the commands to modify
704
version <literal>1.0</> of extension <literal>foo</> into version
709
Given that a suitable update script is available, the command
710
<command>ALTER EXTENSION UPDATE</> will update an installed extension
711
to the specified new version. The update script is run in the same
712
environment that <command>CREATE EXTENSION</> provides for installation
713
scripts: in particular, <varname>search_path</> is set up in the same
714
way, and any new objects created by the script are automatically added
719
If an extension has secondary control files, the control parameters
720
that are used for an update script are those associated with the script's
721
target (new) version.
725
The update mechanism can be used to solve an important special case:
726
converting a <quote>loose</> collection of objects into an extension.
727
Before the extension mechanism was added to
728
<productname>PostgreSQL</productname> (in 9.1), many people wrote
729
extension modules that simply created assorted unpackaged objects.
730
Given an existing database containing such objects, how can we convert
731
the objects into a properly packaged extension? Dropping them and then
732
doing a plain <command>CREATE EXTENSION</> is one way, but it's not
733
desirable if the objects have dependencies (for example, if there are
734
table columns of a data type created by the extension). The way to fix
735
this situation is to create an empty extension, then use <command>ALTER
736
EXTENSION ADD</> to attach each pre-existing object to the extension,
737
then finally create any new objects that are in the current extension
738
version but were not in the unpackaged release. <command>CREATE
739
EXTENSION</> supports this case with its <literal>FROM</> <replaceable
740
class="parameter">old_version</> option, which causes it to not run the
741
normal installation script for the target version, but instead the update
743
<literal><replaceable>extension</>--<replaceable>old_version</>--<replaceable>target_version</>.sql</literal>.
744
The choice of the dummy version name to use as <replaceable
745
class="parameter">old_version</> is up to the extension author, though
746
<literal>unpackaged</> is a common convention. If you have multiple
747
prior versions you need to be able to update into extension style, use
748
multiple dummy version names to identify them.
752
<command>ALTER EXTENSION</> is able to execute sequences of update
753
script files to achieve a requested update. For example, if only
754
<literal>foo--1.0--1.1.sql</> and <literal>foo--1.1--2.0.sql</> are
755
available, <command>ALTER EXTENSION</> will apply them in sequence if an
756
update to version <literal>2.0</> is requested when <literal>1.0</> is
761
<productname>PostgreSQL</> doesn't assume anything about the properties
762
of version names: for example, it does not know whether <literal>1.1</>
763
follows <literal>1.0</>. It just matches up the available version names
764
and follows the path that requires applying the fewest update scripts.
765
(A version name can actually be any string that doesn't contain
766
<literal>--</> or leading or trailing <literal>-</>.)
770
Sometimes it is useful to provide <quote>downgrade</> scripts, for
771
example <literal>foo--1.1--1.0.sql</> to allow reverting the changes
772
associated with version <literal>1.1</>. If you do that, be careful
773
of the possibility that a downgrade script might unexpectedly
774
get applied because it yields a shorter path. The risky case is where
775
there is a <quote>fast path</> update script that jumps ahead several
776
versions as well as a downgrade script to the fast path's start point.
777
It might take fewer steps to apply the downgrade and then the fast
778
path than to move ahead one version at a time. If the downgrade script
779
drops any irreplaceable objects, this will yield undesirable results.
783
To check for unexpected update paths, use this command:
785
SELECT * FROM pg_extension_update_paths('<replaceable>extension_name</>');
787
This shows each pair of distinct known version names for the specified
788
extension, together with the update path sequence that would be taken to
789
get from the source version to the target version, or <literal>NULL</> if
790
there is no available update path. The path is shown in textual form
791
with <literal>--</> separators. You can use
792
<literal>regexp_split_to_array(path,'--')</> if you prefer an array
798
<title>Extension Example</title>
801
Here is a complete example of an <acronym>SQL</>-only
802
extension, a two-element composite type that can store any type of value
803
in its slots, which are named <quote>k</> and <quote>v</>. Non-text
804
values are automatically coerced to text for storage.
808
The script file <filename>pair--1.0.sql</> looks like this:
810
<programlisting><![CDATA[
811
CREATE TYPE pair AS ( k text, v text );
813
CREATE OR REPLACE FUNCTION pair(anyelement, text)
814
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair';
816
CREATE OR REPLACE FUNCTION pair(text, anyelement)
817
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair';
819
CREATE OR REPLACE FUNCTION pair(anyelement, anyelement)
820
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair';
822
CREATE OR REPLACE FUNCTION pair(text, text)
823
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair;';
825
CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = anyelement, PROCEDURE = pair);
826
CREATE OPERATOR ~> (LEFTARG = anyelement, RIGHTARG = text, PROCEDURE = pair);
827
CREATE OPERATOR ~> (LEFTARG = anyelement, RIGHTARG = anyelement, PROCEDURE = pair);
828
CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, PROCEDURE = pair);
834
The control file <filename>pair.control</> looks like this:
838
comment = 'A key/value pair data type'
839
default_version = '1.0'
845
While you hardly need a makefile to install these two files into the
846
correct directory, you could use a <filename>Makefile</> containing this:
852
PG_CONFIG = pg_config
853
PGXS := $(shell $(PG_CONFIG) --pgxs)
857
This makefile relies on <acronym>PGXS</acronym>, which is described
858
in <xref linkend="extend-pgxs">. The command <literal>make install</>
859
will install the control and script files into the correct
860
directory as reported by <application>pg_config</>.
864
Once the files are installed, use the
865
<xref linkend="sql-createextension"> command to load the objects into
866
any particular database.
871
<sect1 id="extend-pgxs">
872
<title>Extension Building Infrastructure</title>
874
<indexterm zone="extend-pgxs">
875
<primary>pgxs</primary>
879
If you are thinking about distributing your
880
<productname>PostgreSQL</> extension modules, setting up a
881
portable build system for them can be fairly difficult. Therefore
882
the <productname>PostgreSQL</> installation provides a build
883
infrastructure for extensions, called <acronym>PGXS</acronym>, so
884
that simple extension modules can be built simply against an
885
already installed server. <acronym>PGXS</acronym> is mainly intended
886
for extensions that include C code, although it can be used for
887
pure-SQL extensions too. Note that <acronym>PGXS</acronym> is not
888
intended to be a universal build system framework that can be used
889
to build any software interfacing to <productname>PostgreSQL</>;
890
it simply automates common build rules for simple server extension
891
modules. For more complicated packages, you might need to write your
896
To use the <acronym>PGXS</acronym> infrastructure for your extension,
897
you must write a simple makefile.
898
In the makefile, you need to set some variables
899
and finally include the global <acronym>PGXS</acronym> makefile.
900
Here is an example that builds an extension module named
901
<literal>isbn_issn</literal>, consisting of a shared library containing
902
some C code, an extension control file, a SQL script, and a documentation
906
EXTENSION = isbn_issn
907
DATA = isbn_issn--1.0.sql
908
DOCS = README.isbn_issn
910
PG_CONFIG = pg_config
911
PGXS := $(shell $(PG_CONFIG) --pgxs)
914
The last three lines should always be the same. Earlier in the
915
file, you assign variables or add custom
916
<application>make</application> rules.
920
Set one of these three variables to specify what is built:
924
<term><varname>MODULES</varname></term>
927
list of shared-library objects to be built from source files with same
928
stem (do not include library suffixes in this list)
934
<term><varname>MODULE_big</varname></term>
937
a shared library to build from multiple source files
938
(list object files in <varname>OBJS</varname>)
944
<term><varname>PROGRAM</varname></term>
947
an executable program to build
948
(list object files in <varname>OBJS</varname>)
954
The following variables can also be set:
958
<term><varname>EXTENSION</varname></term>
961
extension name(s); for each name you must provide an
962
<literal><replaceable>extension</replaceable>.control</literal> file,
963
which will be installed into
964
<literal><replaceable>prefix</replaceable>/share/extension</literal>
970
<term><varname>MODULEDIR</varname></term>
973
subdirectory of <literal><replaceable>prefix</>/share</literal>
974
into which DATA and DOCS files should be installed
975
(if not set, default is <literal>extension</literal> if
976
<varname>EXTENSION</varname> is set,
977
or <literal>contrib</literal> if not)
983
<term><varname>DATA</varname></term>
986
random files to install into <literal><replaceable>prefix</replaceable>/share/$MODULEDIR</literal>
992
<term><varname>DATA_built</varname></term>
995
random files to install into
996
<literal><replaceable>prefix</replaceable>/share/$MODULEDIR</literal>,
997
which need to be built first
1003
<term><varname>DATA_TSEARCH</varname></term>
1006
random files to install under
1007
<literal><replaceable>prefix</replaceable>/share/tsearch_data</literal>
1013
<term><varname>DOCS</varname></term>
1016
random files to install under
1017
<literal><replaceable>prefix</replaceable>/doc/$MODULEDIR</literal>
1023
<term><varname>SCRIPTS</varname></term>
1026
script files (not binaries) to install into
1027
<literal><replaceable>prefix</replaceable>/bin</literal>
1033
<term><varname>SCRIPTS_built</varname></term>
1036
script files (not binaries) to install into
1037
<literal><replaceable>prefix</replaceable>/bin</literal>,
1038
which need to be built first
1044
<term><varname>REGRESS</varname></term>
1047
list of regression test cases (without suffix), see below
1053
<term><varname>EXTRA_CLEAN</varname></term>
1056
extra files to remove in <literal>make clean</literal>
1062
<term><varname>PG_CPPFLAGS</varname></term>
1065
will be added to <varname>CPPFLAGS</varname>
1071
<term><varname>PG_LIBS</varname></term>
1074
will be added to <varname>PROGRAM</varname> link line
1080
<term><varname>SHLIB_LINK</varname></term>
1083
will be added to <varname>MODULE_big</varname> link line
1089
<term><varname>PG_CONFIG</varname></term>
1092
path to <application>pg_config</> program for the
1093
<productname>PostgreSQL</productname> installation to build against
1094
(typically just <literal>pg_config</> to use the first one in your
1103
Put this makefile as <literal>Makefile</literal> in the directory
1104
which holds your extension. Then you can do
1105
<literal>make</literal> to compile, and then <literal>make
1106
install</literal> to install your module. By default, the extension is
1107
compiled and installed for the
1108
<productname>PostgreSQL</productname> installation that
1109
corresponds to the first <command>pg_config</command> program
1110
found in your <varname>PATH</>. You can use a different installation by
1111
setting <varname>PG_CONFIG</varname> to point to its
1112
<command>pg_config</command> program, either within the makefile
1113
or on the <literal>make</literal> command line.
1118
Changing <varname>PG_CONFIG</varname> only works when building
1119
against <productname>PostgreSQL</productname> 8.3 or later.
1120
With older releases it does not work to set it to anything except
1121
<literal>pg_config</>; you must alter your <varname>PATH</>
1122
to select the installation to build against.
1127
The scripts listed in the <varname>REGRESS</> variable are used for
1128
regression testing of your module, which can be invoked by <literal>make
1129
installcheck</literal> after doing <literal>make install</>. For this to
1130
work you must have a running <productname>PostgreSQL</productname> server.
1131
The script files listed in <varname>REGRESS</> must appear in a
1132
subdirectory named <literal>sql/</literal> in your extension's directory.
1133
These files must have extension <literal>.sql</literal>, which must not be
1134
included in the <varname>REGRESS</varname> list in the makefile. For each
1135
test there should also be a file containing the expected output in a
1136
subdirectory named <literal>expected/</literal>, with the same stem and
1137
extension <literal>.out</literal>. <literal>make installcheck</literal>
1138
executes each test script with <application>psql</>, and compares the
1139
resulting output to the matching expected file. Any differences will be
1140
written to the file <literal>regression.diffs</literal> in <command>diff
1141
-c</command> format. Note that trying to run a test that is missing its
1142
expected file will be reported as <quote>trouble</quote>, so make sure you
1143
have all expected files.
1148
The easiest way to create the expected files is to create empty files,
1149
then do a test run (which will of course report differences). Inspect
1150
the actual result files found in the <literal>results/</literal>
1151
directory, then copy them to <literal>expected/</literal> if they match
1152
what you expect from the test.