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