regression.diffs
text/plain
*** /home/postgres/pgwork/repo/git/postgresql/src/test/regress/expected/merge.out 2010-08-11 12:23:50.000000000 -0400
--- /home/postgres/pgwork/repo/git/postgresql/src/test/regress/results/merge.out 2010-08-11 12:33:27.000000000 -0400
***************
*** 44,57 ****
WHEN MATCHED THEN
UPDATE SET balance = t.balance + s.balance
;
! SELECT * FROM target;
! id | balance
! ----+---------
! 1 | 10
! 2 | 25
! 3 | 50
! (3 rows)
!
ROLLBACK;
-- do a simple equivalent of an INSERT SELECT
BEGIN;
--- 44,50 ----
WHEN MATCHED THEN
UPDATE SET balance = t.balance + s.balance
;
! NOTICE: one tuple is ERROR
ROLLBACK;
-- do a simple equivalent of an INSERT SELECT
BEGIN;
***************
*** 61,66 ****
--- 54,61 ----
WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.balance)
;
+ NOTICE: one tuple is ERROR
+ NOTICE: one tuple is ERROR
SELECT * FROM target;
id | balance
----+---------
***************
*** 102,107 ****
--- 97,103 ----
WHEN MATCHED THEN
DELETE
;
+ NOTICE: one tuple is ERROR
SELECT * FROM target;
id | balance
----+---------
***************
*** 165,176 ****
ERROR: multiple actions on single target row
ROLLBACK;
!
-- This next SQL statement
-- fails according to standard
-- suceeds in PostgreSQL implementation by simply ignoring the second
-- matching row since it activates no WHEN clause
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
--- 161,175 ----
ERROR: multiple actions on single target row
ROLLBACK;
! ERROR: syntax error at or near "ERROR"
! LINE 1: ERROR: multiple actions on single target row
! ^
-- This next SQL statement
-- fails according to standard
-- suceeds in PostgreSQL implementation by simply ignoring the second
-- matching row since it activates no WHEN clause
BEGIN;
+ ERROR: current transaction is aborted, commands ignored until end of transaction block
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
***************
*** 179,184 ****
--- 178,184 ----
WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.balance)
;
+ ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- Now lets prepare the test data to generate 2 non-matching rows
DELETE FROM source WHERE id = 3 AND balance = 5;
***************
*** 188,195 ****
----+---------
2 | 5
3 | 20
- 4 | 5
4 | 40
(4 rows)
-- This next SQL statement
--- 188,195 ----
----+---------
2 | 5
3 | 20
4 | 40
+ 4 | 5
(4 rows)
-- This next SQL statement
***************
*** 203,216 ****
WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.balance)
;
SELECT * FROM target;
id | balance
----+---------
1 | 10
2 | 20
3 | 30
- 4 | 5
4 | 40
(5 rows)
ROLLBACK;
--- 203,218 ----
WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.balance)
;
+ NOTICE: one tuple is ERROR
+ NOTICE: one tuple is ERROR
SELECT * FROM target;
id | balance
----+---------
1 | 10
2 | 20
3 | 30
4 | 40
+ 4 | 5
(5 rows)
ROLLBACK;
***************
*** 225,239 ****
WHEN NOT MATCHED AND s.balance > 100 THEN
INSERT VALUES (s.id, s.balance)
;
SELECT * FROM target;
id | balance
----+---------
1 | 10
2 | 20
3 | 30
! |
! |
! (5 rows)
ROLLBACK;
-- This next SQL statement suceeds, but does nothing since there are
--- 227,243 ----
WHEN NOT MATCHED AND s.balance > 100 THEN
INSERT VALUES (s.id, s.balance)
;
+ NOTICE: one tuple is ERROR
+ NOTICE: one tuple is ERROR
+ NOTICE: one tuple is ERROR
+ NOTICE: one tuple is ERROR
SELECT * FROM target;
id | balance
----+---------
1 | 10
2 | 20
3 | 30
! (3 rows)
ROLLBACK;
-- This next SQL statement suceeds, but does nothing since there are
***************
*** 249,262 ****
WHEN NOT MATCHED
DO NOTHING
;
SELECT * FROM target;
! id | balance
! ----+---------
! 1 | 10
! 2 | 20
! 3 | 30
! (3 rows)
!
ROLLBACK;
--
-- Weirdness
--- 253,263 ----
WHEN NOT MATCHED
DO NOTHING
;
+ ERROR: syntax error at or near "DO"
+ LINE 7: DO NOTHING
+ ^
SELECT * FROM target;
! ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
--
-- Weirdness
======================================================================