From 8525f08f823bd5aa6ebb6059bb9f660ce7102da6 Mon Sep 17 00:00:00 2001 From: Jim Jones Date: Mon, 13 Oct 2025 15:28:22 +0200 Subject: [PATCH v5 2/2] Prevent SQL functions with BEGIN ATOMIC from depending on temporary objects SQL functions with BEGIN ATOMIC bodies are intended to be permanently definable and should not depend on session-specific temporary objects. This commit implements dependency validation to enforce this restriction. Key changes: - Add filter_temp_objects() to detect temporary objects (tables, views, types, functions, sequences, domains) and raise descriptive errors - Integrate temp object filtering into ProcedureCreate() for SQL functions with BEGIN ATOMIC bodies - Allow temp-to-temp references: functions in temporary schemas can reference temporary objects since both have the same session lifecycle - Skip filtering during bootstrap and pg_upgrade to avoid interfering with system operations - Preserve existing behavior for regular SQL functions and parameter defaults The implementation leverages the existing collectDependenciesFromExpr() infrastructure to collect dependencies before applying temp object validation, using a collect-then-filter-then-record pattern for SQL function bodies. --- src/backend/catalog/dependency.c | 82 +++++++++++++++++++ src/backend/catalog/pg_proc.c | 29 ++++++- src/include/catalog/dependency.h | 2 + .../regress/expected/create_function_sql.out | 80 ++++++++++++++++++ src/test/regress/expected/returning.out | 52 ++++++------ src/test/regress/sql/create_function_sql.sql | 71 ++++++++++++++++ src/test/regress/sql/returning.sql | 3 +- 7 files changed, 292 insertions(+), 27 deletions(-) diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index d6142b1750..8665e83ca0 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" @@ -2488,6 +2489,87 @@ eliminate_duplicate_dependencies(ObjectAddresses *addrs) addrs->numrefs = newrefs; } +/* + * filter_temp_objects - detect and reject temporary objects in an ObjectAddresses array + * + * This function checks if any dependencies on temporary objects (objects in + * temporary namespaces) exist in the given ObjectAddresses array. If temp objects + * are found, it raises an error to prevent them from being used in SQL functions + * with BEGIN ATOMIC bodies, as such dependencies would be inappropriate for + * permanent function definitions. + * + * Currently checks for temporary tables, views, types, and functions by examining + * their containing namespaces. The function raises an error with a descriptive + * message if any temporary object dependency is detected. + */ +void filter_temp_objects(ObjectAddresses *addrs) +{ + int oldref; + + if (addrs->numrefs <= 0) + return; /* nothing to do */ + + /* Check all dependencies for temp objects */ + for (oldref = 0; oldref < addrs->numrefs; oldref++) + { + ObjectAddress *thisobj = addrs->refs + oldref; + bool is_temp = false; + char *objname = NULL; + + /* Check if this dependency is on a temporary object */ + if (thisobj->classId == RelationRelationId) + { + /* For relations, check if they're in a temp namespace */ + Oid relnamespace = get_rel_namespace(thisobj->objectId); + if (OidIsValid(relnamespace) && isAnyTempNamespace(relnamespace)) + { + is_temp = true; + objname = get_rel_name(thisobj->objectId); + } + } + else if (thisobj->classId == TypeRelationId) + { + /* For types, check if they're in a temp namespace */ + HeapTuple tup; + Form_pg_type typform; + Oid typnamespace = InvalidOid; + + tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(thisobj->objectId)); + if (HeapTupleIsValid(tup)) + { + typform = (Form_pg_type)GETSTRUCT(tup); + typnamespace = typform->typnamespace; + if (OidIsValid(typnamespace) && isAnyTempNamespace(typnamespace)) + { + is_temp = true; + objname = NameStr(typform->typname); + } + ReleaseSysCache(tup); + } + } + else if (thisobj->classId == ProcedureRelationId) + { + /* For functions, check if they're in a temp namespace */ + Oid funcnamespace = get_func_namespace(thisobj->objectId); + if (OidIsValid(funcnamespace) && isAnyTempNamespace(funcnamespace)) + { + is_temp = true; + objname = get_func_name(thisobj->objectId); + } + } + + /* Raise error if temp object found */ + if (is_temp) + { + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use temporary object \"%s\" in SQL function with BEGIN ATOMIC", + objname ? objname : "unknown"), + 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..67cc9851c7 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" @@ -663,7 +664,33 @@ ProcedureCreate(const char *procedureName, /* dependency on SQL routine body */ if (languageObjectId == SQLlanguageId && prosqlbody) - recordDependencyOnExpr(&myself, prosqlbody, NIL, DEPENDENCY_NORMAL); + { + ObjectAddresses *body_addrs; + + /* + * For SQL functions with BEGIN ATOMIC, we use a collect-then-filter-then-record + * approach to handle temp object dependencies appropriately. + */ + body_addrs = new_object_addresses(); + collectDependenciesFromExpr(body_addrs, prosqlbody, NIL); + + /* + * Check for temp objects that are referenced in the function body. + * For SQL functions with BEGIN ATOMIC bodies, we need to prevent + * dependencies on temporary objects since such functions should be + * permanently definable and not depend on session-specific temp objects. + * This will raise an error if any temp objects are found. If the function + * itself is being created in a temporary schema, then it's OK for it to + * reference temp objects. + */ + if (!IsBootstrapProcessingMode() && !IsBinaryUpgrade && + !isAnyTempNamespace(procNamespace)) + filter_temp_objects(body_addrs); + + /* Record the filtered dependencies */ + record_object_address_dependencies(&myself, body_addrs, DEPENDENCY_NORMAL); + free_object_addresses(body_addrs); + } /* dependency on parameter default expressions */ if (parameterDefaults) 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..7a9ce26ffa 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -297,6 +297,86 @@ 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 object "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 object "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 object "temp_view" 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 object "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 +CREATE FUNCTION functest_temp_func_dep() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT pg_temp.temp_func(); +END; +ERROR: cannot use temporary object "temp_func" in SQL function with BEGIN ATOMIC +DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects. +-- 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 object "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 object "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 object "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 object "temp_domain" 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..7f0ad8f11b 100644 --- a/src/test/regress/sql/create_function_sql.sql +++ b/src/test/regress/sql/create_function_sql.sql @@ -199,6 +199,77 @@ 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 +CREATE FUNCTION functest_temp_func_dep() RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT pg_temp.temp_func(); +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; + -- 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