ALTER EXTENSION

ALTER EXTENSION

7

SQL - Language Statements

ALTER EXTENSION

change the definition of an extension

ALTER EXTENSION

name

UPDATE \[ TO

new\_version

\] ALTER EXTENSION

name

SET SCHEMA

new\_schema

ALTER EXTENSION

name

ADD

member\_object

ALTER EXTENSION

name

DROP

member\_object

where

member\_object

is:

ACCESS METHOD

object\_name

\| AGGREGATE

aggregate\_name

(

aggregate\_signature

) \| CAST (

source\_type

AS

target\_type

) \| COLLATION

object\_name

\| CONVERSION

object\_name

\| DOMAIN

object\_name

\| EVENT TRIGGER

object\_name

\| FOREIGN DATA WRAPPER

object\_name

\| FOREIGN TABLE

object\_name

\| FUNCTION

function\_name

\[ ( \[ \[

argmode

\] \[

argname

\]

argtype

\[, \...\] \] ) \] \| MATERIALIZED VIEW

object\_name

\| OPERATOR

operator\_name

(

left\_type

,

right\_type

) \| OPERATOR CLASS

object\_name

USING

index\_method

\| OPERATOR FAMILY

object\_name

USING

index\_method

\| \[ PROCEDURAL \] LANGUAGE

object\_name

\| PROCEDURE

procedure\_name

\[ ( \[ \[

argmode

\] \[

argname

\]

argtype

\[, \...\] \] ) \] \| ROUTINE

routine\_name

\[ ( \[ \[

argmode

\] \[

argname

\]

argtype

\[, \...\] \] ) \] \| SCHEMA

object\_name

\| SEQUENCE

object\_name

\| SERVER

object\_name

\| TABLE

object\_name

\| TEXT SEARCH CONFIGURATION

object\_name

\| TEXT SEARCH DICTIONARY

object\_name

\| TEXT SEARCH PARSER

object\_name

\| TEXT SEARCH TEMPLATE

object\_name

\| TRANSFORM FOR

type\_name

LANGUAGE

lang\_name

\| TYPE

object\_name

\| VIEW

object\_name

and

aggregate\_signature

is:

\* \| \[

argmode

\] \[

argname

\]

argtype

\[ , \... \] \| \[ \[

argmode

\] \[

argname

\]

argtype

\[ , \... \] \] ORDER BY \[

argmode

\] \[

argname

\]

argtype

\[ , \... \]

Description
===========

`ALTER EXTENSION` changes the definition of an installed extension.
There are several subforms:

`UPDATE`

:   This form updates the extension to a newer version. The extension
    must supply a suitable update script (or series of scripts) that can
    modify the currently-installed version into the requested version.

`SET SCHEMA`

:   This form moves the extension\'s objects into another schema. The
    extension has to be relocatable for this command to succeed.

`ADD member_object`

:   This form adds an existing object to the extension. This is mainly
    useful in extension update scripts. The object will subsequently be
    treated as a member of the extension; notably, it can only be
    dropped by dropping the extension.

`DROP member_object`

:   This form removes a member object from the extension. This is mainly
    useful in extension update scripts. The object is not dropped, only
    disassociated from the extension.

See [???](#extend-extensions) for more information about these
operations.

You must own the extension to use `ALTER EXTENSION`. The `ADD`/`DROP`
forms require ownership of the added/dropped object as well.

Parameters
==========

name

:   The name of an installed extension.

new\_version

:   The desired new version of the extension. This can be written as
    either an identifier or a string literal. If not specified,
    `ALTER EXTENSION UPDATE` attempts to update to whatever is shown as
    the default version in the extension\'s control file.

new\_schema

:   The new schema for the extension.

object\_name; aggregate\_name; function\_name; operator\_name; procedure\_name; routine\_name

:   The name of an object to be added to or removed from the extension.
    Names of tables, aggregates, domains, foreign tables, functions,
    operators, operator classes, operator families, procedures,
    routines, sequences, text search objects, types, and views can be
    schema-qualified.

source\_type

:   The name of the source data type of the cast.

target\_type

:   The name of the target data type of the cast.

argmode

:   The mode of a function, procedure, or aggregate argument: `IN`,
    `OUT`, `INOUT`, or `VARIADIC`. If omitted, the default is `IN`. Note
    that `ALTER EXTENSION` does not actually pay any attention to `OUT`
    arguments, since only the input arguments are needed to determine
    the function\'s identity. So it is sufficient to list the `IN`,
    `INOUT`, and `VARIADIC` arguments.

argname

:   The name of a function, procedure, or aggregate argument. Note that
    `ALTER EXTENSION` does not actually pay any attention to argument
    names, since only the argument data types are needed to determine
    the function\'s identity.

argtype

:   The data type of a function, procedure, or aggregate argument.

left\_type; right\_type

:   The data type(s) of the operator\'s arguments (optionally
    schema-qualified). Write `NONE` for the missing argument of a prefix
    operator.

`PROCEDURAL`

:   This is a noise word.

type\_name

:   The name of the data type of the transform.

lang\_name

:   The name of the language of the transform.

Examples
========

To update the `hstore` extension to version 2.0:

    ALTER EXTENSION hstore UPDATE TO '2.0';

To change the schema of the `hstore` extension to `utils`:

    ALTER EXTENSION hstore SET SCHEMA utils;

To add an existing function to the `hstore` extension:

    ALTER EXTENSION hstore ADD FUNCTION populate_record(anyelement, hstore);

Compatibility
=============

`ALTER EXTENSION` is a PostgreSQL extension.

See Also {#sql-alterextension-see-also}
========
