diff options
Diffstat (limited to '')
| -rw-r--r-- | src/test/regress/expected/merge.out | 91 |
1 files changed, 91 insertions, 0 deletions
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 15c6632..edc0043 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1662,6 +1662,10 @@ ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT; INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id; -- try simple MERGE BEGIN; +DO $$ +DECLARE + result integer; +BEGIN MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid @@ -1669,6 +1673,11 @@ MERGE INTO pa_target t UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN INSERT VALUES (sid, delta, 'inserted by merge'); +GET DIAGNOSTICS result := ROW_COUNT; +RAISE NOTICE 'ROW_COUNT = %', result; +END; +$$; +NOTICE: ROW_COUNT = 14 SELECT * FROM pa_target ORDER BY tid; tid | balance | val -----+---------+-------------------------- @@ -1725,6 +1734,10 @@ SELECT * FROM pa_target ORDER BY tid; ROLLBACK; -- try updating the partition key column BEGIN; +DO $$ +DECLARE + result integer; +BEGIN MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid @@ -1732,6 +1745,11 @@ MERGE INTO pa_target t UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN INSERT VALUES (sid, delta, 'inserted by merge'); +GET DIAGNOSTICS result := ROW_COUNT; +RAISE NOTICE 'ROW_COUNT = %', result; +END; +$$; +NOTICE: ROW_COUNT = 14 SELECT * FROM pa_target ORDER BY tid; tid | balance | val -----+---------+-------------------------- @@ -1752,6 +1770,79 @@ SELECT * FROM pa_target ORDER BY tid; (14 rows) ROLLBACK; +-- as above, but blocked by BEFORE DELETE ROW trigger +BEGIN; +CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS + $$ BEGIN RETURN NULL; END; $$; +CREATE TRIGGER del_trig BEFORE DELETE ON pa_target + FOR EACH ROW EXECUTE PROCEDURE trig_fn(); +DO $$ +DECLARE + result integer; +BEGIN +MERGE INTO pa_target t + USING pa_source s + ON t.tid = s.sid + WHEN MATCHED THEN + UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' + WHEN NOT MATCHED THEN + INSERT VALUES (sid, delta, 'inserted by merge'); +GET DIAGNOSTICS result := ROW_COUNT; +RAISE NOTICE 'ROW_COUNT = %', result; +END; +$$; +NOTICE: ROW_COUNT = 10 +SELECT * FROM pa_target ORDER BY tid; + tid | balance | val +-----+---------+-------------------------- + 1 | 100 | initial + 2 | 20 | inserted by merge + 3 | 300 | initial + 4 | 40 | inserted by merge + 6 | 550 | initial updated by merge + 6 | 60 | inserted by merge + 7 | 700 | initial + 8 | 80 | inserted by merge + 9 | 900 | initial + 10 | 100 | inserted by merge + 12 | 1210 | initial updated by merge + 12 | 120 | inserted by merge + 14 | 1430 | initial updated by merge + 14 | 140 | inserted by merge +(14 rows) + +ROLLBACK; +-- as above, but blocked by BEFORE INSERT ROW trigger +BEGIN; +CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS + $$ BEGIN RETURN NULL; END; $$; +CREATE TRIGGER ins_trig BEFORE INSERT ON pa_target + FOR EACH ROW EXECUTE PROCEDURE trig_fn(); +DO $$ +DECLARE + result integer; +BEGIN +MERGE INTO pa_target t + USING pa_source s + ON t.tid = s.sid + WHEN MATCHED THEN + UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' + WHEN NOT MATCHED THEN + INSERT VALUES (sid, delta, 'inserted by merge'); +GET DIAGNOSTICS result := ROW_COUNT; +RAISE NOTICE 'ROW_COUNT = %', result; +END; +$$; +NOTICE: ROW_COUNT = 3 +SELECT * FROM pa_target ORDER BY tid; + tid | balance | val +-----+---------+-------------------------- + 6 | 550 | initial updated by merge + 12 | 1210 | initial updated by merge + 14 | 1430 | initial updated by merge +(3 rows) + +ROLLBACK; -- test RLS enforcement BEGIN; ALTER TABLE pa_target ENABLE ROW LEVEL SECURITY; |