From 248c67d7ed505d98d3a94cd3954835255317ff16 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 2 Sep 2002 02:13:02 +0000 Subject: [PATCH] CREATE OR REPLACE VIEW, CREATE OR REPLACE RULE. Gavin Sherry, Neil Conway, and Tom Lane all got their hands dirty on this one ... --- src/backend/commands/view.c | 119 +++++++++++++++++++++--------- src/backend/nodes/copyfuncs.c | 4 +- src/backend/nodes/equalfuncs.c | 6 +- src/backend/parser/gram.y | 27 ++++--- src/backend/rewrite/rewriteDefine.c | 77 ++++++++++++++----- src/backend/tcop/utility.c | 4 +- src/include/commands/view.h | 4 +- src/include/nodes/parsenodes.h | 4 +- src/test/regress/expected/create_view.out | 40 ++++++++++ src/test/regress/expected/rules.out | 18 +++++ src/test/regress/sql/create_view.sql | 39 ++++++++++ src/test/regress/sql/rules.sql | 18 +++++ 12 files changed, 290 insertions(+), 70 deletions(-) diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index c635a28571..46cdf0aca0 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -6,43 +6,43 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: view.c,v 1.68 2002/08/30 19:23:19 tgl Exp $ + * + * IDENTIFICATION + * $Header: /cvsroot/pgsql/src/backend/commands/view.c,v 1.69 2002/09/02 02:13:01 tgl Exp $ * *------------------------------------------------------------------------- */ #include "postgres.h" -#include "access/xact.h" +#include "access/heapam.h" #include "catalog/dependency.h" -#include "catalog/heap.h" #include "catalog/namespace.h" #include "commands/tablecmds.h" #include "commands/view.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "parser/parse_relation.h" -#include "parser/parse_type.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rewriteManip.h" -#include "rewrite/rewriteRemove.h" #include "rewrite/rewriteSupport.h" -#include "utils/syscache.h" +#include "utils/acl.h" +#include "utils/lsyscache.h" /*--------------------------------------------------------------------- * DefineVirtualRelation * - * Create the "view" relation. - * `DefineRelation' does all the work, we just provide the correct - * arguments! - * - * If the relation already exists, then 'DefineRelation' will abort - * the xact... + * Create the "view" relation. `DefineRelation' does all the work, + * we just provide the correct arguments ... at least when we're + * creating a view. If we're updating an existing view, we have to + * work harder. *--------------------------------------------------------------------- */ static Oid -DefineVirtualRelation(const RangeVar *relation, List *tlist) +DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace) { + Oid viewOid, + namespaceId; CreateStmt *createStmt = makeNode(CreateStmt); List *attrList, *t; @@ -52,7 +52,7 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist) * the (non-junk) targetlist items from the view's SELECT list. */ attrList = NIL; - foreach(t, tlist) + foreach (t, tlist) { TargetEntry *entry = lfirst(t); Resdom *res = entry->resdom; @@ -83,23 +83,74 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist) elog(ERROR, "attempted to define virtual relation with no attrs"); /* - * now create the parameters for keys/inheritance etc. All of them are - * nil... + * Check to see if we want to replace an existing view. */ - createStmt->relation = (RangeVar *) relation; - createStmt->tableElts = attrList; - createStmt->inhRelations = NIL; - createStmt->constraints = NIL; - createStmt->hasoids = false; + namespaceId = RangeVarGetCreationNamespace(relation); + viewOid = get_relname_relid(relation->relname, namespaceId); - /* - * finally create the relation... - */ - return DefineRelation(createStmt, RELKIND_VIEW); + if (OidIsValid(viewOid) && replace) + { + Relation rel; + TupleDesc descriptor; + + /* + * Yes. Get exclusive lock on the existing view ... + */ + rel = relation_open(viewOid, AccessExclusiveLock); + + /* + * Make sure it *is* a view, and do permissions checks. + */ + if (rel->rd_rel->relkind != RELKIND_VIEW) + elog(ERROR, "%s is not a view", + RelationGetRelationName(rel)); + + if (!pg_class_ownercheck(viewOid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, RelationGetRelationName(rel)); + + /* + * Create a tuple descriptor to compare against the existing view, + * and verify it matches. + * + * XXX the error message is a bit cheesy here: would be useful to + * give a more specific complaint about the difference in the + * descriptors. No time for it at the moment though. + */ + descriptor = BuildDescForRelation(attrList); + if (!equalTupleDescs(descriptor, rel->rd_att)) + elog(ERROR, "Cannot change column set of existing view %s", + RelationGetRelationName(rel)); + + /* + * Seems okay, so return the OID of the pre-existing view. + */ + relation_close(rel, NoLock); /* keep the lock! */ + + return viewOid; + } + else + { + /* + * now create the parameters for keys/inheritance etc. All of them are + * nil... + */ + createStmt->relation = (RangeVar *) relation; + createStmt->tableElts = attrList; + createStmt->inhRelations = NIL; + createStmt->constraints = NIL; + createStmt->hasoids = false; + + /* + * finally create the relation (this will error out if there's + * an existing view, so we don't need more code to complain + * if "replace" is false). + */ + return DefineRelation(createStmt, RELKIND_VIEW); + } } static RuleStmt * -FormViewRetrieveRule(const RangeVar *view, Query *viewParse) +FormViewRetrieveRule(const RangeVar *view, Query *viewParse, bool replace) { RuleStmt *rule; @@ -114,12 +165,13 @@ FormViewRetrieveRule(const RangeVar *view, Query *viewParse) rule->event = CMD_SELECT; rule->instead = true; rule->actions = makeList1(viewParse); + rule->replace = replace; return rule; } static void -DefineViewRules(const RangeVar *view, Query *viewParse) +DefineViewRules(const RangeVar *view, Query *viewParse, bool replace) { RuleStmt *retrieve_rule; @@ -129,10 +181,9 @@ DefineViewRules(const RangeVar *view, Query *viewParse) RuleStmt *delete_rule; #endif - retrieve_rule = FormViewRetrieveRule(view, viewParse); + retrieve_rule = FormViewRetrieveRule(view, viewParse, replace); #ifdef NOTYET - replace_rule = FormViewReplaceRule(view, viewParse); append_rule = FormViewAppendRule(view, viewParse); delete_rule = FormViewDeleteRule(view, viewParse); @@ -221,16 +272,18 @@ UpdateRangeTableOfViewParse(Oid viewOid, Query *viewParse) *------------------------------------------------------------------- */ void -DefineView(const RangeVar *view, Query *viewParse) +DefineView(const RangeVar *view, Query *viewParse, bool replace) { Oid viewOid; /* * Create the view relation * - * NOTE: if it already exists, the xact will be aborted. + * NOTE: if it already exists and replace is false, the xact will + * be aborted. */ - viewOid = DefineVirtualRelation(view, viewParse->targetList); + + viewOid = DefineVirtualRelation(view, viewParse->targetList, replace); /* * The relation we have just created is not visible to any other @@ -248,7 +301,7 @@ DefineView(const RangeVar *view, Query *viewParse) /* * Now create the rules associated with the view. */ - DefineViewRules(view, viewParse); + DefineViewRules(view, viewParse, replace); } /* diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 1938e7f473..8a69af3de5 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.209 2002/08/31 22:10:43 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.210 2002/09/02 02:13:01 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -2173,6 +2173,7 @@ _copyRuleStmt(RuleStmt *from) Node_Copy(from, newnode, whereClause); newnode->event = from->event; newnode->instead = from->instead; + newnode->replace = from->replace; Node_Copy(from, newnode, actions); return newnode; @@ -2238,6 +2239,7 @@ _copyViewStmt(ViewStmt *from) Node_Copy(from, newnode, view); Node_Copy(from, newnode, aliases); Node_Copy(from, newnode, query); + newnode->replace = from->replace; return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 10b8e79933..7f677c0837 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -20,7 +20,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.157 2002/08/31 22:10:43 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.158 2002/09/02 02:13:01 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1003,6 +1003,8 @@ _equalRuleStmt(RuleStmt *a, RuleStmt *b) return false; if (a->instead != b->instead) return false; + if (a->replace != b->replace) + return false; if (!equal(a->actions, b->actions)) return false; @@ -1067,6 +1069,8 @@ _equalViewStmt(ViewStmt *a, ViewStmt *b) return false; if (!equal(a->query, b->query)) return false; + if (a->replace != b->replace) + return false; return true; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 463a8d5a4e..674a6d0d92 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.364 2002/08/29 00:17:04 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.365 2002/09/02 02:13:01 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -3346,18 +3346,19 @@ opt_column: COLUMN { $$ = COLUMN; } * *****************************************************************************/ -RuleStmt: CREATE RULE name AS +RuleStmt: CREATE opt_or_replace RULE name AS { QueryIsRule=TRUE; } ON event TO qualified_name where_clause DO opt_instead RuleActionList { RuleStmt *n = makeNode(RuleStmt); - n->relation = $9; - n->rulename = $3; - n->whereClause = $10; - n->event = $7; - n->instead = $12; - n->actions = $13; + n->replace = $2; + n->relation = $10; + n->rulename = $4; + n->whereClause = $11; + n->event = $8; + n->instead = $13; + n->actions = $14; $$ = (Node *)n; QueryIsRule=FALSE; } @@ -3537,12 +3538,14 @@ opt_trans: WORK {} * *****************************************************************************/ -ViewStmt: CREATE VIEW qualified_name opt_column_list AS SelectStmt +ViewStmt: CREATE opt_or_replace VIEW qualified_name opt_column_list + AS SelectStmt { ViewStmt *n = makeNode(ViewStmt); - n->view = $3; - n->aliases = $4; - n->query = (Query *) $6; + n->replace = $2; + n->view = $4; + n->aliases = $5; + n->query = (Query *) $7; $$ = (Node *)n; } ; diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c index 80952d7ddc..f276189426 100644 --- a/src/backend/rewrite/rewriteDefine.c +++ b/src/backend/rewrite/rewriteDefine.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.77 2002/08/05 03:29:17 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.78 2002/09/02 02:13:01 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -48,24 +48,23 @@ InsertRule(char *rulname, AttrNumber evslot_index, bool evinstead, Node *event_qual, - List *action) + List *action, + bool replace) { char *evqual = nodeToString(event_qual); char *actiontree = nodeToString((Node *) action); int i; Datum values[Natts_pg_rewrite]; char nulls[Natts_pg_rewrite]; + char replaces[Natts_pg_rewrite]; NameData rname; Relation pg_rewrite_desc; - TupleDesc tupDesc; - HeapTuple tup; + HeapTuple tup, + oldtup; Oid rewriteObjectId; ObjectAddress myself, referenced; - - if (IsDefinedRewriteRule(eventrel_oid, rulname)) - elog(ERROR, "Attempt to insert rule \"%s\" failed: already exists", - rulname); + bool is_update = false; /* * Set up *nulls and *values arrays @@ -83,22 +82,61 @@ InsertRule(char *rulname, values[i++] = DirectFunctionCall1(textin, CStringGetDatum(actiontree)); /* ev_action */ /* - * create a new pg_rewrite tuple + * Ready to store new pg_rewrite tuple */ pg_rewrite_desc = heap_openr(RewriteRelationName, RowExclusiveLock); - tupDesc = pg_rewrite_desc->rd_att; + /* + * Check to see if we are replacing an existing tuple + */ + oldtup = SearchSysCache(RULERELNAME, + ObjectIdGetDatum(eventrel_oid), + PointerGetDatum(rulname), + 0, 0); + + if (HeapTupleIsValid(oldtup)) + { + if (!replace) + elog(ERROR,"Attempt to insert rule \"%s\" failed: already exists", + rulname); + + /* + * When replacing, we don't need to replace every attribute + */ + MemSet(replaces, ' ', sizeof(replaces)); + replaces[Anum_pg_rewrite_ev_attr - 1] = 'r'; + replaces[Anum_pg_rewrite_ev_type - 1] = 'r'; + replaces[Anum_pg_rewrite_is_instead - 1] = 'r'; + replaces[Anum_pg_rewrite_ev_qual - 1] = 'r'; + replaces[Anum_pg_rewrite_ev_action - 1] = 'r'; + + tup = heap_modifytuple(oldtup, pg_rewrite_desc, + values, nulls, replaces); + + simple_heap_update(pg_rewrite_desc, &tup->t_self, tup); - tup = heap_formtuple(tupDesc, - values, - nulls); + ReleaseSysCache(oldtup); + + rewriteObjectId = HeapTupleGetOid(tup); + is_update = true; + } + else + { + tup = heap_formtuple(pg_rewrite_desc->rd_att, values, nulls); - rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup); + rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup); + } + /* Need to update indexes in either case */ CatalogUpdateIndexes(pg_rewrite_desc, tup); heap_freetuple(tup); + /* If replacing, get rid of old dependencies and make new ones */ + if (is_update) + deleteDependencyRecordsFor(RelationGetRelid(pg_rewrite_desc), + rewriteObjectId); + /* * Install dependency on rule's relation to ensure it will go away * on relation deletion. If the rule is ON SELECT, make the dependency @@ -114,13 +152,14 @@ InsertRule(char *rulname, referenced.objectSubId = 0; recordDependencyOn(&myself, &referenced, - (evtype == CMD_SELECT) ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO); + (evtype == CMD_SELECT) ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO); /* * Also install dependencies on objects referenced in action and qual. */ recordDependencyOnExpr(&myself, (Node *) action, NIL, DEPENDENCY_NORMAL); + if (event_qual != NULL) { /* Find query containing OLD/NEW rtable entries */ @@ -143,6 +182,7 @@ DefineQueryRewrite(RuleStmt *stmt) Node *event_qual = stmt->whereClause; CmdType event_type = stmt->event; bool is_instead = stmt->instead; + bool replace = stmt->replace; List *action = stmt->actions; Relation event_relation; Oid ev_relid; @@ -232,7 +272,7 @@ DefineQueryRewrite(RuleStmt *stmt) * event relation, ... */ i = 0; - foreach(tllist, query->targetList) + foreach (tllist, query->targetList) { TargetEntry *tle = (TargetEntry *) lfirst(tllist); Resdom *resdom = tle->resdom; @@ -282,7 +322,7 @@ DefineQueryRewrite(RuleStmt *stmt) /* * ... there must not be another ON SELECT rule already ... */ - if (event_relation->rd_rules != NULL) + if (!replace && event_relation->rd_rules != NULL) { for (i = 0; i < event_relation->rd_rules->numLocks; i++) { @@ -364,7 +404,8 @@ DefineQueryRewrite(RuleStmt *stmt) event_attno, is_instead, event_qual, - action); + action, + replace); /* * Set pg_class 'relhasrules' field TRUE for event relation. If diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 17a2533b77..40dec9d68b 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.175 2002/08/30 19:23:20 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.176 2002/09/02 02:13:01 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -595,7 +595,7 @@ ProcessUtility(Node *parsetree, { ViewStmt *stmt = (ViewStmt *) parsetree; - DefineView(stmt->view, stmt->query); + DefineView(stmt->view, stmt->query, stmt->replace); } break; diff --git a/src/include/commands/view.h b/src/include/commands/view.h index 3603f2a4fa..e476d8224e 100644 --- a/src/include/commands/view.h +++ b/src/include/commands/view.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: view.h,v 1.16 2002/07/01 15:27:56 tgl Exp $ + * $Id: view.h,v 1.17 2002/09/02 02:13:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -16,7 +16,7 @@ #include "nodes/parsenodes.h" -extern void DefineView(const RangeVar *view, Query *view_parse); +extern void DefineView(const RangeVar *view, Query *view_parse, bool replace); extern void RemoveView(const RangeVar *view, DropBehavior behavior); #endif /* VIEW_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index a426aeba02..be8b7fe7d1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parsenodes.h,v 1.204 2002/08/31 22:10:47 tgl Exp $ + * $Id: parsenodes.h,v 1.205 2002/09/02 02:13:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1371,6 +1371,7 @@ typedef struct RuleStmt CmdType event; /* SELECT, INSERT, etc */ bool instead; /* is a 'do instead'? */ List *actions; /* the action statements */ + bool replace; /* OR REPLACE */ } RuleStmt; /* ---------------------- @@ -1436,6 +1437,7 @@ typedef struct ViewStmt RangeVar *view; /* the view to be created */ List *aliases; /* target column names */ Query *query; /* the SQL statement */ + bool replace; /* replace an existing view? */ } ViewStmt; /* ---------------------- diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index e398440bce..ade98e6c31 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -15,3 +15,43 @@ CREATE VIEW iexit AS CREATE VIEW toyemp AS SELECT name, age, location, 12*salary AS annualsal FROM emp; +-- +-- CREATE OR REPLACE VIEW +-- +CREATE TABLE viewtest_tbl (a int, b int); +COPY viewtest_tbl FROM stdin; +CREATE OR REPLACE VIEW viewtest AS + SELECT * FROM viewtest_tbl; +CREATE OR REPLACE VIEW viewtest AS + SELECT * FROM viewtest_tbl WHERE a > 10; +SELECT * FROM viewtest; + a | b +----+---- + 15 | 20 + 20 | 25 +(2 rows) + +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC; +SELECT * FROM viewtest; + a | b +----+---- + 20 | 25 + 15 | 20 + 10 | 15 +(3 rows) + +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT a FROM viewtest_tbl WHERE a <> 20; +ERROR: Cannot change column set of existing view viewtest +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT 1, * FROM viewtest_tbl; +ERROR: Cannot change column set of existing view viewtest +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b::numeric FROM viewtest_tbl; +ERROR: Cannot change column set of existing view viewtest +DROP VIEW viewtest; +DROP TABLE viewtest_tbl; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 0cfd3f103d..bcce2332c4 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1343,3 +1343,21 @@ SELECT tablename, rulename, definition FROM pg_rules shoelace_ok | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name); (29 rows) +-- +-- CREATE OR REPLACE RULE +-- +CREATE TABLE ruletest_tbl (a int, b int); +CREATE TABLE ruletest_tbl2 (a int, b int); +CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl + DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10); +INSERT INTO ruletest_tbl VALUES (99, 99); +CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl + DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000); +INSERT INTO ruletest_tbl VALUES (99, 99); +SELECT * FROM ruletest_tbl2; + a | b +------+------ + 10 | 10 + 1000 | 1000 +(2 rows) + diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql index 858c8ce960..8c15fc1241 100644 --- a/src/test/regress/sql/create_view.sql +++ b/src/test/regress/sql/create_view.sql @@ -19,3 +19,42 @@ CREATE VIEW toyemp AS SELECT name, age, location, 12*salary AS annualsal FROM emp; +-- +-- CREATE OR REPLACE VIEW +-- + +CREATE TABLE viewtest_tbl (a int, b int); +COPY viewtest_tbl FROM stdin; +5 10 +10 15 +15 20 +20 25 +\. + +CREATE OR REPLACE VIEW viewtest AS + SELECT * FROM viewtest_tbl; + +CREATE OR REPLACE VIEW viewtest AS + SELECT * FROM viewtest_tbl WHERE a > 10; + +SELECT * FROM viewtest; + +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC; + +SELECT * FROM viewtest; + +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT a FROM viewtest_tbl WHERE a <> 20; + +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT 1, * FROM viewtest_tbl; + +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b::numeric FROM viewtest_tbl; + +DROP VIEW viewtest; +DROP TABLE viewtest_tbl; diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 6ee6f2a531..20afc9f0ed 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -765,3 +765,21 @@ SELECT viewname, definition FROM pg_views ORDER BY viewname; SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; +-- +-- CREATE OR REPLACE RULE +-- + +CREATE TABLE ruletest_tbl (a int, b int); +CREATE TABLE ruletest_tbl2 (a int, b int); + +CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl + DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10); + +INSERT INTO ruletest_tbl VALUES (99, 99); + +CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl + DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000); + +INSERT INTO ruletest_tbl VALUES (99, 99); + +SELECT * FROM ruletest_tbl2; -- 2.11.0