2 files changed, 91 insertions, 0 deletions
diff --git a/src/test/regress/expected/temp.out b/src/test/regress/expected/temp.out
index 650f5f704ed..2208fe2d052 100644
--- a/src/test/regress/expected/temp.out
+++ b/src/test/regress/expected/temp.out
@@ -82,3 +82,61 @@ ERROR:  relation "temptest" does not exist
 -- ON COMMIT is only allowed for TEMP
 CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
 ERROR:  ON COMMIT can only be used on temporary tables
+-- Test manipulation of temp schema's placement in search path
+create table public.whereami (f1 text);
+insert into public.whereami values ('public');
+create temp table whereami (f1 text);
+insert into whereami values ('temp');
+create function public.whoami() returns text
+  as $$select 'public'::text$$ language sql;
+create function pg_temp.whoami() returns text
+  as $$select 'temp'::text$$ language sql;
+-- default should have pg_temp implicitly first, but only for tables
+select * from whereami;
+  f1  
+------
+ temp
+(1 row)
+
+select whoami();
+ whoami 
+--------
+ public
+(1 row)
+
+-- can list temp first explicitly, but it still doesn't affect functions
+set search_path = pg_temp, public;
+select * from whereami;
+  f1  
+------
+ temp
+(1 row)
+
+select whoami();
+ whoami 
+--------
+ public
+(1 row)
+
+-- or put it last for security
+set search_path = public, pg_temp;
+select * from whereami;
+   f1   
+--------
+ public
+(1 row)
+
+select whoami();
+ whoami 
+--------
+ public
+(1 row)
+
+-- you can invoke a temp function explicitly, though
+select pg_temp.whoami();
+ whoami 
+--------
+ temp
+(1 row)
+
+drop table public.whereami;
diff --git a/src/test/regress/sql/temp.sql b/src/test/regress/sql/temp.sql
index 397d00bfd94..521b39d9e47 100644
--- a/src/test/regress/sql/temp.sql
+++ b/src/test/regress/sql/temp.sql
@@ -83,3 +83,36 @@ SELECT * FROM temptest;
 -- ON COMMIT is only allowed for TEMP
 
 CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
+
+-- Test manipulation of temp schema's placement in search path
+
+create table public.whereami (f1 text);
+insert into public.whereami values ('public');
+
+create temp table whereami (f1 text);
+insert into whereami values ('temp');
+
+create function public.whoami() returns text
+  as $$select 'public'::text$$ language sql;
+
+create function pg_temp.whoami() returns text
+  as $$select 'temp'::text$$ language sql;
+
+-- default should have pg_temp implicitly first, but only for tables
+select * from whereami;
+select whoami();
+
+-- can list temp first explicitly, but it still doesn't affect functions
+set search_path = pg_temp, public;
+select * from whereami;
+select whoami();
+
+-- or put it last for security
+set search_path = public, pg_temp;
+select * from whereami;
+select whoami();
+
+-- you can invoke a temp function explicitly, though
+select pg_temp.whoami();
+
+drop table public.whereami;