Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * view.c
4 : * use rewrite rules to construct views
5 : *
6 : * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
7 : * Portions Copyright (c) 1994, Regents of the University of California
8 : *
9 : *
10 : * IDENTIFICATION
11 : * src/backend/commands/view.c
12 : *
13 : *-------------------------------------------------------------------------
14 : */
15 : #include "postgres.h"
16 :
17 : #include "access/relation.h"
18 : #include "access/xact.h"
19 : #include "catalog/namespace.h"
20 : #include "commands/tablecmds.h"
21 : #include "commands/view.h"
22 : #include "nodes/makefuncs.h"
23 : #include "nodes/nodeFuncs.h"
24 : #include "parser/analyze.h"
25 : #include "rewrite/rewriteDefine.h"
26 : #include "rewrite/rewriteHandler.h"
27 : #include "rewrite/rewriteSupport.h"
28 : #include "utils/builtins.h"
29 : #include "utils/lsyscache.h"
30 : #include "utils/rel.h"
31 :
32 : static void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc);
33 :
34 : /*---------------------------------------------------------------------
35 : * DefineVirtualRelation
36 : *
37 : * Create a view relation and use the rules system to store the query
38 : * for the view.
39 : *
40 : * EventTriggerAlterTableStart must have been called already.
41 : *---------------------------------------------------------------------
42 : */
43 : static ObjectAddress
44 8782 : DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
45 : List *options, Query *viewParse)
46 : {
47 : Oid viewOid;
48 : LOCKMODE lockmode;
49 : List *attrList;
50 : ListCell *t;
51 :
52 : /*
53 : * create a list of ColumnDef nodes based on the names and types of the
54 : * (non-junk) targetlist items from the view's SELECT list.
55 : */
56 8782 : attrList = NIL;
57 90944 : foreach(t, tlist)
58 : {
59 82162 : TargetEntry *tle = (TargetEntry *) lfirst(t);
60 :
61 82162 : if (!tle->resjunk)
62 : {
63 81831 : ColumnDef *def = makeColumnDef(tle->resname,
64 81831 : exprType((Node *) tle->expr),
65 81831 : exprTypmod((Node *) tle->expr),
66 81831 : exprCollation((Node *) tle->expr));
67 :
68 : /*
69 : * It's possible that the column is of a collatable type but the
70 : * collation could not be resolved, so double-check.
71 : */
72 81831 : if (type_is_collatable(exprType((Node *) tle->expr)))
73 : {
74 43161 : if (!OidIsValid(def->collOid))
75 0 : ereport(ERROR,
76 : (errcode(ERRCODE_INDETERMINATE_COLLATION),
77 : errmsg("could not determine which collation to use for view column \"%s\"",
78 : def->colname),
79 : errhint("Use the COLLATE clause to set the collation explicitly.")));
80 : }
81 : else
82 : Assert(!OidIsValid(def->collOid));
83 :
84 81831 : attrList = lappend(attrList, def);
85 : }
86 : }
87 :
88 : /*
89 : * Look up, check permissions on, and lock the creation namespace; also
90 : * check for a preexisting view with the same name. This will also set
91 : * relation->relpersistence to RELPERSISTENCE_TEMP if the selected
92 : * namespace is temporary.
93 : */
94 8782 : lockmode = replace ? AccessExclusiveLock : NoLock;
95 8782 : (void) RangeVarGetAndCheckCreationNamespace(relation, lockmode, &viewOid);
96 :
97 8776 : if (OidIsValid(viewOid) && replace)
98 : {
99 : Relation rel;
100 : TupleDesc descriptor;
101 116 : List *atcmds = NIL;
102 : AlterTableCmd *atcmd;
103 : ObjectAddress address;
104 :
105 : /* Relation is already locked, but we must build a relcache entry. */
106 116 : rel = relation_open(viewOid, NoLock);
107 :
108 : /* Make sure it *is* a view. */
109 116 : if (rel->rd_rel->relkind != RELKIND_VIEW)
110 0 : ereport(ERROR,
111 : (errcode(ERRCODE_WRONG_OBJECT_TYPE),
112 : errmsg("\"%s\" is not a view",
113 : RelationGetRelationName(rel))));
114 :
115 : /* Also check it's not in use already */
116 116 : CheckTableNotInUse(rel, "CREATE OR REPLACE VIEW");
117 :
118 : /*
119 : * Due to the namespace visibility rules for temporary objects, we
120 : * should only end up replacing a temporary view with another
121 : * temporary view, and similarly for permanent views.
122 : */
123 : Assert(relation->relpersistence == rel->rd_rel->relpersistence);
124 :
125 : /*
126 : * Create a tuple descriptor to compare against the existing view, and
127 : * verify that the old column list is an initial prefix of the new
128 : * column list.
129 : */
130 116 : descriptor = BuildDescForRelation(attrList);
131 116 : checkViewColumns(descriptor, rel->rd_att);
132 :
133 : /*
134 : * If new attributes have been added, we must add pg_attribute entries
135 : * for them. It is convenient (although overkill) to use the ALTER
136 : * TABLE ADD COLUMN infrastructure for this.
137 : *
138 : * Note that we must do this before updating the query for the view,
139 : * since the rules system requires that the correct view columns be in
140 : * place when defining the new rules.
141 : *
142 : * Also note that ALTER TABLE doesn't run parse transformation on
143 : * AT_AddColumnToView commands. The ColumnDef we supply must be ready
144 : * to execute as-is.
145 : */
146 101 : if (list_length(attrList) > rel->rd_att->natts)
147 : {
148 : ListCell *c;
149 16 : int skip = rel->rd_att->natts;
150 :
151 103 : foreach(c, attrList)
152 : {
153 87 : if (skip > 0)
154 : {
155 71 : skip--;
156 71 : continue;
157 : }
158 16 : atcmd = makeNode(AlterTableCmd);
159 16 : atcmd->subtype = AT_AddColumnToView;
160 16 : atcmd->def = (Node *) lfirst(c);
161 16 : atcmds = lappend(atcmds, atcmd);
162 : }
163 :
164 : /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
165 16 : AlterTableInternal(viewOid, atcmds, true);
166 :
167 : /* Make the new view columns visible */
168 16 : CommandCounterIncrement();
169 : }
170 :
171 : /*
172 : * Update the query for the view.
173 : *
174 : * Note that we must do this before updating the view options, because
175 : * the new options may not be compatible with the old view query (for
176 : * example if we attempt to add the WITH CHECK OPTION, we require that
177 : * the new view be automatically updatable, but the old view may not
178 : * have been).
179 : */
180 101 : StoreViewQuery(viewOid, viewParse, replace);
181 :
182 : /* Make the new view query visible */
183 101 : CommandCounterIncrement();
184 :
185 : /*
186 : * Update the view's options.
187 : *
188 : * The new options list replaces the existing options list, even if
189 : * it's empty.
190 : */
191 101 : atcmd = makeNode(AlterTableCmd);
192 101 : atcmd->subtype = AT_ReplaceRelOptions;
193 101 : atcmd->def = (Node *) options;
194 101 : atcmds = list_make1(atcmd);
195 :
196 : /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
197 101 : AlterTableInternal(viewOid, atcmds, true);
198 :
199 : /*
200 : * There is very little to do here to update the view's dependencies.
201 : * Most view-level dependency relationships, such as those on the
202 : * owner, schema, and associated composite type, aren't changing.
203 : * Because we don't allow changing type or collation of an existing
204 : * view column, those dependencies of the existing columns don't
205 : * change either, while the AT_AddColumnToView machinery took care of
206 : * adding such dependencies for new view columns. The dependencies of
207 : * the view's query could have changed arbitrarily, but that was dealt
208 : * with inside StoreViewQuery. What remains is only to check that
209 : * view replacement is allowed when we're creating an extension.
210 : */
211 101 : ObjectAddressSet(address, RelationRelationId, viewOid);
212 :
213 101 : recordDependencyOnCurrentExtension(&address, true);
214 :
215 : /*
216 : * Seems okay, so return the OID of the pre-existing view.
217 : */
218 100 : relation_close(rel, NoLock); /* keep the lock! */
219 :
220 100 : return address;
221 : }
222 : else
223 : {
224 8660 : CreateStmt *createStmt = makeNode(CreateStmt);
225 : ObjectAddress address;
226 :
227 : /*
228 : * Set the parameters for keys/inheritance etc. All of these are
229 : * uninteresting for views...
230 : */
231 8660 : createStmt->relation = relation;
232 8660 : createStmt->tableElts = attrList;
233 8660 : createStmt->inhRelations = NIL;
234 8660 : createStmt->constraints = NIL;
235 8660 : createStmt->options = options;
236 8660 : createStmt->oncommit = ONCOMMIT_NOOP;
237 8660 : createStmt->tablespacename = NULL;
238 8660 : createStmt->if_not_exists = false;
239 :
240 : /*
241 : * Create the relation (this will error out if there's an existing
242 : * view, so we don't need more code to complain if "replace" is
243 : * false).
244 : */
245 8660 : address = DefineRelation(createStmt, RELKIND_VIEW, InvalidOid, NULL,
246 : NULL);
247 : Assert(address.objectId != InvalidOid);
248 :
249 : /* Make the new view relation visible */
250 8651 : CommandCounterIncrement();
251 :
252 : /* Store the query for the view */
253 8651 : StoreViewQuery(address.objectId, viewParse, replace);
254 :
255 8651 : return address;
256 : }
257 : }
258 :
259 : /*
260 : * Verify that the columns associated with proposed new view definition match
261 : * the columns of the old view. This is similar to equalRowTypes(), with code
262 : * added to generate specific complaints. Also, we allow the new view to have
263 : * more columns than the old.
264 : */
265 : static void
266 116 : checkViewColumns(TupleDesc newdesc, TupleDesc olddesc)
267 : {
268 : int i;
269 :
270 116 : if (newdesc->natts < olddesc->natts)
271 3 : ereport(ERROR,
272 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
273 : errmsg("cannot drop columns from view")));
274 :
275 350 : for (i = 0; i < olddesc->natts; i++)
276 : {
277 249 : Form_pg_attribute newattr = TupleDescAttr(newdesc, i);
278 249 : Form_pg_attribute oldattr = TupleDescAttr(olddesc, i);
279 :
280 : /* XXX msg not right, but we don't support DROP COL on view anyway */
281 249 : if (newattr->attisdropped != oldattr->attisdropped)
282 0 : ereport(ERROR,
283 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
284 : errmsg("cannot drop columns from view")));
285 :
286 249 : if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0)
287 3 : ereport(ERROR,
288 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
289 : errmsg("cannot change name of view column \"%s\" to \"%s\"",
290 : NameStr(oldattr->attname),
291 : NameStr(newattr->attname)),
292 : errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.")));
293 :
294 : /*
295 : * We cannot allow type, typmod, or collation to change, since these
296 : * properties may be embedded in Vars of other views/rules referencing
297 : * this one. Other column attributes can be ignored.
298 : */
299 246 : if (newattr->atttypid != oldattr->atttypid ||
300 243 : newattr->atttypmod != oldattr->atttypmod)
301 6 : ereport(ERROR,
302 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
303 : errmsg("cannot change data type of view column \"%s\" from %s to %s",
304 : NameStr(oldattr->attname),
305 : format_type_with_typemod(oldattr->atttypid,
306 : oldattr->atttypmod),
307 : format_type_with_typemod(newattr->atttypid,
308 : newattr->atttypmod))));
309 :
310 : /*
311 : * At this point, attcollations should be both valid or both invalid,
312 : * so applying get_collation_name unconditionally should be fine.
313 : */
314 240 : if (newattr->attcollation != oldattr->attcollation)
315 3 : ereport(ERROR,
316 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
317 : errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"",
318 : NameStr(oldattr->attname),
319 : get_collation_name(oldattr->attcollation),
320 : get_collation_name(newattr->attcollation))));
321 : }
322 :
323 : /*
324 : * We ignore the constraint fields. The new view desc can't have any
325 : * constraints, and the only ones that could be on the old view are
326 : * defaults, which we are happy to leave in place.
327 : */
328 101 : }
329 :
330 : static void
331 8980 : DefineViewRules(Oid viewOid, Query *viewParse, bool replace)
332 : {
333 : /*
334 : * Set up the ON SELECT rule. Since the query has already been through
335 : * parse analysis, we use DefineQueryRewrite() directly.
336 : */
337 8980 : DefineQueryRewrite(pstrdup(ViewSelectRuleName),
338 : viewOid,
339 : NULL,
340 : CMD_SELECT,
341 : true,
342 : replace,
343 8980 : list_make1(viewParse));
344 :
345 : /*
346 : * Someday: automatic ON INSERT, etc
347 : */
348 8980 : }
349 :
350 : /*
351 : * DefineView
352 : * Execute a CREATE VIEW command.
353 : */
354 : ObjectAddress
355 8794 : DefineView(ViewStmt *stmt, const char *queryString,
356 : int stmt_location, int stmt_len)
357 : {
358 : RawStmt *rawstmt;
359 : Query *viewParse;
360 : RangeVar *view;
361 : ListCell *cell;
362 : bool check_option;
363 : ObjectAddress address;
364 : ObjectAddress temp_object;
365 :
366 : /*
367 : * Run parse analysis to convert the raw parse tree to a Query. Note this
368 : * also acquires sufficient locks on the source table(s).
369 : */
370 8794 : rawstmt = makeNode(RawStmt);
371 8794 : rawstmt->stmt = stmt->query;
372 8794 : rawstmt->stmt_location = stmt_location;
373 8794 : rawstmt->stmt_len = stmt_len;
374 :
375 8794 : viewParse = parse_analyze_fixedparams(rawstmt, queryString, NULL, 0, NULL);
376 :
377 : /*
378 : * The grammar should ensure that the result is a single SELECT Query.
379 : * However, it doesn't forbid SELECT INTO, so we have to check for that.
380 : */
381 8785 : if (!IsA(viewParse, Query))
382 0 : elog(ERROR, "unexpected parse analysis result");
383 8785 : if (viewParse->utilityStmt != NULL &&
384 3 : IsA(viewParse->utilityStmt, CreateTableAsStmt))
385 3 : ereport(ERROR,
386 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
387 : errmsg("views must not contain SELECT INTO")));
388 8782 : if (viewParse->commandType != CMD_SELECT)
389 0 : elog(ERROR, "unexpected parse analysis result");
390 :
391 : /*
392 : * Check for unsupported cases. These tests are redundant with ones in
393 : * DefineQueryRewrite(), but that function will complain about a bogus ON
394 : * SELECT rule, and we'd rather the message complain about a view.
395 : */
396 8782 : if (viewParse->hasModifyingCTE)
397 0 : ereport(ERROR,
398 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
399 : errmsg("views must not contain data-modifying statements in WITH")));
400 :
401 : /*
402 : * If the user specified the WITH CHECK OPTION, add it to the list of
403 : * reloptions.
404 : */
405 8782 : if (stmt->withCheckOption == LOCAL_CHECK_OPTION)
406 12 : stmt->options = lappend(stmt->options,
407 12 : makeDefElem("check_option",
408 12 : (Node *) makeString("local"), -1));
409 8770 : else if (stmt->withCheckOption == CASCADED_CHECK_OPTION)
410 51 : stmt->options = lappend(stmt->options,
411 51 : makeDefElem("check_option",
412 51 : (Node *) makeString("cascaded"), -1));
413 :
414 : /*
415 : * Check that the view is auto-updatable if WITH CHECK OPTION was
416 : * specified.
417 : */
418 8782 : check_option = false;
419 :
420 9115 : foreach(cell, stmt->options)
421 : {
422 333 : DefElem *defel = (DefElem *) lfirst(cell);
423 :
424 333 : if (strcmp(defel->defname, "check_option") == 0)
425 64 : check_option = true;
426 : }
427 :
428 : /*
429 : * If the check option is specified, look to see if the view is actually
430 : * auto-updatable or not.
431 : */
432 8782 : if (check_option)
433 : {
434 : const char *view_updatable_error =
435 64 : view_query_is_auto_updatable(viewParse, true);
436 :
437 64 : if (view_updatable_error)
438 0 : ereport(ERROR,
439 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
440 : errmsg("WITH CHECK OPTION is supported only on automatically updatable views"),
441 : errhint("%s", _(view_updatable_error))));
442 : }
443 :
444 : /*
445 : * If a list of column names was given, run through and insert these into
446 : * the actual query tree. - thomas 2000-03-08
447 : */
448 8782 : if (stmt->aliases != NIL)
449 : {
450 28 : ListCell *alist_item = list_head(stmt->aliases);
451 : ListCell *targetList;
452 :
453 40 : foreach(targetList, viewParse->targetList)
454 : {
455 40 : TargetEntry *te = lfirst_node(TargetEntry, targetList);
456 :
457 : /* junk columns don't get aliases */
458 40 : if (te->resjunk)
459 0 : continue;
460 40 : te->resname = pstrdup(strVal(lfirst(alist_item)));
461 40 : alist_item = lnext(stmt->aliases, alist_item);
462 40 : if (alist_item == NULL)
463 28 : break; /* done assigning aliases */
464 : }
465 :
466 28 : if (alist_item != NULL)
467 0 : ereport(ERROR,
468 : (errcode(ERRCODE_SYNTAX_ERROR),
469 : errmsg("CREATE VIEW specifies more column "
470 : "names than columns")));
471 : }
472 :
473 : /* Unlogged views are not sensible. */
474 8782 : if (stmt->view->relpersistence == RELPERSISTENCE_UNLOGGED)
475 0 : ereport(ERROR,
476 : (errcode(ERRCODE_SYNTAX_ERROR),
477 : errmsg("views cannot be unlogged because they do not have storage")));
478 :
479 : /*
480 : * If the user didn't explicitly ask for a temporary view, check whether
481 : * we need one implicitly. We allow TEMP to be inserted automatically as
482 : * long as the CREATE command is consistent with that --- no explicit
483 : * schema name.
484 : */
485 8782 : view = copyObject(stmt->view); /* don't corrupt original command */
486 8782 : if (view->relpersistence == RELPERSISTENCE_PERMANENT
487 8641 : && query_uses_temp_object(viewParse, &temp_object))
488 : {
489 57 : view->relpersistence = RELPERSISTENCE_TEMP;
490 57 : ereport(NOTICE,
491 : (errmsg("view \"%s\" will be a temporary view",
492 : view->relname),
493 : errdetail("It depends on temporary %s.",
494 : getObjectDescription(&temp_object, false))));
495 : }
496 :
497 : /*
498 : * Create the view relation
499 : *
500 : * NOTE: if it already exists and replace is false, the xact will be
501 : * aborted.
502 : */
503 8782 : address = DefineVirtualRelation(view, viewParse->targetList,
504 8782 : stmt->replace, stmt->options, viewParse);
505 :
506 8751 : return address;
507 : }
508 :
509 : /*
510 : * Use the rules system to store the query for the view.
511 : */
512 : void
513 8980 : StoreViewQuery(Oid viewOid, Query *viewParse, bool replace)
514 : {
515 : /*
516 : * Now create the rules associated with the view.
517 : */
518 8980 : DefineViewRules(viewOid, viewParse, replace);
519 8980 : }
|
