From 572c40ba94ef6350c8dd51539ac7d932c1d1a12a Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 23 Nov 2025 15:02:55 -0500
Subject: Issue a NOTICE if a created function depends on any 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 does,
it will silently go away at end of session, due to the automatic
DROP ... CASCADE on the session's temporary objects.  People have
complained that that's surprising; however, we can't really forbid
it because other people (including our own regression tests) rely
on being able to do it.  Let's compromise by emitting a NOTICE
at CREATE FUNCTION time.  This is somewhat comparable to our
ancient practice of emitting a NOTICE when forcing a view to
become temp because it depends on temp tables.

Along the way, refactor recordDependencyOnExpr() so that the
dependencies of an expression can be combined with other
dependencies, instead of being emitted separately and perhaps
duplicatively.

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/test/regress/sql/create_function_sql.sql | 11 +++++++++++
 1 file changed, 11 insertions(+)

(limited to 'src/test/regress/sql/create_function_sql.sql')

diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql
index 3d5f2a92093..4543273f93a 100644
--- a/src/test/regress/sql/create_function_sql.sql
+++ b/src/test/regress/sql/create_function_sql.sql
@@ -241,6 +241,17 @@ SELECT functest_S_14();
 
 DROP TABLE functest3 CASCADE;
 
+-- Check reporting of temporary-object dependencies within SQL-standard body
+-- (tests elsewhere already cover dependencies on arg and result types)
+CREATE TEMP SEQUENCE mytempseq;
+
+CREATE FUNCTION functest_tempseq() RETURNS int
+    RETURN nextval('mytempseq');
+
+-- This discards mytempseq and therefore functest_tempseq().  If it fails to,
+-- the function will appear in the information_schema tests below.
+DISCARD TEMP;
+
 
 -- information_schema tests
 
-- 
cgit v1.2.3

