1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
<!--
doc/src/sgml/ref/alter_materialized_view.sgml
PostgreSQL documentation
-->
<refentry id="sql-altermaterializedview">
<indexterm zone="sql-altermaterializedview">
<primary>ALTER MATERIALIZED VIEW</primary>
</indexterm>
<refmeta>
<refentrytitle>ALTER MATERIALIZED VIEW</refentrytitle>
<manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>ALTER MATERIALIZED VIEW</refname>
<refpurpose>change the definition of a materialized view</refpurpose>
</refnamediv>
<refsynopsisdiv>
<synopsis>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
<replaceable class="parameter">action</replaceable> [, ... ]
ALTER MATERIALIZED VIEW <replaceable class="parameter">name</replaceable>
[ NO ] DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
RENAME [ COLUMN ] <replaceable class="parameter">column_name</replaceable> TO <replaceable class="parameter">new_column_name</replaceable>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
RENAME TO <replaceable class="parameter">new_name</replaceable>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
ALTER MATERIALIZED VIEW ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ]
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [ NOWAIT ]
<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STATISTICS <replaceable class="parameter">integer</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
CLUSTER ON <replaceable class="parameter">index_name</replaceable>
SET WITHOUT CLUSTER
SET ACCESS METHOD <replaceable class="parameter">new_access_method</replaceable>
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )
OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
</synopsis>
</refsynopsisdiv>
<refsect1>
<title>Description</title>
<para>
<command>ALTER MATERIALIZED VIEW</command> changes various auxiliary
properties of an existing materialized view.
</para>
<para>
You must own the materialized view to use <command>ALTER MATERIALIZED
VIEW</command>. To change a materialized view's schema, you must also have
<literal>CREATE</literal> privilege on the new schema.
To alter the owner, you must be able to <literal>SET ROLE</literal> to the
new owning role, and that role must have <literal>CREATE</literal>
privilege on the materialized view's schema.
(These restrictions enforce that altering
the owner doesn't do anything you couldn't do by dropping and recreating the
materialized view. However, a superuser can alter ownership of any view
anyway.)
</para>
<para>
The statement subforms and actions available for
<command>ALTER MATERIALIZED VIEW</command> are a subset of those available
for <command>ALTER TABLE</command>, and have the same meaning when used for
materialized views. See the descriptions for
<link linkend="sql-altertable"><command>ALTER TABLE</command></link>
for details.
</para>
</refsect1>
<refsect1>
<title>Parameters</title>
<variablelist>
<varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
The name (optionally schema-qualified) of an existing materialized view.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">column_name</replaceable></term>
<listitem>
<para>
Name of a new or existing column.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">extension_name</replaceable></term>
<listitem>
<para>
The name of the extension that the materialized view is to depend on (or no longer
dependent on, if <literal>NO</literal> is specified). A materialized view
that's marked as dependent on an extension is automatically dropped when
the extension is dropped.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">new_column_name</replaceable></term>
<listitem>
<para>
New name for an existing column.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">new_owner</replaceable></term>
<listitem>
<para>
The user name of the new owner of the materialized view.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">new_name</replaceable></term>
<listitem>
<para>
The new name for the materialized view.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">new_schema</replaceable></term>
<listitem>
<para>
The new schema for the materialized view.
</para>
</listitem>
</varlistentry>
</variablelist>
</refsect1>
<refsect1>
<title>Examples</title>
<para>
To rename the materialized view <literal>foo</literal> to
<literal>bar</literal>:
<programlisting>
ALTER MATERIALIZED VIEW foo RENAME TO bar;
</programlisting></para>
</refsect1>
<refsect1>
<title>Compatibility</title>
<para>
<command>ALTER MATERIALIZED VIEW</command> is a
<productname>PostgreSQL</productname> extension.
</para>
</refsect1>
<refsect1>
<title>See Also</title>
<simplelist type="inline">
<member><xref linkend="sql-creatematerializedview"/></member>
<member><xref linkend="sql-dropmaterializedview"/></member>
<member><xref linkend="sql-refreshmaterializedview"/></member>
</simplelist>
</refsect1>
</refentry>
|
