Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * view.c
4 : * use rewrite rules to construct views
5 : *
6 : * Portions Copyright (c) 1996-2024, 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 13606 : DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
46 : List *options, Query *viewParse)
47 : {
48 : Oid viewOid;
49 : LOCKMODE lockmode;
50 13606 : CreateStmt *createStmt = makeNode(CreateStmt);
51 : List *attrList;
52 : ListCell *t;
53 :
54 : /*
55 : * create a list of ColumnDef nodes based on the names and types of the
56 : * (non-junk) targetlist items from the view's SELECT list.
57 : */
58 13606 : attrList = NIL;
59 133828 : foreach(t, tlist)
60 : {
61 120222 : TargetEntry *tle = (TargetEntry *) lfirst(t);
62 :
63 120222 : if (!tle->resjunk)
64 : {
65 119704 : ColumnDef *def = makeColumnDef(tle->resname,
66 119704 : exprType((Node *) tle->expr),
67 119704 : exprTypmod((Node *) tle->expr),
68 119704 : exprCollation((Node *) tle->expr));
69 :
70 : /*
71 : * It's possible that the column is of a collatable type but the
72 : * collation could not be resolved, so double-check.
73 : */
74 119704 : if (type_is_collatable(exprType((Node *) tle->expr)))
75 : {
76 65492 : if (!OidIsValid(def->collOid))
77 0 : ereport(ERROR,
78 : (errcode(ERRCODE_INDETERMINATE_COLLATION),
79 : errmsg("could not determine which collation to use for view column \"%s\"",
80 : def->colname),
81 : errhint("Use the COLLATE clause to set the collation explicitly.")));
82 : }
83 : else
84 : Assert(!OidIsValid(def->collOid));
85 :
86 119704 : attrList = lappend(attrList, def);
87 : }
88 : }
89 :
90 : /*
91 : * Look up, check permissions on, and lock the creation namespace; also
92 : * check for a preexisting view with the same name. This will also set
93 : * relation->relpersistence to RELPERSISTENCE_TEMP if the selected
94 : * namespace is temporary.
95 : */
96 13606 : lockmode = replace ? AccessExclusiveLock : NoLock;
97 13606 : (void) RangeVarGetAndCheckCreationNamespace(relation, lockmode, &viewOid);
98 :
99 13594 : if (OidIsValid(viewOid) && replace)
100 : {
101 : Relation rel;
102 : TupleDesc descriptor;
103 224 : List *atcmds = NIL;
104 : AlterTableCmd *atcmd;
105 : ObjectAddress address;
106 :
107 : /* Relation is already locked, but we must build a relcache entry. */
108 224 : rel = relation_open(viewOid, NoLock);
109 :
110 : /* Make sure it *is* a view. */
111 224 : if (rel->rd_rel->relkind != RELKIND_VIEW)
112 0 : ereport(ERROR,
113 : (errcode(ERRCODE_WRONG_OBJECT_TYPE),
114 : errmsg("\"%s\" is not a view",
115 : RelationGetRelationName(rel))));
116 :
117 : /* Also check it's not in use already */
118 224 : CheckTableNotInUse(rel, "CREATE OR REPLACE VIEW");
119 :
120 : /*
121 : * Due to the namespace visibility rules for temporary objects, we
122 : * should only end up replacing a temporary view with another
123 : * temporary view, and similarly for permanent views.
124 : */
125 : Assert(relation->relpersistence == rel->rd_rel->relpersistence);
126 :
127 : /*
128 : * Create a tuple descriptor to compare against the existing view, and
129 : * verify that the old column list is an initial prefix of the new
130 : * column list.
131 : */
132 224 : descriptor = BuildDescForRelation(attrList);
133 224 : checkViewColumns(descriptor, rel->rd_att);
134 :
135 : /*
136 : * If new attributes have been added, we must add pg_attribute entries
137 : * for them. It is convenient (although overkill) to use the ALTER
138 : * TABLE ADD COLUMN infrastructure for this.
139 : *
140 : * Note that we must do this before updating the query for the view,
141 : * since the rules system requires that the correct view columns be in
142 : * place when defining the new rules.
143 : *
144 : * Also note that ALTER TABLE doesn't run parse transformation on
145 : * AT_AddColumnToView commands. The ColumnDef we supply must be ready
146 : * to execute as-is.
147 : */
148 194 : if (list_length(attrList) > rel->rd_att->natts)
149 : {
150 : ListCell *c;
151 24 : int skip = rel->rd_att->natts;
152 :
153 96 : foreach(c, attrList)
154 : {
155 72 : if (skip > 0)
156 : {
157 48 : skip--;
158 48 : continue;
159 : }
160 24 : atcmd = makeNode(AlterTableCmd);
161 24 : atcmd->subtype = AT_AddColumnToView;
162 24 : atcmd->def = (Node *) lfirst(c);
163 24 : atcmds = lappend(atcmds, atcmd);
164 : }
165 :
166 : /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
167 24 : AlterTableInternal(viewOid, atcmds, true);
168 :
169 : /* Make the new view columns visible */
170 24 : CommandCounterIncrement();
171 : }
172 :
173 : /*
174 : * Update the query for the view.
175 : *
176 : * Note that we must do this before updating the view options, because
177 : * the new options may not be compatible with the old view query (for
178 : * example if we attempt to add the WITH CHECK OPTION, we require that
179 : * the new view be automatically updatable, but the old view may not
180 : * have been).
181 : */
182 194 : StoreViewQuery(viewOid, viewParse, replace);
183 :
184 : /* Make the new view query visible */
185 194 : CommandCounterIncrement();
186 :
187 : /*
188 : * Update the view's options.
189 : *
190 : * The new options list replaces the existing options list, even if
191 : * it's empty.
192 : */
193 194 : atcmd = makeNode(AlterTableCmd);
194 194 : atcmd->subtype = AT_ReplaceRelOptions;
195 194 : atcmd->def = (Node *) options;
196 194 : atcmds = list_make1(atcmd);
197 :
198 : /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
199 194 : AlterTableInternal(viewOid, atcmds, true);
200 :
201 : /*
202 : * There is very little to do here to update the view's dependencies.
203 : * Most view-level dependency relationships, such as those on the
204 : * owner, schema, and associated composite type, aren't changing.
205 : * Because we don't allow changing type or collation of an existing
206 : * view column, those dependencies of the existing columns don't
207 : * change either, while the AT_AddColumnToView machinery took care of
208 : * adding such dependencies for new view columns. The dependencies of
209 : * the view's query could have changed arbitrarily, but that was dealt
210 : * with inside StoreViewQuery. What remains is only to check that
211 : * view replacement is allowed when we're creating an extension.
212 : */
213 194 : ObjectAddressSet(address, RelationRelationId, viewOid);
214 :
215 194 : recordDependencyOnCurrentExtension(&address, true);
216 :
217 : /*
218 : * Seems okay, so return the OID of the pre-existing view.
219 : */
220 192 : relation_close(rel, NoLock); /* keep the lock! */
221 :
222 192 : return address;
223 : }
224 : else
225 : {
226 : ObjectAddress address;
227 :
228 : /*
229 : * Set the parameters for keys/inheritance etc. All of these are
230 : * uninteresting for views...
231 : */
232 13370 : createStmt->relation = relation;
233 13370 : createStmt->tableElts = attrList;
234 13370 : createStmt->inhRelations = NIL;
235 13370 : createStmt->constraints = NIL;
236 13370 : createStmt->options = options;
237 13370 : createStmt->oncommit = ONCOMMIT_NOOP;
238 13370 : createStmt->tablespacename = NULL;
239 13370 : 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 13370 : address = DefineRelation(createStmt, RELKIND_VIEW, InvalidOid, NULL,
247 : NULL);
248 : Assert(address.objectId != InvalidOid);
249 :
250 : /* Make the new view relation visible */
251 13352 : CommandCounterIncrement();
252 :
253 : /* Store the query for the view */
254 13352 : StoreViewQuery(address.objectId, viewParse, replace);
255 :
256 13352 : 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 224 : checkViewColumns(TupleDesc newdesc, TupleDesc olddesc)
268 : {
269 : int i;
270 :
271 224 : if (newdesc->natts < olddesc->natts)
272 6 : ereport(ERROR,
273 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
274 : errmsg("cannot drop columns from view")));
275 :
276 598 : for (i = 0; i < olddesc->natts; i++)
277 : {
278 404 : Form_pg_attribute newattr = TupleDescAttr(newdesc, i);
279 404 : Form_pg_attribute oldattr = TupleDescAttr(olddesc, i);
280 :
281 : /* XXX msg not right, but we don't support DROP COL on view anyway */
282 404 : if (newattr->attisdropped != oldattr->attisdropped)
283 0 : ereport(ERROR,
284 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
285 : errmsg("cannot drop columns from view")));
286 :
287 404 : 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 398 : if (newattr->atttypid != oldattr->atttypid ||
301 392 : 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 386 : 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 194 : }
330 :
331 : static void
332 13984 : 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 13984 : DefineQueryRewrite(pstrdup(ViewSelectRuleName),
339 : viewOid,
340 : NULL,
341 : CMD_SELECT,
342 : true,
343 : replace,
344 13984 : list_make1(viewParse));
345 :
346 : /*
347 : * Someday: automatic ON INSERT, etc
348 : */
349 13984 : }
350 :
351 : /*
352 : * DefineView
353 : * Execute a CREATE VIEW command.
354 : */
355 : ObjectAddress
356 13630 : 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 13630 : rawstmt = makeNode(RawStmt);
371 13630 : rawstmt->stmt = stmt->query;
372 13630 : rawstmt->stmt_location = stmt_location;
373 13630 : rawstmt->stmt_len = stmt_len;
374 :
375 13630 : 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 13612 : if (!IsA(viewParse, Query))
382 0 : elog(ERROR, "unexpected parse analysis result");
383 13612 : 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 13606 : 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 13606 : 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 13606 : 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 13582 : else if (stmt->withCheckOption == CASCADED_CHECK_OPTION)
410 96 : stmt->options = lappend(stmt->options,
411 96 : makeDefElem("check_option",
412 96 : (Node *) makeString("cascaded"), -1));
413 :
414 : /*
415 : * Check that the view is auto-updatable if WITH CHECK OPTION was
416 : * specified.
417 : */
418 13606 : check_option = false;
419 :
420 14194 : foreach(cell, stmt->options)
421 : {
422 588 : DefElem *defel = (DefElem *) lfirst(cell);
423 :
424 588 : if (strcmp(defel->defname, "check_option") == 0)
425 122 : 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 13606 : if (check_option)
433 : {
434 : const char *view_updatable_error =
435 122 : view_query_is_auto_updatable(viewParse, true);
436 :
437 122 : 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 13606 : 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 13606 : 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 13606 : view = copyObject(stmt->view); /* don't corrupt original command */
486 13606 : if (view->relpersistence == RELPERSISTENCE_PERMANENT
487 13354 : && 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 13606 : address = DefineVirtualRelation(view, viewParse->targetList,
502 13606 : stmt->replace, stmt->options, viewParse);
503 :
504 13544 : return address;
505 : }
506 :
507 : /*
508 : * Use the rules system to store the query for the view.
509 : */
510 : void
511 13984 : StoreViewQuery(Oid viewOid, Query *viewParse, bool replace)
512 : {
513 : /*
514 : * Now create the rules associated with the view.
515 : */
516 13984 : DefineViewRules(viewOid, viewParse, replace);
517 13984 : }
|
