summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/updatable_views.out153
-rw-r--r--src/test/regress/sql/updatable_views.sql88
2 files changed, 241 insertions, 0 deletions
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 8b13ef3a091..7ee6a54102a 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2774,3 +2774,156 @@ 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.
+-- Table defaults should be used, unless overridden by 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 a;
+ 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)
+
+-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
+-- table defaults, where there are no view 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 a;
+ 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)
+
+-- Using an unconditional DO INSTEAD rule should also cause NULLs to be
+-- inserted where there are no view defaults.
+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 a;
+ 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)
+
+-- A DO ALSO rule should cause each row to be inserted twice. The first
+-- insert should behave the same as an auto-updatable view (using table
+-- defaults, unless overridden by view defaults). The second insert should
+-- behave the same as a rule-updatable view (inserting NULLs where there are
+-- no view defaults).
+drop rule base_tab_def_view_ins_rule on base_tab_def_view;
+create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
+ do also 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 a, c NULLS LAST;
+ 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 |
+ 11 | View default | | View default |
+ 12 | View default | Table default | View default |
+ 12 | View default | | View default |
+ 13 | View default | Table default | View default |
+ 13 | View default | | View default |
+ 14 | View default | Table default | View default |
+ 14 | View default | | View default |
+ 15 | View default | Table default | View default |
+ 15 | View default | | View default |
+ 16 | View default | Table default | View default |
+ 16 | View default | | View default |
+(18 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
index 2ecc3862384..71c4c743367 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -1382,3 +1382,91 @@ 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.
+-- Table defaults should be used, unless overridden by 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 a;
+
+-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
+-- table defaults, where there are no view 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 a;
+
+-- Using an unconditional DO INSTEAD rule should also cause NULLs to be
+-- inserted where there are no view defaults.
+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 a;
+
+-- A DO ALSO rule should cause each row to be inserted twice. The first
+-- insert should behave the same as an auto-updatable view (using table
+-- defaults, unless overridden by view defaults). The second insert should
+-- behave the same as a rule-updatable view (inserting NULLs where there are
+-- no view defaults).
+drop rule base_tab_def_view_ins_rule on base_tab_def_view;
+create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
+ do also 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 a, c NULLS LAST;
+
+drop view base_tab_def_view;
+drop table base_tab_def;
generated by cgit v1.2.3 (git 2.46.0) at 2025-10-09 18:40:44 +0000