diff options
Diffstat (limited to 'src/test/regress/sql/with.sql')
| -rw-r--r-- | src/test/regress/sql/with.sql | 56 |
1 files changed, 56 insertions, 0 deletions
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index e482177557..f1ea3ae22e 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -1270,6 +1270,62 @@ RETURNING k, v; DROP TABLE withz; +-- WITH referenced by MERGE statement +CREATE TABLE m AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i; +ALTER TABLE m ADD UNIQUE (k); + +WITH RECURSIVE cte_basic AS (SELECT 1 a, 'cte_basic val' b) +MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k +WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1) +WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); + +-- Basic: +WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b) +MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k +WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1) +WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); +-- Examine +SELECT * FROM m where k = 0; + +-- See EXPLAIN output for same query: +EXPLAIN (VERBOSE, COSTS OFF) +WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b) +MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k +WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1) +WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); + +-- InitPlan +WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b) +MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON m.k=o.k +WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1) +WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); +-- Examine +SELECT * FROM m where k = 1; + +-- See EXPLAIN output for same query: +EXPLAIN (VERBOSE, COSTS OFF) +WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b) +MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON m.k=o.k +WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1) +WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); + +-- MERGE source comes from CTE: +WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b) +MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a +WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15) +WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte)); +-- Examine +SELECT * FROM m where k = 15; + +-- See EXPLAIN output for same query: +EXPLAIN (VERBOSE, COSTS OFF) +WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b) +MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a +WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15) +WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte)); + +DROP TABLE m; + -- check that run to completion happens in proper ordering TRUNCATE TABLE y; |