Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * view.c
4 : * use rewrite rules to construct views
5 : *
6 : * Portions Copyright (c) 1996-2017, 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/heapam.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/rewriteManip.h"
30 : #include "rewrite/rewriteHandler.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 :
39 : static void checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc);
40 :
41 : /*---------------------------------------------------------------------
42 : * Validator for "check_option" reloption on views. The allowed values
43 : * are "local" and "cascaded".
44 : */
45 : void
46 21 : validateWithCheckOption(char *value)
47 : {
48 42 : if (value == NULL ||
49 37 : (pg_strcasecmp(value, "local") != 0 &&
50 16 : pg_strcasecmp(value, "cascaded") != 0))
51 : {
52 1 : ereport(ERROR,
53 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
54 : errmsg("invalid value for \"check_option\" option"),
55 : errdetail("Valid values are \"local\" and \"cascaded\".")));
56 : }
57 20 : }
58 :
59 : /*---------------------------------------------------------------------
60 : * DefineVirtualRelation
61 : *
62 : * Create a view relation and use the rules system to store the query
63 : * for the view.
64 : *---------------------------------------------------------------------
65 : */
66 : static ObjectAddress
67 410 : DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
68 : List *options, Query *viewParse)
69 : {
70 : Oid viewOid;
71 : LOCKMODE lockmode;
72 410 : CreateStmt *createStmt = makeNode(CreateStmt);
73 : List *attrList;
74 : ListCell *t;
75 :
76 : /*
77 : * create a list of ColumnDef nodes based on the names and types of the
78 : * (non-junk) targetlist items from the view's SELECT list.
79 : */
80 410 : attrList = NIL;
81 2341 : foreach(t, tlist)
82 : {
83 1931 : TargetEntry *tle = (TargetEntry *) lfirst(t);
84 :
85 1931 : if (!tle->resjunk)
86 : {
87 5757 : ColumnDef *def = makeColumnDef(tle->resname,
88 1919 : exprType((Node *) tle->expr),
89 1919 : exprTypmod((Node *) tle->expr),
90 1919 : exprCollation((Node *) tle->expr));
91 :
92 : /*
93 : * It's possible that the column is of a collatable type but the
94 : * collation could not be resolved, so double-check.
95 : */
96 1919 : if (type_is_collatable(exprType((Node *) tle->expr)))
97 : {
98 832 : if (!OidIsValid(def->collOid))
99 0 : ereport(ERROR,
100 : (errcode(ERRCODE_INDETERMINATE_COLLATION),
101 : errmsg("could not determine which collation to use for view column \"%s\"",
102 : def->colname),
103 : errhint("Use the COLLATE clause to set the collation explicitly.")));
104 : }
105 : else
106 1087 : Assert(!OidIsValid(def->collOid));
107 :
108 1919 : attrList = lappend(attrList, def);
109 : }
110 : }
111 :
112 410 : if (attrList == NIL)
113 0 : ereport(ERROR,
114 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
115 : errmsg("view must have at least one column")));
116 :
117 : /*
118 : * Look up, check permissions on, and lock the creation namespace; also
119 : * check for a preexisting view with the same name. This will also set
120 : * relation->relpersistence to RELPERSISTENCE_TEMP if the selected
121 : * namespace is temporary.
122 : */
123 410 : lockmode = replace ? AccessExclusiveLock : NoLock;
124 410 : (void) RangeVarGetAndCheckCreationNamespace(relation, lockmode, &viewOid);
125 :
126 408 : if (OidIsValid(viewOid) && replace)
127 : {
128 : Relation rel;
129 : TupleDesc descriptor;
130 23 : List *atcmds = NIL;
131 : AlterTableCmd *atcmd;
132 : ObjectAddress address;
133 :
134 : /* Relation is already locked, but we must build a relcache entry. */
135 23 : rel = relation_open(viewOid, NoLock);
136 :
137 : /* Make sure it *is* a view. */
138 23 : if (rel->rd_rel->relkind != RELKIND_VIEW)
139 0 : ereport(ERROR,
140 : (errcode(ERRCODE_WRONG_OBJECT_TYPE),
141 : errmsg("\"%s\" is not a view",
142 : RelationGetRelationName(rel))));
143 :
144 : /* Also check it's not in use already */
145 23 : CheckTableNotInUse(rel, "CREATE OR REPLACE VIEW");
146 :
147 : /*
148 : * Due to the namespace visibility rules for temporary objects, we
149 : * should only end up replacing a temporary view with another
150 : * temporary view, and similarly for permanent views.
151 : */
152 23 : Assert(relation->relpersistence == rel->rd_rel->relpersistence);
153 :
154 : /*
155 : * Create a tuple descriptor to compare against the existing view, and
156 : * verify that the old column list is an initial prefix of the new
157 : * column list.
158 : */
159 23 : descriptor = BuildDescForRelation(attrList);
160 23 : checkViewTupleDesc(descriptor, rel->rd_att);
161 :
162 : /*
163 : * If new attributes have been added, we must add pg_attribute entries
164 : * for them. It is convenient (although overkill) to use the ALTER
165 : * TABLE ADD COLUMN infrastructure for this.
166 : *
167 : * Note that we must do this before updating the query for the view,
168 : * since the rules system requires that the correct view columns be in
169 : * place when defining the new rules.
170 : */
171 20 : if (list_length(attrList) > rel->rd_att->natts)
172 : {
173 : ListCell *c;
174 2 : int skip = rel->rd_att->natts;
175 :
176 7 : foreach(c, attrList)
177 : {
178 5 : if (skip > 0)
179 : {
180 3 : skip--;
181 3 : continue;
182 : }
183 2 : atcmd = makeNode(AlterTableCmd);
184 2 : atcmd->subtype = AT_AddColumnToView;
185 2 : atcmd->def = (Node *) lfirst(c);
186 2 : atcmds = lappend(atcmds, atcmd);
187 : }
188 :
189 2 : AlterTableInternal(viewOid, atcmds, true);
190 :
191 : /* Make the new view columns visible */
192 2 : CommandCounterIncrement();
193 : }
194 :
195 : /*
196 : * Update the query for the view.
197 : *
198 : * Note that we must do this before updating the view options, because
199 : * the new options may not be compatible with the old view query (for
200 : * example if we attempt to add the WITH CHECK OPTION, we require that
201 : * the new view be automatically updatable, but the old view may not
202 : * have been).
203 : */
204 20 : StoreViewQuery(viewOid, viewParse, replace);
205 :
206 : /* Make the new view query visible */
207 20 : CommandCounterIncrement();
208 :
209 : /*
210 : * Finally update the view options.
211 : *
212 : * The new options list replaces the existing options list, even if
213 : * it's empty.
214 : */
215 20 : atcmd = makeNode(AlterTableCmd);
216 20 : atcmd->subtype = AT_ReplaceRelOptions;
217 20 : atcmd->def = (Node *) options;
218 20 : atcmds = list_make1(atcmd);
219 :
220 20 : AlterTableInternal(viewOid, atcmds, true);
221 :
222 20 : ObjectAddressSet(address, RelationRelationId, viewOid);
223 :
224 : /*
225 : * Seems okay, so return the OID of the pre-existing view.
226 : */
227 20 : relation_close(rel, NoLock); /* keep the lock! */
228 :
229 20 : return address;
230 : }
231 : else
232 : {
233 : ObjectAddress address;
234 :
235 : /*
236 : * Set the parameters for keys/inheritance etc. All of these are
237 : * uninteresting for views...
238 : */
239 385 : createStmt->relation = relation;
240 385 : createStmt->tableElts = attrList;
241 385 : createStmt->inhRelations = NIL;
242 385 : createStmt->constraints = NIL;
243 385 : createStmt->options = options;
244 385 : createStmt->oncommit = ONCOMMIT_NOOP;
245 385 : createStmt->tablespacename = NULL;
246 385 : createStmt->if_not_exists = false;
247 :
248 : /*
249 : * Create the relation (this will error out if there's an existing
250 : * view, so we don't need more code to complain if "replace" is
251 : * false).
252 : */
253 385 : address = DefineRelation(createStmt, RELKIND_VIEW, InvalidOid, NULL,
254 : NULL);
255 383 : Assert(address.objectId != InvalidOid);
256 :
257 : /* Make the new view relation visible */
258 383 : CommandCounterIncrement();
259 :
260 : /* Store the query for the view */
261 383 : StoreViewQuery(address.objectId, viewParse, replace);
262 :
263 383 : return address;
264 : }
265 : }
266 :
267 : /*
268 : * Verify that tupledesc associated with proposed new view definition
269 : * matches tupledesc of old view. This is basically a cut-down version
270 : * of equalTupleDescs(), with code added to generate specific complaints.
271 : * Also, we allow the new tupledesc to have more columns than the old.
272 : */
273 : static void
274 23 : checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc)
275 : {
276 : int i;
277 :
278 23 : if (newdesc->natts < olddesc->natts)
279 1 : ereport(ERROR,
280 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
281 : errmsg("cannot drop columns from view")));
282 : /* we can ignore tdhasoid */
283 :
284 52 : for (i = 0; i < olddesc->natts; i++)
285 : {
286 32 : Form_pg_attribute newattr = TupleDescAttr(newdesc, i);
287 32 : Form_pg_attribute oldattr = TupleDescAttr(olddesc, i);
288 :
289 : /* XXX msg not right, but we don't support DROP COL on view anyway */
290 32 : if (newattr->attisdropped != oldattr->attisdropped)
291 0 : ereport(ERROR,
292 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
293 : errmsg("cannot drop columns from view")));
294 :
295 32 : if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0)
296 1 : ereport(ERROR,
297 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
298 : errmsg("cannot change name of view column \"%s\" to \"%s\"",
299 : NameStr(oldattr->attname),
300 : NameStr(newattr->attname))));
301 : /* XXX would it be safe to allow atttypmod to change? Not sure */
302 61 : if (newattr->atttypid != oldattr->atttypid ||
303 30 : newattr->atttypmod != oldattr->atttypmod)
304 1 : ereport(ERROR,
305 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
306 : errmsg("cannot change data type of view column \"%s\" from %s to %s",
307 : NameStr(oldattr->attname),
308 : format_type_with_typemod(oldattr->atttypid,
309 : oldattr->atttypmod),
310 : format_type_with_typemod(newattr->atttypid,
311 : newattr->atttypmod))));
312 : /* We can ignore the remaining attributes of an attribute... */
313 : }
314 :
315 : /*
316 : * We ignore the constraint fields. The new view desc can't have any
317 : * constraints, and the only ones that could be on the old view are
318 : * defaults, which we are happy to leave in place.
319 : */
320 20 : }
321 :
322 : static void
323 429 : DefineViewRules(Oid viewOid, Query *viewParse, bool replace)
324 : {
325 : /*
326 : * Set up the ON SELECT rule. Since the query has already been through
327 : * parse analysis, we use DefineQueryRewrite() directly.
328 : */
329 429 : DefineQueryRewrite(pstrdup(ViewSelectRuleName),
330 : viewOid,
331 : NULL,
332 : CMD_SELECT,
333 : true,
334 : replace,
335 : list_make1(viewParse));
336 :
337 : /*
338 : * Someday: automatic ON INSERT, etc
339 : */
340 429 : }
341 :
342 : /*---------------------------------------------------------------
343 : * UpdateRangeTableOfViewParse
344 : *
345 : * Update the range table of the given parsetree.
346 : * This update consists of adding two new entries IN THE BEGINNING
347 : * of the range table (otherwise the rule system will die a slow,
348 : * horrible and painful death, and we do not want that now, do we?)
349 : * one for the OLD relation and one for the NEW one (both of
350 : * them refer in fact to the "view" relation).
351 : *
352 : * Of course we must also increase the 'varnos' of all the Var nodes
353 : * by 2...
354 : *
355 : * These extra RT entries are not actually used in the query,
356 : * except for run-time permission checking.
357 : *---------------------------------------------------------------
358 : */
359 : static Query *
360 429 : UpdateRangeTableOfViewParse(Oid viewOid, Query *viewParse)
361 : {
362 : Relation viewRel;
363 : List *new_rt;
364 : RangeTblEntry *rt_entry1,
365 : *rt_entry2;
366 : ParseState *pstate;
367 :
368 : /*
369 : * Make a copy of the given parsetree. It's not so much that we don't
370 : * want to scribble on our input, it's that the parser has a bad habit of
371 : * outputting multiple links to the same subtree for constructs like
372 : * BETWEEN, and we mustn't have OffsetVarNodes increment the varno of a
373 : * Var node twice. copyObject will expand any multiply-referenced subtree
374 : * into multiple copies.
375 : */
376 429 : viewParse = copyObject(viewParse);
377 :
378 : /* Create a dummy ParseState for addRangeTableEntryForRelation */
379 429 : pstate = make_parsestate(NULL);
380 :
381 : /* need to open the rel for addRangeTableEntryForRelation */
382 429 : viewRel = relation_open(viewOid, AccessShareLock);
383 :
384 : /*
385 : * Create the 2 new range table entries and form the new range table...
386 : * OLD first, then NEW....
387 : */
388 429 : rt_entry1 = addRangeTableEntryForRelation(pstate, viewRel,
389 : makeAlias("old", NIL),
390 : false, false);
391 429 : rt_entry2 = addRangeTableEntryForRelation(pstate, viewRel,
392 : makeAlias("new", NIL),
393 : false, false);
394 : /* Must override addRangeTableEntry's default access-check flags */
395 429 : rt_entry1->requiredPerms = 0;
396 429 : rt_entry2->requiredPerms = 0;
397 :
398 429 : new_rt = lcons(rt_entry1, lcons(rt_entry2, viewParse->rtable));
399 :
400 429 : viewParse->rtable = new_rt;
401 :
402 : /*
403 : * Now offset all var nodes by 2, and jointree RT indexes too.
404 : */
405 429 : OffsetVarNodes((Node *) viewParse, 2, 0);
406 :
407 429 : relation_close(viewRel, AccessShareLock);
408 :
409 429 : return viewParse;
410 : }
411 :
412 : /*
413 : * DefineView
414 : * Execute a CREATE VIEW command.
415 : */
416 : ObjectAddress
417 420 : DefineView(ViewStmt *stmt, const char *queryString,
418 : int stmt_location, int stmt_len)
419 : {
420 : RawStmt *rawstmt;
421 : Query *viewParse;
422 : RangeVar *view;
423 : ListCell *cell;
424 : bool check_option;
425 : ObjectAddress address;
426 :
427 : /*
428 : * Run parse analysis to convert the raw parse tree to a Query. Note this
429 : * also acquires sufficient locks on the source table(s).
430 : *
431 : * Since parse analysis scribbles on its input, copy the raw parse tree;
432 : * this ensures we don't corrupt a prepared statement, for example.
433 : */
434 420 : rawstmt = makeNode(RawStmt);
435 420 : rawstmt->stmt = (Node *) copyObject(stmt->query);
436 420 : rawstmt->stmt_location = stmt_location;
437 420 : rawstmt->stmt_len = stmt_len;
438 :
439 420 : viewParse = parse_analyze(rawstmt, queryString, NULL, 0, NULL);
440 :
441 : /*
442 : * The grammar should ensure that the result is a single SELECT Query.
443 : * However, it doesn't forbid SELECT INTO, so we have to check for that.
444 : */
445 411 : if (!IsA(viewParse, Query))
446 0 : elog(ERROR, "unexpected parse analysis result");
447 412 : if (viewParse->utilityStmt != NULL &&
448 1 : IsA(viewParse->utilityStmt, CreateTableAsStmt))
449 1 : ereport(ERROR,
450 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
451 : errmsg("views must not contain SELECT INTO")));
452 410 : if (viewParse->commandType != CMD_SELECT)
453 0 : elog(ERROR, "unexpected parse analysis result");
454 :
455 : /*
456 : * Check for unsupported cases. These tests are redundant with ones in
457 : * DefineQueryRewrite(), but that function will complain about a bogus ON
458 : * SELECT rule, and we'd rather the message complain about a view.
459 : */
460 410 : if (viewParse->hasModifyingCTE)
461 0 : ereport(ERROR,
462 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
463 : errmsg("views must not contain data-modifying statements in WITH")));
464 :
465 : /*
466 : * If the user specified the WITH CHECK OPTION, add it to the list of
467 : * reloptions.
468 : */
469 410 : if (stmt->withCheckOption == LOCAL_CHECK_OPTION)
470 4 : stmt->options = lappend(stmt->options,
471 4 : makeDefElem("check_option",
472 4 : (Node *) makeString("local"), -1));
473 406 : else if (stmt->withCheckOption == CASCADED_CHECK_OPTION)
474 14 : stmt->options = lappend(stmt->options,
475 14 : makeDefElem("check_option",
476 14 : (Node *) makeString("cascaded"), -1));
477 :
478 : /*
479 : * Check that the view is auto-updatable if WITH CHECK OPTION was
480 : * specified.
481 : */
482 410 : check_option = false;
483 :
484 449 : foreach(cell, stmt->options)
485 : {
486 39 : DefElem *defel = (DefElem *) lfirst(cell);
487 :
488 39 : if (pg_strcasecmp(defel->defname, "check_option") == 0)
489 18 : check_option = true;
490 : }
491 :
492 : /*
493 : * If the check option is specified, look to see if the view is actually
494 : * auto-updatable or not.
495 : */
496 410 : if (check_option)
497 : {
498 18 : const char *view_updatable_error =
499 : view_query_is_auto_updatable(viewParse, true);
500 :
501 18 : if (view_updatable_error)
502 0 : ereport(ERROR,
503 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
504 : errmsg("WITH CHECK OPTION is supported only on automatically updatable views"),
505 : errhint("%s", view_updatable_error)));
506 : }
507 :
508 : /*
509 : * If a list of column names was given, run through and insert these into
510 : * the actual query tree. - thomas 2000-03-08
511 : */
512 410 : if (stmt->aliases != NIL)
513 : {
514 8 : ListCell *alist_item = list_head(stmt->aliases);
515 : ListCell *targetList;
516 :
517 11 : foreach(targetList, viewParse->targetList)
518 : {
519 11 : TargetEntry *te = lfirst_node(TargetEntry, targetList);
520 :
521 : /* junk columns don't get aliases */
522 11 : if (te->resjunk)
523 0 : continue;
524 11 : te->resname = pstrdup(strVal(lfirst(alist_item)));
525 11 : alist_item = lnext(alist_item);
526 11 : if (alist_item == NULL)
527 8 : break; /* done assigning aliases */
528 : }
529 :
530 8 : if (alist_item != NULL)
531 0 : ereport(ERROR,
532 : (errcode(ERRCODE_SYNTAX_ERROR),
533 : errmsg("CREATE VIEW specifies more column "
534 : "names than columns")));
535 : }
536 :
537 : /* Unlogged views are not sensible. */
538 410 : if (stmt->view->relpersistence == RELPERSISTENCE_UNLOGGED)
539 0 : ereport(ERROR,
540 : (errcode(ERRCODE_SYNTAX_ERROR),
541 : errmsg("views cannot be unlogged because they do not have storage")));
542 :
543 : /*
544 : * If the user didn't explicitly ask for a temporary view, check whether
545 : * we need one implicitly. We allow TEMP to be inserted automatically as
546 : * long as the CREATE command is consistent with that --- no explicit
547 : * schema name.
548 : */
549 410 : view = copyObject(stmt->view); /* don't corrupt original command */
550 410 : if (view->relpersistence == RELPERSISTENCE_PERMANENT
551 380 : && isQueryUsingTempRelation(viewParse))
552 : {
553 18 : view->relpersistence = RELPERSISTENCE_TEMP;
554 18 : ereport(NOTICE,
555 : (errmsg("view \"%s\" will be a temporary view",
556 : view->relname)));
557 : }
558 :
559 : /*
560 : * Create the view relation
561 : *
562 : * NOTE: if it already exists and replace is false, the xact will be
563 : * aborted.
564 : */
565 820 : address = DefineVirtualRelation(view, viewParse->targetList,
566 410 : stmt->replace, stmt->options, viewParse);
567 :
568 403 : return address;
569 : }
570 :
571 : /*
572 : * Use the rules system to store the query for the view.
573 : */
574 : void
575 429 : StoreViewQuery(Oid viewOid, Query *viewParse, bool replace)
576 : {
577 : /*
578 : * The range table of 'viewParse' does not contain entries for the "OLD"
579 : * and "NEW" relations. So... add them!
580 : */
581 429 : viewParse = UpdateRangeTableOfViewParse(viewOid, viewParse);
582 :
583 : /*
584 : * Now create the rules associated with the view.
585 : */
586 429 : DefineViewRules(viewOid, viewParse, replace);
587 429 : }
|
