diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/merge.out | 266 | ||||
| -rw-r--r-- | src/test/regress/expected/rowsecurity.out | 32 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 16 | ||||
| -rw-r--r-- | src/test/regress/expected/updatable_views.out | 30 | ||||
| -rw-r--r-- | src/test/regress/expected/with.out | 10 | ||||
| -rw-r--r-- | src/test/regress/sql/merge.sql | 169 | ||||
| -rw-r--r-- | src/test/regress/sql/rowsecurity.sql | 21 | ||||
| -rw-r--r-- | src/test/regress/sql/rules.sql | 6 | ||||
| -rw-r--r-- | src/test/regress/sql/updatable_views.sql | 9 | ||||
| -rw-r--r-- | src/test/regress/sql/with.sql | 8 |
10 files changed, 521 insertions, 46 deletions
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 1a6f6ad43d1..07561f02407 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -124,20 +124,20 @@ ON tid = tid WHEN MATCHED THEN DO NOTHING; ERROR: name "target" specified more than once DETAIL: The name is used both as MERGE target table and data source. --- used in a CTE +-- used in a CTE without RETURNING WITH foo AS ( MERGE INTO target USING source ON (true) WHEN MATCHED THEN DELETE ) SELECT * FROM foo; -ERROR: MERGE not supported in WITH query -LINE 1: WITH foo AS ( - ^ --- used in COPY +ERROR: WITH query "foo" does not have a RETURNING clause +LINE 4: ) SELECT * FROM foo; + ^ +-- used in COPY without RETURNING COPY ( MERGE INTO target USING source ON (true) WHEN MATCHED THEN DELETE ) TO stdout; -ERROR: MERGE not supported in COPY +ERROR: COPY query must have a RETURNING clause -- unsupported relation types -- materialized view CREATE MATERIALIZED VIEW mv AS SELECT * FROM target; @@ -1255,7 +1255,7 @@ BEGIN; MERGE INTO sq_target USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -1270,7 +1270,7 @@ INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10); MERGE INTO sq_target t USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -1294,7 +1294,7 @@ WITH targq AS ( MERGE INTO sq_target t USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -1302,21 +1302,207 @@ WHEN MATCHED AND tid < 2 THEN DELETE; ROLLBACK; -- RETURNING +SELECT * FROM sq_source ORDER BY sid; + delta | sid | balance +-------+-----+--------- + 10 | 1 | 0 + 20 | 2 | 0 + 40 | 4 | 0 +(3 rows) + +SELECT * FROM sq_target ORDER BY tid; + tid | balance +-----+--------- + 1 | 100 + 2 | 200 + 3 | 300 +(3 rows) + BEGIN; -INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10); +CREATE TABLE merge_actions(action text, abbrev text); +INSERT INTO merge_actions VALUES ('INSERT', 'ins'), ('UPDATE', 'upd'), ('DELETE', 'del'); MERGE INTO sq_target t -USING v +USING sq_source s ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN - INSERT (balance, tid) VALUES (balance + delta, sid) + INSERT (balance, tid) VALUES (balance + delta, sid) WHEN MATCHED AND tid < 2 THEN - DELETE -RETURNING *; -ERROR: syntax error at or near "RETURNING" -LINE 10: RETURNING *; - ^ + DELETE +RETURNING (SELECT abbrev FROM merge_actions + WHERE action = merge_action()) AS action, + t.*, + CASE merge_action() + WHEN 'INSERT' THEN 'Inserted '||t + WHEN 'UPDATE' THEN 'Added '||delta||' to balance' + WHEN 'DELETE' THEN 'Removed '||t + END AS description; + action | tid | balance | description +--------+-----+---------+--------------------- + del | 1 | 100 | Removed (1,100) + upd | 2 | 220 | Added 20 to balance + ins | 4 | 40 | Inserted (4,40) +(3 rows) + +ROLLBACK; +-- error when using merge_action() outside MERGE +SELECT merge_action() FROM sq_target; +ERROR: MERGE_ACTION() can only be used in the RETURNING list of a MERGE command +LINE 1: SELECT merge_action() FROM sq_target; + ^ +UPDATE sq_target SET balance = balance + 1 RETURNING merge_action(); +ERROR: MERGE_ACTION() can only be used in the RETURNING list of a MERGE command +LINE 1: ...ATE sq_target SET balance = balance + 1 RETURNING merge_acti... + ^ +-- RETURNING in CTEs +CREATE TABLE sq_target_merge_log (tid integer NOT NULL, last_change text); +INSERT INTO sq_target_merge_log VALUES (1, 'Original value'); +BEGIN; +WITH m AS ( + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action() AS action, t.*, + CASE merge_action() + WHEN 'INSERT' THEN 'Inserted '||t + WHEN 'UPDATE' THEN 'Added '||delta||' to balance' + WHEN 'DELETE' THEN 'Removed '||t + END AS description +), m2 AS ( + MERGE INTO sq_target_merge_log l + USING m + ON l.tid = m.tid + WHEN MATCHED THEN + UPDATE SET last_change = description + WHEN NOT MATCHED THEN + INSERT VALUES (m.tid, description) + RETURNING action, merge_action() AS log_action, l.* +) +SELECT * FROM m2; + action | log_action | tid | last_change +--------+------------+-----+--------------------- + DELETE | UPDATE | 1 | Removed (1,100) + UPDATE | INSERT | 2 | Added 20 to balance + INSERT | INSERT | 4 | Inserted (4,40) +(3 rows) + +SELECT * FROM sq_target_merge_log ORDER BY tid; + tid | last_change +-----+--------------------- + 1 | Removed (1,100) + 2 | Added 20 to balance + 4 | Inserted (4,40) +(3 rows) + +ROLLBACK; +-- COPY (MERGE ... RETURNING) TO ... +BEGIN; +COPY ( + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.* +) TO stdout; +DELETE 1 100 +UPDATE 2 220 +INSERT 4 40 +ROLLBACK; +-- SQL function with MERGE ... RETURNING +BEGIN; +CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int, + OUT action text, OUT tid int, OUT new_balance int) +LANGUAGE sql AS +$$ + MERGE INTO sq_target t + USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta) + ON tid = v.sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + v.delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.*; +$$; +SELECT m.* +FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta), +LATERAL (SELECT action, tid, new_balance FROM merge_into_sq_target(sid, balance, delta)) m; + action | tid | new_balance +--------+-----+------------- + DELETE | 1 | 100 + UPDATE | 3 | 320 + INSERT | 4 | 110 +(3 rows) + +ROLLBACK; +-- SQL SRF with MERGE ... RETURNING +BEGIN; +CREATE FUNCTION merge_sq_source_into_sq_target() +RETURNS TABLE (action text, tid int, balance int) +LANGUAGE sql AS +$$ + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.*; +$$; +SELECT * FROM merge_sq_source_into_sq_target(); + action | tid | balance +--------+-----+--------- + DELETE | 1 | 100 + UPDATE | 2 | 220 + INSERT | 4 | 40 +(3 rows) + +ROLLBACK; +-- PL/pgSQL function with MERGE ... RETURNING ... INTO +BEGIN; +CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int, + OUT r_action text, OUT r_tid int, OUT r_balance int) +LANGUAGE plpgsql AS +$$ +BEGIN + MERGE INTO sq_target t + USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta) + ON tid = v.sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + v.delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.* INTO r_action, r_tid, r_balance; +END; +$$; +SELECT m.* +FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta), +LATERAL (SELECT r_action, r_tid, r_balance FROM merge_into_sq_target(sid, balance, delta)) m; + r_action | r_tid | r_balance +----------+-------+----------- + DELETE | 1 | 100 + UPDATE | 3 | 320 + INSERT | 4 | 110 +(3 rows) + ROLLBACK; -- EXPLAIN CREATE TABLE ex_mtarget (a int, b int) @@ -1563,7 +1749,7 @@ SELECT * FROM sq_target WHERE tid = 1; (1 row) ROLLBACK; -DROP TABLE sq_target, sq_source CASCADE; +DROP TABLE sq_target, sq_target_merge_log, sq_source CASCADE; NOTICE: drop cascades to view v CREATE TABLE pa_target (tid integer, balance float, val text) PARTITION BY LIST (tid); @@ -1689,6 +1875,32 @@ SELECT * FROM pa_target ORDER BY tid; (14 rows) ROLLBACK; +-- update partition key to partition not initially scanned +BEGIN; +MERGE INTO pa_target t + USING pa_source s + ON t.tid = s.sid AND t.tid = 1 + WHEN MATCHED THEN + UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' + RETURNING merge_action(), t.*; + merge_action | tid | balance | val +--------------+-----+---------+-------------------------- + UPDATE | 2 | 110 | initial updated by merge +(1 row) + +SELECT * FROM pa_target ORDER BY tid; + tid | balance | val +-----+---------+-------------------------- + 2 | 110 | initial updated by merge + 3 | 300 | initial + 5 | 500 | initial + 7 | 700 | initial + 9 | 900 | initial + 11 | 1100 | initial + 13 | 1300 | initial +(7 rows) + +ROLLBACK; DROP TABLE pa_target CASCADE; -- The target table is partitioned in the same way, but this time by attaching -- partitions which have columns in different order, dropped columns etc. @@ -1938,7 +2150,21 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge'); + INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge') + RETURNING merge_action(), t.*; + merge_action | logts | tid | balance | val +--------------+--------------------------+-----+---------+-------------------------- + UPDATE | Tue Jan 31 00:00:00 2017 | 1 | 110 | initial updated by merge + UPDATE | Tue Feb 28 00:00:00 2017 | 2 | 220 | initial updated by merge + INSERT | Sun Jan 15 00:00:00 2017 | 3 | 30 | inserted by merge + UPDATE | Tue Jan 31 00:00:00 2017 | 4 | 440 | initial updated by merge + UPDATE | Tue Feb 28 00:00:00 2017 | 5 | 550 | initial updated by merge + INSERT | Sun Jan 15 00:00:00 2017 | 6 | 60 | inserted by merge + UPDATE | Tue Jan 31 00:00:00 2017 | 7 | 770 | initial updated by merge + UPDATE | Tue Feb 28 00:00:00 2017 | 8 | 880 | initial updated by merge + INSERT | Sun Jan 15 00:00:00 2017 | 9 | 90 | inserted by merge +(9 rows) + SELECT * FROM pa_target ORDER BY tid; logts | tid | balance | val --------------------------+-----+---------+-------------------------- diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 4538f0c37d5..d507a2c8ca5 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -2302,6 +2302,35 @@ WHEN MATCHED THEN UPDATE SET dnotes = dnotes || ' notes added by merge8 ' WHEN NOT MATCHED THEN INSERT VALUES (13, 44, 1, 'regress_rls_bob', 'new manga'); +SELECT * FROM document WHERE did = 13; + did | cid | dlevel | dauthor | dtitle | dnotes +-----+-----+--------+---------+--------+-------- +(0 rows) + +-- but not OK if RETURNING is used +MERGE INTO document d +USING (SELECT 14 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge9 ' +WHEN NOT MATCHED THEN + INSERT VALUES (14, 44, 1, 'regress_rls_bob', 'new manga') +RETURNING *; +ERROR: new row violates row-level security policy for table "document" +-- but OK if new row is visible +MERGE INTO document d +USING (SELECT 14 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge10 ' +WHEN NOT MATCHED THEN + INSERT VALUES (14, 11, 1, 'regress_rls_bob', 'new novel') +RETURNING *; + sdid | did | cid | dlevel | dauthor | dtitle | dnotes +------+-----+-----+--------+-----------------+-----------+-------- + 14 | 14 | 11 | 1 | regress_rls_bob | new novel | +(1 row) + RESET SESSION AUTHORIZATION; -- drop the restrictive SELECT policy so that we can look at the -- final state of the table @@ -2325,7 +2354,8 @@ SELECT * FROM document; 12 | 11 | 1 | regress_rls_bob | another novel | 1 | 11 | 1 | regress_rls_bob | my first novel | notes added by merge2 notes added by merge3 notes added by merge4 notes added by merge7 13 | 44 | 1 | regress_rls_bob | new manga | -(15 rows) + 14 | 11 | 1 | regress_rls_bob | new novel | +(16 rows) -- -- ROLE/GROUP diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 0cd2c64fca1..84e359f6ed9 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3638,7 +3638,7 @@ MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s -- test deparsing CREATE TABLE sf_target(id int, data text, filling int[]); CREATE FUNCTION merge_sf_test() - RETURNS void + RETURNS TABLE(action text, a int, b text, id int, data text, filling int[]) LANGUAGE sql BEGIN ATOMIC MERGE INTO sf_target t @@ -3675,11 +3675,13 @@ WHEN NOT MATCHED VALUES (s.a, s.b, DEFAULT) WHEN NOT MATCHED THEN INSERT (filling[1], id) - VALUES (s.a, s.a); + VALUES (s.a, s.a) +RETURNING + merge_action() AS action, *; END; \sf merge_sf_test CREATE OR REPLACE FUNCTION public.merge_sf_test() - RETURNS void + RETURNS TABLE(action text, a integer, b text, id integer, data text, filling integer[]) LANGUAGE sql BEGIN ATOMIC MERGE INTO sf_target t @@ -3716,7 +3718,13 @@ BEGIN ATOMIC VALUES (s.a, s.b, DEFAULT) WHEN NOT MATCHED THEN INSERT (filling[1], id) - VALUES (s.a, s.a); + VALUES (s.a, s.a) + RETURNING MERGE_ACTION() AS action, + s.a, + s.b, + t.id, + t.data, + t.filling; END DROP FUNCTION merge_sf_test; DROP TABLE sf_target; diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 794cf9cf930..1062c341d8e 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -479,7 +479,15 @@ MERGE INTO rw_view1 t (2, 'ROW 2'), (3, 'ROW 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b WHEN MATCHED THEN DELETE - WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a); + WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) + RETURNING merge_action(), v.*, t.*; + merge_action | a | b | a | b +--------------+---+-------+---+------------- + UPDATE | 1 | ROW 1 | 1 | ROW 1 + DELETE | 3 | ROW 3 | 3 | Row 3 + INSERT | 2 | ROW 2 | 2 | Unspecified +(3 rows) + SELECT * FROM base_tbl ORDER BY a; a | b ----+------------- @@ -631,7 +639,15 @@ MERGE INTO rw_view2 t USING (VALUES (3, 'R3'), (4, 'R4'), (5, 'R5')) AS v(a,b) ON aaa = v.a WHEN MATCHED AND aaa = 3 THEN DELETE WHEN MATCHED THEN UPDATE SET bbb = v.b - WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a); + WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) + RETURNING merge_action(), v.*, t.*; + merge_action | a | b | aaa | bbb +--------------+---+----+-----+------------- + DELETE | 3 | R3 | 3 | Row 3 + UPDATE | 4 | R4 | 4 | R4 + INSERT | 5 | R5 | 5 | Unspecified +(3 rows) + SELECT * FROM rw_view2 ORDER BY aaa; aaa | bbb -----+------------- @@ -1071,7 +1087,15 @@ MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED AND t.a <= 1 THEN DELETE WHEN MATCHED THEN UPDATE SET b = s.b - WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); + WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) + RETURNING merge_action(), s.*, t.*; + merge_action | a | b | a | b +--------------+---+----+---+------- + DELETE | 1 | R1 | 1 | Row 1 + UPDATE | 2 | R2 | 2 | R2 + INSERT | 3 | R3 | 3 | R3 +(3 rows) + SELECT * FROM base_tbl ORDER BY a; a | b ----+-------- diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 7d796ea69cd..6de347b528d 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -3493,6 +3493,16 @@ SELECT * FROM t; ERROR: WITH query "t" does not have a RETURNING clause LINE 4: SELECT * FROM t; ^ +-- RETURNING tries to return its own output +WITH RECURSIVE t(action, a) AS ( + MERGE INTO y USING (VALUES (11)) v(a) ON y.a = v.a + WHEN NOT MATCHED THEN INSERT VALUES (v.a) + RETURNING merge_action(), (SELECT a FROM t) +) +SELECT * FROM t; +ERROR: recursive query "t" must not contain data-modifying statements +LINE 1: WITH RECURSIVE t(action, a) AS ( + ^ -- data-modifying WITH allowed only at the top level SELECT * FROM ( WITH t AS (UPDATE y SET a=a+1 RETURNING *) diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index dbbba2a1cdd..875cf6f93d7 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -88,12 +88,12 @@ MERGE INTO target USING target ON tid = tid WHEN MATCHED THEN DO NOTHING; --- used in a CTE +-- used in a CTE without RETURNING WITH foo AS ( MERGE INTO target USING source ON (true) WHEN MATCHED THEN DELETE ) SELECT * FROM foo; --- used in COPY +-- used in COPY without RETURNING COPY ( MERGE INTO target USING source ON (true) WHEN MATCHED THEN DELETE @@ -817,7 +817,7 @@ BEGIN; MERGE INTO sq_target USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -830,7 +830,7 @@ INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10); MERGE INTO sq_target t USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -848,7 +848,7 @@ WITH targq AS ( MERGE INTO sq_target t USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -857,18 +857,149 @@ WHEN MATCHED AND tid < 2 THEN ROLLBACK; -- RETURNING +SELECT * FROM sq_source ORDER BY sid; +SELECT * FROM sq_target ORDER BY tid; + BEGIN; -INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10); +CREATE TABLE merge_actions(action text, abbrev text); +INSERT INTO merge_actions VALUES ('INSERT', 'ins'), ('UPDATE', 'upd'), ('DELETE', 'del'); MERGE INTO sq_target t -USING v +USING sq_source s ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN - INSERT (balance, tid) VALUES (balance + delta, sid) + INSERT (balance, tid) VALUES (balance + delta, sid) WHEN MATCHED AND tid < 2 THEN - DELETE -RETURNING *; + DELETE +RETURNING (SELECT abbrev FROM merge_actions + WHERE action = merge_action()) AS action, + t.*, + CASE merge_action() + WHEN 'INSERT' THEN 'Inserted '||t + WHEN 'UPDATE' THEN 'Added '||delta||' to balance' + WHEN 'DELETE' THEN 'Removed '||t + END AS description; +ROLLBACK; + +-- error when using merge_action() outside MERGE +SELECT merge_action() FROM sq_target; +UPDATE sq_target SET balance = balance + 1 RETURNING merge_action(); + +-- RETURNING in CTEs +CREATE TABLE sq_target_merge_log (tid integer NOT NULL, last_change text); +INSERT INTO sq_target_merge_log VALUES (1, 'Original value'); +BEGIN; +WITH m AS ( + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action() AS action, t.*, + CASE merge_action() + WHEN 'INSERT' THEN 'Inserted '||t + WHEN 'UPDATE' THEN 'Added '||delta||' to balance' + WHEN 'DELETE' THEN 'Removed '||t + END AS description +), m2 AS ( + MERGE INTO sq_target_merge_log l + USING m + ON l.tid = m.tid + WHEN MATCHED THEN + UPDATE SET last_change = description + WHEN NOT MATCHED THEN + INSERT VALUES (m.tid, description) + RETURNING action, merge_action() AS log_action, l.* +) +SELECT * FROM m2; +SELECT * FROM sq_target_merge_log ORDER BY tid; +ROLLBACK; + +-- COPY (MERGE ... RETURNING) TO ... +BEGIN; +COPY ( + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.* +) TO stdout; +ROLLBACK; + +-- SQL function with MERGE ... RETURNING +BEGIN; +CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int, + OUT action text, OUT tid int, OUT new_balance int) +LANGUAGE sql AS +$$ + MERGE INTO sq_target t + USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta) + ON tid = v.sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + v.delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.*; +$$; +SELECT m.* +FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta), +LATERAL (SELECT action, tid, new_balance FROM merge_into_sq_target(sid, balance, delta)) m; +ROLLBACK; + +-- SQL SRF with MERGE ... RETURNING +BEGIN; +CREATE FUNCTION merge_sq_source_into_sq_target() +RETURNS TABLE (action text, tid int, balance int) +LANGUAGE sql AS +$$ + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.*; +$$; +SELECT * FROM merge_sq_source_into_sq_target(); +ROLLBACK; + +-- PL/pgSQL function with MERGE ... RETURNING ... INTO +BEGIN; +CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int, + OUT r_action text, OUT r_tid int, OUT r_balance int) +LANGUAGE plpgsql AS +$$ +BEGIN + MERGE INTO sq_target t + USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta) + ON tid = v.sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + v.delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.* INTO r_action, r_tid, r_balance; +END; +$$; +SELECT m.* +FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta), +LATERAL (SELECT r_action, r_tid, r_balance FROM merge_into_sq_target(sid, balance, delta)) m; ROLLBACK; -- EXPLAIN @@ -984,7 +1115,7 @@ WHEN MATCHED THEN SELECT * FROM sq_target WHERE tid = 1; ROLLBACK; -DROP TABLE sq_target, sq_source CASCADE; +DROP TABLE sq_target, sq_target_merge_log, sq_source CASCADE; CREATE TABLE pa_target (tid integer, balance float, val text) PARTITION BY LIST (tid); @@ -1051,6 +1182,17 @@ SELECT merge_func(); SELECT * FROM pa_target ORDER BY tid; ROLLBACK; +-- update partition key to partition not initially scanned +BEGIN; +MERGE INTO pa_target t + USING pa_source s + ON t.tid = s.sid AND t.tid = 1 + WHEN MATCHED THEN + UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' + RETURNING merge_action(), t.*; +SELECT * FROM pa_target ORDER BY tid; +ROLLBACK; + DROP TABLE pa_target CASCADE; -- The target table is partitioned in the same way, but this time by attaching @@ -1227,7 +1369,8 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge'); + INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge') + RETURNING merge_action(), t.*; SELECT * FROM pa_target ORDER BY tid; ROLLBACK; diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index dec7340538c..1d5ed0a647b 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -992,6 +992,27 @@ WHEN MATCHED THEN UPDATE SET dnotes = dnotes || ' notes added by merge8 ' WHEN NOT MATCHED THEN INSERT VALUES (13, 44, 1, 'regress_rls_bob', 'new manga'); +SELECT * FROM document WHERE did = 13; + +-- but not OK if RETURNING is used +MERGE INTO document d +USING (SELECT 14 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge9 ' +WHEN NOT MATCHED THEN + INSERT VALUES (14, 44, 1, 'regress_rls_bob', 'new manga') +RETURNING *; + +-- but OK if new row is visible +MERGE INTO document d +USING (SELECT 14 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge10 ' +WHEN NOT MATCHED THEN + INSERT VALUES (14, 11, 1, 'regress_rls_bob', 'new novel') +RETURNING *; RESET SESSION AUTHORIZATION; -- drop the restrictive SELECT policy so that we can look at the diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 6924012a10c..27340bacbc5 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1294,7 +1294,7 @@ MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s CREATE TABLE sf_target(id int, data text, filling int[]); CREATE FUNCTION merge_sf_test() - RETURNS void + RETURNS TABLE(action text, a int, b text, id int, data text, filling int[]) LANGUAGE sql BEGIN ATOMIC MERGE INTO sf_target t @@ -1331,7 +1331,9 @@ WHEN NOT MATCHED VALUES (s.a, s.b, DEFAULT) WHEN NOT MATCHED THEN INSERT (filling[1], id) - VALUES (s.a, s.a); + VALUES (s.a, s.a) +RETURNING + merge_action() AS action, *; END; \sf merge_sf_test diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index ae11e46da28..afdf331fed6 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -175,7 +175,8 @@ MERGE INTO rw_view1 t (2, 'ROW 2'), (3, 'ROW 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b WHEN MATCHED THEN DELETE - WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a); + WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) + RETURNING merge_action(), v.*, t.*; SELECT * FROM base_tbl ORDER BY a; EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5; @@ -246,7 +247,8 @@ MERGE INTO rw_view2 t USING (VALUES (3, 'R3'), (4, 'R4'), (5, 'R5')) AS v(a,b) ON aaa = v.a WHEN MATCHED AND aaa = 3 THEN DELETE WHEN MATCHED THEN UPDATE SET bbb = v.b - WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a); + WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) + RETURNING merge_action(), v.*, t.*; SELECT * FROM rw_view2 ORDER BY aaa; EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4; @@ -458,7 +460,8 @@ MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED AND t.a <= 1 THEN DELETE WHEN MATCHED THEN UPDATE SET b = s.b - WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); + WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) + RETURNING merge_action(), s.*, t.*; SELECT * FROM base_tbl ORDER BY a; EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2; diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index f8a213e3570..037bc0a511b 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -1591,6 +1591,14 @@ WITH t AS ( ) SELECT * FROM t; +-- RETURNING tries to return its own output +WITH RECURSIVE t(action, a) AS ( + MERGE INTO y USING (VALUES (11)) v(a) ON y.a = v.a + WHEN NOT MATCHED THEN INSERT VALUES (v.a) + RETURNING merge_action(), (SELECT a FROM t) +) +SELECT * FROM t; + -- data-modifying WITH allowed only at the top level SELECT * FROM ( WITH t AS (UPDATE y SET a=a+1 RETURNING *) |