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