From 64aabe232ca4825e99f6ac9f74a2713625b49ab1 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Fri, 30 Oct 2026 21:56:52 +0900 Subject: [PATCH v1 1/2] Add tests for column dependencies of MERGE actions A SQL-standard function body that uses MERGE does not depend on the target columns of the MERGE actions, so ALTER COLUMN TYPE and DROP COLUMN on those columns succeed and the stored query is left referring to dropped columns. The expected output shows the current behavior; the next commit fixes it. --- .../regress/expected/create_function_sql.out | 35 +++++++++++++++++++ src/test/regress/sql/create_function_sql.sql | 20 +++++++++++ 2 files changed, 55 insertions(+) diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out index 50aca5940ff..73a1743a09c 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -454,6 +454,41 @@ DROP TABLE functest3 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view functestv3 drop cascades to function functest_s_14() +-- Check dependencies on the target columns of MERGE actions within +-- SQL-standard body, like those of INSERT and UPDATE +CREATE TABLE functest_mtgt (k int, a int, b int, c int); +CREATE TABLE functest_msrc (k int, v int); +CREATE FUNCTION functest_merge() RETURNS void + BEGIN ATOMIC + MERGE INTO functest_mtgt t USING functest_msrc s ON t.k = s.k + WHEN MATCHED THEN UPDATE SET a = s.v + WHEN NOT MATCHED THEN INSERT (k, b) VALUES (s.k, s.v); + END; +ALTER TABLE functest_mtgt ALTER COLUMN a TYPE bigint; +ALTER TABLE functest_mtgt DROP COLUMN a; +ALTER TABLE functest_mtgt DROP COLUMN b; +ALTER TABLE functest_mtgt DROP COLUMN c; -- not assigned, so OK +SELECT pg_get_functiondef('functest_merge'::regproc); + pg_get_functiondef +------------------------------------------------------------ + CREATE OR REPLACE FUNCTION temp_func_test.functest_merge()+ + RETURNS void + + LANGUAGE sql + + BEGIN ATOMIC + + MERGE INTO functest_mtgt AS t + + USING functest_msrc s + + ON (t.k = s.k) + + WHEN MATCHED + + THEN UPDATE SET "........pg.dropped.2........" = s.v + + WHEN NOT MATCHED + + THEN INSERT (k, "........pg.dropped.3........") + + VALUES (s.k, s.v); + + END + + +(1 row) + +DROP FUNCTION functest_merge(); +DROP TABLE functest_mtgt, functest_msrc; -- information_schema tests CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo') RETURNS int diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql index 89e9af3a499..f28ed03da2b 100644 --- a/src/test/regress/sql/create_function_sql.sql +++ b/src/test/regress/sql/create_function_sql.sql @@ -241,6 +241,26 @@ SELECT functest_S_14(); DROP TABLE functest3 CASCADE; +-- Check dependencies on the target columns of MERGE actions within +-- SQL-standard body, like those of INSERT and UPDATE +CREATE TABLE functest_mtgt (k int, a int, b int, c int); +CREATE TABLE functest_msrc (k int, v int); + +CREATE FUNCTION functest_merge() RETURNS void + BEGIN ATOMIC + MERGE INTO functest_mtgt t USING functest_msrc s ON t.k = s.k + WHEN MATCHED THEN UPDATE SET a = s.v + WHEN NOT MATCHED THEN INSERT (k, b) VALUES (s.k, s.v); + END; + +ALTER TABLE functest_mtgt ALTER COLUMN a TYPE bigint; +ALTER TABLE functest_mtgt DROP COLUMN a; +ALTER TABLE functest_mtgt DROP COLUMN b; +ALTER TABLE functest_mtgt DROP COLUMN c; -- not assigned, so OK +SELECT pg_get_functiondef('functest_merge'::regproc); + +DROP FUNCTION functest_merge(); +DROP TABLE functest_mtgt, functest_msrc; -- information_schema tests -- 2.54.0 (Apple Git-157)