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