diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c new file mode 100644 index 0338e4e..eedf003 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -73,8 +73,8 @@ static TargetEntry *process_matched_tle( TargetEntry *prior_tle, const char *attrName); static Node *get_assignment_input(Node *node); -static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, - List *attrnos); +static void rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, + Relation target_relation, List *attrnos); static void markQueryForLocking(Query *qry, Node *jtnode, LockClauseStrength strength, LockWaitPolicy waitPolicy, bool pushedDown); @@ -1225,10 +1225,12 @@ searchForDefault(RangeTblEntry *rte) * reject any such attempt with "multiple assignments to same column". */ static void -rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos) +rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, + Relation target_relation, List *attrnos) { List *newValues; ListCell *lc; + bool autoUpdatableView; /* * Rebuilding all the lists is a pretty expensive proposition in a big @@ -1241,6 +1243,53 @@ rewriteValuesRTE(RangeTblEntry *rte, Rel /* Check list lengths (we can assume all the VALUES sublists are alike) */ Assert(list_length(attrnos) == list_length(linitial(rte->values_lists))); + /* + * If we see a DEFAULT placeholder in the VALUES list for a column that + * has no default, we explicitly set the column to NULL except if the + * target relation is an auto-updatable view, in which case we leave the + * value untouched so that any default on the underlying base relation + * will be applied instead. + * + * This exception only applies to auto-updatable views --- for rule- and + * trigger-updatable views, it is the rule/trigger's responsibility to + * work out whether to insert default values into the base relation, and + * we must explicitly set the column to NULL, as for tables. + */ + autoUpdatableView = false; + if (target_relation->rd_rel->relkind == RELKIND_VIEW && + !view_has_instead_trigger(target_relation, CMD_INSERT)) + { + List *locks; + bool hasUpdate; + bool found; + ListCell *l; + + /* Look for an unconditional DO INSTEAD rule */ + locks = matchLocks(CMD_INSERT, target_relation->rd_rules, + parsetree->resultRelation, parsetree, &hasUpdate); + + found = false; + foreach(l, locks) + { + RewriteRule *rule_lock = (RewriteRule *) lfirst(l); + + if (rule_lock->isInstead && + rule_lock->qual == NULL) + { + found = true; + break; + } + } + + /* + * If we didn't find an unconditional DO INSTEAD rule, assume that the + * view is auto-updatable. If it isn't, rewriteTargetView() will + * throw an error. + */ + if (!found) + autoUpdatableView = true; + } + newValues = NIL; foreach(lc, rte->values_lists) { @@ -1268,10 +1317,18 @@ rewriteValuesRTE(RangeTblEntry *rte, Rel /* * If there is no default (ie, default is effectively NULL), - * we've got to explicitly set the column to NULL. + * we've got to explicitly set the column to NULL, unless the + * target relation is an auto-updatable view. */ if (!new_expr) { + if (autoUpdatableView) + { + /* Leave the value untouched */ + newList = lappend(newList, col); + continue; + } + new_expr = (Node *) makeConst(att_tup->atttypid, -1, att_tup->attcollation, @@ -3432,7 +3489,8 @@ RewriteQuery(Query *parsetree, List *rew parsetree->resultRelation, &attrnos); /* ... and the VALUES expression lists */ - rewriteValuesRTE(values_rte, rt_entry_relation, attrnos); + rewriteValuesRTE(parsetree, values_rte, rt_entry_relation, + attrnos); } else { diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out new file mode 100644 index 420c5a5..bc22033 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -2772,3 +2772,111 @@ drop view rw_view1; drop table base_tbl; drop user regress_view_user1; drop user regress_view_user2; +-- Test single- and multi-row inserts with table and view defaults +create table base_tab_def (a int, b text default 'Table default', + c text default 'Table default', d text, e text); +create view base_tab_def_view as select * from base_tab_def; +alter view base_tab_def_view alter b set default 'View default'; +alter view base_tab_def_view alter d set default 'View default'; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by 1; + a | b | c | d | e +----+---------------+---------------+--------------+--- + 1 | Table default | Table default | | + 2 | Table default | Table default | | + 3 | Table default | Table default | | + 4 | Table default | Table default | | + 5 | Table default | Table default | | + 6 | Table default | Table default | | + 11 | View default | Table default | View default | + 12 | View default | Table default | View default | + 13 | View default | Table default | View default | + 14 | View default | Table default | View default | + 15 | View default | Table default | View default | + 16 | View default | Table default | View default | +(12 rows) + +-- INSTEAD OF trigger inserts NULLs rather than the table defaults +create function base_tab_def_view_instrig_func() returns trigger +as +$$ +begin + insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); + return new; +end; +$$ +language plpgsql; +create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view + for each row execute function base_tab_def_view_instrig_func(); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by 1; + a | b | c | d | e +----+---------------+---------------+--------------+--- + 1 | Table default | Table default | | + 2 | Table default | Table default | | + 3 | Table default | Table default | | + 4 | Table default | Table default | | + 5 | Table default | Table default | | + 6 | Table default | Table default | | + 11 | View default | | View default | + 12 | View default | | View default | + 13 | View default | | View default | + 14 | View default | | View default | + 15 | View default | | View default | + 16 | View default | | View default | +(12 rows) + +-- Unconditional DO INSTEAD rule also inserts NULLs +drop trigger base_tab_def_view_instrig on base_tab_def_view; +drop function base_tab_def_view_instrig_func; +create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view + do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by 1; + a | b | c | d | e +----+---------------+---------------+--------------+--- + 1 | Table default | Table default | | + 2 | Table default | Table default | | + 3 | Table default | Table default | | + 4 | Table default | Table default | | + 5 | Table default | Table default | | + 6 | Table default | Table default | | + 11 | View default | | View default | + 12 | View default | | View default | + 13 | View default | | View default | + 14 | View default | | View default | + 15 | View default | | View default | + 16 | View default | | View default | +(12 rows) + +drop view base_tab_def_view; +drop table base_tab_def; diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql new file mode 100644 index dc6d5cb..0276286 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -1379,3 +1379,66 @@ drop view rw_view1; drop table base_tbl; drop user regress_view_user1; drop user regress_view_user2; + +-- Test single- and multi-row inserts with table and view defaults +create table base_tab_def (a int, b text default 'Table default', + c text default 'Table default', d text, e text); +create view base_tab_def_view as select * from base_tab_def; +alter view base_tab_def_view alter b set default 'View default'; +alter view base_tab_def_view alter d set default 'View default'; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by 1; + +-- INSTEAD OF trigger inserts NULLs rather than the table defaults +create function base_tab_def_view_instrig_func() returns trigger +as +$$ +begin + insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); + return new; +end; +$$ +language plpgsql; +create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view + for each row execute function base_tab_def_view_instrig_func(); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by 1; + +-- Unconditional DO INSTEAD rule also inserts NULLs +drop trigger base_tab_def_view_instrig on base_tab_def_view; +drop function base_tab_def_view_instrig_func; +create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view + do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by 1; +drop view base_tab_def_view; +drop table base_tab_def;