Add notification on BEGIN ATOMIC SQL functions using temp relations

From 4b6ed67534ee4c4f840b463c47cbc85f99b5b396 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Sun, 21 Sep 2025 23:21:31 +0200
Subject: [PATCH v2] Disallow BEGIN ATOMIC functions depending on temp
 relations

When a SQL function is defined with a BEGIN ATOMIC block and
references a temporary relation, the function is dropped at
session end along with the temp relation. From the user's
perspective, the function looks like a persistent database
object in a permanent schema, yet it disappears silently.

To avoid confusion and inconsistent behavior, reject creation
of such functions with an ERROR at CREATE FUNCTION time.

Regular SQL functions without BEGIN ATOMIC are unaffected: they
may also reference temporary objects, but they remain present
after session end and simply fail at execution time if called.

This restriction prevents surprising behavior and aligns with
the expectation that permanent objects should not depend on
session-local state.

This patch also slightly modifies the regress tests in returning.sql,
as it creates a BEGIN ATOMIC SQL function in its tests. The table
used in the test is now permanent.
---
 src/backend/catalog/pg_proc.c                 |  9 ++-
 src/backend/executor/functions.c              | 55 +++++++++++++++++--
 src/include/executor/functions.h              |  2 +-
 .../regress/expected/create_function_sql.out  | 13 +++++
 src/test/regress/expected/returning.out       | 52 +++++++++---------
 src/test/regress/sql/create_function_sql.sql  | 13 +++++
 src/test/regress/sql/returning.sql            |  3 +-
 7 files changed, 113 insertions(+), 34 deletions(-)

diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 75b17fed15..82dd3b9c97 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -27,6 +27,7 @@
 #include "catalog/pg_transform.h"
 #include "catalog/pg_type.h"
 #include "executor/functions.h"
+#include "catalog/namespace.h"
 #include "funcapi.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
@@ -974,7 +975,13 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
 			Oid			rettype;
 			TupleDesc	rettupdesc;
 
-			check_sql_fn_statements(querytree_list);
+			/* If true, validator will error if the function body references any
+			 * temporary relation.  Computed here as: function has a prosqlbody (i.e.
+			 * defined with BEGIN ATOMIC) and is being created in a non-temp schema.
+			 */
+			bool error_on_temp_deps = !isnull && !isAnyTempNamespace(proc->pronamespace);
+
+			check_sql_fn_statements(querytree_list, error_on_temp_deps);
 
 			(void) get_func_result_type(funcoid, &rettype, &rettupdesc);
 
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 630d708d2a..71416a0812 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -223,7 +223,7 @@ static void sql_compile_error_callback(void *arg);
 static void sql_exec_error_callback(void *arg);
 static void ShutdownSQLFunction(Datum arg);
 static void RemoveSQLFunctionCache(void *arg);
-static void check_sql_fn_statement(List *queryTreeList);
+static void check_sql_fn_statement(List *queryTreeList, bool enforce_no_temp_deps);
 static bool check_sql_stmt_retval(List *queryTreeList,
 								  Oid rettype, TupleDesc rettupdesc,
 								  char prokind, bool insertDroppedCols);
@@ -950,7 +950,7 @@ prepare_next_query(SQLFunctionHashEntry *func)
 	/*
 	 * Check that there are no statements we don't want to allow.
 	 */
-	check_sql_fn_statement(queryTree_list);
+	check_sql_fn_statement(queryTree_list, false);
 
 	/*
 	 * If this is the last query, check that the function returns the type it
@@ -1247,7 +1247,7 @@ sql_postrewrite_callback(List *querytree_list, void *arg)
 	 * there's no real point in this because the result can't change from what
 	 * we saw originally.  But it's cheap and maybe someday it will matter.)
 	 */
-	check_sql_fn_statement(querytree_list);
+	check_sql_fn_statement(querytree_list, false);
 
 	/*
 	 * If this is the last query, we must re-do what check_sql_stmt_retval did
@@ -2030,9 +2030,13 @@ RemoveSQLFunctionCache(void *arg)
  *
  * Check statements in an SQL function.  Error out if there is anything that
  * is not acceptable.
+ *
+ * The enforce_no_temp_deps flag indicates whether the function was defined
+ * using SQL-standard BEGIN ATOMIC.  In that case we additionally check for
+ * dependencies on temporary relations and throw an ERROR if found.
  */
 void
-check_sql_fn_statements(List *queryTreeLists)
+check_sql_fn_statements(List *queryTreeLists, bool enforce_no_temp_deps)
 {
 	ListCell   *lc;
 
@@ -2041,7 +2045,7 @@ check_sql_fn_statements(List *queryTreeLists)
 	{
 		List	   *sublist = lfirst_node(List, lc);
 
-		check_sql_fn_statement(sublist);
+		check_sql_fn_statement(sublist, enforce_no_temp_deps);
 	}
 }
 
@@ -2049,7 +2053,7 @@ check_sql_fn_statements(List *queryTreeLists)
  * As above, for a single sublist of Queries.
  */
 static void
-check_sql_fn_statement(List *queryTreeList)
+check_sql_fn_statement(List *queryTreeList, bool enforce_no_temp_deps)
 {
 	ListCell   *lc;
 
@@ -2057,6 +2061,45 @@ check_sql_fn_statement(List *queryTreeList)
 	{
 		Query	   *query = lfirst_node(Query, lc);
 
+		if (enforce_no_temp_deps)
+		{
+			ListCell *lc2;
+
+			/*
+			 * Walk the range table of this query to detect references to
+			 * temporary relations.
+			 *
+			 * For ordinary SQL functions, the function definition is stored
+			 * permanently even if it refers to temporary relations.  Once
+			 * the session ends and the temp relation vanishes, the function
+			 * still exists but will fail at execution time with "relation
+			 * does not exist". We do not throw any error at creation
+			 * time in that case; the function is simply left in place.
+			 *
+			 * For functions USING SQL-standard BEGIN ATOMIC definition,
+			 * this situation is more confusing: the function appears to be
+			 * a permanent database object, but in fact its lifetime is tied
+			 * to session-local objects that vanish at session end. To avoid
+			 * silent surprises, we throw an ERROR at CREATE FUNCTION time if
+			 * an ATOMIC function depends on a temporary relation.
+			 */
+			foreach (lc2, query->rtable)
+			{
+				RangeTblEntry *rte = lfirst_node(RangeTblEntry, lc2);
+
+				if (rte->rtekind == RTE_RELATION)
+				{
+					Oid relnsp = get_rel_namespace(rte->relid);
+
+					if (isAnyTempNamespace(relnsp))
+						ereport(ERROR,
+								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+								 errmsg("SQL function defined with BEGIN ATOMIC cannot depend on temporary relation \"%s\"",
+										get_rel_name(rte->relid))));
+				}
+			}
+		}
+
 		/*
 		 * Disallow calling procedures with output arguments.  The current
 		 * implementation would just throw the output values away, unless the
diff --git a/src/include/executor/functions.h b/src/include/executor/functions.h
index 58bdff9b03..ebc73ff40d 100644
--- a/src/include/executor/functions.h
+++ b/src/include/executor/functions.h
@@ -43,7 +43,7 @@ extern SQLFunctionParseInfoPtr prepare_sql_fn_parse_info(HeapTuple procedureTupl
 extern void sql_fn_parser_setup(struct ParseState *pstate,
 								SQLFunctionParseInfoPtr pinfo);
 
-extern void check_sql_fn_statements(List *queryTreeLists);
+extern void check_sql_fn_statements(List *queryTreeLists, bool enforce_no_temp_deps);
 
 extern bool check_sql_fn_retval(List *queryTreeLists,
 								Oid rettype, TupleDesc rettupdesc,
diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out
index 73c6730d45..0269175ea6 100644
--- a/src/test/regress/expected/create_function_sql.out
+++ b/src/test/regress/expected/create_function_sql.out
@@ -292,6 +292,19 @@ CREATE FUNCTION functest_S_xxx(x int) RETURNS int
     AS $$ SELECT x * 2 $$
     RETURN x * 3;
 ERROR:  duplicate function body specified
+-- error: BEGIN ATOMIC SQL-functions cannot depend on temporary tables
+CREATE TEMPORARY TABLE functest_temp AS SELECT 1 AS val;
+CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+  SELECT val FROM functest_temp;
+END;
+ERROR:  SQL function defined with BEGIN ATOMIC cannot depend on temporary relation "functest_temp"
+CONTEXT:  SQL function "functest_temp_dep"
+-- this should work, as the function is created in a temp schema
+CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+  SELECT val FROM functest_temp;
+END;
 -- polymorphic arguments not allowed in this form
 CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
     LANGUAGE SQL
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index 341b689f76..5b2e468d1d 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 1), (SubPlan 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 1), (SubPlan 2), (SubPlan 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 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 1), (SubPlan 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 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..f16844ee29 100644
--- a/src/test/regress/sql/create_function_sql.sql
+++ b/src/test/regress/sql/create_function_sql.sql
@@ -194,6 +194,19 @@ CREATE FUNCTION functest_S_xxx(x int) RETURNS int
     AS $$ SELECT x * 2 $$
     RETURN x * 3;
 
+-- error: BEGIN ATOMIC SQL-functions cannot depend on temporary tables
+CREATE TEMPORARY TABLE functest_temp AS SELECT 1 AS val;
+CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+  SELECT val FROM functest_temp;
+END;
+
+-- this should work, as the function is created in a temp schema
+CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+  SELECT val FROM functest_temp;
+END;
+
 -- polymorphic arguments not allowed in this form
 CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
     LANGUAGE SQL
diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql
index cc99cb53f6..193ce6275e 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;
-- 
2.43.0

From 0ace0c986371a3bc13b4e0e7128b06e751382aa6 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Wed, 8 Oct 2025 21:12:32 +0200
Subject: [PATCH v3] Disallow ATOMIC functions depending on temp  relations

When a SQL function is defined with a BEGIN ATOMIC block and
references a temporary relation, the function is dropped at
session end along with the temp relation. From the user's
perspective, the function looks like a persistent database
object in a permanent schema, yet it disappears silently.

To avoid confusion and inconsistent behavior, reject creation
of such functions with an ERROR at CREATE FUNCTION time.

Regular SQL functions without BEGIN ATOMIC are unaffected: they
may also reference temporary objects, but they remain present
after session end and simply fail at execution time if called.

This restriction prevents surprising behavior and aligns with
the expectation that permanent objects should not depend on
session-local state.

This patch also slightly modifies the regress tests in returning.sql,
as it creates a BEGIN ATOMIC SQL function in its tests. The table
used in the test is now permanent.
---
 src/backend/catalog/pg_proc.c                 |  9 ++-
 src/backend/executor/functions.c              | 55 +++++++++++++++++--
 src/include/executor/functions.h              |  2 +-
 .../regress/expected/create_function_sql.out  | 13 +++++
 src/test/regress/expected/returning.out       | 52 +++++++++---------
 src/test/regress/sql/create_function_sql.sql  | 13 +++++
 src/test/regress/sql/returning.sql            |  3 +-
 7 files changed, 113 insertions(+), 34 deletions(-)

diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index b89b9ccda0..67a0ff3c2b 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -27,6 +27,7 @@
 #include "catalog/pg_transform.h"
 #include "catalog/pg_type.h"
 #include "executor/functions.h"
+#include "catalog/namespace.h"
 #include "funcapi.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
@@ -974,7 +975,13 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
 			Oid			rettype;
 			TupleDesc	rettupdesc;
 
-			check_sql_fn_statements(querytree_list);
+			/* If true, validator will error if the function body references any
+			 * temporary relation.  Computed here as: function has a prosqlbody (i.e.
+			 * defined with BEGIN ATOMIC) and is being created in a non-temp schema.
+			 */
+			bool error_on_temp_deps = !isnull && !isAnyTempNamespace(proc->pronamespace);
+
+			check_sql_fn_statements(querytree_list, error_on_temp_deps);
 
 			(void) get_func_result_type(funcoid, &rettype, &rettupdesc);
 
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 630d708d2a..71416a0812 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -223,7 +223,7 @@ static void sql_compile_error_callback(void *arg);
 static void sql_exec_error_callback(void *arg);
 static void ShutdownSQLFunction(Datum arg);
 static void RemoveSQLFunctionCache(void *arg);
-static void check_sql_fn_statement(List *queryTreeList);
+static void check_sql_fn_statement(List *queryTreeList, bool enforce_no_temp_deps);
 static bool check_sql_stmt_retval(List *queryTreeList,
 								  Oid rettype, TupleDesc rettupdesc,
 								  char prokind, bool insertDroppedCols);
@@ -950,7 +950,7 @@ prepare_next_query(SQLFunctionHashEntry *func)
 	/*
 	 * Check that there are no statements we don't want to allow.
 	 */
-	check_sql_fn_statement(queryTree_list);
+	check_sql_fn_statement(queryTree_list, false);
 
 	/*
 	 * If this is the last query, check that the function returns the type it
@@ -1247,7 +1247,7 @@ sql_postrewrite_callback(List *querytree_list, void *arg)
 	 * there's no real point in this because the result can't change from what
 	 * we saw originally.  But it's cheap and maybe someday it will matter.)
 	 */
-	check_sql_fn_statement(querytree_list);
+	check_sql_fn_statement(querytree_list, false);
 
 	/*
 	 * If this is the last query, we must re-do what check_sql_stmt_retval did
@@ -2030,9 +2030,13 @@ RemoveSQLFunctionCache(void *arg)
  *
  * Check statements in an SQL function.  Error out if there is anything that
  * is not acceptable.
+ *
+ * The enforce_no_temp_deps flag indicates whether the function was defined
+ * using SQL-standard BEGIN ATOMIC.  In that case we additionally check for
+ * dependencies on temporary relations and throw an ERROR if found.
  */
 void
-check_sql_fn_statements(List *queryTreeLists)
+check_sql_fn_statements(List *queryTreeLists, bool enforce_no_temp_deps)
 {
 	ListCell   *lc;
 
@@ -2041,7 +2045,7 @@ check_sql_fn_statements(List *queryTreeLists)
 	{
 		List	   *sublist = lfirst_node(List, lc);
 
-		check_sql_fn_statement(sublist);
+		check_sql_fn_statement(sublist, enforce_no_temp_deps);
 	}
 }
 
@@ -2049,7 +2053,7 @@ check_sql_fn_statements(List *queryTreeLists)
  * As above, for a single sublist of Queries.
  */
 static void
-check_sql_fn_statement(List *queryTreeList)
+check_sql_fn_statement(List *queryTreeList, bool enforce_no_temp_deps)
 {
 	ListCell   *lc;
 
@@ -2057,6 +2061,45 @@ check_sql_fn_statement(List *queryTreeList)
 	{
 		Query	   *query = lfirst_node(Query, lc);
 
+		if (enforce_no_temp_deps)
+		{
+			ListCell *lc2;
+
+			/*
+			 * Walk the range table of this query to detect references to
+			 * temporary relations.
+			 *
+			 * For ordinary SQL functions, the function definition is stored
+			 * permanently even if it refers to temporary relations.  Once
+			 * the session ends and the temp relation vanishes, the function
+			 * still exists but will fail at execution time with "relation
+			 * does not exist". We do not throw any error at creation
+			 * time in that case; the function is simply left in place.
+			 *
+			 * For functions USING SQL-standard BEGIN ATOMIC definition,
+			 * this situation is more confusing: the function appears to be
+			 * a permanent database object, but in fact its lifetime is tied
+			 * to session-local objects that vanish at session end. To avoid
+			 * silent surprises, we throw an ERROR at CREATE FUNCTION time if
+			 * an ATOMIC function depends on a temporary relation.
+			 */
+			foreach (lc2, query->rtable)
+			{
+				RangeTblEntry *rte = lfirst_node(RangeTblEntry, lc2);
+
+				if (rte->rtekind == RTE_RELATION)
+				{
+					Oid relnsp = get_rel_namespace(rte->relid);
+
+					if (isAnyTempNamespace(relnsp))
+						ereport(ERROR,
+								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+								 errmsg("SQL function defined with BEGIN ATOMIC cannot depend on temporary relation \"%s\"",
+										get_rel_name(rte->relid))));
+				}
+			}
+		}
+
 		/*
 		 * Disallow calling procedures with output arguments.  The current
 		 * implementation would just throw the output values away, unless the
diff --git a/src/include/executor/functions.h b/src/include/executor/functions.h
index 58bdff9b03..ebc73ff40d 100644
--- a/src/include/executor/functions.h
+++ b/src/include/executor/functions.h
@@ -43,7 +43,7 @@ extern SQLFunctionParseInfoPtr prepare_sql_fn_parse_info(HeapTuple procedureTupl
 extern void sql_fn_parser_setup(struct ParseState *pstate,
 								SQLFunctionParseInfoPtr pinfo);
 
-extern void check_sql_fn_statements(List *queryTreeLists);
+extern void check_sql_fn_statements(List *queryTreeLists, bool enforce_no_temp_deps);
 
 extern bool check_sql_fn_retval(List *queryTreeLists,
 								Oid rettype, TupleDesc rettupdesc,
diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out
index 73c6730d45..0269175ea6 100644
--- a/src/test/regress/expected/create_function_sql.out
+++ b/src/test/regress/expected/create_function_sql.out
@@ -292,6 +292,19 @@ CREATE FUNCTION functest_S_xxx(x int) RETURNS int
     AS $$ SELECT x * 2 $$
     RETURN x * 3;
 ERROR:  duplicate function body specified
+-- error: BEGIN ATOMIC SQL-functions cannot depend on temporary tables
+CREATE TEMPORARY TABLE functest_temp AS SELECT 1 AS val;
+CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+  SELECT val FROM functest_temp;
+END;
+ERROR:  SQL function defined with BEGIN ATOMIC cannot depend on temporary relation "functest_temp"
+CONTEXT:  SQL function "functest_temp_dep"
+-- this should work, as the function is created in a temp schema
+CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+  SELECT val FROM functest_temp;
+END;
 -- polymorphic arguments not allowed in this form
 CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
     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..f16844ee29 100644
--- a/src/test/regress/sql/create_function_sql.sql
+++ b/src/test/regress/sql/create_function_sql.sql
@@ -194,6 +194,19 @@ CREATE FUNCTION functest_S_xxx(x int) RETURNS int
     AS $$ SELECT x * 2 $$
     RETURN x * 3;
 
+-- error: BEGIN ATOMIC SQL-functions cannot depend on temporary tables
+CREATE TEMPORARY TABLE functest_temp AS SELECT 1 AS val;
+CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+  SELECT val FROM functest_temp;
+END;
+
+-- this should work, as the function is created in a temp schema
+CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+  SELECT val FROM functest_temp;
+END;
+
 -- polymorphic arguments not allowed in this form
 CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
     LANGUAGE SQL
diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql
index cc99cb53f6..193ce6275e 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;
-- 
2.43.0

From 5e538c3cab1db93ffdff821007b900d1ffd60e39 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Mon, 13 Oct 2025 13:48:08 +0200
Subject: [PATCH v4 1/2] Refactor dependency recording to enable dependency
 collection

Add new function collectDependenciesFromExpr() that collects object
dependencies into caller-supplied ObjectAddresses structures without
immediately recording them.

This enables more flexible dependency handling patterns where callers
need to examine, filter, or modify dependencies before recording them.
The existing recordDependencyOnExpr() function is reimplemented using
the new collection function, maintaining full backward compatibility.

This refactoring lays the groundwork for enhanced dependency validation
in SQL functions while preserving all existing functionality.
---
 src/backend/catalog/dependency.c | 64 ++++++++++++++++++++++----------
 src/include/catalog/dependency.h |  3 ++
 2 files changed, 48 insertions(+), 19 deletions(-)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 7dded634eb..d6142b1750 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1553,10 +1553,41 @@ void
 recordDependencyOnExpr(const ObjectAddress *depender,
 					   Node *expr, List *rtable,
 					   DependencyType behavior)
+{
+	ObjectAddresses *addrs;
+
+	addrs = new_object_addresses();
+
+	/* Collect all dependencies from the expression */
+	collectDependenciesFromExpr(addrs, expr, rtable);
+
+	/* And record 'em */
+	recordMultipleDependencies(depender,
+							   addrs->refs, addrs->numrefs,
+							   behavior);
+
+	free_object_addresses(addrs);
+}
+
+/*
+ * collectDependenciesFromExpr - collect expression dependencies
+ *
+ * This function analyzes an expression or query in node-tree form to find all
+ * the objects it refers to (tables, columns, operators, functions, etc.) and
+ * adds them to the provided ObjectAddresses structure. Unlike recordDependencyOnExpr,
+ * this function does not immediately record the dependencies, allowing the caller
+ * to examine, filter, or modify the collected dependencies before recording them.
+ *
+ * This is particularly useful when dependency recording needs to be conditional
+ * or when dependencies from multiple sources need to be merged before recording.
+ */
+void
+collectDependenciesFromExpr(ObjectAddresses *addrs,
+							Node *expr, List *rtable)
 {
 	find_expr_references_context context;
 
-	context.addrs = new_object_addresses();
+	context.addrs = addrs;
 
 	/* Set up interpretation for Vars at varlevelsup = 0 */
 	context.rtables = list_make1(rtable);
@@ -1565,14 +1596,7 @@ recordDependencyOnExpr(const ObjectAddress *depender,
 	find_expr_references_walker(expr, &context);
 
 	/* Remove any duplicates */
-	eliminate_duplicate_dependencies(context.addrs);
-
-	/* And record 'em */
-	recordMultipleDependencies(depender,
-							   context.addrs->refs, context.addrs->numrefs,
-							   behavior);
-
-	free_object_addresses(context.addrs);
+	eliminate_duplicate_dependencies(addrs);
 }
 
 /*
@@ -1599,10 +1623,12 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 								DependencyType self_behavior,
 								bool reverse_self)
 {
+	ObjectAddresses *addrs;
 	find_expr_references_context context;
 	RangeTblEntry rte = {0};
 
-	context.addrs = new_object_addresses();
+	addrs = new_object_addresses();
+	context.addrs = addrs;
 
 	/* We gin up a rather bogus rangetable list to handle Vars */
 	rte.type = T_RangeTblEntry;
@@ -1617,11 +1643,11 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 	find_expr_references_walker(expr, &context);
 
 	/* Remove any duplicates */
-	eliminate_duplicate_dependencies(context.addrs);
+	eliminate_duplicate_dependencies(addrs);
 
 	/* Separate self-dependencies if necessary */
 	if ((behavior != self_behavior || reverse_self) &&
-		context.addrs->numrefs > 0)
+		addrs->numrefs > 0)
 	{
 		ObjectAddresses *self_addrs;
 		ObjectAddress *outobj;
@@ -1630,11 +1656,11 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 
 		self_addrs = new_object_addresses();
 
-		outobj = context.addrs->refs;
+		outobj = addrs->refs;
 		outrefs = 0;
-		for (oldref = 0; oldref < context.addrs->numrefs; oldref++)
+		for (oldref = 0; oldref < addrs->numrefs; oldref++)
 		{
-			ObjectAddress *thisobj = context.addrs->refs + oldref;
+			ObjectAddress *thisobj = addrs->refs + oldref;
 
 			if (thisobj->classId == RelationRelationId &&
 				thisobj->objectId == relId)
@@ -1644,13 +1670,13 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 			}
 			else
 			{
-				/* Keep it in context.addrs */
+				/* Keep it in addrs */
 				*outobj = *thisobj;
 				outobj++;
 				outrefs++;
 			}
 		}
-		context.addrs->numrefs = outrefs;
+		addrs->numrefs = outrefs;
 
 		/* Record the self-dependencies with the appropriate direction */
 		if (!reverse_self)
@@ -1675,10 +1701,10 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 
 	/* Record the external dependencies */
 	recordMultipleDependencies(depender,
-							   context.addrs->refs, context.addrs->numrefs,
+							   addrs->refs, addrs->numrefs,
 							   behavior);
 
-	free_object_addresses(context.addrs);
+	free_object_addresses(addrs);
 }
 
 /*
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 0ea7ccf524..f5061605dd 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -114,6 +114,9 @@ extern void recordDependencyOnExpr(const ObjectAddress *depender,
 								   Node *expr, List *rtable,
 								   DependencyType behavior);
 
+extern void collectDependenciesFromExpr(ObjectAddresses *addrs,
+										Node *expr, List *rtable);
+
 extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 											Node *expr, Oid relId,
 											DependencyType behavior,
-- 
2.43.0

From 5e40e54a18ee4dcd8c7fecd511ab909eebb6f210 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Mon, 13 Oct 2025 15:28:22 +0200
Subject: [PATCH v4 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..9839690bcc 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(tp))
+			{
+				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

From 5e538c3cab1db93ffdff821007b900d1ffd60e39 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Mon, 13 Oct 2025 13:48:08 +0200
Subject: [PATCH v5 1/2] Refactor dependency recording to enable dependency
 collection

Add new function collectDependenciesFromExpr() that collects object
dependencies into caller-supplied ObjectAddresses structures without
immediately recording them.

This enables more flexible dependency handling patterns where callers
need to examine, filter, or modify dependencies before recording them.
The existing recordDependencyOnExpr() function is reimplemented using
the new collection function, maintaining full backward compatibility.

This refactoring lays the groundwork for enhanced dependency validation
in SQL functions while preserving all existing functionality.
---
 src/backend/catalog/dependency.c | 64 ++++++++++++++++++++++----------
 src/include/catalog/dependency.h |  3 ++
 2 files changed, 48 insertions(+), 19 deletions(-)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 7dded634eb..d6142b1750 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1553,10 +1553,41 @@ void
 recordDependencyOnExpr(const ObjectAddress *depender,
 					   Node *expr, List *rtable,
 					   DependencyType behavior)
+{
+	ObjectAddresses *addrs;
+
+	addrs = new_object_addresses();
+
+	/* Collect all dependencies from the expression */
+	collectDependenciesFromExpr(addrs, expr, rtable);
+
+	/* And record 'em */
+	recordMultipleDependencies(depender,
+							   addrs->refs, addrs->numrefs,
+							   behavior);
+
+	free_object_addresses(addrs);
+}
+
+/*
+ * collectDependenciesFromExpr - collect expression dependencies
+ *
+ * This function analyzes an expression or query in node-tree form to find all
+ * the objects it refers to (tables, columns, operators, functions, etc.) and
+ * adds them to the provided ObjectAddresses structure. Unlike recordDependencyOnExpr,
+ * this function does not immediately record the dependencies, allowing the caller
+ * to examine, filter, or modify the collected dependencies before recording them.
+ *
+ * This is particularly useful when dependency recording needs to be conditional
+ * or when dependencies from multiple sources need to be merged before recording.
+ */
+void
+collectDependenciesFromExpr(ObjectAddresses *addrs,
+							Node *expr, List *rtable)
 {
 	find_expr_references_context context;
 
-	context.addrs = new_object_addresses();
+	context.addrs = addrs;
 
 	/* Set up interpretation for Vars at varlevelsup = 0 */
 	context.rtables = list_make1(rtable);
@@ -1565,14 +1596,7 @@ recordDependencyOnExpr(const ObjectAddress *depender,
 	find_expr_references_walker(expr, &context);
 
 	/* Remove any duplicates */
-	eliminate_duplicate_dependencies(context.addrs);
-
-	/* And record 'em */
-	recordMultipleDependencies(depender,
-							   context.addrs->refs, context.addrs->numrefs,
-							   behavior);
-
-	free_object_addresses(context.addrs);
+	eliminate_duplicate_dependencies(addrs);
 }
 
 /*
@@ -1599,10 +1623,12 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 								DependencyType self_behavior,
 								bool reverse_self)
 {
+	ObjectAddresses *addrs;
 	find_expr_references_context context;
 	RangeTblEntry rte = {0};
 
-	context.addrs = new_object_addresses();
+	addrs = new_object_addresses();
+	context.addrs = addrs;
 
 	/* We gin up a rather bogus rangetable list to handle Vars */
 	rte.type = T_RangeTblEntry;
@@ -1617,11 +1643,11 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 	find_expr_references_walker(expr, &context);
 
 	/* Remove any duplicates */
-	eliminate_duplicate_dependencies(context.addrs);
+	eliminate_duplicate_dependencies(addrs);
 
 	/* Separate self-dependencies if necessary */
 	if ((behavior != self_behavior || reverse_self) &&
-		context.addrs->numrefs > 0)
+		addrs->numrefs > 0)
 	{
 		ObjectAddresses *self_addrs;
 		ObjectAddress *outobj;
@@ -1630,11 +1656,11 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 
 		self_addrs = new_object_addresses();
 
-		outobj = context.addrs->refs;
+		outobj = addrs->refs;
 		outrefs = 0;
-		for (oldref = 0; oldref < context.addrs->numrefs; oldref++)
+		for (oldref = 0; oldref < addrs->numrefs; oldref++)
 		{
-			ObjectAddress *thisobj = context.addrs->refs + oldref;
+			ObjectAddress *thisobj = addrs->refs + oldref;
 
 			if (thisobj->classId == RelationRelationId &&
 				thisobj->objectId == relId)
@@ -1644,13 +1670,13 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 			}
 			else
 			{
-				/* Keep it in context.addrs */
+				/* Keep it in addrs */
 				*outobj = *thisobj;
 				outobj++;
 				outrefs++;
 			}
 		}
-		context.addrs->numrefs = outrefs;
+		addrs->numrefs = outrefs;
 
 		/* Record the self-dependencies with the appropriate direction */
 		if (!reverse_self)
@@ -1675,10 +1701,10 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 
 	/* Record the external dependencies */
 	recordMultipleDependencies(depender,
-							   context.addrs->refs, context.addrs->numrefs,
+							   addrs->refs, addrs->numrefs,
 							   behavior);
 
-	free_object_addresses(context.addrs);
+	free_object_addresses(addrs);
 }
 
 /*
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 0ea7ccf524..f5061605dd 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -114,6 +114,9 @@ extern void recordDependencyOnExpr(const ObjectAddress *depender,
 								   Node *expr, List *rtable,
 								   DependencyType behavior);
 
+extern void collectDependenciesFromExpr(ObjectAddresses *addrs,
+										Node *expr, List *rtable);
+
 extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 											Node *expr, Oid relId,
 											DependencyType behavior,
-- 
2.43.0

From 8525f08f823bd5aa6ebb6059bb9f660ce7102da6 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
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

From 3af74aa118e81dc5b9a84d670fb973026d532227 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Wed, 5 Nov 2025 09:55:31 +0100
Subject: [PATCH v6 1/2] Refactor dependency recording to enable dependency
 collection

Add new function collectDependenciesFromExpr() that collects object
dependencies into caller-supplied ObjectAddresses structures without
immediately recording them.

This enables more flexible dependency handling patterns where callers
need to examine, filter, or modify dependencies before recording them.
The caller is responsible for calling eliminate_duplicate_dependencies()
on the collected dependencies before recording them. This design avoids
redundant deduplication work when collecting dependencies from multiple
sources.

The existing recordDependencyOnExpr() function is reimplemented using
the new collection function, maintaining full backward compatibility.

This refactoring lays the groundwork for enhanced dependency validation
in SQL functions while preserving all existing functionality.
---
 src/backend/catalog/dependency.c | 54 +++++++++++++++++++++++++-------
 src/include/catalog/dependency.h |  3 ++
 2 files changed, 46 insertions(+), 11 deletions(-)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 7dded634eb..bd5d705936 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1554,25 +1554,57 @@ recordDependencyOnExpr(const ObjectAddress *depender,
 					   Node *expr, List *rtable,
 					   DependencyType behavior)
 {
-	find_expr_references_context context;
+	ObjectAddresses *addrs;
 
-	context.addrs = new_object_addresses();
+	addrs = new_object_addresses();
 
-	/* Set up interpretation for Vars at varlevelsup = 0 */
-	context.rtables = list_make1(rtable);
+	/* Collect all dependencies from the expression */
+	collectDependenciesFromExpr(addrs, expr, rtable);
 
-	/* Scan the expression tree for referenceable objects */
-	find_expr_references_walker(expr, &context);
-
-	/* Remove any duplicates */
-	eliminate_duplicate_dependencies(context.addrs);
+	/* Remove duplicates */
+	eliminate_duplicate_dependencies(addrs);
 
 	/* And record 'em */
 	recordMultipleDependencies(depender,
-							   context.addrs->refs, context.addrs->numrefs,
+							   addrs->refs, addrs->numrefs,
 							   behavior);
 
-	free_object_addresses(context.addrs);
+	free_object_addresses(addrs);
+}
+
+/*
+ * collectDependenciesFromExpr - collect expression dependencies
+ *
+ * This function analyzes an expression or query in node-tree form to
+ * find all the objects it refers to (tables, columns, operators,
+ * functions, etc.) and adds them to the provided ObjectAddresses
+ * structure. Unlike recordDependencyOnExpr, this function does not
+ * immediately record the dependencies, allowing the caller to examine,
+ * filter, or modify the collected dependencies before recording them.
+ *
+ * This is particularly useful when dependency recording needs to be
+ * conditional or when dependencies from multiple sources need to be
+ * merged before recording.
+ *
+ * Note: the caller is responsible for calling
+ * eliminate_duplicate_dependencies() on the ObjectAddresses structure
+ * before recording the dependencies, if duplicate elimination is
+ * desired. This design allows collecting dependencies from multiple
+ * sources without redundant deduplication work.
+ */
+void
+collectDependenciesFromExpr(ObjectAddresses *addrs,
+							Node *expr, List *rtable)
+{
+	find_expr_references_context context;
+
+	context.addrs = addrs;
+
+	/* Set up interpretation for Vars at varlevelsup = 0 */
+	context.rtables = list_make1(rtable);
+
+	/* Scan the expression tree for referenceable objects */
+	find_expr_references_walker(expr, &context);
 }
 
 /*
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 0ea7ccf524..f5061605dd 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -114,6 +114,9 @@ extern void recordDependencyOnExpr(const ObjectAddress *depender,
 								   Node *expr, List *rtable,
 								   DependencyType behavior);
 
+extern void collectDependenciesFromExpr(ObjectAddresses *addrs,
+										Node *expr, List *rtable);
+
 extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 											Node *expr, Oid relId,
 											DependencyType behavior,
-- 
2.43.0

From 6d3cadd472fed5157d46b4ccd72f3d4bb4ecbf00 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
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

From 6c33204112d3790cf438d6408dc4dd01ac0d313b Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 21 Nov 2025 16:05:06 -0500
Subject: [PATCH v7 1/2] Refactor dependency recording to enable dependency
 collection.

Add new function collectDependenciesOfExpr() that collects expression
dependencies into a caller-supplied ObjectAddresses structure, without
immediately recording them.

This enables more flexible dependency handling patterns where callers
need to examine, filter, or modify dependencies before recording them.

The caller is responsible for ensuring that the results are
de-duplicated before being put into pg_depend.  (External callers
will not need to do that explicitly, since they must go through
record_object_address_dependencies() which will take care of it.)
This design avoids redundant de-duplication work when collecting
dependencies from multiple sources.

The existing recordDependencyOnExpr() function is reimplemented using
the new collection function, maintaining backward compatibility.

Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/19cf6ae1-04cd-422c-a760-d7e75fe6cba9@uni-muenster.de
---
 src/backend/catalog/dependency.c | 54 +++++++++++++++++++++++++-------
 src/include/catalog/dependency.h |  3 ++
 2 files changed, 46 insertions(+), 11 deletions(-)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 7dded634eb8..61bdf4a577c 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1554,25 +1554,57 @@ recordDependencyOnExpr(const ObjectAddress *depender,
 					   Node *expr, List *rtable,
 					   DependencyType behavior)
 {
-	find_expr_references_context context;
-
-	context.addrs = new_object_addresses();
+	ObjectAddresses *addrs;
 
-	/* Set up interpretation for Vars at varlevelsup = 0 */
-	context.rtables = list_make1(rtable);
+	addrs = new_object_addresses();
 
-	/* Scan the expression tree for referenceable objects */
-	find_expr_references_walker(expr, &context);
+	/* Collect all dependencies from the expression */
+	collectDependenciesOfExpr(addrs, expr, rtable);
 
-	/* Remove any duplicates */
-	eliminate_duplicate_dependencies(context.addrs);
+	/* Remove duplicates */
+	eliminate_duplicate_dependencies(addrs);
 
 	/* And record 'em */
 	recordMultipleDependencies(depender,
-							   context.addrs->refs, context.addrs->numrefs,
+							   addrs->refs, addrs->numrefs,
 							   behavior);
 
-	free_object_addresses(context.addrs);
+	free_object_addresses(addrs);
+}
+
+/*
+ * collectDependenciesOfExpr - collect expression dependencies
+ *
+ * This function analyzes an expression or query in node-tree form to
+ * find all the objects it refers to (tables, columns, operators,
+ * functions, etc.) and adds them to the provided ObjectAddresses
+ * structure. Unlike recordDependencyOnExpr, this function does not
+ * immediately record the dependencies, allowing the caller to add to,
+ * filter, or modify the collected dependencies before recording them.
+ *
+ * rtable is the rangetable to be used to interpret Vars with varlevelsup=0.
+ * It can be NIL if no such variables are expected.
+ *
+ * Note: the returned list may well contain duplicates.  The caller should
+ * de-duplicate before recording the dependencies.  Within this file, callers
+ * must call eliminate_duplicate_dependencies().  External callers typically
+ * go through record_object_address_dependencies() which will see to that.
+ * This choice allows collecting dependencies from multiple sources without
+ * redundant de-duplication work.
+ */
+void
+collectDependenciesOfExpr(ObjectAddresses *addrs,
+						  Node *expr, List *rtable)
+{
+	find_expr_references_context context;
+
+	context.addrs = addrs;
+
+	/* Set up interpretation for Vars at varlevelsup = 0 */
+	context.rtables = list_make1(rtable);
+
+	/* Scan the expression tree for referenceable objects */
+	find_expr_references_walker(expr, &context);
 }
 
 /*
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 0ea7ccf5243..2712befc8e7 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -114,6 +114,9 @@ extern void recordDependencyOnExpr(const ObjectAddress *depender,
 								   Node *expr, List *rtable,
 								   DependencyType behavior);
 
+extern void collectDependenciesOfExpr(ObjectAddresses *addrs,
+									  Node *expr, List *rtable);
+
 extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 											Node *expr, Oid relId,
 											DependencyType behavior,
-- 
2.43.7

From d77d4701198283b72bff47c3b2c912b7523cb44a Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 21 Nov 2025 18:24:22 -0500
Subject: [PATCH v7 2/2] Disallow dependencies from non-temporary functions to
 temp objects.

We don't have an official concept of temporary functions.  (You can
make one explicitly in pg_temp, but then you have to explicitly
schema-qualify it on every call.)  However, until now we were quite
laissez-faire about whether a non-temporary function could depend on
a temporary object, such as a temp table or view.  If one did, it
would silently go away at end of session, due to the implied
DROP ... CASCADE on the session's temporary objects.  People have
complained that that's surprising, so let's fix it by disallowing
the case.  Hand-made temp functions are still allowed to reference
temp objects, so long as they're in our own session's temp schema.

This required modifying several existing regression tests that were
depending on exactly the case that's now forbidden.  Maybe that's
an indication that this is a bad idea?

We should probably make the implementation of temp-by-default
views use the same infrastructure used here, but that's for
another patch.  It's unclear whether there are any other object
classes that deserve similar treatment.

Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/19cf6ae1-04cd-422c-a760-d7e75fe6cba9@uni-muenster.de
---
 src/backend/catalog/dependency.c              |  42 +++++++
 src/backend/catalog/pg_proc.c                 |  42 +++++--
 src/backend/commands/functioncmds.c           |   2 +
 src/backend/commands/typecmds.c               |   3 +
 src/include/catalog/dependency.h              |   4 +
 .../expected/temp-schema-cleanup.out          |  10 --
 .../isolation/specs/temp-schema-cleanup.spec  |   5 -
 .../regress/expected/create_function_sql.out  | 118 ++++++++++++++++++
 src/test/regress/expected/rangefuncs.out      |  44 +++----
 src/test/regress/expected/returning.out       |  65 ++++++----
 src/test/regress/expected/rowtypes.out        |  13 +-
 src/test/regress/sql/create_function_sql.sql  | 105 ++++++++++++++++
 src/test/regress/sql/rangefuncs.sql           |  28 ++---
 src/test/regress/sql/returning.sql            |  12 +-
 src/test/regress/sql/rowtypes.sql             |   8 +-
 15 files changed, 409 insertions(+), 92 deletions(-)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 61bdf4a577c..9cb4c64f3d9 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -22,6 +22,7 @@
 #include "catalog/dependency.h"
 #include "catalog/heap.h"
 #include "catalog/index.h"
+#include "catalog/namespace.h"
 #include "catalog/objectaccess.h"
 #include "catalog/pg_am.h"
 #include "catalog/pg_amop.h"
@@ -2434,6 +2435,47 @@ process_function_rte_ref(RangeTblEntry *rte, AttrNumber attnum,
 					attnum, rte->eref->aliasname)));
 }
 
+/*
+ * find_temp_object - search an array of dependency references for temp objects
+ *
+ * Scan an ObjectAddresses array for references to temporary objects (objects
+ * in temporary namespaces), ignoring those in our own temp namespace if
+ * local_temp_okay is true.  If one is found, return true after storing its
+ * address in *foundobj.
+ *
+ * There's no need to identify all such objects; the caller will throw an
+ * error anyway, so identifying the first one seems sufficiently courteous.
+ * (We'd throw the error here, except we don't know the referencing object.)
+ */
+bool
+find_temp_object(const ObjectAddresses *addrs, bool local_temp_okay,
+				 ObjectAddress *foundobj)
+{
+	for (int i = 0; i < addrs->numrefs; i++)
+	{
+		const 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, except if
+		 * local_temp_okay and it's our own temp namespace.
+		 */
+		if (OidIsValid(objnamespace) && isAnyTempNamespace(objnamespace) &&
+			!(local_temp_okay && isTempNamespace(objnamespace)))
+		{
+			*foundobj = *thisobj;
+			return true;
+		}
+	}
+	return false;
+}
+
 /*
  * Given an array of dependency references, eliminate any duplicates.
  */
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index b89b9ccda0e..c5e1389ffd5 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -20,6 +20,7 @@
 #include "catalog/catalog.h"
 #include "catalog/dependency.h"
 #include "catalog/indexing.h"
+#include "catalog/namespace.h"
 #include "catalog/objectaccess.h"
 #include "catalog/pg_language.h"
 #include "catalog/pg_namespace.h"
@@ -141,7 +142,8 @@ ProcedureCreate(const char *procedureName,
 	TupleDesc	tupDesc;
 	bool		is_update;
 	ObjectAddress myself,
-				referenced;
+				referenced,
+				temp_object;
 	char	   *detailmsg;
 	int			i;
 	ObjectAddresses *addrs;
@@ -658,17 +660,41 @@ 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 */
+	/* dependencies appearing in new-style SQL routine body */
 	if (languageObjectId == SQLlanguageId && prosqlbody)
-		recordDependencyOnExpr(&myself, prosqlbody, NIL, DEPENDENCY_NORMAL);
+		collectDependenciesOfExpr(addrs, prosqlbody, NIL);
 
 	/* dependency on parameter default expressions */
 	if (parameterDefaults)
-		recordDependencyOnExpr(&myself, (Node *) parameterDefaults,
-							   NIL, DEPENDENCY_NORMAL);
+		collectDependenciesOfExpr(addrs, (Node *) parameterDefaults, NIL);
+
+	/*
+	 * Now that we have all the normal dependencies, thumb through them and
+	 * complain if any are to temporary objects.  This prevents the scenario
+	 * where a non-temp function silently goes away at session exit due to a
+	 * dependency on a temp object.  However, we allow a function created in
+	 * our own pg_temp namespace to refer to other objects in that namespace,
+	 * since then they'd have similar lifespans anyway (and there are field
+	 * uses of that scenario).
+	 */
+	if (find_temp_object(addrs, isTempNamespace(procNamespace), &temp_object))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("cannot use temporary %s in a non-temporary function",
+						getObjectDescription(&temp_object, false)),
+				 errdetail("Only functions created in pg_temp can depend on other temporary objects.")));
+
+	/*
+	 * Now record all normal dependencies at once.  This will also remove any
+	 * duplicates in the list.  (Role and extension dependencies are handled
+	 * separately below.  Role dependencies would have to be separate anyway
+	 * since they are shared dependencies.  An extension dependency could be
+	 * folded into the addrs list, but pg_depend.c doesn't make that easy, and
+	 * it won't duplicate anything we've collected so far anyway.)
+	 */
+	record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL);
+
+	free_object_addresses(addrs);
 
 	/* dependency on owner */
 	if (!is_update)
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 0335e982b31..59d00638ee6 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -153,6 +153,8 @@ compute_return_type(TypeName *returnType, Oid languageOid,
 		address = TypeShellMake(typname, namespaceId, GetUserId());
 		rettype = address.objectId;
 		Assert(OidIsValid(rettype));
+		/* Ensure the new shell type is visible to ProcedureCreate */
+		CommandCounterIncrement();
 	}
 
 	aclresult = object_aclcheck(TypeRelationId, rettype, GetUserId(), ACL_USAGE);
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 5979580139f..47d5047fe8b 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1742,6 +1742,9 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt)
 			   false,			/* Type NOT NULL */
 			   InvalidOid);		/* typcollation */
 
+	/* Ensure these new types are visible to ProcedureCreate */
+	CommandCounterIncrement();
+
 	/* And create the constructor functions for this range type */
 	makeRangeConstructors(typeName, typeNamespace, typoid, rangeSubtype);
 	makeMultirangeConstructors(multirangeTypeName, typeNamespace,
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 2712befc8e7..81309b8ce32 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -123,6 +123,10 @@ extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 											DependencyType self_behavior,
 											bool reverse_self);
 
+extern bool find_temp_object(const ObjectAddresses *addrs,
+							 bool local_temp_okay,
+							 ObjectAddress *foundobj);
+
 extern ObjectAddresses *new_object_addresses(void);
 
 extern void add_exact_object_address(const ObjectAddress *object,
diff --git a/src/test/isolation/expected/temp-schema-cleanup.out b/src/test/isolation/expected/temp-schema-cleanup.out
index d10aee53a80..321ad2c70c2 100644
--- a/src/test/isolation/expected/temp-schema-cleanup.out
+++ b/src/test/isolation/expected/temp-schema-cleanup.out
@@ -15,11 +15,6 @@ step s1_create_temp_objects:
     -- table after.
     CREATE TEMPORARY TABLE invalidate_catalog_cache();
 
-    -- test non-temp function is dropped when depending on temp table
-    CREATE TEMPORARY TABLE just_give_me_a_type(id serial primary key);
-
-    CREATE FUNCTION uses_a_temp_type(just_give_me_a_type) RETURNS int LANGUAGE sql AS $$SELECT 1;$$;
-
 exec
 ----
     
@@ -72,11 +67,6 @@ step s1_create_temp_objects:
     -- table after.
     CREATE TEMPORARY TABLE invalidate_catalog_cache();
 
-    -- test non-temp function is dropped when depending on temp table
-    CREATE TEMPORARY TABLE just_give_me_a_type(id serial primary key);
-
-    CREATE FUNCTION uses_a_temp_type(just_give_me_a_type) RETURNS int LANGUAGE sql AS $$SELECT 1;$$;
-
 exec
 ----
     
diff --git a/src/test/isolation/specs/temp-schema-cleanup.spec b/src/test/isolation/specs/temp-schema-cleanup.spec
index 72decba6cbf..82416e1c76a 100644
--- a/src/test/isolation/specs/temp-schema-cleanup.spec
+++ b/src/test/isolation/specs/temp-schema-cleanup.spec
@@ -35,11 +35,6 @@ step s1_create_temp_objects {
     -- that newer objects are deleted before older objects, so create a
     -- table after.
     CREATE TEMPORARY TABLE invalidate_catalog_cache();
-
-    -- test non-temp function is dropped when depending on temp table
-    CREATE TEMPORARY TABLE just_give_me_a_type(id serial primary key);
-
-    CREATE FUNCTION uses_a_temp_type(just_give_me_a_type) RETURNS int LANGUAGE sql AS $$SELECT 1;$$;
 }
 
 step s1_discard_temp {
diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out
index 73c6730d459..c29cf1e1857 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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 a non-temporary function
+DETAIL:  Only functions created in pg_temp can depend on other 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/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 30241e22da2..d8e8bde49e6 100644
--- a/src/test/regress/expected/rangefuncs.out
+++ b/src/test/regress/expected/rangefuncs.out
@@ -2171,12 +2171,12 @@ NOTICE:  drop cascades to function testrngfunc()
 --
 -- Check some cases involving added/dropped columns in a rowtype result
 --
-create temp table users (userid text, seq int, email text, todrop bool, moredrop int, enabled bool);
-insert into users values ('id',1,'email',true,11,true);
-insert into users values ('id2',2,'email2',true,12,true);
-alter table users drop column todrop;
-create or replace function get_first_user() returns users as
-$$ SELECT * FROM users ORDER BY userid LIMIT 1; $$
+create table rngf_users (userid text, seq int, email text, todrop bool, moredrop int, enabled bool);
+insert into rngf_users values ('id',1,'email',true,11,true);
+insert into rngf_users values ('id2',2,'email2',true,12,true);
+alter table rngf_users drop column todrop;
+create function get_first_user() returns rngf_users as
+$$ SELECT * FROM rngf_users ORDER BY userid LIMIT 1; $$
 language sql stable;
 SELECT get_first_user();
   get_first_user   
@@ -2190,8 +2190,8 @@ SELECT * FROM get_first_user();
  id     |   1 | email |       11 | t
 (1 row)
 
-create or replace function get_users() returns setof users as
-$$ SELECT * FROM users ORDER BY userid; $$
+create function get_users() returns setof rngf_users as
+$$ SELECT * FROM rngf_users ORDER BY userid; $$
 language sql stable;
 SELECT get_users();
       get_users      
@@ -2239,7 +2239,7 @@ select * from usersview;
  id2    |   2 | email2 |       12 | t       |              11 |          2
 (2 rows)
 
-alter table users add column junk text;
+alter table rngf_users add column junk text;
 select * from usersview;
  userid | seq | email  | moredrop | enabled | generate_series | ordinality 
 --------+-----+--------+----------+---------+-----------------+------------
@@ -2247,9 +2247,9 @@ select * from usersview;
  id2    |   2 | email2 |       12 | t       |              11 |          2
 (2 rows)
 
-alter table users drop column moredrop;  -- fail, view has reference
-ERROR:  cannot drop column moredrop of table users because other objects depend on it
-DETAIL:  view usersview depends on column moredrop of table users
+alter table rngf_users drop column moredrop;  -- fail, view has reference
+ERROR:  cannot drop column moredrop of table rngf_users because other objects depend on it
+DETAIL:  view usersview depends on column moredrop of table rngf_users
 HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 -- We used to have a bug that would allow the above to succeed, posing
 -- hazards for later execution of the view.  Check that the internal
@@ -2264,16 +2264,16 @@ delete from pg_depend where
 returning pg_describe_object(classid, objid, objsubid) as obj,
           pg_describe_object(refclassid, refobjid, refobjsubid) as ref,
           deptype;
-              obj               |              ref               | deptype 
---------------------------------+--------------------------------+---------
- rule _RETURN on view usersview | column moredrop of table users | n
+              obj               |                 ref                 | deptype 
+--------------------------------+-------------------------------------+---------
+ rule _RETURN on view usersview | column moredrop of table rngf_users | n
 (1 row)
 
-alter table users drop column moredrop;
+alter table rngf_users drop column moredrop;
 select * from usersview;  -- expect clean failure
 ERROR:  attribute 5 of type record has been dropped
 rollback;
-alter table users alter column seq type numeric;  -- fail, view has reference
+alter table rngf_users alter column seq type numeric;  -- fail, view has reference
 ERROR:  cannot alter type of a column used by a view or rule
 DETAIL:  rule _RETURN on view usersview depends on column "seq"
 -- likewise, check we don't crash if the dependency goes wrong
@@ -2286,12 +2286,12 @@ delete from pg_depend where
 returning pg_describe_object(classid, objid, objsubid) as obj,
           pg_describe_object(refclassid, refobjid, refobjsubid) as ref,
           deptype;
-              obj               |            ref            | deptype 
---------------------------------+---------------------------+---------
- rule _RETURN on view usersview | column seq of table users | n
+              obj               |              ref               | deptype 
+--------------------------------+--------------------------------+---------
+ rule _RETURN on view usersview | column seq of table rngf_users | n
 (1 row)
 
-alter table users alter column seq type numeric;
+alter table rngf_users alter column seq type numeric;
 select * from usersview;  -- expect clean failure
 ERROR:  attribute 2 of type record has wrong type
 DETAIL:  Table has type numeric, but query expects integer.
@@ -2299,7 +2299,7 @@ rollback;
 drop view usersview;
 drop function get_first_user();
 drop function get_users();
-drop table users;
+drop table rngf_users;
 -- check behavior with type coercion required for a set-op
 create or replace function rngfuncbar() returns setof text as
 $$ select 'foo'::varchar union all select 'bar'::varchar ; $$
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index d02c2ceab53..9adf39d15ef 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -1,8 +1,12 @@
 --
 -- Test INSERT/UPDATE/DELETE RETURNING
 --
+-- This script is full of poorly-chosen object names.
+-- Put them in a separate schema to avoid collisions with concurrent scripts.
+CREATE SCHEMA returning_test;
+SET search_path = returning_test, public;
 -- 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 +451,7 @@ INSERT INTO foo VALUES (4)
             new.tableoid::regclass, new.ctid, new.*, *;
                                                                                     QUERY PLAN                                                                                    
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Insert on pg_temp.foo
+ Insert on returning_test.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 +475,7 @@ INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok')
             n.tableoid::regclass, n.ctid, n.*, *;
                                                                         QUERY PLAN                                                                        
 ----------------------------------------------------------------------------------------------------------------------------------------------------------
- Insert on pg_temp.foo
+ Insert on returning_test.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 +502,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 returning_test.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 returning_test.foo foo_1
    ->  Result
          Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
-         ->  Seq Scan on pg_temp.foo foo_1
+         ->  Seq Scan on returning_test.foo foo_1
                Output: foo_1.tableoid, foo_1.ctid
                Filter: (foo_1.f1 = 5)
 (8 rows)
@@ -524,10 +528,10 @@ DELETE FROM foo WHERE f1 = 5
             new.tableoid::regclass, new.ctid, new.*, *;
                                                                                         QUERY PLAN                                                                                        
 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Delete on pg_temp.foo
+ Delete on returning_test.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 returning_test.foo foo_1
+   ->  Seq Scan on returning_test.foo foo_1
          Output: foo_1.tableoid, foo_1.ctid
          Filter: (foo_1.f1 = 5)
 (6 rows)
@@ -547,7 +551,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 returning_test.foo
    Output: (SubPlan expr_1), (SubPlan expr_2)
    ->  Result
          Output: 5, 'subquery test'::text, 42, '99'::bigint
@@ -580,12 +584,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 returning_test.foo
    Output: (SubPlan expr_1), (SubPlan expr_2), (SubPlan expr_3)
-   Update on pg_temp.foo foo_1
+   Update on returning_test.foo foo_1
    ->  Result
          Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
-         ->  Seq Scan on pg_temp.foo foo_1
+         ->  Seq Scan on returning_test.foo foo_1
                Output: foo_1.tableoid, foo_1.ctid
                Filter: (foo_1.f1 = 5)
    SubPlan expr_1
@@ -620,10 +624,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 returning_test.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 returning_test.foo foo_1
+   ->  Seq Scan on returning_test.foo foo_1
          Output: foo_1.tableoid, foo_1.ctid
          Filter: (foo_1.f1 = 5)
    SubPlan expr_1
@@ -656,15 +660,15 @@ EXPLAIN (verbose, costs off)
 DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *;
                                                               QUERY PLAN                                                               
 ---------------------------------------------------------------------------------------------------------------------------------------
- Update on pg_temp.foo
+ Update on returning_test.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 returning_test.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 returning_test.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 returning_test.foo foo_1
                Output: foo_1.ctid, foo_1.f1, foo_1.tableoid
                Filter: (foo_1.f1 = 4)
 (11 rows)
@@ -681,9 +685,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 returning_test.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 returning_test.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 +698,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 returning_test.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 +709,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 returning_test.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 +772,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 returning_test.foo
                      Output: foo.f3, foo.f1, foo.f2, foo.f4, foo.ctid, foo.tableoid
                      Filter: (foo.f3 = 58)
 (12 rows)
@@ -931,7 +935,7 @@ BEGIN ATOMIC
               (SELECT count(*) FROM foo WHERE foo = n);
 END;
 \sf foo_update
-CREATE OR REPLACE FUNCTION public.foo_update()
+CREATE OR REPLACE FUNCTION returning_test.foo_update()
  RETURNS void
  LANGUAGE sql
 BEGIN ATOMIC
@@ -985,4 +989,11 @@ BEGIN ATOMIC
             FROM foo foo_1
            WHERE (foo_1.* = n.*)) AS count;
 END
-DROP FUNCTION foo_update;
+-- Clean up
+RESET search_path;
+DROP SCHEMA returning_test CASCADE;
+NOTICE:  drop cascades to 4 other objects
+DETAIL:  drop cascades to table returning_test.foo
+drop cascades to view voo
+drop cascades to function returning_test.joinview_upd_trig_fn()
+drop cascades to function returning_test.foo_update()
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 677ad2ab9ad..897d6548c7a 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -3,7 +3,7 @@
 --
 -- Make both a standalone composite type and a table rowtype
 create type complex as (r float8, i float8);
-create temp table fullname (first text, last text);
+create table fullname (first text, last text);
 -- Nested composite
 create type quad as (c1 complex, c2 complex);
 -- Some simple tests of I/O conversions and row construction
@@ -903,7 +903,7 @@ rollback;
 -- Test case derived from bug #9085: check * qualification of composite
 -- parameters for SQL functions
 --
-create temp table compos (f1 int, f2 text);
+create table compos (f1 int, f2 text);
 create function fcompos1(v compos) returns void as $$
 insert into compos values (v);  -- fail
 $$ language sql;
@@ -946,6 +946,11 @@ select * from compos;
   3 | three
 (3 rows)
 
+drop table compos cascade;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to function fcompos1(compos)
+drop cascades to function fcompos2(compos)
+drop cascades to function fcompos3(compos)
 --
 -- We allow I/O conversion casts from composite types to strings to be
 -- invoked via cast syntax, but not functional syntax.  This is because
@@ -1038,6 +1043,10 @@ select longname(f) from fullname f;
  Joe Blow
 (1 row)
 
+drop table fullname cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to column fn of table people
+drop cascades to function longname(fullname)
 --
 -- Test that composite values are seen to have the correct column names
 -- (bug #11210 and other reports)
diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql
index 3d5f2a92093..bf2533b7fcc 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/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql
index 3c47c98e113..956ab5c720f 100644
--- a/src/test/regress/sql/rangefuncs.sql
+++ b/src/test/regress/sql/rangefuncs.sql
@@ -651,20 +651,20 @@ drop type rngfunc_type cascade;
 -- Check some cases involving added/dropped columns in a rowtype result
 --
 
-create temp table users (userid text, seq int, email text, todrop bool, moredrop int, enabled bool);
-insert into users values ('id',1,'email',true,11,true);
-insert into users values ('id2',2,'email2',true,12,true);
-alter table users drop column todrop;
+create table rngf_users (userid text, seq int, email text, todrop bool, moredrop int, enabled bool);
+insert into rngf_users values ('id',1,'email',true,11,true);
+insert into rngf_users values ('id2',2,'email2',true,12,true);
+alter table rngf_users drop column todrop;
 
-create or replace function get_first_user() returns users as
-$$ SELECT * FROM users ORDER BY userid LIMIT 1; $$
+create function get_first_user() returns rngf_users as
+$$ SELECT * FROM rngf_users ORDER BY userid LIMIT 1; $$
 language sql stable;
 
 SELECT get_first_user();
 SELECT * FROM get_first_user();
 
-create or replace function get_users() returns setof users as
-$$ SELECT * FROM users ORDER BY userid; $$
+create function get_users() returns setof rngf_users as
+$$ SELECT * FROM rngf_users ORDER BY userid; $$
 language sql stable;
 
 SELECT get_users();
@@ -680,10 +680,10 @@ create temp view usersview as
 SELECT * FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY;
 
 select * from usersview;
-alter table users add column junk text;
+alter table rngf_users add column junk text;
 select * from usersview;
 
-alter table users drop column moredrop;  -- fail, view has reference
+alter table rngf_users drop column moredrop;  -- fail, view has reference
 
 -- We used to have a bug that would allow the above to succeed, posing
 -- hazards for later execution of the view.  Check that the internal
@@ -700,11 +700,11 @@ returning pg_describe_object(classid, objid, objsubid) as obj,
           pg_describe_object(refclassid, refobjid, refobjsubid) as ref,
           deptype;
 
-alter table users drop column moredrop;
+alter table rngf_users drop column moredrop;
 select * from usersview;  -- expect clean failure
 rollback;
 
-alter table users alter column seq type numeric;  -- fail, view has reference
+alter table rngf_users alter column seq type numeric;  -- fail, view has reference
 
 -- likewise, check we don't crash if the dependency goes wrong
 begin;
@@ -718,14 +718,14 @@ returning pg_describe_object(classid, objid, objsubid) as obj,
           pg_describe_object(refclassid, refobjid, refobjsubid) as ref,
           deptype;
 
-alter table users alter column seq type numeric;
+alter table rngf_users alter column seq type numeric;
 select * from usersview;  -- expect clean failure
 rollback;
 
 drop view usersview;
 drop function get_first_user();
 drop function get_users();
-drop table users;
+drop table rngf_users;
 
 -- check behavior with type coercion required for a set-op
 
diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql
index cc99cb53f63..6efa9c7ee6f 100644
--- a/src/test/regress/sql/returning.sql
+++ b/src/test/regress/sql/returning.sql
@@ -2,9 +2,14 @@
 -- Test INSERT/UPDATE/DELETE RETURNING
 --
 
+-- This script is full of poorly-chosen object names.
+-- Put them in a separate schema to avoid collisions with concurrent scripts.
+CREATE SCHEMA returning_test;
+SET search_path = returning_test, public;
+
 -- 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)
@@ -407,4 +412,7 @@ BEGIN ATOMIC
 END;
 
 \sf foo_update
-DROP FUNCTION foo_update;
+
+-- Clean up
+RESET search_path;
+DROP SCHEMA returning_test CASCADE;
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index 174b062144a..31b9881e689 100644
--- a/src/test/regress/sql/rowtypes.sql
+++ b/src/test/regress/sql/rowtypes.sql
@@ -6,7 +6,7 @@
 
 create type complex as (r float8, i float8);
 
-create temp table fullname (first text, last text);
+create table fullname (first text, last text);
 
 -- Nested composite
 
@@ -365,7 +365,7 @@ rollback;
 -- parameters for SQL functions
 --
 
-create temp table compos (f1 int, f2 text);
+create table compos (f1 int, f2 text);
 
 create function fcompos1(v compos) returns void as $$
 insert into compos values (v);  -- fail
@@ -388,6 +388,8 @@ select fcompos2(row(2,'two'));
 select fcompos3(row(3,'three'));
 select * from compos;
 
+drop table compos cascade;
+
 --
 -- We allow I/O conversion casts from composite types to strings to be
 -- invoked via cast syntax, but not functional syntax.  This is because
@@ -423,6 +425,8 @@ alter table fullname add column longname text;
 select f.longname from fullname f;
 select longname(f) from fullname f;
 
+drop table fullname cascade;
+
 --
 -- Test that composite values are seen to have the correct column names
 -- (bug #11210 and other reports)
-- 
2.43.7

From 7a86471c0b9c5d2e1493c37fbb1faa156e6a2dc8 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Sat, 22 Nov 2025 22:43:14 +0100
Subject: [PATCH v8 2/2] Disallow dependencies from non-temporary functions to 
 temp objects.

We don't have an official concept of temporary functions.  (You can
make one explicitly in pg_temp, but then you have to explicitly
schema-qualify it on every call.)  However, until now we were quite
laissez-faire about whether a non-temporary function could depend on
a temporary object, such as a temp table or view.  If one did, it
would silently go away at end of session, due to the implied
DROP ... CASCADE on the session's temporary objects.  People have
complained that that's surprising, so let's fix it by disallowing
the case.  Hand-made temp functions are still allowed to reference
temp objects, so long as they're in our own session's temp schema.

This required modifying several existing regression tests that were
depending on exactly the case that's now forbidden.  Maybe that's
an indication that this is a bad idea?

We should probably make the implementation of temp-by-default
views use the same infrastructure used here, but that's for
another patch.  It's unclear whether there are any other object
classes that deserve similar treatment.

Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
---
 src/backend/catalog/dependency.c              | 42 +++++++++++
 src/backend/catalog/pg_proc.c                 | 43 +++++++++--
 src/backend/commands/functioncmds.c           |  2 +
 src/backend/commands/typecmds.c               |  3 +
 src/include/catalog/dependency.h              |  4 +
 .../expected/temp-schema-cleanup.out          | 10 ---
 .../isolation/specs/temp-schema-cleanup.spec  |  5 --
 .../regress/expected/create_function_sql.out  | 73 +++++++++++++++----
 src/test/regress/expected/rangefuncs.out      |  4 +
 src/test/regress/expected/returning.out       | 17 ++++-
 src/test/regress/expected/rowtypes.out        | 10 +++
 src/test/regress/sql/create_function_sql.sql  | 39 ++++++++++
 src/test/regress/sql/returning.sql            | 10 ++-
 13 files changed, 222 insertions(+), 40 deletions(-)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 61bdf4a577..9cb4c64f3d 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -22,6 +22,7 @@
 #include "catalog/dependency.h"
 #include "catalog/heap.h"
 #include "catalog/index.h"
+#include "catalog/namespace.h"
 #include "catalog/objectaccess.h"
 #include "catalog/pg_am.h"
 #include "catalog/pg_amop.h"
@@ -2434,6 +2435,47 @@ process_function_rte_ref(RangeTblEntry *rte, AttrNumber attnum,
 					attnum, rte->eref->aliasname)));
 }
 
+/*
+ * find_temp_object - search an array of dependency references for temp objects
+ *
+ * Scan an ObjectAddresses array for references to temporary objects (objects
+ * in temporary namespaces), ignoring those in our own temp namespace if
+ * local_temp_okay is true.  If one is found, return true after storing its
+ * address in *foundobj.
+ *
+ * There's no need to identify all such objects; the caller will throw an
+ * error anyway, so identifying the first one seems sufficiently courteous.
+ * (We'd throw the error here, except we don't know the referencing object.)
+ */
+bool
+find_temp_object(const ObjectAddresses *addrs, bool local_temp_okay,
+				 ObjectAddress *foundobj)
+{
+	for (int i = 0; i < addrs->numrefs; i++)
+	{
+		const 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, except if
+		 * local_temp_okay and it's our own temp namespace.
+		 */
+		if (OidIsValid(objnamespace) && isAnyTempNamespace(objnamespace) &&
+			!(local_temp_okay && isTempNamespace(objnamespace)))
+		{
+			*foundobj = *thisobj;
+			return true;
+		}
+	}
+	return false;
+}
+
 /*
  * Given an array of dependency references, eliminate any duplicates.
  */
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index b89b9ccda0..e2f8d61ace 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -20,6 +20,7 @@
 #include "catalog/catalog.h"
 #include "catalog/dependency.h"
 #include "catalog/indexing.h"
+#include "catalog/namespace.h"
 #include "catalog/objectaccess.h"
 #include "catalog/pg_language.h"
 #include "catalog/pg_namespace.h"
@@ -141,7 +142,8 @@ ProcedureCreate(const char *procedureName,
 	TupleDesc	tupDesc;
 	bool		is_update;
 	ObjectAddress myself,
-				referenced;
+				referenced,
+				temp_object;
 	char	   *detailmsg;
 	int			i;
 	ObjectAddresses *addrs;
@@ -658,17 +660,42 @@ 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 */
+	/* dependencies appearing in new-style SQL routine body */
 	if (languageObjectId == SQLlanguageId && prosqlbody)
-		recordDependencyOnExpr(&myself, prosqlbody, NIL, DEPENDENCY_NORMAL);
+		collectDependenciesOfExpr(addrs, prosqlbody, NIL);
 
 	/* dependency on parameter default expressions */
 	if (parameterDefaults)
-		recordDependencyOnExpr(&myself, (Node *) parameterDefaults,
-							   NIL, DEPENDENCY_NORMAL);
+		collectDependenciesOfExpr(addrs, (Node *) parameterDefaults, NIL);
+
+	/*
+	 * Now that we have all the normal dependencies, thumb through them and
+	 * complain if any are to temporary objects.  This prevents the scenario
+	 * where a non-temp function silently goes away at session exit due to a
+	 * dependency on a temp object.  However, we allow a function created in
+	 * our own pg_temp namespace to refer to other objects in that namespace,
+	 * since then they'd have similar lifespans anyway (and there are field
+	 * uses of that scenario).
+	 */
+	if (find_temp_object(addrs, isTempNamespace(procNamespace), &temp_object))
+		ereport(NOTICE,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("function \"%s\" will be effectively temporary",
+						procedureName),
+				 errdetail("It depends on temporary %s.",
+						  getObjectDescription(&temp_object, false))));
+
+	/*
+	 * Now record all normal dependencies at once.  This will also remove any
+	 * duplicates in the list.  (Role and extension dependencies are handled
+	 * separately below.  Role dependencies would have to be separate anyway
+	 * since they are shared dependencies.  An extension dependency could be
+	 * folded into the addrs list, but pg_depend.c doesn't make that easy, and
+	 * it won't duplicate anything we've collected so far anyway.)
+	 */
+	record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL);
+
+	free_object_addresses(addrs);
 
 	/* dependency on owner */
 	if (!is_update)
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 0335e982b3..59d00638ee 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -153,6 +153,8 @@ compute_return_type(TypeName *returnType, Oid languageOid,
 		address = TypeShellMake(typname, namespaceId, GetUserId());
 		rettype = address.objectId;
 		Assert(OidIsValid(rettype));
+		/* Ensure the new shell type is visible to ProcedureCreate */
+		CommandCounterIncrement();
 	}
 
 	aclresult = object_aclcheck(TypeRelationId, rettype, GetUserId(), ACL_USAGE);
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 5979580139..47d5047fe8 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1742,6 +1742,9 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt)
 			   false,			/* Type NOT NULL */
 			   InvalidOid);		/* typcollation */
 
+	/* Ensure these new types are visible to ProcedureCreate */
+	CommandCounterIncrement();
+
 	/* And create the constructor functions for this range type */
 	makeRangeConstructors(typeName, typeNamespace, typoid, rangeSubtype);
 	makeMultirangeConstructors(multirangeTypeName, typeNamespace,
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 2712befc8e..81309b8ce3 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -123,6 +123,10 @@ extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 											DependencyType self_behavior,
 											bool reverse_self);
 
+extern bool find_temp_object(const ObjectAddresses *addrs,
+							 bool local_temp_okay,
+							 ObjectAddress *foundobj);
+
 extern ObjectAddresses *new_object_addresses(void);
 
 extern void add_exact_object_address(const ObjectAddress *object,
diff --git a/src/test/isolation/expected/temp-schema-cleanup.out b/src/test/isolation/expected/temp-schema-cleanup.out
index d10aee53a8..321ad2c70c 100644
--- a/src/test/isolation/expected/temp-schema-cleanup.out
+++ b/src/test/isolation/expected/temp-schema-cleanup.out
@@ -15,11 +15,6 @@ step s1_create_temp_objects:
     -- table after.
     CREATE TEMPORARY TABLE invalidate_catalog_cache();
 
-    -- test non-temp function is dropped when depending on temp table
-    CREATE TEMPORARY TABLE just_give_me_a_type(id serial primary key);
-
-    CREATE FUNCTION uses_a_temp_type(just_give_me_a_type) RETURNS int LANGUAGE sql AS $$SELECT 1;$$;
-
 exec
 ----
     
@@ -72,11 +67,6 @@ step s1_create_temp_objects:
     -- table after.
     CREATE TEMPORARY TABLE invalidate_catalog_cache();
 
-    -- test non-temp function is dropped when depending on temp table
-    CREATE TEMPORARY TABLE just_give_me_a_type(id serial primary key);
-
-    CREATE FUNCTION uses_a_temp_type(just_give_me_a_type) RETURNS int LANGUAGE sql AS $$SELECT 1;$$;
-
 exec
 ----
     
diff --git a/src/test/isolation/specs/temp-schema-cleanup.spec b/src/test/isolation/specs/temp-schema-cleanup.spec
index 72decba6cb..82416e1c76 100644
--- a/src/test/isolation/specs/temp-schema-cleanup.spec
+++ b/src/test/isolation/specs/temp-schema-cleanup.spec
@@ -35,11 +35,6 @@ step s1_create_temp_objects {
     -- that newer objects are deleted before older objects, so create a
     -- table after.
     CREATE TEMPORARY TABLE invalidate_catalog_cache();
-
-    -- test non-temp function is dropped when depending on temp table
-    CREATE TEMPORARY TABLE just_give_me_a_type(id serial primary key);
-
-    CREATE FUNCTION uses_a_temp_type(just_give_me_a_type) RETURNS int LANGUAGE sql AS $$SELECT 1;$$;
 }
 
 step s1_discard_temp {
diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out
index 73c6730d45..da86701a77 100644
--- a/src/test/regress/expected/create_function_sql.out
+++ b/src/test/regress/expected/create_function_sql.out
@@ -297,6 +297,45 @@ 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 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;
+-- 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 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;
+NOTICE:  function "functest_temp_view" will be effectively temporary
+DETAIL:  It depends on temporary view temp_view.
+-- 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;
+NOTICE:  function "functest_temp_sequence" will be effectively temporary
+DETAIL:  It depends on temporary sequence temp_seq.
+-- 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;
+NOTICE:  function "functest_temp_domain" will be effectively temporary
+DETAIL:  It depends on temporary type temp_domain.
 -- check reporting of parse-analysis errors
 CREATE FUNCTION functest_S_xx(x date) RETURNS boolean
     LANGUAGE SQL
@@ -516,27 +555,30 @@ SELECT r0.routine_name, r1.routine_name
 SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage
   WHERE routine_schema = 'temp_func_test'
   ORDER BY 1, 2;
- routine_name  | sequence_name 
----------------+---------------
- functest_is_5 | functest1
- functest_is_6 | functest1
-(2 rows)
+      routine_name      | sequence_name 
+------------------------+---------------
+ functest_is_5          | functest1
+ functest_is_6          | functest1
+ functest_temp_sequence | temp_seq
+(3 rows)
 
 SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage
   WHERE routine_schema = 'temp_func_test'
   ORDER BY 1, 2;
- routine_name  | table_name | column_name 
----------------+------------+-------------
- functest_is_7 | functest2  | a
-(1 row)
+    routine_name    | table_name | column_name 
+--------------------+------------+-------------
+ functest_is_7      | functest2  | a
+ functest_temp_view | temp_view  | val
+(2 rows)
 
 SELECT routine_name, table_name FROM information_schema.routine_table_usage
   WHERE routine_schema = 'temp_func_test'
   ORDER BY 1, 2;
- routine_name  | table_name 
----------------+------------
- functest_is_7 | functest2
-(1 row)
+    routine_name    | table_name 
+--------------------+------------
+ functest_is_7      | functest2
+ functest_temp_view | temp_view
+(2 rows)
 
 DROP FUNCTION functest_IS_4a CASCADE;
 NOTICE:  drop cascades to function functest_is_4b(integer)
@@ -790,7 +832,7 @@ CONTEXT:  SQL function "test1" during startup
 RESET check_function_bodies;
 -- Cleanup
 DROP SCHEMA temp_func_test CASCADE;
-NOTICE:  drop cascades to 38 other objects
+NOTICE:  drop cascades to 41 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()
@@ -812,6 +854,9 @@ drop cascades to function functest_s_3()
 drop cascades to function functest_s_3a()
 drop cascades to function functest_s_10(text,date)
 drop cascades to function functest_s_13()
+drop cascades to function functest_temp_view()
+drop cascades to function functest_temp_sequence()
+drop cascades to function functest_temp_domain()
 drop cascades to function functest_s_15(integer)
 drop cascades to function functest_b_2(bigint)
 drop cascades to function functest_srf0()
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 30241e22da..5cc94011e9 100644
--- a/src/test/regress/expected/rangefuncs.out
+++ b/src/test/regress/expected/rangefuncs.out
@@ -2178,6 +2178,8 @@ alter table users drop column todrop;
 create or replace function get_first_user() returns users as
 $$ SELECT * FROM users ORDER BY userid LIMIT 1; $$
 language sql stable;
+NOTICE:  function "get_first_user" will be effectively temporary
+DETAIL:  It depends on temporary type users.
 SELECT get_first_user();
   get_first_user   
 -------------------
@@ -2193,6 +2195,8 @@ SELECT * FROM get_first_user();
 create or replace function get_users() returns setof users as
 $$ SELECT * FROM users ORDER BY userid; $$
 language sql stable;
+NOTICE:  function "get_users" will be effectively temporary
+DETAIL:  It depends on temporary type users.
 SELECT get_users();
       get_users      
 ---------------------
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index d02c2ceab5..16012af6d1 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -1,6 +1,10 @@
 --
 -- Test INSERT/UPDATE/DELETE RETURNING
 --
+-- This script is full of poorly-chosen object names.
+-- Put them in a separate schema to avoid collisions with concurrent scripts.
+CREATE SCHEMA returning_test;
+SET search_path = returning_test, public;
 -- Simple cases
 CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
 INSERT INTO foo (f2,f3)
@@ -306,6 +310,8 @@ SELECT * FROM foo;
 -- Check use of a whole-row variable for an inlined set-returning function
 CREATE FUNCTION foo_f() RETURNS SETOF foo AS
   $$ SELECT * FROM foo OFFSET 0 $$ LANGUAGE sql STABLE;
+NOTICE:  function "foo_f" will be effectively temporary
+DETAIL:  It depends on temporary type foo.
 UPDATE foo SET f2 = foo_f.f2 FROM foo_f() WHERE foo_f.f1 = foo.f1
   RETURNING foo_f;
       foo_f      
@@ -930,8 +936,10 @@ BEGIN ATOMIC
               (SELECT count(*) FROM foo WHERE foo = o),
               (SELECT count(*) FROM foo WHERE foo = n);
 END;
+NOTICE:  function "foo_update" will be effectively temporary
+DETAIL:  It depends on temporary table foo.
 \sf foo_update
-CREATE OR REPLACE FUNCTION public.foo_update()
+CREATE OR REPLACE FUNCTION returning_test.foo_update()
  RETURNS void
  LANGUAGE sql
 BEGIN ATOMIC
@@ -985,4 +993,9 @@ BEGIN ATOMIC
             FROM foo foo_1
            WHERE (foo_1.* = n.*)) AS count;
 END
-DROP FUNCTION foo_update;
+-- Clean up
+RESET search_path;
+DROP SCHEMA returning_test CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to function returning_test.joinview_upd_trig_fn()
+drop cascades to function returning_test.foo_update()
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 677ad2ab9a..956bc2d02f 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -907,6 +907,8 @@ create temp table compos (f1 int, f2 text);
 create function fcompos1(v compos) returns void as $$
 insert into compos values (v);  -- fail
 $$ language sql;
+NOTICE:  function "fcompos1" will be effectively temporary
+DETAIL:  It depends on temporary type compos.
 ERROR:  column "f1" is of type integer but expression is of type compos
 LINE 2: insert into compos values (v);  -- fail
                                    ^
@@ -914,12 +916,18 @@ HINT:  You will need to rewrite or cast the expression.
 create function fcompos1(v compos) returns void as $$
 insert into compos values (v.*);
 $$ language sql;
+NOTICE:  function "fcompos1" will be effectively temporary
+DETAIL:  It depends on temporary type compos.
 create function fcompos2(v compos) returns void as $$
 select fcompos1(v);
 $$ language sql;
+NOTICE:  function "fcompos2" will be effectively temporary
+DETAIL:  It depends on temporary type compos.
 create function fcompos3(v compos) returns void as $$
 select fcompos1(fcompos3.v.*);
 $$ language sql;
+NOTICE:  function "fcompos3" will be effectively temporary
+DETAIL:  It depends on temporary type compos.
 select fcompos1(row(1,'one'));
  fcompos1 
 ----------
@@ -1012,6 +1020,8 @@ select last(f) from fullname f;
 
 create function longname(fullname) returns text language sql
 as $$select $1.first || ' ' || $1.last$$;
+NOTICE:  function "longname" will be effectively temporary
+DETAIL:  It depends on temporary type fullname.
 select f.longname from fullname f;
  longname 
 ----------
diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql
index 3d5f2a9209..7c6361d606 100644
--- a/src/test/regress/sql/create_function_sql.sql
+++ b/src/test/regress/sql/create_function_sql.sql
@@ -199,6 +199,45 @@ 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 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;
+
+-- 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 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 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..f1c85a9731 100644
--- a/src/test/regress/sql/returning.sql
+++ b/src/test/regress/sql/returning.sql
@@ -2,6 +2,11 @@
 -- Test INSERT/UPDATE/DELETE RETURNING
 --
 
+-- This script is full of poorly-chosen object names.
+-- Put them in a separate schema to avoid collisions with concurrent scripts.
+CREATE SCHEMA returning_test;
+SET search_path = returning_test, public;
+
 -- Simple cases
 
 CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
@@ -407,4 +412,7 @@ BEGIN ATOMIC
 END;
 
 \sf foo_update
-DROP FUNCTION foo_update;
+
+-- Clean up
+RESET search_path;
+DROP SCHEMA returning_test CASCADE;
-- 
2.43.0

From 8f11537136a4821090b1e96695eafe6f4870dcbd Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 21 Nov 2025 16:05:06 -0500
Subject: [PATCH v8 1/2] Refactor dependency recording to enable dependency
 collection.

Add new function collectDependenciesOfExpr() that collects expression
dependencies into a caller-supplied ObjectAddresses structure, without
immediately recording them.

This enables more flexible dependency handling patterns where callers
need to examine, filter, or modify dependencies before recording them.

The caller is responsible for ensuring that the results are
de-duplicated before being put into pg_depend.  (External callers
will not need to do that explicitly, since they must go through
record_object_address_dependencies() which will take care of it.)
This design avoids redundant de-duplication work when collecting
dependencies from multiple sources.

The existing recordDependencyOnExpr() function is reimplemented using
the new collection function, maintaining backward compatibility.

Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/19cf6ae1-04cd-422c-a760-d7e75fe6cba9@uni-muenster.de
---
 src/backend/catalog/dependency.c | 54 +++++++++++++++++++++++++-------
 src/include/catalog/dependency.h |  3 ++
 2 files changed, 46 insertions(+), 11 deletions(-)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 7dded634eb..61bdf4a577 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1554,25 +1554,57 @@ recordDependencyOnExpr(const ObjectAddress *depender,
 					   Node *expr, List *rtable,
 					   DependencyType behavior)
 {
-	find_expr_references_context context;
-
-	context.addrs = new_object_addresses();
+	ObjectAddresses *addrs;
 
-	/* Set up interpretation for Vars at varlevelsup = 0 */
-	context.rtables = list_make1(rtable);
+	addrs = new_object_addresses();
 
-	/* Scan the expression tree for referenceable objects */
-	find_expr_references_walker(expr, &context);
+	/* Collect all dependencies from the expression */
+	collectDependenciesOfExpr(addrs, expr, rtable);
 
-	/* Remove any duplicates */
-	eliminate_duplicate_dependencies(context.addrs);
+	/* Remove duplicates */
+	eliminate_duplicate_dependencies(addrs);
 
 	/* And record 'em */
 	recordMultipleDependencies(depender,
-							   context.addrs->refs, context.addrs->numrefs,
+							   addrs->refs, addrs->numrefs,
 							   behavior);
 
-	free_object_addresses(context.addrs);
+	free_object_addresses(addrs);
+}
+
+/*
+ * collectDependenciesOfExpr - collect expression dependencies
+ *
+ * This function analyzes an expression or query in node-tree form to
+ * find all the objects it refers to (tables, columns, operators,
+ * functions, etc.) and adds them to the provided ObjectAddresses
+ * structure. Unlike recordDependencyOnExpr, this function does not
+ * immediately record the dependencies, allowing the caller to add to,
+ * filter, or modify the collected dependencies before recording them.
+ *
+ * rtable is the rangetable to be used to interpret Vars with varlevelsup=0.
+ * It can be NIL if no such variables are expected.
+ *
+ * Note: the returned list may well contain duplicates.  The caller should
+ * de-duplicate before recording the dependencies.  Within this file, callers
+ * must call eliminate_duplicate_dependencies().  External callers typically
+ * go through record_object_address_dependencies() which will see to that.
+ * This choice allows collecting dependencies from multiple sources without
+ * redundant de-duplication work.
+ */
+void
+collectDependenciesOfExpr(ObjectAddresses *addrs,
+						  Node *expr, List *rtable)
+{
+	find_expr_references_context context;
+
+	context.addrs = addrs;
+
+	/* Set up interpretation for Vars at varlevelsup = 0 */
+	context.rtables = list_make1(rtable);
+
+	/* Scan the expression tree for referenceable objects */
+	find_expr_references_walker(expr, &context);
 }
 
 /*
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 0ea7ccf524..2712befc8e 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -114,6 +114,9 @@ extern void recordDependencyOnExpr(const ObjectAddress *depender,
 								   Node *expr, List *rtable,
 								   DependencyType behavior);
 
+extern void collectDependenciesOfExpr(ObjectAddresses *addrs,
+									  Node *expr, List *rtable);
+
 extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 											Node *expr, Oid relId,
 											DependencyType behavior,
-- 
2.43.0

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index fba5b2e0d09..8e70a85a3f7 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -2479,6 +2479,31 @@ find_temp_object(const ObjectAddresses *addrs, bool local_temp_okay,
 	return false;
 }
 
+/*
+ * query_uses_temp_object - convenience wrapper for find_temp_object
+ *
+ * If the Query includes any use of a temporary object, fill *temp_object
+ * with the address of one such object and return true.
+ */
+bool
+query_uses_temp_object(Query *query, ObjectAddress *temp_object)
+{
+	bool		result;
+	ObjectAddresses *addrs;
+
+	addrs = new_object_addresses();
+
+	/* Collect all dependencies from the Query */
+	collectDependenciesOfExpr(addrs, (Node *) query, NIL);
+
+	/* Look for one that is temp */
+	result = find_temp_object(addrs, false, temp_object);
+
+	free_object_addresses(addrs);
+
+	return result;
+}
+
 /*
  * Given an array of dependency references, eliminate any duplicates.
  */
diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c
index 6f0301555e0..4cc2af7b5ec 100644
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -22,7 +22,6 @@
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
 #include "parser/analyze.h"
-#include "parser/parse_relation.h"
 #include "rewrite/rewriteDefine.h"
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteSupport.h"
@@ -362,6 +361,7 @@ DefineView(ViewStmt *stmt, const char *queryString,
 	ListCell   *cell;
 	bool		check_option;
 	ObjectAddress address;
+	ObjectAddress temp_object;
 
 	/*
 	 * Run parse analysis to convert the raw parse tree to a Query.  Note this
@@ -484,12 +484,14 @@ DefineView(ViewStmt *stmt, const char *queryString,
 	 */
 	view = copyObject(stmt->view);	/* don't corrupt original command */
 	if (view->relpersistence == RELPERSISTENCE_PERMANENT
-		&& isQueryUsingTempRelation(viewParse))
+		&& query_uses_temp_object(viewParse, &temp_object))
 	{
 		view->relpersistence = RELPERSISTENCE_TEMP;
 		ereport(NOTICE,
 				(errmsg("view \"%s\" will be a temporary view",
-						view->relname)));
+						view->relname),
+				 errdetail("It depends on temporary %s.",
+						   getObjectDescription(&temp_object, false))));
 	}
 
 	/*
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 3b392b084ad..7843a0c857e 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -25,6 +25,7 @@
 #include "postgres.h"
 
 #include "access/sysattr.h"
+#include "catalog/dependency.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -3129,6 +3130,8 @@ transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt)
 	/* additional work needed for CREATE MATERIALIZED VIEW */
 	if (stmt->objtype == OBJECT_MATVIEW)
 	{
+		ObjectAddress temp_object;
+
 		/*
 		 * Prohibit a data-modifying CTE in the query used to create a
 		 * materialized view. It's not sufficiently clear what the user would
@@ -3144,10 +3147,12 @@ transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt)
 		 * creation query. It would be hard to refresh data or incrementally
 		 * maintain it if a source disappeared.
 		 */
-		if (isQueryUsingTempRelation(query))
+		if (query_uses_temp_object(query, &temp_object))
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 errmsg("materialized views must not use temporary tables or views")));
+					 errmsg("materialized views must not use temporary objects"),
+					 errdetail("This view depends on temporary %s.",
+							   getObjectDescription(&temp_object, false))));
 
 		/*
 		 * A materialized view would either need to save parameters for use in
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 3c80bf1b9ce..d544a69fc80 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -18,11 +18,9 @@
 
 #include "access/htup_details.h"
 #include "access/relation.h"
-#include "access/sysattr.h"
 #include "access/table.h"
 #include "catalog/heap.h"
 #include "catalog/namespace.h"
-#include "catalog/pg_type.h"
 #include "funcapi.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
@@ -33,7 +31,6 @@
 #include "storage/lmgr.h"
 #include "utils/builtins.h"
 #include "utils/lsyscache.h"
-#include "utils/rel.h"
 #include "utils/syscache.h"
 #include "utils/varlena.h"
 
@@ -103,7 +100,6 @@ static void expandTupleDesc(TupleDesc tupdesc, Alias *eref,
 static int	specialAttNum(const char *attname);
 static bool rte_visible_if_lateral(ParseState *pstate, RangeTblEntry *rte);
 static bool rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte);
-static bool isQueryUsingTempRelation_walker(Node *node, void *context);
 
 
 /*
@@ -3922,53 +3918,6 @@ rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte)
 }
 
 
-/*
- * Examine a fully-parsed query, and return true iff any relation underlying
- * the query is a temporary relation (table, view, or materialized view).
- */
-bool
-isQueryUsingTempRelation(Query *query)
-{
-	return isQueryUsingTempRelation_walker((Node *) query, NULL);
-}
-
-static bool
-isQueryUsingTempRelation_walker(Node *node, void *context)
-{
-	if (node == NULL)
-		return false;
-
-	if (IsA(node, Query))
-	{
-		Query	   *query = (Query *) node;
-		ListCell   *rtable;
-
-		foreach(rtable, query->rtable)
-		{
-			RangeTblEntry *rte = lfirst(rtable);
-
-			if (rte->rtekind == RTE_RELATION)
-			{
-				Relation	rel = table_open(rte->relid, AccessShareLock);
-				char		relpersistence = rel->rd_rel->relpersistence;
-
-				table_close(rel, AccessShareLock);
-				if (relpersistence == RELPERSISTENCE_TEMP)
-					return true;
-			}
-		}
-
-		return query_tree_walker(query,
-								 isQueryUsingTempRelation_walker,
-								 context,
-								 QTW_IGNORE_JOINALIASES);
-	}
-
-	return expression_tree_walker(node,
-								  isQueryUsingTempRelation_walker,
-								  context);
-}
-
 /*
  * addRTEPermissionInfo
  *		Creates RTEPermissionInfo for a given RTE and adds it into the
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 81309b8ce32..06a8761e3fe 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -127,6 +127,8 @@ extern bool find_temp_object(const ObjectAddresses *addrs,
 							 bool local_temp_okay,
 							 ObjectAddress *foundobj);
 
+extern bool query_uses_temp_object(Query *query, ObjectAddress *temp_object);
+
 extern ObjectAddresses *new_object_addresses(void);
 
 extern void add_exact_object_address(const ObjectAddress *object,
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index d59599cf242..aceb43f995a 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -127,6 +127,5 @@ extern int	attnameAttNum(Relation rd, const char *attname, bool sysColOK);
 extern const NameData *attnumAttName(Relation rd, int attid);
 extern Oid	attnumTypeId(Relation rd, int attid);
 extern Oid	attnumCollationId(Relation rd, int attid);
-extern bool isQueryUsingTempRelation(Query *query);
 
 #endif							/* PARSE_RELATION_H */
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index 49dd13c345c..6891f27b893 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -114,6 +114,7 @@ CREATE VIEW v1 AS SELECT * FROM base_table;
 -- should be created in temp object schema
 CREATE VIEW v1_temp AS SELECT * FROM temp_table;
 NOTICE:  view "v1_temp" will be a temporary view
+DETAIL:  It depends on temporary table temp_table.
 -- should be created in temp object schema
 CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
 -- should be created in temp_views schema
@@ -121,6 +122,7 @@ CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
 -- should fail
 CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table;
 NOTICE:  view "v3_temp" will be a temporary view
+DETAIL:  It depends on temporary table temp_table.
 ERROR:  cannot create temporary relation in non-temporary schema
 -- should fail
 CREATE SCHEMA test_view_schema
@@ -139,12 +141,14 @@ CREATE VIEW v4_temp AS
     FROM base_table t1, temp_table t2
     WHERE t1.id = t2.id;
 NOTICE:  view "v4_temp" will be a temporary view
+DETAIL:  It depends on temporary table temp_table.
 -- should be temp
 CREATE VIEW v5_temp AS
     SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a
     FROM base_table t1, base_table2 t2, temp_table t3
     WHERE t1.id = t2.id and t2.id = t3.id;
 NOTICE:  view "v5_temp" will be a temporary view
+DETAIL:  It depends on temporary table temp_table.
 -- subqueries
 CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2);
 CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2;
@@ -153,25 +157,33 @@ CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_
 CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
 CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table);
 NOTICE:  view "v6_temp" will be a temporary view
+DETAIL:  It depends on temporary table temp_table.
 CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2;
 NOTICE:  view "v7_temp" will be a temporary view
+DETAIL:  It depends on temporary table temp_table.
 CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
 NOTICE:  view "v8_temp" will be a temporary view
+DETAIL:  It depends on temporary table temp_table.
 CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
 NOTICE:  view "v9_temp" will be a temporary view
+DETAIL:  It depends on temporary table temp_table.
 -- a view should also be temporary if it references a temporary view
 CREATE VIEW v10_temp AS SELECT * FROM v7_temp;
 NOTICE:  view "v10_temp" will be a temporary view
+DETAIL:  It depends on temporary view v7_temp.
 CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2;
 NOTICE:  view "v11_temp" will be a temporary view
+DETAIL:  It depends on temporary view v10_temp.
 CREATE VIEW v12_temp AS SELECT true FROM v11_temp;
 NOTICE:  view "v12_temp" will be a temporary view
+DETAIL:  It depends on temporary view v11_temp.
 -- a view should also be temporary if it references a temporary sequence
 CREATE SEQUENCE seq1;
 CREATE TEMPORARY SEQUENCE seq1_temp;
 CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1;
 CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp;
 NOTICE:  view "v13_temp" will be a temporary view
+DETAIL:  It depends on temporary sequence seq1_temp.
 SELECT relname FROM pg_class
     WHERE relname LIKE 'v_'
     AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test')
@@ -217,15 +229,19 @@ CREATE TEMP TABLE tt (num2 int, value text);
 CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2;
 CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt;
 NOTICE:  view "temporal1" will be a temporary view
+DETAIL:  It depends on temporary table tt.
 CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2;
 CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2;
 NOTICE:  view "temporal2" will be a temporary view
+DETAIL:  It depends on temporary table tt.
 CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
 CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
 NOTICE:  view "temporal3" will be a temporary view
+DETAIL:  It depends on temporary table tt.
 CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
 CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx';
 NOTICE:  view "temporal4" will be a temporary view
+DETAIL:  It depends on temporary table tt.
 SELECT relname FROM pg_class
     WHERE relname LIKE 'nontemp%'
     AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2')
@@ -272,6 +288,7 @@ BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
 AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f)
 AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
 NOTICE:  view "mytempview" will be a temporary view
+DETAIL:  It depends on temporary table tmptbl.
 SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
 And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
  count 
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 398cf6965e0..39d35a195bc 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -602,6 +602,7 @@ explain (costs off)
 CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c)
   from gstest2 group by rollup ((a,b,c),(c,d));
 NOTICE:  view "gstest_view" will be a temporary view
+DETAIL:  It depends on temporary table gstest2.
 select pg_get_viewdef('gstest_view'::regclass, true);
             pg_get_viewdef             
 ---------------------------------------
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 9e2f53726f5..7a04d3a7a9f 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -5497,6 +5497,7 @@ FROM planets
 WINDOW w AS (ORDER BY name)
 ;
 NOTICE:  view "planets_view" will be a temporary view
+DETAIL:  It depends on temporary table planets.
 SELECT pg_get_viewdef('planets_view');
                   pg_get_viewdef                  
 --------------------------------------------------