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