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
|
'\" t
.\" Title: ALTER VIEW
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\" Date: 2024
.\" Manual: PostgreSQL 15.6 Documentation
.\" Source: PostgreSQL 15.6
.\" Language: English
.\"
.TH "ALTER VIEW" "7" "2024" "PostgreSQL 15.6" "PostgreSQL 15.6 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
ALTER_VIEW \- change the definition of a view
.SH "SYNOPSIS"
.sp
.nf
ALTER VIEW [ IF EXISTS ] \fIname\fR ALTER [ COLUMN ] \fIcolumn_name\fR SET DEFAULT \fIexpression\fR
ALTER VIEW [ IF EXISTS ] \fIname\fR ALTER [ COLUMN ] \fIcolumn_name\fR DROP DEFAULT
ALTER VIEW [ IF EXISTS ] \fIname\fR OWNER TO { \fInew_owner\fR | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] \fIname\fR RENAME [ COLUMN ] \fIcolumn_name\fR TO \fInew_column_name\fR
ALTER VIEW [ IF EXISTS ] \fIname\fR RENAME TO \fInew_name\fR
ALTER VIEW [ IF EXISTS ] \fIname\fR SET SCHEMA \fInew_schema\fR
ALTER VIEW [ IF EXISTS ] \fIname\fR SET ( \fIview_option_name\fR [= \fIview_option_value\fR] [, \&.\&.\&. ] )
ALTER VIEW [ IF EXISTS ] \fIname\fR RESET ( \fIview_option_name\fR [, \&.\&.\&. ] )
.fi
.SH "DESCRIPTION"
.PP
\fBALTER VIEW\fR
changes various auxiliary properties of a view\&. (If you want to modify the view\*(Aqs defining query, use
\fBCREATE OR REPLACE VIEW\fR\&.)
.PP
You must own the view to use
\fBALTER VIEW\fR\&. To change a view\*(Aqs schema, you must also have
CREATE
privilege on the new schema\&. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have
CREATE
privilege on the view\*(Aqs schema\&. (These restrictions enforce that altering the owner doesn\*(Aqt do anything you couldn\*(Aqt do by dropping and recreating the view\&. However, a superuser can alter ownership of any view anyway\&.)
.SH "PARAMETERS"
.PP
\fIname\fR
.RS 4
The name (optionally schema\-qualified) of an existing view\&.
.RE
.PP
\fIcolumn_name\fR
.RS 4
Name of an existing column\&.
.RE
.PP
\fInew_column_name\fR
.RS 4
New name for an existing column\&.
.RE
.PP
IF EXISTS
.RS 4
Do not throw an error if the view does not exist\&. A notice is issued in this case\&.
.RE
.PP
SET/DROP DEFAULT
.RS 4
These forms set or remove the default value for a column\&. A view column\*(Aqs default value is substituted into any
\fBINSERT\fR
or
\fBUPDATE\fR
command whose target is the view, before applying any rules or triggers for the view\&. The view\*(Aqs default will therefore take precedence over any default values from underlying relations\&.
.RE
.PP
\fInew_owner\fR
.RS 4
The user name of the new owner of the view\&.
.RE
.PP
\fInew_name\fR
.RS 4
The new name for the view\&.
.RE
.PP
\fInew_schema\fR
.RS 4
The new schema for the view\&.
.RE
.PP
SET ( \fIview_option_name\fR [= \fIview_option_value\fR] [, \&.\&.\&. ] )
.br
RESET ( \fIview_option_name\fR [, \&.\&.\&. ] )
.RS 4
Sets or resets a view option\&. Currently supported options are:
.PP
check_option (enum)
.RS 4
Changes the check option of the view\&. The value must be
local
or
cascaded\&.
.RE
.PP
security_barrier (boolean)
.RS 4
Changes the security\-barrier property of the view\&. The value must be a Boolean value, such as
true
or
false\&.
.RE
.PP
security_invoker (boolean)
.RS 4
Changes the security\-invoker property of the view\&. The value must be a Boolean value, such as
true
or
false\&.
.RE
.RE
.SH "NOTES"
.PP
For historical reasons,
\fBALTER TABLE\fR
can be used with views too; but the only variants of
\fBALTER TABLE\fR
that are allowed with views are equivalent to the ones shown above\&.
.SH "EXAMPLES"
.PP
To rename the view
foo
to
bar:
.sp
.if n \{\
.RS 4
.\}
.nf
ALTER VIEW foo RENAME TO bar;
.fi
.if n \{\
.RE
.\}
.PP
To attach a default column value to an updatable view:
.sp
.if n \{\
.RS 4
.\}
.nf
CREATE TABLE base_table (id int, ts timestamptz);
CREATE VIEW a_view AS SELECT * FROM base_table;
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
INSERT INTO base_table(id) VALUES(1); \-\- ts will receive a NULL
INSERT INTO a_view(id) VALUES(2); \-\- ts will receive the current time
.fi
.if n \{\
.RE
.\}
.SH "COMPATIBILITY"
.PP
\fBALTER VIEW\fR
is a
PostgreSQL
extension of the SQL standard\&.
.SH "SEE ALSO"
CREATE VIEW (\fBCREATE_VIEW\fR(7)), DROP VIEW (\fBDROP_VIEW\fR(7))
|
