From fa6d838768b29b979e5458356aa20cd44476a5b4 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Fri, 2 Oct 2026 22:39:11 +0900 Subject: [PATCH v1 1/2] Add tests for parameter references in SQL-function bodies A parameter reference in an SQL-function body is not always read back as the same parameter. The qualified form funcname.argname is taken as a column, a system column or a call of a function on the whole row when a name visible as a relation qualifier is the name of the function. Without relations, the bare name of a parameter used as an ORDER BY, DISTINCT ON or GROUP BY key is taken as an output column. An unnamed parameter is printed as an empty identifier. The expected output shows the behavior before the fix. The next commit fixes it and updates the output. --- .../regress/expected/create_function_sql.out | 365 +++++++++++++++++- src/test/regress/sql/create_function_sql.sql | 116 ++++++ 2 files changed, 480 insertions(+), 1 deletion(-) diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out index 50aca5940ff..541d0ebd24c 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -706,9 +706,353 @@ LINE 2: AS 'SELECT $2;'; CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL AS 'a', 'b'; ERROR: only one AS item needed for language "sql" +-- unnamed parameters of an SQL-body function are printed as $N +CREATE FUNCTION functest_unnamed(a int, int) RETURNS int + BEGIN ATOMIC SELECT a + $2; END; +SELECT functest_unnamed(1, 2); + functest_unnamed +------------------ + 3 +(1 row) + +SELECT pg_get_functiondef('functest_unnamed'::regproc); + pg_get_functiondef +-------------------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_unnamed(a integer, integer)+ + RETURNS integer + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT (a + ""); + + END + + +(1 row) + +-- a parameter must not be qualified with the function name if a relation of +-- that name is visible: a column named like the parameter, even one added +-- after the function was created, would capture the qualified name +CREATE TABLE functest_cap (x int); +INSERT INTO functest_cap VALUES (5); +CREATE FUNCTION functest_cap(a int) RETURNS int + BEGIN ATOMIC SELECT a + x FROM functest_cap; END; +SELECT functest_cap(10); + functest_cap +-------------- + 15 +(1 row) + +ALTER TABLE functest_cap ADD COLUMN a int DEFAULT 100; +SELECT pg_get_functiondef('functest_cap'::regproc); + pg_get_functiondef +------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_cap(a integer)+ + RETURNS integer + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT (functest_cap.a + functest_cap.x) + + FROM functest_cap; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_cap'::regproc) AS def \gset +:def ; +SELECT functest_cap(10); + functest_cap +-------------- + 105 +(1 row) + +-- the same if a column is renamed to the name of a parameter that the body +-- refers to as funcname.argname +CREATE TABLE functest_ren (b int); +INSERT INTO functest_ren VALUES (5); +CREATE FUNCTION functest_ren(a int) RETURNS int + BEGIN ATOMIC SELECT functest_ren.a + functest_ren.b FROM functest_ren; END; +SELECT functest_ren(10); + functest_ren +-------------- + 15 +(1 row) + +ALTER TABLE functest_ren RENAME b TO a; +SELECT pg_get_functiondef('functest_ren'::regproc); + pg_get_functiondef +------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_ren(a integer)+ + RETURNS integer + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT (functest_ren.a + functest_ren.a) + + FROM functest_ren; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_ren'::regproc) AS def \gset +:def ; +SELECT functest_ren(10); + functest_ren +-------------- + 10 +(1 row) + +-- the same for any name the parser can take as a qualifier: a function +-- taking the whole row, as in num_nulls(functest_nn.*) +CREATE TABLE functest_nn (x int); +INSERT INTO functest_nn VALUES (1); +CREATE FUNCTION functest_nn(num_nulls int) RETURNS int + BEGIN ATOMIC SELECT num_nulls + x FROM functest_nn; END; +SELECT functest_nn(7); + functest_nn +------------- + 8 +(1 row) + +SELECT pg_get_functiondef('functest_nn'::regproc); + pg_get_functiondef +-------------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_nn(num_nulls integer)+ + RETURNS integer + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT (functest_nn.num_nulls + functest_nn.x) + + FROM functest_nn; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_nn'::regproc) AS def \gset +:def ; +SELECT functest_nn(7); + functest_nn +------------- + 1 +(1 row) + +-- a system column +CREATE TABLE functest_sys (x int); +INSERT INTO functest_sys VALUES (1); +CREATE FUNCTION functest_sys(cmin int) RETURNS int + BEGIN ATOMIC SELECT $1 FROM functest_sys; END; +SELECT functest_sys(5); + functest_sys +-------------- + 5 +(1 row) + +SELECT pg_get_functiondef('functest_sys'::regproc); + pg_get_functiondef +---------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_sys(cmin integer)+ + RETURNS integer + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT functest_sys.cmin + + FROM functest_sys; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_sys'::regproc) AS def \gset +:def ; +ERROR: return type mismatch in function declared to return integer +DETAIL: Actual return type is cid. +CONTEXT: SQL function "functest_sys" +SELECT functest_sys(5); + functest_sys +-------------- + 5 +(1 row) + +-- a column added later to a member of a join with that alias +CREATE TABLE functest_j1 (b int); +CREATE TABLE functest_j2 (c int); +INSERT INTO functest_j1 VALUES (1); +INSERT INTO functest_j2 VALUES (1); +CREATE FUNCTION functest_join(a int) RETURNS int + BEGIN ATOMIC SELECT a FROM (functest_j1 CROSS JOIN functest_j2) AS functest_join; END; +SELECT functest_join(5); + functest_join +--------------- + 5 +(1 row) + +ALTER TABLE functest_j1 ADD COLUMN a int DEFAULT 100; +SELECT pg_get_functiondef('functest_join'::regproc); + pg_get_functiondef +-------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_join(a integer)+ + RETURNS integer + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT functest_join.a AS a + + FROM (functest_j1 + + CROSS JOIN functest_j2) functest_join; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_join'::regproc) AS def \gset +:def ; +SELECT functest_join(5); + functest_join +--------------- + 100 +(1 row) + +-- a JOIN/USING alias +CREATE TABLE functest_u1 (a int, b int); +CREATE TABLE functest_u2 (a int, c int); +INSERT INTO functest_u1 VALUES (100, 1); +INSERT INTO functest_u2 VALUES (100, 1); +CREATE FUNCTION functest_using(a int) RETURNS int + BEGIN ATOMIC SELECT $1 FROM functest_u1 JOIN functest_u2 USING (a) AS functest_using; END; +SELECT functest_using(5); + functest_using +---------------- + 5 +(1 row) + +SELECT pg_get_functiondef('functest_using'::regproc); + pg_get_functiondef +--------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_using(a integer)+ + RETURNS integer + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT functest_using.a + + FROM (functest_u1 + + JOIN functest_u2 USING (a) AS functest_using); + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_using'::regproc) AS def \gset +:def ; +SELECT functest_using(5); + functest_using +---------------- + 100 +(1 row) + +-- without relations a parameter is printed by its bare name, but as a sort +-- or grouping key that name would be taken as an output column +CREATE FUNCTION functest_sort(a int) RETURNS int[] + RETURN ARRAY(SELECT 4 - generate_series(1, 3) AS a ORDER BY $1); +SELECT functest_sort(5); + functest_sort +--------------- + {3,2,1} +(1 row) + +SELECT pg_get_functiondef('functest_sort'::regproc); + pg_get_functiondef +-------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_sort(a integer)+ + RETURNS integer[] + + LANGUAGE sql + + RETURN ARRAY(SELECT (4 - generate_series(1, 3)) AS a ORDER BY a) + + +(1 row) + +SELECT pg_get_functiondef('functest_sort'::regproc) AS def \gset +:def ; +SELECT functest_sort(5); + functest_sort +--------------- + {1,2,3} +(1 row) + +CREATE FUNCTION functest_distinct(a int) RETURNS int[] + RETURN ARRAY(SELECT DISTINCT ON ($1) generate_series(1, 3) AS a); +SELECT functest_distinct(5); + functest_distinct +------------------- + {1} +(1 row) + +SELECT pg_get_functiondef('functest_distinct'::regproc); + pg_get_functiondef +------------------------------------------------------------------------ + CREATE OR REPLACE FUNCTION temp_func_test.functest_distinct(a integer)+ + RETURNS integer[] + + LANGUAGE sql + + RETURN ARRAY(SELECT DISTINCT ON (a) generate_series(1, 3) AS a) + + +(1 row) + +SELECT pg_get_functiondef('functest_distinct'::regproc) AS def \gset +:def ; +SELECT functest_distinct(5); + functest_distinct +------------------- + {1,2,3} +(1 row) + +CREATE FUNCTION functest_group(a int) RETURNS int[] + RETURN ARRAY(SELECT (4 - generate_series(1, 3)) % 2 AS a GROUP BY $1); +SELECT functest_group(5); + functest_group +---------------- + {1,0,1} +(1 row) + +SELECT pg_get_functiondef('functest_group'::regproc); + pg_get_functiondef +------------------------------------------------------------------------ + CREATE OR REPLACE FUNCTION temp_func_test.functest_group(a integer) + + RETURNS integer[] + + LANGUAGE sql + + RETURN ARRAY(SELECT ((4 - generate_series(1, 3)) % 2) AS a GROUP BY a)+ + +(1 row) + +SELECT pg_get_functiondef('functest_group'::regproc) AS def \gset +:def ; +SELECT functest_group(5); + functest_group +---------------- + {0,1} +(1 row) + +-- the output column may have no ordering operator, so the body fails to +-- parse again +CREATE FUNCTION functest_point(a int) RETURNS SETOF point + BEGIN ATOMIC SELECT point(1, 2) AS a ORDER BY $1; END; +SELECT functest_point(5); + functest_point +---------------- + (1,2) +(1 row) + +SELECT pg_get_functiondef('functest_point'::regproc); + pg_get_functiondef +--------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_point(a integer)+ + RETURNS SETOF point + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT point((1)::double precision, (2)::double precision) AS a + + ORDER BY a; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_point'::regproc) AS def \gset +:def ; +ERROR: could not identify an ordering operator for type point +LINE 6: ORDER BY a; + ^ +HINT: Use an explicit ordering operator or modify the query. +SELECT functest_point(5); + functest_point +---------------- + (1,2) +(1 row) + -- Cleanup DROP SCHEMA temp_func_test CASCADE; -NOTICE: drop cascades to 30 other objects +NOTICE: drop cascades to 49 other objects DETAIL: drop cascades to function functest_a_1(text,date) drop cascades to function functest_a_2(text[]) drop cascades to function functest_a_3() @@ -739,5 +1083,24 @@ drop cascades to function voidtest3(integer) drop cascades to function voidtest4(integer) drop cascades to function voidtest5(integer) drop cascades to function double_append(anyarray,anyelement) +drop cascades to function functest_unnamed(integer,integer) +drop cascades to table functest_cap +drop cascades to function functest_cap(integer) +drop cascades to table functest_ren +drop cascades to function functest_ren(integer) +drop cascades to table functest_nn +drop cascades to function functest_nn(integer) +drop cascades to table functest_sys +drop cascades to function functest_sys(integer) +drop cascades to table functest_j1 +drop cascades to table functest_j2 +drop cascades to function functest_join(integer) +drop cascades to table functest_u1 +drop cascades to table functest_u2 +drop cascades to function functest_using(integer) +drop cascades to function functest_sort(integer) +drop cascades to function functest_distinct(integer) +drop cascades to function functest_group(integer) +drop cascades to function functest_point(integer) DROP USER regress_unpriv_user; RESET search_path; diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql index 89e9af3a499..a20911dc2e6 100644 --- a/src/test/regress/sql/create_function_sql.sql +++ b/src/test/regress/sql/create_function_sql.sql @@ -415,6 +415,122 @@ CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL AS 'a', 'b'; +-- unnamed parameters of an SQL-body function are printed as $N +CREATE FUNCTION functest_unnamed(a int, int) RETURNS int + BEGIN ATOMIC SELECT a + $2; END; +SELECT functest_unnamed(1, 2); +SELECT pg_get_functiondef('functest_unnamed'::regproc); + +-- a parameter must not be qualified with the function name if a relation of +-- that name is visible: a column named like the parameter, even one added +-- after the function was created, would capture the qualified name +CREATE TABLE functest_cap (x int); +INSERT INTO functest_cap VALUES (5); +CREATE FUNCTION functest_cap(a int) RETURNS int + BEGIN ATOMIC SELECT a + x FROM functest_cap; END; +SELECT functest_cap(10); +ALTER TABLE functest_cap ADD COLUMN a int DEFAULT 100; +SELECT pg_get_functiondef('functest_cap'::regproc); +SELECT pg_get_functiondef('functest_cap'::regproc) AS def \gset +:def ; +SELECT functest_cap(10); + +-- the same if a column is renamed to the name of a parameter that the body +-- refers to as funcname.argname +CREATE TABLE functest_ren (b int); +INSERT INTO functest_ren VALUES (5); +CREATE FUNCTION functest_ren(a int) RETURNS int + BEGIN ATOMIC SELECT functest_ren.a + functest_ren.b FROM functest_ren; END; +SELECT functest_ren(10); +ALTER TABLE functest_ren RENAME b TO a; +SELECT pg_get_functiondef('functest_ren'::regproc); +SELECT pg_get_functiondef('functest_ren'::regproc) AS def \gset +:def ; +SELECT functest_ren(10); + +-- the same for any name the parser can take as a qualifier: a function +-- taking the whole row, as in num_nulls(functest_nn.*) +CREATE TABLE functest_nn (x int); +INSERT INTO functest_nn VALUES (1); +CREATE FUNCTION functest_nn(num_nulls int) RETURNS int + BEGIN ATOMIC SELECT num_nulls + x FROM functest_nn; END; +SELECT functest_nn(7); +SELECT pg_get_functiondef('functest_nn'::regproc); +SELECT pg_get_functiondef('functest_nn'::regproc) AS def \gset +:def ; +SELECT functest_nn(7); + +-- a system column +CREATE TABLE functest_sys (x int); +INSERT INTO functest_sys VALUES (1); +CREATE FUNCTION functest_sys(cmin int) RETURNS int + BEGIN ATOMIC SELECT $1 FROM functest_sys; END; +SELECT functest_sys(5); +SELECT pg_get_functiondef('functest_sys'::regproc); +SELECT pg_get_functiondef('functest_sys'::regproc) AS def \gset +:def ; +SELECT functest_sys(5); + +-- a column added later to a member of a join with that alias +CREATE TABLE functest_j1 (b int); +CREATE TABLE functest_j2 (c int); +INSERT INTO functest_j1 VALUES (1); +INSERT INTO functest_j2 VALUES (1); +CREATE FUNCTION functest_join(a int) RETURNS int + BEGIN ATOMIC SELECT a FROM (functest_j1 CROSS JOIN functest_j2) AS functest_join; END; +SELECT functest_join(5); +ALTER TABLE functest_j1 ADD COLUMN a int DEFAULT 100; +SELECT pg_get_functiondef('functest_join'::regproc); +SELECT pg_get_functiondef('functest_join'::regproc) AS def \gset +:def ; +SELECT functest_join(5); + +-- a JOIN/USING alias +CREATE TABLE functest_u1 (a int, b int); +CREATE TABLE functest_u2 (a int, c int); +INSERT INTO functest_u1 VALUES (100, 1); +INSERT INTO functest_u2 VALUES (100, 1); +CREATE FUNCTION functest_using(a int) RETURNS int + BEGIN ATOMIC SELECT $1 FROM functest_u1 JOIN functest_u2 USING (a) AS functest_using; END; +SELECT functest_using(5); +SELECT pg_get_functiondef('functest_using'::regproc); +SELECT pg_get_functiondef('functest_using'::regproc) AS def \gset +:def ; +SELECT functest_using(5); + +-- without relations a parameter is printed by its bare name, but as a sort +-- or grouping key that name would be taken as an output column +CREATE FUNCTION functest_sort(a int) RETURNS int[] + RETURN ARRAY(SELECT 4 - generate_series(1, 3) AS a ORDER BY $1); +SELECT functest_sort(5); +SELECT pg_get_functiondef('functest_sort'::regproc); +SELECT pg_get_functiondef('functest_sort'::regproc) AS def \gset +:def ; +SELECT functest_sort(5); +CREATE FUNCTION functest_distinct(a int) RETURNS int[] + RETURN ARRAY(SELECT DISTINCT ON ($1) generate_series(1, 3) AS a); +SELECT functest_distinct(5); +SELECT pg_get_functiondef('functest_distinct'::regproc); +SELECT pg_get_functiondef('functest_distinct'::regproc) AS def \gset +:def ; +SELECT functest_distinct(5); +CREATE FUNCTION functest_group(a int) RETURNS int[] + RETURN ARRAY(SELECT (4 - generate_series(1, 3)) % 2 AS a GROUP BY $1); +SELECT functest_group(5); +SELECT pg_get_functiondef('functest_group'::regproc); +SELECT pg_get_functiondef('functest_group'::regproc) AS def \gset +:def ; +SELECT functest_group(5); +-- the output column may have no ordering operator, so the body fails to +-- parse again +CREATE FUNCTION functest_point(a int) RETURNS SETOF point + BEGIN ATOMIC SELECT point(1, 2) AS a ORDER BY $1; END; +SELECT functest_point(5); +SELECT pg_get_functiondef('functest_point'::regproc); +SELECT pg_get_functiondef('functest_point'::regproc) AS def \gset +:def ; +SELECT functest_point(5); + -- Cleanup DROP SCHEMA temp_func_test CASCADE; DROP USER regress_unpriv_user; -- 2.54.0 (Apple Git-157)