1<!--
2doc/src/sgml/ref/alter_extension.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="SQL-ALTEREXTENSION">
7 <indexterm zone="sql-alterextension">
8  <primary>ALTER EXTENSION</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>ALTER EXTENSION</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>ALTER EXTENSION</refname>
19  <refpurpose>
20   change the definition of an extension
21  </refpurpose>
22 </refnamediv>
23
24 <refsynopsisdiv>
25<synopsis>
26ALTER EXTENSION <replaceable class="PARAMETER">name</replaceable> UPDATE [ TO <replaceable class="PARAMETER">new_version</replaceable> ]
27ALTER EXTENSION <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
28ALTER EXTENSION <replaceable class="PARAMETER">name</replaceable> ADD <replaceable class="PARAMETER">member_object</replaceable>
29ALTER EXTENSION <replaceable class="PARAMETER">name</replaceable> DROP <replaceable class="PARAMETER">member_object</replaceable>
30
31<phrase>where <replaceable class="PARAMETER">member_object</replaceable> is:</phrase>
32
33  ACCESS METHOD <replaceable class="PARAMETER">object_name</replaceable> |
34  AGGREGATE <replaceable class="PARAMETER">aggregate_name</replaceable> ( <replaceable>aggregate_signature</replaceable> ) |
35  CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>) |
36  COLLATION <replaceable class="PARAMETER">object_name</replaceable> |
37  CONVERSION <replaceable class="PARAMETER">object_name</replaceable> |
38  DOMAIN <replaceable class="PARAMETER">object_name</replaceable> |
39  EVENT TRIGGER <replaceable class="PARAMETER">object_name</replaceable> |
40  FOREIGN DATA WRAPPER <replaceable class="PARAMETER">object_name</replaceable> |
41  FOREIGN TABLE <replaceable class="PARAMETER">object_name</replaceable> |
42  FUNCTION <replaceable class="PARAMETER">function_name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ] |
43  MATERIALIZED VIEW <replaceable class="PARAMETER">object_name</replaceable> |
44  OPERATOR <replaceable class="PARAMETER">operator_name</replaceable> (<replaceable class="PARAMETER">left_type</replaceable>, <replaceable class="PARAMETER">right_type</replaceable>) |
45  OPERATOR CLASS <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
46  OPERATOR FAMILY <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
47  [ PROCEDURAL ] LANGUAGE <replaceable class="PARAMETER">object_name</replaceable> |
48  SCHEMA <replaceable class="PARAMETER">object_name</replaceable> |
49  SEQUENCE <replaceable class="PARAMETER">object_name</replaceable> |
50  SERVER <replaceable class="PARAMETER">object_name</replaceable> |
51  TABLE <replaceable class="PARAMETER">object_name</replaceable> |
52  TEXT SEARCH CONFIGURATION <replaceable class="PARAMETER">object_name</replaceable> |
53  TEXT SEARCH DICTIONARY <replaceable class="PARAMETER">object_name</replaceable> |
54  TEXT SEARCH PARSER <replaceable class="PARAMETER">object_name</replaceable> |
55  TEXT SEARCH TEMPLATE <replaceable class="PARAMETER">object_name</replaceable> |
56  TRANSFORM FOR <replaceable>type_name</replaceable> LANGUAGE <replaceable>lang_name</replaceable> |
57  TYPE <replaceable class="PARAMETER">object_name</replaceable> |
58  VIEW <replaceable class="PARAMETER">object_name</replaceable>
59
60<phrase>and <replaceable>aggregate_signature</replaceable> is:</phrase>
61
62* |
63[ <replaceable>argmode</replaceable> ] [ <replaceable>argname</replaceable> ] <replaceable>argtype</replaceable> [ , ... ] |
64[ [ <replaceable>argmode</replaceable> ] [ <replaceable>argname</replaceable> ] <replaceable>argtype</replaceable> [ , ... ] ] ORDER BY [ <replaceable>argmode</replaceable> ] [ <replaceable>argname</replaceable> ] <replaceable>argtype</replaceable> [ , ... ]
65</synopsis>
66 </refsynopsisdiv>
67
68 <refsect1>
69  <title>Description</title>
70
71  <para>
72   <command>ALTER EXTENSION</command> changes the definition of an installed
73   extension.  There are several subforms:
74
75   <variablelist>
76   <varlistentry>
77    <term><literal>UPDATE</literal></term>
78    <listitem>
79     <para>
80      This form updates the extension to a newer version.  The extension
81      must supply a suitable update script (or series of scripts) that can
82      modify the currently-installed version into the requested version.
83     </para>
84    </listitem>
85   </varlistentry>
86
87   <varlistentry>
88    <term><literal>SET SCHEMA</literal></term>
89    <listitem>
90     <para>
91      This form moves the extension's objects into another schema. The
92      extension has to be <firstterm>relocatable</> for this command to
93      succeed.
94     </para>
95    </listitem>
96   </varlistentry>
97
98   <varlistentry>
99    <term><literal>ADD <replaceable class="PARAMETER">member_object</replaceable></literal></term>
100    <listitem>
101     <para>
102      This form adds an existing object to the extension.  This is mainly
103      useful in extension update scripts.  The object will subsequently
104      be treated as a member of the extension; notably, it can only be
105      dropped by dropping the extension.
106     </para>
107    </listitem>
108   </varlistentry>
109
110   <varlistentry>
111    <term><literal>DROP <replaceable class="PARAMETER">member_object</replaceable></literal></term>
112    <listitem>
113     <para>
114      This form removes a member object from the extension.  This is mainly
115      useful in extension update scripts.  The object is not dropped, only
116      disassociated from the extension.
117     </para>
118    </listitem>
119   </varlistentry>
120   </variablelist>
121
122   See <xref linkend="extend-extensions"> for more information about these
123   operations.
124  </para>
125
126  <para>
127   You must own the extension to use <command>ALTER EXTENSION</command>.
128   The <literal>ADD</>/<literal>DROP</> forms require ownership of the
129   added/dropped object as well.
130  </para>
131 </refsect1>
132
133 <refsect1>
134  <title>Parameters</title>
135
136  <para>
137   <variablelist>
138    <varlistentry>
139     <term><replaceable class="PARAMETER">name</replaceable></term>
140     <listitem>
141      <para>
142       The name of an installed extension.
143      </para>
144     </listitem>
145    </varlistentry>
146
147    <varlistentry>
148     <term><replaceable class="PARAMETER">new_version</replaceable></term>
149     <listitem>
150      <para>
151       The desired new version of the extension.  This can be written as
152       either an identifier or a string literal.  If not specified,
153       <command>ALTER EXTENSION UPDATE</> attempts to update to whatever is
154       shown as the default version in the extension's control file.
155      </para>
156     </listitem>
157    </varlistentry>
158
159    <varlistentry>
160     <term><replaceable class="PARAMETER">new_schema</replaceable></term>
161     <listitem>
162      <para>
163       The new schema for the extension.
164      </para>
165     </listitem>
166    </varlistentry>
167
168    <varlistentry>
169     <term><replaceable class="parameter">object_name</replaceable></term>
170     <term><replaceable class="parameter">aggregate_name</replaceable></term>
171     <term><replaceable class="parameter">function_name</replaceable></term>
172     <term><replaceable class="parameter">operator_name</replaceable></term>
173     <listitem>
174      <para>
175       The name of an object to be added to or removed from the extension.
176       Names of tables,
177       aggregates, domains, foreign tables, functions, operators,
178       operator classes, operator families, sequences, text search objects,
179       types, and views can be schema-qualified.
180      </para>
181     </listitem>
182    </varlistentry>
183
184    <varlistentry>
185     <term><replaceable>source_type</replaceable></term>
186     <listitem>
187      <para>
188       The name of the source data type of the cast.
189      </para>
190     </listitem>
191    </varlistentry>
192
193    <varlistentry>
194     <term><replaceable>target_type</replaceable></term>
195     <listitem>
196      <para>
197       The name of the target data type of the cast.
198      </para>
199     </listitem>
200    </varlistentry>
201
202    <varlistentry>
203     <term><replaceable class="parameter">argmode</replaceable></term>
204
205     <listitem>
206      <para>
207       The mode of a function or aggregate
208       argument: <literal>IN</>, <literal>OUT</>,
209       <literal>INOUT</>, or <literal>VARIADIC</>.
210       If omitted, the default is <literal>IN</>.
211       Note that <command>ALTER EXTENSION</command> does not actually pay
212       any attention to <literal>OUT</> arguments, since only the input
213       arguments are needed to determine the function's identity.
214       So it is sufficient to list the <literal>IN</>, <literal>INOUT</>,
215       and <literal>VARIADIC</> arguments.
216      </para>
217     </listitem>
218    </varlistentry>
219
220    <varlistentry>
221     <term><replaceable class="parameter">argname</replaceable></term>
222
223     <listitem>
224      <para>
225       The name of a function or aggregate argument.
226       Note that <command>ALTER EXTENSION</command> does not actually pay
227       any attention to argument names, since only the argument data
228       types are needed to determine the function's identity.
229      </para>
230     </listitem>
231    </varlistentry>
232
233    <varlistentry>
234     <term><replaceable class="parameter">argtype</replaceable></term>
235
236     <listitem>
237      <para>
238       The data type of a function or aggregate argument.
239      </para>
240     </listitem>
241    </varlistentry>
242
243    <varlistentry>
244     <term><replaceable class="parameter">left_type</replaceable></term>
245     <term><replaceable class="parameter">right_type</replaceable></term>
246     <listitem>
247      <para>
248       The data type(s) of the operator's arguments (optionally
249       schema-qualified).  Write <literal>NONE</> for the missing argument
250       of a prefix or postfix operator.
251      </para>
252     </listitem>
253    </varlistentry>
254
255    <varlistentry>
256     <term><literal>PROCEDURAL</literal></term>
257
258     <listitem>
259      <para>
260       This is a noise word.
261      </para>
262     </listitem>
263    </varlistentry>
264
265    <varlistentry>
266     <term><replaceable>type_name</replaceable></term>
267
268     <listitem>
269      <para>
270       The name of the data type of the transform.
271      </para>
272     </listitem>
273    </varlistentry>
274
275    <varlistentry>
276     <term><replaceable>lang_name</replaceable></term>
277
278     <listitem>
279      <para>
280       The name of the language of the transform.
281      </para>
282     </listitem>
283    </varlistentry>
284   </variablelist>
285  </para>
286 </refsect1>
287
288 <refsect1>
289  <title>Examples</title>
290
291  <para>
292   To update the <literal>hstore</literal> extension to version 2.0:
293<programlisting>
294ALTER EXTENSION hstore UPDATE TO '2.0';
295</programlisting>
296  </para>
297
298  <para>
299   To change the schema of the <literal>hstore</literal> extension
300   to <literal>utils</literal>:
301<programlisting>
302ALTER EXTENSION hstore SET SCHEMA utils;
303</programlisting>
304  </para>
305
306  <para>
307   To add an existing function to the <literal>hstore</literal> extension:
308<programlisting>
309ALTER EXTENSION hstore ADD FUNCTION populate_record(anyelement, hstore);
310</programlisting></para>
311 </refsect1>
312
313 <refsect1>
314  <title>Compatibility</title>
315
316  <para>
317   <command>ALTER EXTENSION</command> is a <productname>PostgreSQL</>
318   extension.
319  </para>
320 </refsect1>
321
322 <refsect1 id="SQL-ALTEREXTENSION-see-also">
323  <title>See Also</title>
324
325  <simplelist type="inline">
326   <member><xref linkend="sql-createextension"></member>
327   <member><xref linkend="sql-dropextension"></member>
328  </simplelist>
329 </refsect1>
330</refentry>
331