Debian Codegraph @

doc/src/sgml/ref/alter_view.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_view.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-alterview">
7 <indexterm zone="sql-alterview">
8 <primary>ALTER VIEW</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER VIEW</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER VIEW</refname>
19 <refpurpose>change the definition of a view</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
25 ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT
26 ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
27 ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME [ COLUMN ] <replaceable class="parameter">column_name</replaceable> TO <replaceable class="parameter">new_column_name</replaceable>
28 ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
29 ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
30 ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )
31 ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">view_option_name</replaceable> [, ... ] )
32 </synopsis>
33 </refsynopsisdiv>
34
35 <refsect1>
36 <title>Description</title>
37
38 <para>
39 <command>ALTER VIEW</command> changes various auxiliary properties
40 of a view. (If you want to modify the view's defining query,
41 use <command>CREATE OR REPLACE VIEW</command>.)
42 </para>
43
44 <para>
45 You must own the view to use <command>ALTER VIEW</command>.
46 To change a view's schema, you must also have <literal>CREATE</literal>
47 privilege on the new schema.
48 To alter the owner, you must be able to <literal>SET ROLE</literal> to the
49 new owning role, and that role must have <literal>CREATE</literal>
50 privilege on the view's schema.
51 (These restrictions enforce that altering the owner
52 doesn't do anything you couldn't do by dropping and recreating the view.
53 However, a superuser can alter ownership of any view anyway.)
54 </para>
55 </refsect1>
56
57 <refsect1>
58 <title>Parameters</title>
59
60 <variablelist>
61 <varlistentry>
62 <term><replaceable class="parameter">name</replaceable></term>
63 <listitem>
64 <para>
65 The name (optionally schema-qualified) of an existing view.
66 </para>
67 </listitem>
68 </varlistentry>
69
70 <varlistentry>
71 <term><replaceable class="parameter">column_name</replaceable></term>
72 <listitem>
73 <para>
74 Name of an existing column.
75 </para>
76 </listitem>
77 </varlistentry>
78
79 <varlistentry>
80 <term><replaceable class="parameter">new_column_name</replaceable></term>
81 <listitem>
82 <para>
83 New name for an existing column.
84 </para>
85 </listitem>
86 </varlistentry>
87
88 <varlistentry>
89 <term><literal>IF EXISTS</literal></term>
90 <listitem>
91 <para>
92 Do not throw an error if the view does not exist. A notice is issued
93 in this case.
94 </para>
95 </listitem>
96 </varlistentry>
97
98 <varlistentry>
99 <term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
100 <listitem>
101 <para>
102 These forms set or remove the default value for a column.
103 A view column's default value is substituted into any
104 <command>INSERT</command> or <command>UPDATE</command> command whose target is the
105 view, before applying any rules or triggers for the view. The view's
106 default will therefore take precedence over any default values from
107 underlying relations.
108 </para>
109 </listitem>
110 </varlistentry>
111
112 <varlistentry>
113 <term><replaceable class="parameter">new_owner</replaceable></term>
114 <listitem>
115 <para>
116 The user name of the new owner of the view.
117 </para>
118 </listitem>
119 </varlistentry>
120
121 <varlistentry>
122 <term><replaceable class="parameter">new_name</replaceable></term>
123 <listitem>
124 <para>
125 The new name for the view.
126 </para>
127 </listitem>
128 </varlistentry>
129
130 <varlistentry>
131 <term><replaceable class="parameter">new_schema</replaceable></term>
132 <listitem>
133 <para>
134 The new schema for the view.
135 </para>
136 </listitem>
137 </varlistentry>
138
139 <varlistentry>
140 <term><literal>SET ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )</literal></term>
141 <term><literal>RESET ( <replaceable class="parameter">view_option_name</replaceable> [, ... ] )</literal></term>
142 <listitem>
143 <para>
144 Sets or resets a view option. Currently supported options are:
145 <variablelist>
146 <varlistentry>
147 <term><literal>check_option</literal> (<type>enum</type>)</term>
148 <listitem>
149 <para>
150 Changes the check option of the view. The value must
151 be <literal>local</literal> or <literal>cascaded</literal>.
152 </para>
153 </listitem>
154 </varlistentry>
155 <varlistentry>
156 <term><literal>security_barrier</literal> (<type>boolean</type>)</term>
157 <listitem>
158 <para>
159 Changes the security-barrier property of the view. The value must
160 be a Boolean value, such as <literal>true</literal>
161 or <literal>false</literal>.
162 </para>
163 </listitem>
164 </varlistentry>
165 <varlistentry>
166 <term><literal>security_invoker</literal> (<type>boolean</type>)</term>
167 <listitem>
168 <para>
169 Changes the security-invoker property of the view. The value must
170 be a Boolean value, such as <literal>true</literal>
171 or <literal>false</literal>.
172 </para>
173 </listitem>
174 </varlistentry>
175 </variablelist></para>
176 </listitem>
177 </varlistentry>
178 </variablelist>
179 </refsect1>
180
181 <refsect1>
182 <title>Notes</title>
183
184 <para>
185 For historical reasons, <command>ALTER TABLE</command> can be used with
186 views too; but the only variants of <command>ALTER TABLE</command>
187 that are allowed with views are equivalent to the ones shown above.
188 </para>
189 </refsect1>
190
191 <refsect1>
192 <title>Examples</title>
193
194 <para>
195 To rename the view <literal>foo</literal> to
196 <literal>bar</literal>:
197 <programlisting>
198 ALTER VIEW foo RENAME TO bar;
199 </programlisting>
200 </para>
201
202 <para>
203 To attach a default column value to an updatable view:
204 <programlisting>
205 CREATE TABLE base_table (id int, ts timestamptz);
206 CREATE VIEW a_view AS SELECT * FROM base_table;
207 ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
208 INSERT INTO base_table(id) VALUES(1); -- ts will receive a NULL
209 INSERT INTO a_view(id) VALUES(2); -- ts will receive the current time
210 </programlisting></para>
211 </refsect1>
212
213 <refsect1>
214 <title>Compatibility</title>
215
216 <para>
217 <command>ALTER VIEW</command> is a <productname>PostgreSQL</productname>
218 extension of the SQL standard.
219 </para>
220 </refsect1>
221
222 <refsect1>
223 <title>See Also</title>
224
225 <simplelist type="inline">
226 <member><xref linkend="sql-createview"/></member>
227 <member><xref linkend="sql-dropview"/></member>
228 </simplelist>
229 </refsect1>
230 </refentry>