Jim Jones <[email protected]> writes:
> PFA v6 with these changes.
I went through this and made one big change and some cosmetic ones.
The big change is that it makes zero sense to me to apply this
restriction only to new-style SQL functions. If it's bad for an
allegedly non-temporary function to disappear at session exit,
surely it's not less bad just because it's old-style SQL or not
SQL-language at all. New-style SQL has a somewhat larger attack
surface because dependencies within the function body matter,
but the problem exists for all function languages when it comes
to argument types, result types, or default-argument expressions.
So I changed the code to make the check all the time, and was
rather depressed by how much that broke:
1. We need a couple more CommandCounterIncrement calls to handle
cases where a function is created on a just-created type.
(Without this, get_object_namespace() falls over when it tries
to look up the type.)
2. There are several more regression tests depending on the old
semantics than what you found, and even one test specifically
checking that the implicitly-temp function will go away.
Point 2 scares me quite a bit; if we've depended on this behavior
in our own tests, I wonder if there aren't plenty of end users
depending on it too. We could be in for a lot of push-back.
Although I've left the patch throwing an error (with new wording)
for now, I wonder if it'd be better to reduce the error to a NOTICE,
perhaps worded like "function f will be effectively temporary due to
its dependence on <object>". This would make the behavior more
similar to what we've done for decades with implicitly-temp views:
regression=# create temp table foo (f1 int);
CREATE TABLE
regression=# create view voo as select * from foo;
NOTICE: view "voo" will be a temporary view
CREATE VIEW
Some people might find such a notice annoying, but it's better than
failing. (I wonder if it'd be sane to make the notice come out
only if check_function_bodies is true?)
I did not touch the test cases you added to create_function_sql.sql,
but I find them quite excessive now that the patch doesn't have
any specific dependencies on object kinds. (Also, if we go with a
NOTICE and undo the changes made here to existing tests, then those
test cases would produce the NOTICE and arguably be providing
nearly enough test coverage already.)
Thoughts?
regards, tom lane
From 6c33204112d3790cf438d6408dc4dd01ac0d313b Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
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 <[email protected]>
Reviewed-by: Tom Lane <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
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 <[email protected]>
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 <[email protected]>
Reviewed-by: Tom Lane <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
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
