<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>38.16. Packaging Related Objects into an Extension</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="xindex.html" title="38.15. Interfacing Extensions To Indexes" /><link rel="next" href="extend-pgxs.html" title="38.17. Extension Building Infrastructure" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">38.16. Packaging Related Objects into an Extension</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xindex.html" title="38.15. Interfacing Extensions To Indexes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 38. Extending <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="extend-pgxs.html" title="38.17. Extension Building Infrastructure">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="EXTEND-EXTENSIONS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">38.16. Packaging Related Objects into an Extension</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-STYLE">38.16.1. Defining Extension Objects</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#id-1.8.3.19.12">38.16.2. Extension Files</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION">38.16.3. Extension Relocatability</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES">38.16.4. Extension Configuration Tables</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#id-1.8.3.19.15">38.16.5. Extension Updates</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#id-1.8.3.19.16">38.16.6. Installing Extensions using Update Scripts</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE">38.16.7. Extension Example</a></span></dt></dl></div><a id="id-1.8.3.19.2" class="indexterm"></a><p> A useful extension to <span class="productname">PostgreSQL</span> typically includes multiple SQL objects; for example, a new data type will require new functions, new operators, and probably new index operator classes. It is helpful to collect all these objects into a single package to simplify database management. <span class="productname">PostgreSQL</span> calls such a package an <em class="firstterm">extension</em>. To define an extension, you need at least a <em class="firstterm">script file</em> that contains the <acronym class="acronym">SQL</acronym> commands to create the extension's objects, and a <em class="firstterm">control file</em> that specifies a few basic properties of the extension itself. If the extension includes C code, there will typically also be a shared library file into which the C code has been built. Once you have these files, a simple <a class="xref" href="sql-createextension.html" title="CREATE EXTENSION"><span class="refentrytitle">CREATE EXTENSION</span></a> command loads the objects into your database. </p><p> The main advantage of using an extension, rather than just running the <acronym class="acronym">SQL</acronym> script to load a bunch of <span class="quote">“<span class="quote">loose</span>”</span> objects into your database, is that <span class="productname">PostgreSQL</span> will then understand that the objects of the extension go together. You can drop all the objects with a single <a class="xref" href="sql-dropextension.html" title="DROP EXTENSION"><span class="refentrytitle">DROP EXTENSION</span></a> command (no need to maintain a separate <span class="quote">“<span class="quote">uninstall</span>”</span> script). Even more useful, <span class="application">pg_dump</span> knows that it should not dump the individual member objects of the extension — it will just include a <code class="command">CREATE EXTENSION</code> command in dumps, instead. This vastly simplifies migration to a new version of the extension that might contain more or different objects than the old version. Note however that you must have the extension's control, script, and other files available when loading such a dump into a new database. </p><p> <span class="productname">PostgreSQL</span> will not let you drop an individual object contained in an extension, except by dropping the whole extension. Also, while you can change the definition of an extension member object (for example, via <code class="command">CREATE OR REPLACE FUNCTION</code> for a function), bear in mind that the modified definition will not be dumped by <span class="application">pg_dump</span>. Such a change is usually only sensible if you concurrently make the same change in the extension's script file. (But there are special provisions for tables containing configuration data; see <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES" title="38.16.4. Extension Configuration Tables">Section 38.16.4</a>.) In production situations, it's generally better to create an extension update script to perform changes to extension member objects. </p><p> The extension script may set privileges on objects that are part of the extension via <code class="command">GRANT</code> and <code class="command">REVOKE</code> statements. The final set of privileges for each object (if any are set) will be stored in the <a class="link" href="catalog-pg-init-privs.html" title="52.28. pg_init_privs"><code class="structname">pg_init_privs</code></a> system catalog. When <span class="application">pg_dump</span> is used, the <code class="command">CREATE EXTENSION</code> command will be included in the dump, followed by the set of <code class="command">GRANT</code> and <code class="command">REVOKE</code> statements necessary to set the privileges on the objects to what they were at the time the dump was taken. </p><p> <span class="productname">PostgreSQL</span> does not currently support extension scripts issuing <code class="command">CREATE POLICY</code> or <code class="command">SECURITY LABEL</code> statements. These are expected to be set after the extension has been created. All RLS policies and security labels on extension objects will be included in dumps created by <span class="application">pg_dump</span>. </p><p> The extension mechanism also has provisions for packaging modification scripts that adjust the definitions of the SQL objects contained in an extension. For example, if version 1.1 of an extension adds one function and changes the body of another function compared to 1.0, the extension author can provide an <em class="firstterm">update script</em> that makes just those two changes. The <code class="command">ALTER EXTENSION UPDATE</code> command can then be used to apply these changes and track which version of the extension is actually installed in a given database. </p><p> The kinds of SQL objects that can be members of an extension are shown in the description of <a class="xref" href="sql-alterextension.html" title="ALTER EXTENSION"><span class="refentrytitle">ALTER EXTENSION</span></a>. Notably, objects that are database-cluster-wide, such as databases, roles, and tablespaces, cannot be extension members since an extension is only known within one database. (Although an extension script is not prohibited from creating such objects, if it does so they will not be tracked as part of the extension.) Also notice that while a table can be a member of an extension, its subsidiary objects such as indexes are not directly considered members of the extension. Another important point is that schemas can belong to extensions, but not vice versa: an extension as such has an unqualified name and does not exist <span class="quote">“<span class="quote">within</span>”</span> any schema. The extension's member objects, however, will belong to schemas whenever appropriate for their object types. It may or may not be appropriate for an extension to own the schema(s) its member objects are within. </p><p> If an extension's script creates any temporary objects (such as temp tables), those objects are treated as extension members for the remainder of the current session, but are automatically dropped at session end, as any temporary object would be. This is an exception to the rule that extension member objects cannot be dropped without dropping the whole extension. </p><div class="sect2" id="EXTEND-EXTENSIONS-STYLE"><div class="titlepage"><div><div><h3 class="title">38.16.1. Defining Extension Objects</h3></div></div></div><p> Widely-distributed extensions should assume little about the database they occupy. In particular, unless you issued <code class="literal">SET search_path = pg_temp</code>, assume each unqualified name could resolve to an object that a malicious user has defined. Beware of constructs that depend on <code class="varname">search_path</code> implicitly: <code class="token">IN</code> and <code class="literal">CASE <em class="replaceable"><code>expression</code></em> WHEN</code> always select an operator using the search path. In their place, use <code class="literal">OPERATOR(<em class="replaceable"><code>schema</code></em>.=) ANY</code> and <code class="literal">CASE WHEN <em class="replaceable"><code>expression</code></em></code>. </p></div><div class="sect2" id="id-1.8.3.19.12"><div class="titlepage"><div><div><h3 class="title">38.16.2. Extension Files</h3></div></div></div><a id="id-1.8.3.19.12.2" class="indexterm"></a><p> The <a class="xref" href="sql-createextension.html" title="CREATE EXTENSION"><span class="refentrytitle">CREATE EXTENSION</span></a> command relies on a control file for each extension, which must be named the same as the extension with a suffix of <code class="literal">.control</code>, and must be placed in the installation's <code class="literal">SHAREDIR/extension</code> directory. There must also be at least one <acronym class="acronym">SQL</acronym> script file, which follows the naming pattern <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>version</code></em>.sql</code> (for example, <code class="literal">foo--1.0.sql</code> for version <code class="literal">1.0</code> of extension <code class="literal">foo</code>). By default, the script file(s) are also placed in the <code class="literal">SHAREDIR/extension</code> directory; but the control file can specify a different directory for the script file(s). </p><p> The file format for an extension control file is the same as for the <code class="filename">postgresql.conf</code> file, namely a list of <em class="replaceable"><code>parameter_name</code></em> <code class="literal">=</code> <em class="replaceable"><code>value</code></em> assignments, one per line. Blank lines and comments introduced by <code class="literal">#</code> are allowed. Be sure to quote any value that is not a single word or number. </p><p> A control file can set the following parameters: </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="varname">directory</code> (<code class="type">string</code>)</span></dt><dd><p> The directory containing the extension's <acronym class="acronym">SQL</acronym> script file(s). Unless an absolute path is given, the name is relative to the installation's <code class="literal">SHAREDIR</code> directory. The default behavior is equivalent to specifying <code class="literal">directory = 'extension'</code>. </p></dd><dt><span class="term"><code class="varname">default_version</code> (<code class="type">string</code>)</span></dt><dd><p> The default version of the extension (the one that will be installed if no version is specified in <code class="command">CREATE EXTENSION</code>). Although this can be omitted, that will result in <code class="command">CREATE EXTENSION</code> failing if no <code class="literal">VERSION</code> option appears, so you generally don't want to do that. </p></dd><dt><span class="term"><code class="varname">comment</code> (<code class="type">string</code>)</span></dt><dd><p> A comment (any string) about the extension. The comment is applied when initially creating an extension, but not during extension updates (since that might override user-added comments). Alternatively, the extension's comment can be set by writing a <a class="xref" href="sql-comment.html" title="COMMENT"><span class="refentrytitle">COMMENT</span></a> command in the script file. </p></dd><dt><span class="term"><code class="varname">encoding</code> (<code class="type">string</code>)</span></dt><dd><p> The character set encoding used by the script file(s). This should be specified if the script files contain any non-ASCII characters. Otherwise the files will be assumed to be in the database encoding. </p></dd><dt><span class="term"><code class="varname">module_pathname</code> (<code class="type">string</code>)</span></dt><dd><p> The value of this parameter will be substituted for each occurrence of <code class="literal">MODULE_PATHNAME</code> in the script file(s). If it is not set, no substitution is made. Typically, this is set to <code class="literal">$libdir/<em class="replaceable"><code>shared_library_name</code></em></code> and then <code class="literal">MODULE_PATHNAME</code> is used in <code class="command">CREATE FUNCTION</code> commands for C-language functions, so that the script files do not need to hard-wire the name of the shared library. </p></dd><dt><span class="term"><code class="varname">requires</code> (<code class="type">string</code>)</span></dt><dd><p> A list of names of extensions that this extension depends on, for example <code class="literal">requires = 'foo, bar'</code>. Those extensions must be installed before this one can be installed. </p></dd><dt><span class="term"><code class="varname">superuser</code> (<code class="type">boolean</code>)</span></dt><dd><p> If this parameter is <code class="literal">true</code> (which is the default), only superusers can create the extension or update it to a new version. If it is set to <code class="literal">false</code>, just the privileges required to execute the commands in the installation or update script are required. </p></dd><dt><span class="term"><code class="varname">relocatable</code> (<code class="type">boolean</code>)</span></dt><dd><p> An extension is <em class="firstterm">relocatable</em> if it is possible to move its contained objects into a different schema after initial creation of the extension. The default is <code class="literal">false</code>, i.e. the extension is not relocatable. See <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION" title="38.16.3. Extension Relocatability">Section 38.16.3</a> for more information. </p></dd><dt><span class="term"><code class="varname">schema</code> (<code class="type">string</code>)</span></dt><dd><p> This parameter can only be set for non-relocatable extensions. It forces the extension to be loaded into exactly the named schema and not any other. The <code class="varname">schema</code> parameter is consulted only when initially creating an extension, not during extension updates. See <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION" title="38.16.3. Extension Relocatability">Section 38.16.3</a> for more information. </p></dd></dl></div><p> In addition to the primary control file <code class="literal"><em class="replaceable"><code>extension</code></em>.control</code>, an extension can have secondary control files named in the style <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>version</code></em>.control</code>. If supplied, these must be located in the script file directory. Secondary control files follow the same format as the primary control file. Any parameters set in a secondary control file override the primary control file when installing or updating to that version of the extension. However, the parameters <code class="varname">directory</code> and <code class="varname">default_version</code> cannot be set in a secondary control file. </p><p> An extension's <acronym class="acronym">SQL</acronym> script files can contain any SQL commands, except for transaction control commands (<code class="command">BEGIN</code>, <code class="command">COMMIT</code>, etc) and commands that cannot be executed inside a transaction block (such as <code class="command">VACUUM</code>). This is because the script files are implicitly executed within a transaction block. </p><p> An extension's <acronym class="acronym">SQL</acronym> script files can also contain lines beginning with <code class="literal">\echo</code>, which will be ignored (treated as comments) by the extension mechanism. This provision is commonly used to throw an error if the script file is fed to <span class="application">psql</span> rather than being loaded via <code class="command">CREATE EXTENSION</code> (see example script in <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE" title="38.16.7. Extension Example">Section 38.16.7</a>). Without that, users might accidentally load the extension's contents as <span class="quote">“<span class="quote">loose</span>”</span> objects rather than as an extension, a state of affairs that's a bit tedious to recover from. </p><p> While the script files can contain any characters allowed by the specified encoding, control files should contain only plain ASCII, because there is no way for <span class="productname">PostgreSQL</span> to know what encoding a control file is in. In practice this is only an issue if you want to use non-ASCII characters in the extension's comment. Recommended practice in that case is to not use the control file <code class="varname">comment</code> parameter, but instead use <code class="command">COMMENT ON EXTENSION</code> within a script file to set the comment. </p></div><div class="sect2" id="EXTEND-EXTENSIONS-RELOCATION"><div class="titlepage"><div><div><h3 class="title">38.16.3. Extension Relocatability</h3></div></div></div><p> Users often wish to load the objects contained in an extension into a different schema than the extension's author had in mind. There are three supported levels of relocatability: </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> A fully relocatable extension can be moved into another schema at any time, even after it's been loaded into a database. This is done with the <code class="command">ALTER EXTENSION SET SCHEMA</code> command, which automatically renames all the member objects into the new schema. Normally, this is only possible if the extension contains no internal assumptions about what schema any of its objects are in. Also, the extension's objects must all be in one schema to begin with (ignoring objects that do not belong to any schema, such as procedural languages). Mark a fully relocatable extension by setting <code class="literal">relocatable = true</code> in its control file. </p></li><li class="listitem"><p> An extension might be relocatable during installation but not afterwards. This is typically the case if the extension's script file needs to reference the target schema explicitly, for example in setting <code class="literal">search_path</code> properties for SQL functions. For such an extension, set <code class="literal">relocatable = false</code> in its control file, and use <code class="literal">@extschema@</code> to refer to the target schema in the script file. All occurrences of this string will be replaced by the actual target schema's name before the script is executed. The user can set the target schema using the <code class="literal">SCHEMA</code> option of <code class="command">CREATE EXTENSION</code>. </p></li><li class="listitem"><p> If the extension does not support relocation at all, set <code class="literal">relocatable = false</code> in its control file, and also set <code class="literal">schema</code> to the name of the intended target schema. This will prevent use of the <code class="literal">SCHEMA</code> option of <code class="command">CREATE EXTENSION</code>, unless it specifies the same schema named in the control file. This choice is typically necessary if the extension contains internal assumptions about schema names that can't be replaced by uses of <code class="literal">@extschema@</code>. The <code class="literal">@extschema@</code> substitution mechanism is available in this case too, although it is of limited use since the schema name is determined by the control file. </p></li></ul></div><p> In all cases, the script file will be executed with <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> initially set to point to the target schema; that is, <code class="command">CREATE EXTENSION</code> does the equivalent of this: </p><pre class="programlisting"> SET LOCAL search_path TO @extschema@; </pre><p> This allows the objects created by the script file to go into the target schema. The script file can change <code class="varname">search_path</code> if it wishes, but that is generally undesirable. <code class="varname">search_path</code> is restored to its previous setting upon completion of <code class="command">CREATE EXTENSION</code>. </p><p> The target schema is determined by the <code class="varname">schema</code> parameter in the control file if that is given, otherwise by the <code class="literal">SCHEMA</code> option of <code class="command">CREATE EXTENSION</code> if that is given, otherwise the current default object creation schema (the first one in the caller's <code class="varname">search_path</code>). When the control file <code class="varname">schema</code> parameter is used, the target schema will be created if it doesn't already exist, but in the other two cases it must already exist. </p><p> If any prerequisite extensions are listed in <code class="varname">requires</code> in the control file, their target schemas are appended to the initial setting of <code class="varname">search_path</code>. This allows their objects to be visible to the new extension's script file. </p><p> Although a non-relocatable extension can contain objects spread across multiple schemas, it is usually desirable to place all the objects meant for external use into a single schema, which is considered the extension's target schema. Such an arrangement works conveniently with the default setting of <code class="varname">search_path</code> during creation of dependent extensions. </p></div><div class="sect2" id="EXTEND-EXTENSIONS-CONFIG-TABLES"><div class="titlepage"><div><div><h3 class="title">38.16.4. Extension Configuration Tables</h3></div></div></div><p> Some extensions include configuration tables, which contain data that might be added or changed by the user after installation of the extension. Ordinarily, if a table is part of an extension, neither the table's definition nor its content will be dumped by <span class="application">pg_dump</span>. But that behavior is undesirable for a configuration table; any data changes made by the user need to be included in dumps, or the extension will behave differently after a dump and reload. </p><a id="id-1.8.3.19.14.3" class="indexterm"></a><p> To solve this problem, an extension's script file can mark a table or a sequence it has created as a configuration relation, which will cause <span class="application">pg_dump</span> to include the table's or the sequence's contents (not its definition) in dumps. To do that, call the function <code class="function">pg_extension_config_dump(regclass, text)</code> after creating the table or the sequence, for example </p><pre class="programlisting"> CREATE TABLE my_config (key text, value text); CREATE SEQUENCE my_config_seq; SELECT pg_catalog.pg_extension_config_dump('my_config', ''); SELECT pg_catalog.pg_extension_config_dump('my_config_seq', ''); </pre><p> Any number of tables or sequences can be marked this way. Sequences associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns can be marked as well. </p><p> When the second argument of <code class="function">pg_extension_config_dump</code> is an empty string, the entire contents of the table are dumped by <span class="application">pg_dump</span>. This is usually only correct if the table is initially empty as created by the extension script. If there is a mixture of initial data and user-provided data in the table, the second argument of <code class="function">pg_extension_config_dump</code> provides a <code class="literal">WHERE</code> condition that selects the data to be dumped. For example, you might do </p><pre class="programlisting"> CREATE TABLE my_config (key text, value text, standard_entry boolean); SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry'); </pre><p> and then make sure that <code class="structfield">standard_entry</code> is true only in the rows created by the extension's script. </p><p> For sequences, the second argument of <code class="function">pg_extension_config_dump</code> has no effect. </p><p> More complicated situations, such as initially-provided rows that might be modified by users, can be handled by creating triggers on the configuration table to ensure that modified rows are marked correctly. </p><p> You can alter the filter condition associated with a configuration table by calling <code class="function">pg_extension_config_dump</code> again. (This would typically be useful in an extension update script.) The only way to mark a table as no longer a configuration table is to dissociate it from the extension with <code class="command">ALTER EXTENSION ... DROP TABLE</code>. </p><p> Note that foreign key relationships between these tables will dictate the order in which the tables are dumped out by pg_dump. Specifically, pg_dump will attempt to dump the referenced-by table before the referencing table. As the foreign key relationships are set up at CREATE EXTENSION time (prior to data being loaded into the tables) circular dependencies are not supported. When circular dependencies exist, the data will still be dumped out but the dump will not be able to be restored directly and user intervention will be required. </p><p> Sequences associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns need to be directly marked to dump their state. Marking their parent relation is not enough for this purpose. </p></div><div class="sect2" id="id-1.8.3.19.15"><div class="titlepage"><div><div><h3 class="title">38.16.5. Extension Updates</h3></div></div></div><p> One advantage of the extension mechanism is that it provides convenient ways to manage updates to the SQL commands that define an extension's objects. This is done by associating a version name or number with each released version of the extension's installation script. In addition, if you want users to be able to update their databases dynamically from one version to the next, you should provide <em class="firstterm">update scripts</em> that make the necessary changes to go from one version to the next. Update scripts have names following the pattern <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>oldversion</code></em>--<em class="replaceable"><code>newversion</code></em>.sql</code> (for example, <code class="literal">foo--1.0--1.1.sql</code> contains the commands to modify version <code class="literal">1.0</code> of extension <code class="literal">foo</code> into version <code class="literal">1.1</code>). </p><p> Given that a suitable update script is available, the command <code class="command">ALTER EXTENSION UPDATE</code> will update an installed extension to the specified new version. The update script is run in the same environment that <code class="command">CREATE EXTENSION</code> provides for installation scripts: in particular, <code class="varname">search_path</code> is set up in the same way, and any new objects created by the script are automatically added to the extension. Also, if the script chooses to drop extension member objects, they are automatically dissociated from the extension. </p><p> If an extension has secondary control files, the control parameters that are used for an update script are those associated with the script's target (new) version. </p><p> The update mechanism can be used to solve an important special case: converting a <span class="quote">“<span class="quote">loose</span>”</span> collection of objects into an extension. Before the extension mechanism was added to <span class="productname">PostgreSQL</span> (in 9.1), many people wrote extension modules that simply created assorted unpackaged objects. Given an existing database containing such objects, how can we convert the objects into a properly packaged extension? Dropping them and then doing a plain <code class="command">CREATE EXTENSION</code> is one way, but it's not desirable if the objects have dependencies (for example, if there are table columns of a data type created by the extension). The way to fix this situation is to create an empty extension, then use <code class="command">ALTER EXTENSION ADD</code> to attach each pre-existing object to the extension, then finally create any new objects that are in the current extension version but were not in the unpackaged release. <code class="command">CREATE EXTENSION</code> supports this case with its <code class="literal">FROM</code> <em class="replaceable"><code>old_version</code></em> option, which causes it to not run the normal installation script for the target version, but instead the update script named <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>old_version</code></em>--<em class="replaceable"><code>target_version</code></em>.sql</code>. The choice of the dummy version name to use as <em class="replaceable"><code>old_version</code></em> is up to the extension author, though <code class="literal">unpackaged</code> is a common convention. If you have multiple prior versions you need to be able to update into extension style, use multiple dummy version names to identify them. </p><p> <code class="command">ALTER EXTENSION</code> is able to execute sequences of update script files to achieve a requested update. For example, if only <code class="literal">foo--1.0--1.1.sql</code> and <code class="literal">foo--1.1--2.0.sql</code> are available, <code class="command">ALTER EXTENSION</code> will apply them in sequence if an update to version <code class="literal">2.0</code> is requested when <code class="literal">1.0</code> is currently installed. </p><p> <span class="productname">PostgreSQL</span> doesn't assume anything about the properties of version names: for example, it does not know whether <code class="literal">1.1</code> follows <code class="literal">1.0</code>. It just matches up the available version names and follows the path that requires applying the fewest update scripts. (A version name can actually be any string that doesn't contain <code class="literal">--</code> or leading or trailing <code class="literal">-</code>.) </p><p> Sometimes it is useful to provide <span class="quote">“<span class="quote">downgrade</span>”</span> scripts, for example <code class="literal">foo--1.1--1.0.sql</code> to allow reverting the changes associated with version <code class="literal">1.1</code>. If you do that, be careful of the possibility that a downgrade script might unexpectedly get applied because it yields a shorter path. The risky case is where there is a <span class="quote">“<span class="quote">fast path</span>”</span> update script that jumps ahead several versions as well as a downgrade script to the fast path's start point. It might take fewer steps to apply the downgrade and then the fast path than to move ahead one version at a time. If the downgrade script drops any irreplaceable objects, this will yield undesirable results. </p><p> To check for unexpected update paths, use this command: </p><pre class="programlisting"> SELECT * FROM pg_extension_update_paths('<em class="replaceable"><code>extension_name</code></em>'); </pre><p> This shows each pair of distinct known version names for the specified extension, together with the update path sequence that would be taken to get from the source version to the target version, or <code class="literal">NULL</code> if there is no available update path. The path is shown in textual form with <code class="literal">--</code> separators. You can use <code class="literal">regexp_split_to_array(path,'--')</code> if you prefer an array format. </p></div><div class="sect2" id="id-1.8.3.19.16"><div class="titlepage"><div><div><h3 class="title">38.16.6. Installing Extensions using Update Scripts</h3></div></div></div><p> An extension that has been around for awhile will probably exist in several versions, for which the author will need to write update scripts. For example, if you have released a <code class="literal">foo</code> extension in versions <code class="literal">1.0</code>, <code class="literal">1.1</code>, and <code class="literal">1.2</code>, there should be update scripts <code class="filename">foo--1.0--1.1.sql</code> and <code class="filename">foo--1.1--1.2.sql</code>. Before <span class="productname">PostgreSQL</span> 10, it was necessary to also create new script files <code class="filename">foo--1.1.sql</code> and <code class="filename">foo--1.2.sql</code> that directly build the newer extension versions, or else the newer versions could not be installed directly, only by installing <code class="literal">1.0</code> and then updating. That was tedious and duplicative, but now it's unnecessary, because <code class="command">CREATE EXTENSION</code> can follow update chains automatically. For example, if only the script files <code class="filename">foo--1.0.sql</code>, <code class="filename">foo--1.0--1.1.sql</code>, and <code class="filename">foo--1.1--1.2.sql</code> are available then a request to install version <code class="literal">1.2</code> is honored by running those three scripts in sequence. The processing is the same as if you'd first installed <code class="literal">1.0</code> and then updated to <code class="literal">1.2</code>. (As with <code class="command">ALTER EXTENSION UPDATE</code>, if multiple pathways are available then the shortest is preferred.) Arranging an extension's script files in this style can reduce the amount of maintenance effort needed to produce small updates. </p><p> If you use secondary (version-specific) control files with an extension maintained in this style, keep in mind that each version needs a control file even if it has no stand-alone installation script, as that control file will determine how the implicit update to that version is performed. For example, if <code class="filename">foo--1.0.control</code> specifies <code class="literal">requires = 'bar'</code> but <code class="literal">foo</code>'s other control files do not, the extension's dependency on <code class="literal">bar</code> will be dropped when updating from <code class="literal">1.0</code> to another version. </p></div><div class="sect2" id="EXTEND-EXTENSIONS-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">38.16.7. Extension Example</h3></div></div></div><p> Here is a complete example of an <acronym class="acronym">SQL</acronym>-only extension, a two-element composite type that can store any type of value in its slots, which are named <span class="quote">“<span class="quote">k</span>”</span> and <span class="quote">“<span class="quote">v</span>”</span>. Non-text values are automatically coerced to text for storage. </p><p> The script file <code class="filename">pair--1.0.sql</code> looks like this: </p><pre class="programlisting"> -- complain if script is sourced in psql, rather than via CREATE EXTENSION \echo Use "CREATE EXTENSION pair" to load this file. \quit CREATE TYPE pair AS ( k text, v text ); CREATE OR REPLACE FUNCTION pair(text, text) RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;'; CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair); -- "SET search_path" is easy to get right, but qualified names perform better. CREATE OR REPLACE FUNCTION lower(pair) RETURNS pair LANGUAGE SQL AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;' SET search_path = pg_temp; CREATE OR REPLACE FUNCTION pair_concat(pair, pair) RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k, $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;'; </pre><p> </p><p> The control file <code class="filename">pair.control</code> looks like this: </p><pre class="programlisting"> # pair extension comment = 'A key/value pair data type' default_version = '1.0' relocatable = false </pre><p> </p><p> While you hardly need a makefile to install these two files into the correct directory, you could use a <code class="filename">Makefile</code> containing this: </p><pre class="programlisting"> EXTENSION = pair DATA = pair--1.0.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) </pre><p> This makefile relies on <acronym class="acronym">PGXS</acronym>, which is described in <a class="xref" href="extend-pgxs.html" title="38.17. Extension Building Infrastructure">Section 38.17</a>. The command <code class="literal">make install</code> will install the control and script files into the correct directory as reported by <span class="application">pg_config</span>. </p><p> Once the files are installed, use the <a class="xref" href="sql-createextension.html" title="CREATE EXTENSION"><span class="refentrytitle">CREATE EXTENSION</span></a> command to load the objects into any particular database. </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xindex.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="extend-pgxs.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">38.15. Interfacing Extensions To Indexes </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 38.17. Extension Building Infrastructure</td></tr></table></div></body></html>