From 6d3cadd472fed5157d46b4ccd72f3d4bb4ecbf00 Mon Sep 17 00:00:00 2001 From: Jim Jones Date: Wed, 5 Nov 2025 13:43:33 +0100 Subject: [PATCH v6 2/2] Disallow temp objects in SQL BEGIN ATOMIC functions SQL functions with BEGIN ATOMIC bodies are intended to be permanently definable and should not depend on session-specific temporary objects. This implements dependency validation to enforce this restriction. Key changes: - Add filter_temp_objects() to detect temporary objects in any schema using get_object_namespace() from the objectaddress.c infrastructure - Check ALL function dependencies (return types, parameter types, default parameter values, and function body) for temporary object references - Integrate temp object filtering into ProcedureCreate() for SQL functions with BEGIN ATOMIC bodies using a collect-then-filter-then-record pattern - Allow temp-to-temp references: functions in temporary schemas can reference temporary objects since both have the same session lifecycle The implementation collects all function dependencies (including parameter types, return types, default expressions, and SQL body) into a single ObjectAddresses structure, filters out temporary object references, then records the dependencies once. This ensures that not only the function body but also the function signature cannot reference temporary objects. --- src/backend/catalog/dependency.c | 43 +++++++ src/backend/catalog/pg_proc.c | 45 ++++++- src/include/catalog/dependency.h | 2 + .../regress/expected/create_function_sql.out | 118 ++++++++++++++++++ src/test/regress/expected/returning.out | 52 ++++---- src/test/regress/sql/create_function_sql.sql | 105 ++++++++++++++++ src/test/regress/sql/returning.sql | 3 +- 7 files changed, 336 insertions(+), 32 deletions(-) diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index bd5d705936..3162bc0ad7 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -44,6 +44,7 @@ #include "catalog/pg_language.h" #include "catalog/pg_largeobject.h" #include "catalog/pg_namespace.h" +#include "catalog/namespace.h" #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_opfamily.h" @@ -2494,6 +2495,48 @@ eliminate_duplicate_dependencies(ObjectAddresses *addrs) addrs->numrefs = newrefs; } +/* + * filter_temp_objects - reject temporary object references + * + * Scan an ObjectAddresses array for references to temporary objects + * (objects in temporary namespaces) and raise an error if any are found. + * This is used to prevent SQL functions with BEGIN ATOMIC bodies from + * depending on temporary objects, as such dependencies would be + * inappropriate for permanent function definitions. + * + * Uses get_object_namespace() to identify which objects belong to + * schemas, then checks if those schemas are temporary. + */ +void +filter_temp_objects(ObjectAddresses *addrs) +{ + int i; + + for (i = 0; i < addrs->numrefs; i++) + { + ObjectAddress *thisobj = addrs->refs + i; + Oid objnamespace; + + /* + * Use get_object_namespace() to see if this object belongs to a + * schema. If not, we can skip it. + */ + objnamespace = get_object_namespace(thisobj); + + /* + * If the object is in a temporary namespace, complain. + */ + if (OidIsValid(objnamespace) && isAnyTempNamespace(objnamespace)) + { + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use temporary %s in SQL function with BEGIN ATOMIC", + getObjectDescription(thisobj, false)), + errdetail("SQL functions with BEGIN ATOMIC cannot depend on temporary objects."))); + } + } +} + /* * qsort comparator for ObjectAddress items */ diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index b89b9ccda0..9b23201e83 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -24,6 +24,7 @@ #include "catalog/pg_language.h" #include "catalog/pg_namespace.h" #include "catalog/pg_proc.h" +#include "catalog/namespace.h" #include "catalog/pg_transform.h" #include "catalog/pg_type.h" #include "executor/functions.h" @@ -658,15 +659,47 @@ ProcedureCreate(const char *procedureName, add_exact_object_address(&referenced, addrs); } - record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL); - free_object_addresses(addrs); - /* dependency on SQL routine body */ if (languageObjectId == SQLlanguageId && prosqlbody) - recordDependencyOnExpr(&myself, prosqlbody, NIL, DEPENDENCY_NORMAL); + { + collectDependenciesFromExpr(addrs, prosqlbody, NIL); + + /* Also collect dependencies from parameter defaults */ + if (parameterDefaults) + collectDependenciesFromExpr(addrs, (Node *) parameterDefaults, NIL); - /* dependency on parameter default expressions */ - if (parameterDefaults) + /* + * Check for temp objects before recording dependencies, but only + * for SQL functions with BEGIN ATOMIC bodies. We check for temp + * objects here so that the check applies to all dependencies, not + * just those from the SQL body. For example, a function with a + * temp table type as an argument or return type should be rejected, + * not just one that references a temp table in its body. + * + * We skip the check if the function is being created in a temp + * schema (in which case it's fine for it to depend on temp objects), + * or if we're in bootstrap or binary upgrade mode (where we need to + * restore whatever was in the dump without complaints). + */ + if (!IsBootstrapProcessingMode() && !IsBinaryUpgrade && + !isAnyTempNamespace(procNamespace)) + filter_temp_objects(addrs); + } + + /* + * Now record all dependencies at once. This will also remove any + * duplicates. + */ + record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL); + free_object_addresses(addrs); + + /* + * Dependency on parameter default expressions, but only if we didn't + * already handle them above. For SQL functions with BEGIN ATOMIC bodies, + * parameter defaults are included in the temp object check and recorded + * above. + */ + if (parameterDefaults && (languageObjectId != SQLlanguageId || !prosqlbody)) recordDependencyOnExpr(&myself, (Node *) parameterDefaults, NIL, DEPENDENCY_NORMAL); diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h index f5061605dd..551ffe921f 100644 --- a/src/include/catalog/dependency.h +++ b/src/include/catalog/dependency.h @@ -123,6 +123,8 @@ extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender, DependencyType self_behavior, bool reverse_self); +extern void filter_temp_objects(ObjectAddresses *addrs); + extern ObjectAddresses *new_object_addresses(void); extern void add_exact_object_address(const ObjectAddress *object, diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out index 73c6730d45..ef05243f92 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -297,6 +297,124 @@ CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement LANGUAGE SQL RETURN x[1]; ERROR: SQL function with unquoted function body cannot have polymorphic arguments +CREATE TEMPORARY TABLE temp_table AS SELECT 1 AS val; +CREATE TEMPORARY VIEW temp_view AS SELECT 42 AS val; +CREATE TYPE pg_temp.temp_type AS (x int, y text); +CREATE TEMPORARY SEQUENCE temp_seq; +CREATE DOMAIN pg_temp.temp_domain AS int CHECK (VALUE > 0); +CREATE FUNCTION pg_temp.temp_func() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; +-- these should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary tables +CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT val FROM temp_table; +END; +ERROR: cannot use temporary table temp_table in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +CREATE FUNCTION functest_temp_dep_subquery() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT (SELECT COUNT(*) FROM temp_table); +END; +ERROR: cannot use temporary table temp_table in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +CREATE FUNCTION functest_temp_dep_join() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT t1.val FROM temp_table t1 + JOIN temp_view t2 ON t1.val = t2.val; +END; +ERROR: cannot use temporary table temp_table in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +CREATE FUNCTION functest_temp_indirect_dep() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT * FROM pg_class WHERE oid = 'temp_table'::regclass; +END; +ERROR: cannot use temporary table temp_table in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- this should work: the function is created in a temp schema +CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT val FROM temp_table; +END; +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary functions +-- (wrapped in DO block because error message includes session-specific temp schema name) +DO $$ +BEGIN + EXECUTE 'CREATE FUNCTION functest_temp_func_dep() RETURNS int LANGUAGE sql BEGIN ATOMIC SELECT pg_temp.temp_func(); END'; +EXCEPTION + WHEN feature_not_supported THEN + RAISE NOTICE 'caught expected error'; +END $$; +NOTICE: caught expected error +-- this should work: temp function calling temp function (both in temp schema) +CREATE FUNCTION pg_temp.functest_temp_to_temp() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT pg_temp.temp_func(); +END; +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary views +CREATE FUNCTION functest_temp_view() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT val FROM temp_view; +END; +ERROR: cannot use temporary view temp_view in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary types +CREATE FUNCTION functest_temp_type() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT (ROW(1,'test')::pg_temp.temp_type).x; +END; +ERROR: cannot use temporary column x of composite type temp_type in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary sequences +CREATE FUNCTION functest_temp_sequence() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT nextval('temp_seq'); +END; +ERROR: cannot use temporary sequence temp_seq in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary domains +CREATE FUNCTION functest_temp_domain() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 5::pg_temp.temp_domain; +END; +ERROR: cannot use temporary type temp_domain in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- this should fail: BEGIN ATOMIC SQL-functions cannot contain parameters with temporary types +CREATE FUNCTION functest_temp_parameter(foo temp_type) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; +ERROR: cannot use temporary type temp_type in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- this should fail: BEGIN ATOMIC SQL-functions cannot return temporary types +CREATE FUNCTION functest_temp_return() RETURNS temp_type LANGUAGE sql +BEGIN ATOMIC; + SELECT NULL; +END; +ERROR: cannot use temporary type temp_type in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- this should fail: BEGIN ATOMIC SQL-functions cannot contain array of temporary type as parameter +CREATE FUNCTION functest_temp_array_param(foo temp_type[]) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; +ERROR: cannot use temporary type temp_type[] in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- this should fail: BEGIN ATOMIC SQL-functions cannot contain default parameter value referencing temporary object +CREATE FUNCTION functest_temp_default(seq_val int DEFAULT nextval('temp_seq')) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT seq_val; +END; +ERROR: cannot use temporary sequence temp_seq in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- this should fail: BEGIN ATOMIC SQL-functions cannot return SETOF temporary type +CREATE FUNCTION functest_temp_setof_return() RETURNS SETOF temp_type LANGUAGE sql +BEGIN ATOMIC; + SELECT NULL; +END; +ERROR: cannot use temporary type temp_type in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. -- check reporting of parse-analysis errors CREATE FUNCTION functest_S_xx(x date) RETURNS boolean LANGUAGE SQL diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out index d02c2ceab5..30538c8526 100644 --- a/src/test/regress/expected/returning.out +++ b/src/test/regress/expected/returning.out @@ -2,7 +2,7 @@ -- Test INSERT/UPDATE/DELETE RETURNING -- -- Simple cases -CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42); +CREATE TABLE foo (f1 serial, f2 text, f3 int default 42); INSERT INTO foo (f2,f3) VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9) RETURNING *, f1+f3 AS sum; @@ -447,7 +447,7 @@ INSERT INTO foo VALUES (4) new.tableoid::regclass, new.ctid, new.*, *; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Insert on pg_temp.foo + Insert on public.foo Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo.f1, foo.f2, foo.f3, foo.f4 -> Result Output: 4, NULL::text, 42, '99'::bigint @@ -471,7 +471,7 @@ INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok') n.tableoid::regclass, n.ctid, n.*, *; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- - Insert on pg_temp.foo + Insert on public.foo Output: (o.tableoid)::regclass, o.ctid, o.f1, o.f2, o.f3, o.f4, (n.tableoid)::regclass, n.ctid, n.f1, n.f2, n.f3, n.f4, foo.f1, foo.f2, foo.f3, foo.f4 Conflict Resolution: UPDATE Conflict Arbiter Indexes: foo_f1_idx @@ -498,12 +498,12 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5 old.f4::text||'->'||new.f4::text AS change; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Update on pg_temp.foo + Update on public.foo Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, old.*, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, new.*, (((old.f4)::text || '->'::text) || (new.f4)::text) - Update on pg_temp.foo foo_1 + Update on public.foo foo_1 -> Result Output: '100'::bigint, foo_1.tableoid, foo_1.ctid - -> Seq Scan on pg_temp.foo foo_1 + -> Seq Scan on public.foo foo_1 Output: foo_1.tableoid, foo_1.ctid Filter: (foo_1.f1 = 5) (8 rows) @@ -524,10 +524,10 @@ DELETE FROM foo WHERE f1 = 5 new.tableoid::regclass, new.ctid, new.*, *; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - Delete on pg_temp.foo + Delete on public.foo Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4 - Delete on pg_temp.foo foo_1 - -> Seq Scan on pg_temp.foo foo_1 + Delete on public.foo foo_1 + -> Seq Scan on public.foo foo_1 Output: foo_1.tableoid, foo_1.ctid Filter: (foo_1.f1 = 5) (6 rows) @@ -547,7 +547,7 @@ INSERT INTO foo VALUES (5, 'subquery test') (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; QUERY PLAN --------------------------------------------------------------- - Insert on pg_temp.foo + Insert on public.foo Output: (SubPlan expr_1), (SubPlan expr_2) -> Result Output: 5, 'subquery test'::text, 42, '99'::bigint @@ -580,12 +580,12 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5 (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; QUERY PLAN ---------------------------------------------------------------- - Update on pg_temp.foo + Update on public.foo Output: (SubPlan expr_1), (SubPlan expr_2), (SubPlan expr_3) - Update on pg_temp.foo foo_1 + Update on public.foo foo_1 -> Result Output: '100'::bigint, foo_1.tableoid, foo_1.ctid - -> Seq Scan on pg_temp.foo foo_1 + -> Seq Scan on public.foo foo_1 Output: foo_1.tableoid, foo_1.ctid Filter: (foo_1.f1 = 5) SubPlan expr_1 @@ -620,10 +620,10 @@ DELETE FROM foo WHERE f1 = 5 (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; QUERY PLAN --------------------------------------------------------------- - Delete on pg_temp.foo + Delete on public.foo Output: (SubPlan expr_1), (SubPlan expr_2) - Delete on pg_temp.foo foo_1 - -> Seq Scan on pg_temp.foo foo_1 + Delete on public.foo foo_1 + -> Seq Scan on public.foo foo_1 Output: foo_1.tableoid, foo_1.ctid Filter: (foo_1.f1 = 5) SubPlan expr_1 @@ -656,15 +656,15 @@ EXPLAIN (verbose, costs off) DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- - Update on pg_temp.foo + Update on public.foo Output: old.f1, old.f2, old.f3, old.f4, new.f1, new.f2, new.f3, new.f4, foo_2.f1, foo_2.f2, foo_2.f3, foo_2.f4 - Update on pg_temp.foo foo_2 + Update on public.foo foo_2 -> Nested Loop Output: (foo_2.f2 || ' (deleted)'::text), '-1'::integer, '-1'::bigint, foo_1.ctid, foo_1.tableoid, foo_2.tableoid, foo_2.ctid - -> Seq Scan on pg_temp.foo foo_2 + -> Seq Scan on public.foo foo_2 Output: foo_2.f2, foo_2.f1, foo_2.tableoid, foo_2.ctid Filter: (foo_2.f1 = 4) - -> Seq Scan on pg_temp.foo foo_1 + -> Seq Scan on public.foo foo_1 Output: foo_1.ctid, foo_1.f1, foo_1.tableoid Filter: (foo_1.f1 = 4) (11 rows) @@ -681,9 +681,9 @@ UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57 RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Update on pg_temp.foo + Update on public.foo Output: old.f1, old.f2, old.f3, old.f4, joinme.other, new.f1, new.f2, new.f3, new.f4, joinme.other, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4, joinme.other, (new.f3 - old.f3) - Update on pg_temp.foo foo_1 + Update on public.foo foo_1 -> Hash Join Output: foo_2.f1, (foo_2.f3 + 1), joinme.ctid, foo_2.ctid, joinme_1.ctid, joinme.other, foo_1.tableoid, foo_1.ctid, foo_2.tableoid Hash Cond: (foo_1.f2 = joinme.f2j) @@ -694,7 +694,7 @@ UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57 Output: joinme_1.ctid, joinme_1.f2j -> Hash Output: foo_1.f2, foo_1.tableoid, foo_1.ctid - -> Seq Scan on pg_temp.foo foo_1 + -> Seq Scan on public.foo foo_1 Output: foo_1.f2, foo_1.tableoid, foo_1.ctid -> Hash Output: joinme.ctid, joinme.other, joinme.f2j, foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid @@ -705,7 +705,7 @@ UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57 Output: joinme.ctid, joinme.other, joinme.f2j -> Hash Output: foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid - -> Seq Scan on pg_temp.foo foo_2 + -> Seq Scan on public.foo foo_2 Output: foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid Filter: (foo_2.f3 = 57) (27 rows) @@ -768,7 +768,7 @@ UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58 Output: joinme.other, joinme.ctid, joinme.f2j -> Hash Output: foo.f3, foo.f1, foo.f2, foo.f4, foo.ctid, foo.tableoid - -> Seq Scan on pg_temp.foo + -> Seq Scan on public.foo Output: foo.f3, foo.f1, foo.f2, foo.f4, foo.ctid, foo.tableoid Filter: (foo.f3 = 58) (12 rows) @@ -986,3 +986,5 @@ BEGIN ATOMIC WHERE (foo_1.* = n.*)) AS count; END DROP FUNCTION foo_update; +DROP TABLE foo CASCADE; +NOTICE: drop cascades to view voo diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql index 3d5f2a9209..bf2533b7fc 100644 --- a/src/test/regress/sql/create_function_sql.sql +++ b/src/test/regress/sql/create_function_sql.sql @@ -199,6 +199,111 @@ CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement LANGUAGE SQL RETURN x[1]; +CREATE TEMPORARY TABLE temp_table AS SELECT 1 AS val; +CREATE TEMPORARY VIEW temp_view AS SELECT 42 AS val; +CREATE TYPE pg_temp.temp_type AS (x int, y text); +CREATE TEMPORARY SEQUENCE temp_seq; +CREATE DOMAIN pg_temp.temp_domain AS int CHECK (VALUE > 0); +CREATE FUNCTION pg_temp.temp_func() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; + +-- these should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary tables +CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT val FROM temp_table; +END; +CREATE FUNCTION functest_temp_dep_subquery() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT (SELECT COUNT(*) FROM temp_table); +END; +CREATE FUNCTION functest_temp_dep_join() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT t1.val FROM temp_table t1 + JOIN temp_view t2 ON t1.val = t2.val; +END; +CREATE FUNCTION functest_temp_indirect_dep() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT * FROM pg_class WHERE oid = 'temp_table'::regclass; +END; + +-- this should work: the function is created in a temp schema +CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT val FROM temp_table; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary functions +-- (wrapped in DO block because error message includes session-specific temp schema name) +DO $$ +BEGIN + EXECUTE 'CREATE FUNCTION functest_temp_func_dep() RETURNS int LANGUAGE sql BEGIN ATOMIC SELECT pg_temp.temp_func(); END'; +EXCEPTION + WHEN feature_not_supported THEN + RAISE NOTICE 'caught expected error'; +END $$; + +-- this should work: temp function calling temp function (both in temp schema) +CREATE FUNCTION pg_temp.functest_temp_to_temp() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT pg_temp.temp_func(); +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary views +CREATE FUNCTION functest_temp_view() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT val FROM temp_view; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary types +CREATE FUNCTION functest_temp_type() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT (ROW(1,'test')::pg_temp.temp_type).x; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary sequences +CREATE FUNCTION functest_temp_sequence() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT nextval('temp_seq'); +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary domains +CREATE FUNCTION functest_temp_domain() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 5::pg_temp.temp_domain; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot contain parameters with temporary types +CREATE FUNCTION functest_temp_parameter(foo temp_type) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot return temporary types +CREATE FUNCTION functest_temp_return() RETURNS temp_type LANGUAGE sql +BEGIN ATOMIC; + SELECT NULL; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot contain array of temporary type as parameter +CREATE FUNCTION functest_temp_array_param(foo temp_type[]) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot contain default parameter value referencing temporary object +CREATE FUNCTION functest_temp_default(seq_val int DEFAULT nextval('temp_seq')) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT seq_val; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot return SETOF temporary type +CREATE FUNCTION functest_temp_setof_return() RETURNS SETOF temp_type LANGUAGE sql +BEGIN ATOMIC; + SELECT NULL; +END; + -- check reporting of parse-analysis errors CREATE FUNCTION functest_S_xx(x date) RETURNS boolean LANGUAGE SQL diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql index cc99cb53f6..8c2bb836ea 100644 --- a/src/test/regress/sql/returning.sql +++ b/src/test/regress/sql/returning.sql @@ -4,7 +4,7 @@ -- Simple cases -CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42); +CREATE TABLE foo (f1 serial, f2 text, f3 int default 42); INSERT INTO foo (f2,f3) VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9) @@ -408,3 +408,4 @@ END; \sf foo_update DROP FUNCTION foo_update; +DROP TABLE foo CASCADE; \ No newline at end of file -- 2.43.0