Branch data 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 : 575 : DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
45 : : List *options, Query *viewParse)
46 : : {
47 : 575 : Oid viewOid;
48 : 575 : LOCKMODE lockmode;
49 : 575 : List *attrList;
50 : 575 : 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 : 575 : attrList = NIL;
57 [ + - + + : 3377 : foreach(t, tlist)
+ + ]
58 : : {
59 : 2802 : TargetEntry *tle = (TargetEntry *) lfirst(t);
60 : :
61 [ + + ]: 2802 : if (!tle->resjunk)
62 : : {
63 : 5552 : ColumnDef *def = makeColumnDef(tle->resname,
64 : 2776 : exprType((Node *) tle->expr),
65 : 2776 : exprTypmod((Node *) tle->expr),
66 : 2776 : 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 [ + + ]: 2776 : if (type_is_collatable(exprType((Node *) tle->expr)))
73 : : {
74 [ + - ]: 1181 : 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 : 1181 : }
81 : : else
82 [ - + ]: 1595 : Assert(!OidIsValid(def->collOid));
83 : :
84 : 2776 : attrList = lappend(attrList, def);
85 : 2776 : }
86 : 2802 : }
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 : 575 : lockmode = replace ? AccessExclusiveLock : NoLock;
95 : 575 : (void) RangeVarGetAndCheckCreationNamespace(relation, lockmode, &viewOid);
96 : :
97 [ + + + + ]: 575 : if (OidIsValid(viewOid) && replace)
98 : : {
99 : 31 : Relation rel;
100 : 31 : TupleDesc descriptor;
101 : 31 : List *atcmds = NIL;
102 : 31 : AlterTableCmd *atcmd;
103 : : ObjectAddress address;
104 : :
105 : : /* Relation is already locked, but we must build a relcache entry. */
106 : 31 : rel = relation_open(viewOid, NoLock);
107 : :
108 : : /* Make sure it *is* a view. */
109 [ + - ]: 31 : 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 : 31 : 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 [ + - ]: 31 : 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 : 31 : descriptor = BuildDescForRelation(attrList);
131 : 31 : 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 [ + + ]: 31 : if (list_length(attrList) > rel->rd_att->natts)
147 : : {
148 : 4 : ListCell *c;
149 : 4 : int skip = rel->rd_att->natts;
150 : :
151 [ + - + + : 16 : foreach(c, attrList)
+ + ]
152 : : {
153 [ + + ]: 12 : if (skip > 0)
154 : : {
155 : 8 : skip--;
156 : 8 : continue;
157 : : }
158 : 4 : atcmd = makeNode(AlterTableCmd);
159 : 4 : atcmd->subtype = AT_AddColumnToView;
160 : 4 : atcmd->def = (Node *) lfirst(c);
161 : 4 : atcmds = lappend(atcmds, atcmd);
162 : 4 : }
163 : :
164 : : /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
165 : 4 : AlterTableInternal(viewOid, atcmds, true);
166 : :
167 : : /* Make the new view columns visible */
168 : 4 : CommandCounterIncrement();
169 : 4 : }
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 : 31 : StoreViewQuery(viewOid, viewParse, replace);
181 : :
182 : : /* Make the new view query visible */
183 : 31 : 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 : 31 : atcmd = makeNode(AlterTableCmd);
192 : 31 : atcmd->subtype = AT_ReplaceRelOptions;
193 : 31 : atcmd->def = (Node *) options;
194 : 31 : atcmds = list_make1(atcmd);
195 : :
196 : : /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
197 : 31 : 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 : 31 : ObjectAddressSet(address, RelationRelationId, viewOid);
212 : :
213 : 31 : recordDependencyOnCurrentExtension(&address, true);
214 : :
215 : : /*
216 : : * Seems okay, so return the OID of the pre-existing view.
217 : : */
218 : 31 : relation_close(rel, NoLock); /* keep the lock! */
219 : :
220 : : return address;
221 : 31 : }
222 : : else
223 : : {
224 : 544 : 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 : 544 : createStmt->relation = relation;
232 : 544 : createStmt->tableElts = attrList;
233 : 544 : createStmt->inhRelations = NIL;
234 : 544 : createStmt->constraints = NIL;
235 : 544 : createStmt->options = options;
236 : 544 : createStmt->oncommit = ONCOMMIT_NOOP;
237 : 544 : createStmt->tablespacename = NULL;
238 : 544 : 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 : 544 : address = DefineRelation(createStmt, RELKIND_VIEW, InvalidOid, NULL,
246 : : NULL);
247 [ + - ]: 544 : Assert(address.objectId != InvalidOid);
248 : :
249 : : /* Make the new view relation visible */
250 : 544 : CommandCounterIncrement();
251 : :
252 : : /* Store the query for the view */
253 : 544 : StoreViewQuery(address.objectId, viewParse, replace);
254 : :
255 : : return address;
256 : 544 : }
257 : 575 : }
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 : 36 : checkViewColumns(TupleDesc newdesc, TupleDesc olddesc)
267 : : {
268 : 36 : int i;
269 : :
270 [ + + ]: 36 : if (newdesc->natts < olddesc->natts)
271 [ + - + - ]: 1 : ereport(ERROR,
272 : : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
273 : : errmsg("cannot drop columns from view")));
274 : :
275 [ + + ]: 97 : for (i = 0; i < olddesc->natts; i++)
276 : : {
277 : 66 : Form_pg_attribute newattr = TupleDescAttr(newdesc, i);
278 : 66 : Form_pg_attribute oldattr = TupleDescAttr(olddesc, i);
279 : :
280 : : /* XXX msg not right, but we don't support DROP COL on view anyway */
281 [ + - ]: 66 : if (newattr->attisdropped != oldattr->attisdropped)
282 [ # # # # ]: 0 : ereport(ERROR,
283 : : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
284 : : errmsg("cannot drop columns from view")));
285 : :
286 [ + + ]: 66 : if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0)
287 [ + - + - ]: 1 : 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 [ + + ]: 65 : if (newattr->atttypid != oldattr->atttypid ||
300 : 63 : newattr->atttypmod != oldattr->atttypmod)
301 [ + - + - ]: 2 : 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 [ + + ]: 63 : if (newattr->attcollation != oldattr->attcollation)
315 [ + - + - ]: 1 : 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 : 62 : }
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 : 31 : }
329 : :
330 : : static void
331 : 625 : 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 : 1250 : DefineQueryRewrite(pstrdup(ViewSelectRuleName),
338 : 625 : viewOid,
339 : : NULL,
340 : : CMD_SELECT,
341 : : true,
342 : 625 : replace,
343 : 625 : list_make1(viewParse));
344 : :
345 : : /*
346 : : * Someday: automatic ON INSERT, etc
347 : : */
348 : 625 : }
349 : :
350 : : /*
351 : : * DefineView
352 : : * Execute a CREATE VIEW command.
353 : : */
354 : : ObjectAddress
355 : 586 : DefineView(ViewStmt *stmt, const char *queryString,
356 : : int stmt_location, int stmt_len)
357 : : {
358 : 586 : RawStmt *rawstmt;
359 : 586 : Query *viewParse;
360 : 586 : RangeVar *view;
361 : 586 : ListCell *cell;
362 : 586 : bool check_option;
363 : : ObjectAddress address;
364 : 586 : 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 : 586 : rawstmt = makeNode(RawStmt);
371 : 586 : rawstmt->stmt = stmt->query;
372 : 586 : rawstmt->stmt_location = stmt_location;
373 : 586 : rawstmt->stmt_len = stmt_len;
374 : :
375 : 586 : 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 [ + - ]: 586 : if (!IsA(viewParse, Query))
382 [ # # # # ]: 0 : elog(ERROR, "unexpected parse analysis result");
383 [ + + - + ]: 586 : if (viewParse->utilityStmt != NULL &&
384 : 1 : IsA(viewParse->utilityStmt, CreateTableAsStmt))
385 [ + - + - ]: 1 : ereport(ERROR,
386 : : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
387 : : errmsg("views must not contain SELECT INTO")));
388 [ + - ]: 585 : 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 [ + - ]: 585 : 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 [ + + ]: 585 : if (stmt->withCheckOption == LOCAL_CHECK_OPTION)
406 : 8 : stmt->options = lappend(stmt->options,
407 : 4 : makeDefElem("check_option",
408 : 4 : (Node *) makeString("local"), -1));
409 [ + + ]: 581 : else if (stmt->withCheckOption == CASCADED_CHECK_OPTION)
410 : 32 : stmt->options = lappend(stmt->options,
411 : 16 : makeDefElem("check_option",
412 : 16 : (Node *) makeString("cascaded"), -1));
413 : :
414 : : /*
415 : : * Check that the view is auto-updatable if WITH CHECK OPTION was
416 : : * specified.
417 : : */
418 : 585 : check_option = false;
419 : :
420 [ + + + + : 647 : foreach(cell, stmt->options)
+ + ]
421 : : {
422 : 62 : DefElem *defel = (DefElem *) lfirst(cell);
423 : :
424 [ + + ]: 62 : if (strcmp(defel->defname, "check_option") == 0)
425 : 20 : check_option = true;
426 : 62 : }
427 : :
428 : : /*
429 : : * If the check option is specified, look to see if the view is actually
430 : : * auto-updatable or not.
431 : : */
432 [ + + ]: 585 : if (check_option)
433 : : {
434 : 40 : const char *view_updatable_error =
435 : 20 : view_query_is_auto_updatable(viewParse, true);
436 : :
437 [ + - ]: 20 : 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 : 20 : }
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 [ + + ]: 585 : if (stmt->aliases != NIL)
449 : : {
450 : 9 : ListCell *alist_item = list_head(stmt->aliases);
451 : 9 : ListCell *targetList;
452 : :
453 [ + - - + : 22 : foreach(targetList, viewParse->targetList)
- + ]
454 : : {
455 : 13 : TargetEntry *te = lfirst_node(TargetEntry, targetList);
456 : :
457 : : /* junk columns don't get aliases */
458 [ - + ]: 13 : if (te->resjunk)
459 : 0 : continue;
460 : 13 : te->resname = pstrdup(strVal(lfirst(alist_item)));
461 : 13 : alist_item = lnext(stmt->aliases, alist_item);
462 [ + + ]: 13 : if (alist_item == NULL)
463 : 9 : break; /* done assigning aliases */
464 [ - + + ]: 13 : }
465 : :
466 [ + - ]: 9 : 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 : 9 : }
472 : :
473 : : /* Unlogged views are not sensible. */
474 [ + - ]: 585 : 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 : 585 : view = copyObject(stmt->view); /* don't corrupt original command */
486 : 585 : if (view->relpersistence == RELPERSISTENCE_PERMANENT
487 [ + + + + ]: 585 : && query_uses_temp_object(viewParse, &temp_object))
488 : : {
489 : 19 : view->relpersistence = RELPERSISTENCE_TEMP;
490 [ - + + - ]: 19 : 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 : 19 : }
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 : 1170 : address = DefineVirtualRelation(view, viewParse->targetList,
504 : 585 : stmt->replace, stmt->options, viewParse);
505 : :
506 : : return address;
507 : 585 : }
508 : :
509 : : /*
510 : : * Use the rules system to store the query for the view.
511 : : */
512 : : void
513 : 625 : StoreViewQuery(Oid viewOid, Query *viewParse, bool replace)
514 : : {
515 : : /*
516 : : * Now create the rules associated with the view.
517 : : */
518 : 625 : DefineViewRules(viewOid, viewParse, replace);
519 : 625 : }
|
