0001-refactor-functions-2.patch

diff --git a/src/test/regress/expected/alter_generic.out b/src/test/regress/expected/alter_generic.out
index 505eb7ede5..54d3fe5764 100644
--- a/src/test/regress/expected/alter_generic.out
+++ b/src/test/regress/expected/alter_generic.out
@@ -1,6 +1,14 @@
 --
 -- Test for ALTER some_object {RENAME TO, OWNER TO, SET SCHEMA}
 --
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
+CREATE FUNCTION test_opclass_options_func(internal)
+    RETURNS void
+    AS :'regresslib', 'test_opclass_options_func'
+    LANGUAGE C;
 -- Clean up in case a prior regression run failed
 SET client_min_messages TO 'warning';
 DROP ROLE IF EXISTS regress_alter_generic_user1;
diff --git a/src/test/regress/expected/conversion.out b/src/test/regress/expected/conversion.out
index f8a64f616e..442e7aff2b 100644
--- a/src/test/regress/expected/conversion.out
+++ b/src/test/regress/expected/conversion.out
@@ -1,6 +1,13 @@
 --
 -- create user defined conversion
 --
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
+CREATE FUNCTION test_enc_conversion(bytea, name, name, bool, validlen OUT int, result OUT bytea)
+    AS :'regresslib', 'test_enc_conversion'
+    LANGUAGE C STRICT;
 CREATE USER regress_conversion_user WITH NOCREATEDB NOCREATEROLE;
 SET SESSION AUTHORIZATION regress_conversion_user;
 CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
diff --git a/src/test/regress/expected/create_function_0.out b/src/test/regress/expected/create_function_0.out
index 6e96d6c5d6..ed674af971 100644
--- a/src/test/regress/expected/create_function_0.out
+++ b/src/test/regress/expected/create_function_0.out
@@ -1,88 +1,20 @@
 --
 -- CREATE_FUNCTION_0
 --
+-- This script used to create C functions for other scripts to use.
+-- But to get rid of the ordering dependencies that caused, such
+-- functions are now made either in test_setup.sql or in the specific
+-- test script that needs them.  All that remains here is error cases.
 -- directory path and dlsuffix are passed to us in environment variables
 \getenv libdir PG_LIBDIR
 \getenv dlsuffix PG_DLSUFFIX
-\set autoinclib :libdir '/autoinc' :dlsuffix
-\set refintlib :libdir '/refint' :dlsuffix
 \set regresslib :libdir '/regress' :dlsuffix
--- Create a bunch of C functions that will be used by later tests:
-CREATE FUNCTION check_primary_key ()
-	RETURNS trigger
-	AS :'refintlib'
-	LANGUAGE C;
-CREATE FUNCTION check_foreign_key ()
-	RETURNS trigger
-	AS :'refintlib'
-	LANGUAGE C;
-CREATE FUNCTION autoinc ()
-	RETURNS trigger
-	AS :'autoinclib'
-	LANGUAGE C;
-CREATE FUNCTION trigger_return_old ()
-        RETURNS trigger
-        AS :'regresslib'
-        LANGUAGE C;
-CREATE FUNCTION ttdummy ()
-        RETURNS trigger
-        AS :'regresslib'
-        LANGUAGE C;
-CREATE FUNCTION set_ttdummy (int4)
-        RETURNS int4
-        AS :'regresslib'
-        LANGUAGE C STRICT;
-CREATE FUNCTION make_tuple_indirect (record)
-        RETURNS record
-        AS :'regresslib'
-        LANGUAGE C STRICT;
-CREATE FUNCTION test_atomic_ops()
-    RETURNS bool
-    AS :'regresslib'
-    LANGUAGE C;
-CREATE FUNCTION test_fdw_handler()
-    RETURNS fdw_handler
-    AS :'regresslib', 'test_fdw_handler'
-    LANGUAGE C;
-CREATE FUNCTION test_support_func(internal)
-    RETURNS internal
-    AS :'regresslib', 'test_support_func'
-    LANGUAGE C STRICT;
-CREATE FUNCTION test_opclass_options_func(internal)
-    RETURNS void
-    AS :'regresslib', 'test_opclass_options_func'
-    LANGUAGE C;
-CREATE FUNCTION test_enc_conversion(bytea, name, name, bool, validlen OUT int, result OUT bytea)
-    AS :'regresslib', 'test_enc_conversion'
-    LANGUAGE C STRICT;
-CREATE FUNCTION binary_coercible(oid, oid)
-    RETURNS bool
-    AS :'regresslib', 'binary_coercible'
-    LANGUAGE C STRICT STABLE PARALLEL SAFE;
+--
+-- Check LOAD command.  (The alternative of implicitly loading the library
+-- is checked in many other test scripts.)
+--
+LOAD :'regresslib';
 -- Things that shouldn't work:
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'SELECT ''not an integer'';';
-ERROR:  return type mismatch in function declared to return integer
-DETAIL:  Actual return type is text.
-CONTEXT:  SQL function "test1"
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'not even SQL';
-ERROR:  syntax error at or near "not"
-LINE 2:     AS 'not even SQL';
-                ^
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'SELECT 1, 2, 3;';
-ERROR:  return type mismatch in function declared to return integer
-DETAIL:  Final statement must return exactly one column.
-CONTEXT:  SQL function "test1"
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'SELECT $2;';
-ERROR:  there is no parameter $2
-LINE 2:     AS 'SELECT $2;';
-                       ^
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'a', 'b';
-ERROR:  only one AS item needed for language "sql"
 CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C
     AS 'nosuchfile';
 ERROR:  could not access file "nosuchfile": No such file or directory
diff --git a/src/test/regress/expected/create_function_1.out b/src/test/regress/expected/create_function_1.out
deleted file mode 100644
index 6141b7060b..0000000000
--- a/src/test/regress/expected/create_function_1.out
+++ /dev/null
@@ -1,34 +0,0 @@
---
--- CREATE_FUNCTION_1
---
--- directory path and dlsuffix are passed to us in environment variables
-\getenv libdir PG_LIBDIR
-\getenv dlsuffix PG_DLSUFFIX
-\set regresslib :libdir '/regress' :dlsuffix
--- Create C functions needed by create_type.sql
-CREATE FUNCTION widget_in(cstring)
-   RETURNS widget
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
-NOTICE:  type "widget" is not yet defined
-DETAIL:  Creating a shell type definition.
-CREATE FUNCTION widget_out(widget)
-   RETURNS cstring
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
-NOTICE:  argument type widget is only a shell
-CREATE FUNCTION int44in(cstring)
-   RETURNS city_budget
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
-NOTICE:  type "city_budget" is not yet defined
-DETAIL:  Creating a shell type definition.
-CREATE FUNCTION int44out(city_budget)
-   RETURNS cstring
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
-NOTICE:  argument type city_budget is only a shell
-CREATE FUNCTION test_canonicalize_path(text)
-   RETURNS text
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
diff --git a/src/test/regress/expected/create_function_2.out b/src/test/regress/expected/create_function_2.out
deleted file mode 100644
index a366294add..0000000000
--- a/src/test/regress/expected/create_function_2.out
+++ /dev/null
@@ -1,73 +0,0 @@
---
--- CREATE_FUNCTION_2
---
--- directory path and dlsuffix are passed to us in environment variables
-\getenv libdir PG_LIBDIR
-\getenv dlsuffix PG_DLSUFFIX
-\set regresslib :libdir '/regress' :dlsuffix
-CREATE FUNCTION hobbies(person)
-   RETURNS setof hobbies_r
-   AS 'select * from hobbies_r where person = $1.name'
-   LANGUAGE SQL;
-CREATE FUNCTION hobby_construct(text, text)
-   RETURNS hobbies_r
-   AS 'select $1 as name, $2 as hobby'
-   LANGUAGE SQL;
-CREATE FUNCTION hobby_construct_named(name text, hobby text)
-   RETURNS hobbies_r
-   AS 'select name, hobby'
-   LANGUAGE SQL;
-CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
-   RETURNS hobbies_r.person%TYPE
-   AS 'select person from hobbies_r where name = $1'
-   LANGUAGE SQL;
-NOTICE:  type reference hobbies_r.name%TYPE converted to text
-NOTICE:  type reference hobbies_r.person%TYPE converted to text
-CREATE FUNCTION equipment(hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where hobby = $1.name'
-   LANGUAGE SQL;
-CREATE FUNCTION equipment_named(hobby hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where equipment_r.hobby = equipment_named.hobby.name'
-   LANGUAGE SQL;
-CREATE FUNCTION equipment_named_ambiguous_1a(hobby hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_1a.hobby.name'
-   LANGUAGE SQL;
-CREATE FUNCTION equipment_named_ambiguous_1b(hobby hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where equipment_r.hobby = hobby.name'
-   LANGUAGE SQL;
-CREATE FUNCTION equipment_named_ambiguous_1c(hobby hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where hobby = hobby.name'
-   LANGUAGE SQL;
-CREATE FUNCTION equipment_named_ambiguous_2a(hobby text)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_2a.hobby'
-   LANGUAGE SQL;
-CREATE FUNCTION equipment_named_ambiguous_2b(hobby text)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where equipment_r.hobby = hobby'
-   LANGUAGE SQL;
-CREATE FUNCTION pt_in_widget(point, widget)
-   RETURNS bool
-   AS :'regresslib'
-   LANGUAGE C STRICT;
-CREATE FUNCTION overpaid(emp)
-   RETURNS bool
-   AS :'regresslib'
-   LANGUAGE C STRICT;
-CREATE FUNCTION interpt_pp(path, path)
-   RETURNS point
-   AS :'regresslib'
-   LANGUAGE C STRICT;
-CREATE FUNCTION reverse_name(name)
-   RETURNS name
-   AS :'regresslib'
-   LANGUAGE C STRICT;
---
--- Function dynamic loading
---
-LOAD :'regresslib';
diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out
index e0c4bee893..1e2dfd87d3 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -666,6 +666,30 @@ SELECT * FROM voidtest5(3);
 -----------
 (0 rows)
 
+-- Things that shouldn't work:
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'SELECT ''not an integer'';';
+ERROR:  return type mismatch in function declared to return integer
+DETAIL:  Actual return type is text.
+CONTEXT:  SQL function "test1"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'not even SQL';
+ERROR:  syntax error at or near "not"
+LINE 2:     AS 'not even SQL';
+                ^
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'SELECT 1, 2, 3;';
+ERROR:  return type mismatch in function declared to return integer
+DETAIL:  Final statement must return exactly one column.
+CONTEXT:  SQL function "test1"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'SELECT $2;';
+ERROR:  there is no parameter $2
+LINE 2:     AS 'SELECT $2;';
+                       ^
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'a', 'b';
+ERROR:  only one AS item needed for language "sql"
 -- Cleanup
 DROP SCHEMA temp_func_test CASCADE;
 NOTICE:  drop cascades to 29 other objects
diff --git a/src/test/regress/expected/create_operator.out b/src/test/regress/expected/create_operator.out
index 5303277591..f71b601f2d 100644
--- a/src/test/regress/expected/create_operator.out
+++ b/src/test/regress/expected/create_operator.out
@@ -7,13 +7,6 @@ CREATE OPERATOR ## (
    function = path_inter,
    commutator = ##
 );
-CREATE OPERATOR <% (
-   leftarg = point,
-   rightarg = widget,
-   procedure = pt_in_widget,
-   commutator = >% ,
-   negator = >=%
-);
 CREATE OPERATOR @#@ (
    rightarg = int8,		-- prefix
    procedure = factorial
@@ -25,11 +18,10 @@ CREATE OPERATOR #%# (
 ERROR:  operator right argument type must be specified
 DETAIL:  Postfix operators are not supported.
 -- Test operator created above
-SELECT point '(1,2)' <% widget '(0,0,3)' AS t,
-       point '(1,2)' <% widget '(0,0,1)' AS f;
- t | f 
----+---
- t | f
+SELECT @#@ 24;
+         ?column?         
+--------------------------
+ 620448401733239439360000
 (1 row)
 
 -- Test comments
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index 14394cc95c..8e105ad8f4 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -1,11 +1,36 @@
 --
 -- CREATE_TYPE
 --
+-- directory path and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
 --
--- Note: widget_in/out were created in create_function_1, without any
--- prior shell-type creation.  These commands therefore complete a test
--- of the "old style" approach of making the functions first.
+-- Test the "old style" approach of making the I/O functions first,
+-- with no explicit shell type creation.
 --
+CREATE FUNCTION widget_in(cstring)
+   RETURNS widget
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
+NOTICE:  type "widget" is not yet defined
+DETAIL:  Creating a shell type definition.
+CREATE FUNCTION widget_out(widget)
+   RETURNS cstring
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
+NOTICE:  argument type widget is only a shell
+CREATE FUNCTION int44in(cstring)
+   RETURNS city_budget
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
+NOTICE:  type "city_budget" is not yet defined
+DETAIL:  Creating a shell type definition.
+CREATE FUNCTION int44out(city_budget)
+   RETURNS cstring
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
+NOTICE:  argument type city_budget is only a shell
 CREATE TYPE widget (
    internallength = 24,
    input = widget_in,
@@ -224,6 +249,25 @@ select format_type('bpchar'::regtype, -1);
  bpchar
 (1 row)
 
+-- Test creation of an operator over a user-defined type
+CREATE FUNCTION pt_in_widget(point, widget)
+   RETURNS bool
+   AS :'regresslib'
+   LANGUAGE C STRICT;
+CREATE OPERATOR <% (
+   leftarg = point,
+   rightarg = widget,
+   procedure = pt_in_widget,
+   commutator = >% ,
+   negator = >=%
+);
+SELECT point '(1,2)' <% widget '(0,0,3)' AS t,
+       point '(1,2)' <% widget '(0,0,1)' AS f;
+ t | f 
+---+---
+ t | f
+(1 row)
+
 --
 -- Test CREATE/ALTER TYPE using a type that's compatible with varchar,
 -- so we can re-use those support functions
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index 509e930fc7..041e858065 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -3,15 +3,24 @@
 -- Virtual class definitions
 --	(this also tests the query rewrite system)
 --
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv abs_srcdir PG_ABS_SRCDIR
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
+CREATE FUNCTION interpt_pp(path, path)
+    RETURNS point
+    AS :'regresslib'
+    LANGUAGE C STRICT;
 CREATE VIEW street AS
    SELECT r.name, r.thepath, c.cname AS cname
    FROM ONLY road r, real_city c
-   WHERE c.outline ## r.thepath;
+   WHERE c.outline ?# r.thepath;
 CREATE VIEW iexit AS
    SELECT ih.name, ih.thepath,
 	interpt_pp(ih.thepath, r.thepath) AS exit
    FROM ihighway ih, ramp r
-   WHERE ih.thepath ## r.thepath;
+   WHERE ih.thepath ?# r.thepath;
 CREATE VIEW toyemp AS
    SELECT name, age, location, 12*salary AS annualsal
    FROM emp;
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index a6a68d1fa2..5bf03680d2 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1,6 +1,14 @@
 --
 -- Test foreign-data wrapper and server management.
 --
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
+CREATE FUNCTION test_fdw_handler()
+    RETURNS fdw_handler
+    AS :'regresslib', 'test_fdw_handler'
+    LANGUAGE C;
 -- Clean up in case a prior regression run failed
 -- Suppress NOTICE messages when roles don't exist
 SET client_min_messages TO 'warning';
diff --git a/src/test/regress/expected/indirect_toast.out b/src/test/regress/expected/indirect_toast.out
index ab1fa5e707..44b54dc37f 100644
--- a/src/test/regress/expected/indirect_toast.out
+++ b/src/test/regress/expected/indirect_toast.out
@@ -1,6 +1,14 @@
 --
 -- Tests for external toast datums
 --
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
+CREATE FUNCTION make_tuple_indirect (record)
+        RETURNS record
+        AS :'regresslib'
+        LANGUAGE C STRICT;
 -- Other compression algorithms may cause the compressed data to be stored
 -- inline.  pglz guarantees that the data is externalized, so stick to it.
 SET default_toast_compression = 'pglz';
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 5063a3dc22..dd4354fc7d 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -408,28 +408,6 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p
 (9 rows)
 
 -- direct partition inserts should check hash partition bound constraint
--- Use hand-rolled hash functions and operator classes to get predictable
--- result on different machines.  The hash function for int4 simply returns
--- the sum of the values passed to it and the one for text returns the length
--- of the non-empty string value passed to it or 0.
-create or replace function part_hashint4_noop(value int4, seed int8)
-returns int8 as $$
-select value + seed;
-$$ language sql immutable;
-create operator class part_test_int4_ops
-for type int4
-using hash as
-operator 1 =,
-function 2 part_hashint4_noop(int4, int8);
-create or replace function part_hashtext_length(value text, seed int8)
-RETURNS int8 AS $$
-select length(coalesce(value, ''))::int8
-$$ language sql immutable;
-create operator class part_test_text_ops
-for type text
-using hash as
-operator 1 =,
-function 2 part_hashtext_length(text, int8);
 create table hash_parted (
 	a int
 ) partition by hash (a part_test_int4_ops);
diff --git a/src/test/regress/expected/lock.out b/src/test/regress/expected/lock.out
index d43bee0c56..01d467a6e0 100644
--- a/src/test/regress/expected/lock.out
+++ b/src/test/regress/expected/lock.out
@@ -1,6 +1,10 @@
 --
 -- Test the LOCK statement
 --
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
 -- Setup
 CREATE SCHEMA lock_schema1;
 SET search_path = lock_schema1;
@@ -170,6 +174,10 @@ DROP SCHEMA lock_schema1 CASCADE;
 DROP ROLE regress_rol_lock1;
 -- atomic ops tests
 RESET search_path;
+CREATE FUNCTION test_atomic_ops()
+    RETURNS bool
+    AS :'regresslib'
+    LANGUAGE C;
 SELECT test_atomic_ops();
  test_atomic_ops 
 -----------------
diff --git a/src/test/regress/expected/misc.out b/src/test/regress/expected/misc.out
index dd65e6ed62..c317f1484e 100644
--- a/src/test/regress/expected/misc.out
+++ b/src/test/regress/expected/misc.out
@@ -1,9 +1,20 @@
 --
 -- MISC
 --
--- directory paths are passed to us in environment variables
+-- directory paths and dlsuffix are passed to us in environment variables
 \getenv abs_srcdir PG_ABS_SRCDIR
 \getenv abs_builddir PG_ABS_BUILDDIR
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
+CREATE FUNCTION overpaid(emp)
+   RETURNS bool
+   AS :'regresslib'
+   LANGUAGE C STRICT;
+CREATE FUNCTION reverse_name(name)
+   RETURNS name
+   AS :'regresslib'
+   LANGUAGE C STRICT;
 --
 -- BTREE
 --
@@ -456,6 +467,52 @@ SELECT class, aa, a FROM a_star*;
 --
 -- postquel functions
 --
+CREATE FUNCTION hobbies(person)
+   RETURNS setof hobbies_r
+   AS 'select * from hobbies_r where person = $1.name'
+   LANGUAGE SQL;
+CREATE FUNCTION hobby_construct(text, text)
+   RETURNS hobbies_r
+   AS 'select $1 as name, $2 as hobby'
+   LANGUAGE SQL;
+CREATE FUNCTION hobby_construct_named(name text, hobby text)
+   RETURNS hobbies_r
+   AS 'select name, hobby'
+   LANGUAGE SQL;
+CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
+   RETURNS hobbies_r.person%TYPE
+   AS 'select person from hobbies_r where name = $1'
+   LANGUAGE SQL;
+NOTICE:  type reference hobbies_r.name%TYPE converted to text
+NOTICE:  type reference hobbies_r.person%TYPE converted to text
+CREATE FUNCTION equipment(hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = $1.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = equipment_named.hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_1a(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_1a.hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_1b(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_1c(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_2a(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_2a.hobby'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_2b(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby'
+   LANGUAGE SQL;
 --
 -- mike does post_hacking,
 -- joe and sally play basketball, and
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 6cf39c6bd2..8567fcc2b4 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -1,3 +1,7 @@
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
 --
 -- num_nulls()
 --
@@ -136,6 +140,10 @@ HINT:  No function matches the given name and argument types. You might need to
 --
 -- canonicalize_path()
 --
+CREATE FUNCTION test_canonicalize_path(text)
+   RETURNS text
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
 SELECT test_canonicalize_path('/');
  test_canonicalize_path 
 ------------------------
@@ -456,6 +464,10 @@ WHERE my_int_eq(a.unique2, 42);
 (6 rows)
 
 -- With support function that knows it's int4eq, we get a different plan
+CREATE FUNCTION test_support_func(internal)
+    RETURNS internal
+    AS :'regresslib', 'test_support_func'
+    LANGUAGE C STRICT;
 ALTER FUNCTION my_int_eq(int, int) SUPPORT test_support_func;
 EXPLAIN (COSTS OFF)
 SELECT * FROM tenk1 a JOIN tenk1 b ON a.unique1 = b.unique1
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index 2eaed6e1f4..04ccd5d451 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1,7 +1,7 @@
 -- Tests for range data types.
-create type textrange as range (subtype=text, collation="C");
 --
 -- test input parser
+-- (type textrange was already made in test_setup.sql)
 --
 -- negative tests; should fail
 select ''::textrange;
@@ -1482,12 +1482,11 @@ LINE 1: select '[2010-01-01 01:00:00 -08, 2010-01-01 02:00:00 -05)':...
 set timezone to default;
 --
 -- Test user-defined range of floats
+-- (type float8range was already made in test_setup.sql)
 --
 --should fail
-create type float8range as range (subtype=float8, subtype_diff=float4mi);
+create type bogus_float8range as range (subtype=float8, subtype_diff=float4mi);
 ERROR:  function float4mi(double precision, double precision) does not exist
---should succeed
-create type float8range as range (subtype=float8, subtype_diff=float8mi);
 select '[123.001, 5.e9)'::float8range @> 888.882::float8;
  ?column? 
 ----------
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index d652f7b5fb..cc77a20ca8 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1290,7 +1290,7 @@ iexit| SELECT ih.name,
     interpt_pp(ih.thepath, r.thepath) AS exit
    FROM ihighway ih,
     ramp r
-  WHERE (ih.thepath ## r.thepath);
+  WHERE (ih.thepath ?# r.thepath);
 key_dependent_view| SELECT view_base_table.key,
     view_base_table.data
    FROM view_base_table
@@ -2678,7 +2678,7 @@ street| SELECT r.name,
     c.cname
    FROM ONLY road r,
     real_city c
-  WHERE (c.outline ## r.thepath);
+  WHERE (c.outline ?# r.thepath);
 test_tablesample_v1| SELECT test_tablesample.id
    FROM test_tablesample TABLESAMPLE system ((10 * 2)) REPEATABLE (2);
 test_tablesample_v2| SELECT test_tablesample.id
diff --git a/src/test/regress/expected/test_setup.out b/src/test/regress/expected/test_setup.out
index bea7a7e265..34e0aa70b5 100644
--- a/src/test/regress/expected/test_setup.out
+++ b/src/test/regress/expected/test_setup.out
@@ -1,3 +1,51 @@
+--
+-- TEST_SETUP --- prepare environment expected by regression test scripts
+--
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv abs_srcdir PG_ABS_SRCDIR
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
+--
 -- Postgres formerly made the public schema read/write by default,
 -- and most of the core regression tests still expect that.
+--
 GRANT ALL ON SCHEMA public TO public;
+--
+-- We must have some enum type in the database for opr_sanity and type_sanity.
+--
+create type stoplight as enum ('red', 'yellow', 'green');
+--
+-- Also create some non-built-in range types.
+--
+create type float8range as range (subtype = float8, subtype_diff = float8mi);
+create type textrange as range (subtype = text, collation = "C");
+--
+-- Create some C functions that will be used by various tests.
+--
+CREATE FUNCTION binary_coercible(oid, oid)
+    RETURNS bool
+    AS :'regresslib', 'binary_coercible'
+    LANGUAGE C STRICT STABLE PARALLEL SAFE;
+CREATE FUNCTION ttdummy ()
+    RETURNS trigger
+    AS :'regresslib'
+    LANGUAGE C;
+-- Use hand-rolled hash functions and operator classes to get predictable
+-- result on different machines.  The hash function for int4 simply returns
+-- the sum of the values passed to it and the one for text returns the length
+-- of the non-empty string value passed to it or 0.
+create function part_hashint4_noop(value int4, seed int8)
+    returns int8 as $$
+    select value + seed;
+    $$ language sql strict immutable parallel safe;
+create operator class part_test_int4_ops for type int4 using hash as
+    operator 1 =,
+    function 2 part_hashint4_noop(int4, int8);
+create function part_hashtext_length(value text, seed int8)
+    returns int8 as $$
+    select length(coalesce(value, ''))::int8
+    $$ language sql strict immutable parallel safe;
+create operator class part_test_text_ops for type text using hash as
+    operator 1 =,
+    function 2 part_hashtext_length(text, int8);
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 447ecd9553..d3e02ca63b 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -1,6 +1,32 @@
 --
 -- TRIGGERS
 --
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set autoinclib :libdir '/autoinc' :dlsuffix
+\set refintlib :libdir '/refint' :dlsuffix
+\set regresslib :libdir '/regress' :dlsuffix
+CREATE FUNCTION autoinc ()
+	RETURNS trigger
+	AS :'autoinclib'
+	LANGUAGE C;
+CREATE FUNCTION check_primary_key ()
+	RETURNS trigger
+	AS :'refintlib'
+	LANGUAGE C;
+CREATE FUNCTION check_foreign_key ()
+	RETURNS trigger
+	AS :'refintlib'
+	LANGUAGE C;
+CREATE FUNCTION trigger_return_old ()
+        RETURNS trigger
+        AS :'regresslib'
+        LANGUAGE C;
+CREATE FUNCTION set_ttdummy (int4)
+        RETURNS int4
+        AS :'regresslib'
+        LANGUAGE C STRICT;
 create table pkeys (pkey1 int4 not null, pkey2 text not null);
 create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
 create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out
index 257b6cac12..d3f9344253 100644
--- a/src/test/regress/expected/type_sanity.out
+++ b/src/test/regress/expected/type_sanity.out
@@ -731,28 +731,23 @@ CREATE TABLE tab_core_types AS SELECT
   'n'::information_schema.sql_identifier,
   'now'::information_schema.time_stamp,
   'YES'::information_schema.yes_or_no,
-  'venus'::planets,
-  'i16'::insenum,
   '(1,2)'::int4range, '{(1,2)}'::int4multirange,
   '(3,4)'::int8range, '{(3,4)}'::int8multirange,
-  '(1,2)'::float8range, '{(1,2)}'::float8multirange,
   '(3,4)'::numrange, '{(3,4)}'::nummultirange,
-  '(a,b)'::textrange, '{(a,b)}'::textmultirange,
-  '(12.34, 56.78)'::cashrange, '{(12.34, 56.78)}'::cashmultirange,
   '(2020-01-02, 2021-02-03)'::daterange,
   '{(2020-01-02, 2021-02-03)}'::datemultirange,
   '(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tsrange,
   '{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tsmultirange,
   '(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tstzrange,
-  '{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tstzmultirange,
-  arrayrange(ARRAY[1,2], ARRAY[2,1]),
-  arraymultirange(arrayrange(ARRAY[1,2], ARRAY[2,1]));
+  '{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tstzmultirange;
 -- Sanity check on the previous table, checking that all core types are
 -- included in this table.
-SELECT oid, typname, typtype, typelem, typarray, typarray
+SELECT oid, typname, typtype, typelem, typarray
   FROM pg_type t
-  WHERE typtype NOT IN ('p', 'c') AND
-    -- reg* types cannot be pg_upgraded, so discard them.
+  WHERE oid < 16384 AND
+    -- Exclude pseudotypes and composite types.
+    typtype NOT IN ('p', 'c') AND
+    -- These reg* types cannot be pg_upgraded, so discard them.
     oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper',
                      'regoperator', 'regconfig', 'regdictionary',
                      'regnamespace', 'regcollation']::regtype[]) AND
@@ -772,7 +767,7 @@ SELECT oid, typname, typtype, typelem, typarray, typarray
                     WHERE a.atttypid=t.oid AND
                           a.attnum > 0 AND
                           a.attrelid='tab_core_types'::regclass);
- oid | typname | typtype | typelem | typarray | typarray 
------+---------+---------+---------+----------+----------
+ oid | typname | typtype | typelem | typarray 
+-----+---------+---------+---------+----------
 (0 rows)
 
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 861c30a73a..a5cd7e0878 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -36,12 +36,10 @@ test: strings numerology point lseg line box path polygon circle date time timet
 test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc
 
 # ----------
-# These four each depend on the previous one
+# These each depend on the previous one
 # ----------
-test: create_function_1
 test: create_type
 test: create_table
-test: create_function_2
 
 # ----------
 # Load huge amounts of data
diff --git a/src/test/regress/sql/alter_generic.sql b/src/test/regress/sql/alter_generic.sql
index 8c5d0e5e1f..de58d268d3 100644
--- a/src/test/regress/sql/alter_generic.sql
+++ b/src/test/regress/sql/alter_generic.sql
@@ -2,6 +2,17 @@
 -- Test for ALTER some_object {RENAME TO, OWNER TO, SET SCHEMA}
 --
 
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
+CREATE FUNCTION test_opclass_options_func(internal)
+    RETURNS void
+    AS :'regresslib', 'test_opclass_options_func'
+    LANGUAGE C;
+
 -- Clean up in case a prior regression run failed
 SET client_min_messages TO 'warning';
 
diff --git a/src/test/regress/sql/conversion.sql b/src/test/regress/sql/conversion.sql
index e178e2479b..9a65fca91f 100644
--- a/src/test/regress/sql/conversion.sql
+++ b/src/test/regress/sql/conversion.sql
@@ -1,6 +1,17 @@
 --
 -- create user defined conversion
 --
+
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
+CREATE FUNCTION test_enc_conversion(bytea, name, name, bool, validlen OUT int, result OUT bytea)
+    AS :'regresslib', 'test_enc_conversion'
+    LANGUAGE C STRICT;
+
 CREATE USER regress_conversion_user WITH NOCREATEDB NOCREATEROLE;
 SET SESSION AUTHORIZATION regress_conversion_user;
 CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
diff --git a/src/test/regress/sql/create_function_0.sql b/src/test/regress/sql/create_function_0.sql
index c5224742f9..54da95f249 100644
--- a/src/test/regress/sql/create_function_0.sql
+++ b/src/test/regress/sql/create_function_0.sql
@@ -1,98 +1,25 @@
 --
 -- CREATE_FUNCTION_0
 --
+-- This script used to create C functions for other scripts to use.
+-- But to get rid of the ordering dependencies that caused, such
+-- functions are now made either in test_setup.sql or in the specific
+-- test script that needs them.  All that remains here is error cases.
 
 -- directory path and dlsuffix are passed to us in environment variables
 \getenv libdir PG_LIBDIR
 \getenv dlsuffix PG_DLSUFFIX
 
-\set autoinclib :libdir '/autoinc' :dlsuffix
-\set refintlib :libdir '/refint' :dlsuffix
 \set regresslib :libdir '/regress' :dlsuffix
 
--- Create a bunch of C functions that will be used by later tests:
-
-CREATE FUNCTION check_primary_key ()
-	RETURNS trigger
-	AS :'refintlib'
-	LANGUAGE C;
-
-CREATE FUNCTION check_foreign_key ()
-	RETURNS trigger
-	AS :'refintlib'
-	LANGUAGE C;
-
-CREATE FUNCTION autoinc ()
-	RETURNS trigger
-	AS :'autoinclib'
-	LANGUAGE C;
-
-CREATE FUNCTION trigger_return_old ()
-        RETURNS trigger
-        AS :'regresslib'
-        LANGUAGE C;
-
-CREATE FUNCTION ttdummy ()
-        RETURNS trigger
-        AS :'regresslib'
-        LANGUAGE C;
-
-CREATE FUNCTION set_ttdummy (int4)
-        RETURNS int4
-        AS :'regresslib'
-        LANGUAGE C STRICT;
-
-CREATE FUNCTION make_tuple_indirect (record)
-        RETURNS record
-        AS :'regresslib'
-        LANGUAGE C STRICT;
-
-CREATE FUNCTION test_atomic_ops()
-    RETURNS bool
-    AS :'regresslib'
-    LANGUAGE C;
-
-CREATE FUNCTION test_fdw_handler()
-    RETURNS fdw_handler
-    AS :'regresslib', 'test_fdw_handler'
-    LANGUAGE C;
-
-CREATE FUNCTION test_support_func(internal)
-    RETURNS internal
-    AS :'regresslib', 'test_support_func'
-    LANGUAGE C STRICT;
-
-CREATE FUNCTION test_opclass_options_func(internal)
-    RETURNS void
-    AS :'regresslib', 'test_opclass_options_func'
-    LANGUAGE C;
-
-CREATE FUNCTION test_enc_conversion(bytea, name, name, bool, validlen OUT int, result OUT bytea)
-    AS :'regresslib', 'test_enc_conversion'
-    LANGUAGE C STRICT;
-
-CREATE FUNCTION binary_coercible(oid, oid)
-    RETURNS bool
-    AS :'regresslib', 'binary_coercible'
-    LANGUAGE C STRICT STABLE PARALLEL SAFE;
+--
+-- Check LOAD command.  (The alternative of implicitly loading the library
+-- is checked in many other test scripts.)
+--
+LOAD :'regresslib';
 
 -- Things that shouldn't work:
 
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'SELECT ''not an integer'';';
-
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'not even SQL';
-
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'SELECT 1, 2, 3;';
-
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'SELECT $2;';
-
-CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
-    AS 'a', 'b';
-
 CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C
     AS 'nosuchfile';
 
diff --git a/src/test/regress/sql/create_function_1.sql b/src/test/regress/sql/create_function_1.sql
deleted file mode 100644
index 34cc7c6efc..0000000000
--- a/src/test/regress/sql/create_function_1.sql
+++ /dev/null
@@ -1,36 +0,0 @@
---
--- CREATE_FUNCTION_1
---
-
--- directory path and dlsuffix are passed to us in environment variables
-\getenv libdir PG_LIBDIR
-\getenv dlsuffix PG_DLSUFFIX
-
-\set regresslib :libdir '/regress' :dlsuffix
-
--- Create C functions needed by create_type.sql
-
-CREATE FUNCTION widget_in(cstring)
-   RETURNS widget
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
-
-CREATE FUNCTION widget_out(widget)
-   RETURNS cstring
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
-
-CREATE FUNCTION int44in(cstring)
-   RETURNS city_budget
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
-
-CREATE FUNCTION int44out(city_budget)
-   RETURNS cstring
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
-
-CREATE FUNCTION test_canonicalize_path(text)
-   RETURNS text
-   AS :'regresslib'
-   LANGUAGE C STRICT IMMUTABLE;
diff --git a/src/test/regress/sql/create_function_2.sql b/src/test/regress/sql/create_function_2.sql
deleted file mode 100644
index 67510aed23..0000000000
--- a/src/test/regress/sql/create_function_2.sql
+++ /dev/null
@@ -1,96 +0,0 @@
---
--- CREATE_FUNCTION_2
---
-
--- directory path and dlsuffix are passed to us in environment variables
-\getenv libdir PG_LIBDIR
-\getenv dlsuffix PG_DLSUFFIX
-
-\set regresslib :libdir '/regress' :dlsuffix
-
-
-CREATE FUNCTION hobbies(person)
-   RETURNS setof hobbies_r
-   AS 'select * from hobbies_r where person = $1.name'
-   LANGUAGE SQL;
-
-
-CREATE FUNCTION hobby_construct(text, text)
-   RETURNS hobbies_r
-   AS 'select $1 as name, $2 as hobby'
-   LANGUAGE SQL;
-
-
-CREATE FUNCTION hobby_construct_named(name text, hobby text)
-   RETURNS hobbies_r
-   AS 'select name, hobby'
-   LANGUAGE SQL;
-
-
-CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
-   RETURNS hobbies_r.person%TYPE
-   AS 'select person from hobbies_r where name = $1'
-   LANGUAGE SQL;
-
-
-CREATE FUNCTION equipment(hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where hobby = $1.name'
-   LANGUAGE SQL;
-
-
-CREATE FUNCTION equipment_named(hobby hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where equipment_r.hobby = equipment_named.hobby.name'
-   LANGUAGE SQL;
-
-CREATE FUNCTION equipment_named_ambiguous_1a(hobby hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_1a.hobby.name'
-   LANGUAGE SQL;
-
-CREATE FUNCTION equipment_named_ambiguous_1b(hobby hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where equipment_r.hobby = hobby.name'
-   LANGUAGE SQL;
-
-CREATE FUNCTION equipment_named_ambiguous_1c(hobby hobbies_r)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where hobby = hobby.name'
-   LANGUAGE SQL;
-
-CREATE FUNCTION equipment_named_ambiguous_2a(hobby text)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_2a.hobby'
-   LANGUAGE SQL;
-
-CREATE FUNCTION equipment_named_ambiguous_2b(hobby text)
-   RETURNS setof equipment_r
-   AS 'select * from equipment_r where equipment_r.hobby = hobby'
-   LANGUAGE SQL;
-
-
-CREATE FUNCTION pt_in_widget(point, widget)
-   RETURNS bool
-   AS :'regresslib'
-   LANGUAGE C STRICT;
-
-CREATE FUNCTION overpaid(emp)
-   RETURNS bool
-   AS :'regresslib'
-   LANGUAGE C STRICT;
-
-CREATE FUNCTION interpt_pp(path, path)
-   RETURNS point
-   AS :'regresslib'
-   LANGUAGE C STRICT;
-
-CREATE FUNCTION reverse_name(name)
-   RETURNS name
-   AS :'regresslib'
-   LANGUAGE C STRICT;
-
---
--- Function dynamic loading
---
-LOAD :'regresslib';
diff --git a/src/test/regress/sql/create_function_3.sql b/src/test/regress/sql/create_function_3.sql
index 7edd757b8f..254f444d94 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -385,6 +385,23 @@ CREATE FUNCTION voidtest5(a int) RETURNS SETOF VOID LANGUAGE SQL AS
 $$ SELECT generate_series(1, a) $$ STABLE;
 SELECT * FROM voidtest5(3);
 
+-- Things that shouldn't work:
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'SELECT ''not an integer'';';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'not even SQL';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'SELECT 1, 2, 3;';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'SELECT $2;';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+    AS 'a', 'b';
+
 -- Cleanup
 DROP SCHEMA temp_func_test CASCADE;
 DROP USER regress_unpriv_user;
diff --git a/src/test/regress/sql/create_operator.sql b/src/test/regress/sql/create_operator.sql
index 4ff2c0ff21..f53e24db3c 100644
--- a/src/test/regress/sql/create_operator.sql
+++ b/src/test/regress/sql/create_operator.sql
@@ -9,14 +9,6 @@ CREATE OPERATOR ## (
    commutator = ##
 );
 
-CREATE OPERATOR <% (
-   leftarg = point,
-   rightarg = widget,
-   procedure = pt_in_widget,
-   commutator = >% ,
-   negator = >=%
-);
-
 CREATE OPERATOR @#@ (
    rightarg = int8,		-- prefix
    procedure = factorial
@@ -28,8 +20,7 @@ CREATE OPERATOR #%# (
 );
 
 -- Test operator created above
-SELECT point '(1,2)' <% widget '(0,0,3)' AS t,
-       point '(1,2)' <% widget '(0,0,1)' AS f;
+SELECT @#@ 24;
 
 -- Test comments
 COMMENT ON OPERATOR ###### (NONE, int4) IS 'bad prefix';
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index a32a9e6795..f75272468f 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -2,11 +2,36 @@
 -- CREATE_TYPE
 --
 
+-- directory path and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
 --
--- Note: widget_in/out were created in create_function_1, without any
--- prior shell-type creation.  These commands therefore complete a test
--- of the "old style" approach of making the functions first.
+-- Test the "old style" approach of making the I/O functions first,
+-- with no explicit shell type creation.
 --
+CREATE FUNCTION widget_in(cstring)
+   RETURNS widget
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
+
+CREATE FUNCTION widget_out(widget)
+   RETURNS cstring
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
+
+CREATE FUNCTION int44in(cstring)
+   RETURNS city_budget
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
+
+CREATE FUNCTION int44out(city_budget)
+   RETURNS cstring
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
+
 CREATE TYPE widget (
    internallength = 24,
    input = widget_in,
@@ -167,6 +192,24 @@ select format_type('bpchar'::regtype, null);
 -- this behavior difference is intentional
 select format_type('bpchar'::regtype, -1);
 
+-- Test creation of an operator over a user-defined type
+
+CREATE FUNCTION pt_in_widget(point, widget)
+   RETURNS bool
+   AS :'regresslib'
+   LANGUAGE C STRICT;
+
+CREATE OPERATOR <% (
+   leftarg = point,
+   rightarg = widget,
+   procedure = pt_in_widget,
+   commutator = >% ,
+   negator = >=%
+);
+
+SELECT point '(1,2)' <% widget '(0,0,3)' AS t,
+       point '(1,2)' <% widget '(0,0,1)' AS f;
+
 --
 -- Test CREATE/ALTER TYPE using a type that's compatible with varchar,
 -- so we can re-use those support functions
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 82df4b7cac..609fa4d7c4 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -4,16 +4,28 @@
 --	(this also tests the query rewrite system)
 --
 
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv abs_srcdir PG_ABS_SRCDIR
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
+CREATE FUNCTION interpt_pp(path, path)
+    RETURNS point
+    AS :'regresslib'
+    LANGUAGE C STRICT;
+
 CREATE VIEW street AS
    SELECT r.name, r.thepath, c.cname AS cname
    FROM ONLY road r, real_city c
-   WHERE c.outline ## r.thepath;
+   WHERE c.outline ?# r.thepath;
 
 CREATE VIEW iexit AS
    SELECT ih.name, ih.thepath,
 	interpt_pp(ih.thepath, r.thepath) AS exit
    FROM ihighway ih, ramp r
-   WHERE ih.thepath ## r.thepath;
+   WHERE ih.thepath ?# r.thepath;
 
 CREATE VIEW toyemp AS
    SELECT name, age, location, 12*salary AS annualsal
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index a65f4ffdca..9dfff66f54 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -2,6 +2,17 @@
 -- Test foreign-data wrapper and server management.
 --
 
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
+CREATE FUNCTION test_fdw_handler()
+    RETURNS fdw_handler
+    AS :'regresslib', 'test_fdw_handler'
+    LANGUAGE C;
+
 -- Clean up in case a prior regression run failed
 
 -- Suppress NOTICE messages when roles don't exist
diff --git a/src/test/regress/sql/indirect_toast.sql b/src/test/regress/sql/indirect_toast.sql
index 9156a44b7d..3e2f6c0237 100644
--- a/src/test/regress/sql/indirect_toast.sql
+++ b/src/test/regress/sql/indirect_toast.sql
@@ -2,6 +2,17 @@
 -- Tests for external toast datums
 --
 
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
+CREATE FUNCTION make_tuple_indirect (record)
+        RETURNS record
+        AS :'regresslib'
+        LANGUAGE C STRICT;
+
 -- Other compression algorithms may cause the compressed data to be stored
 -- inline.  pglz guarantees that the data is externalized, so stick to it.
 SET default_toast_compression = 'pglz';
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index bfaa8a3b27..bdcffd0314 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -250,33 +250,6 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p
 
 -- direct partition inserts should check hash partition bound constraint
 
--- Use hand-rolled hash functions and operator classes to get predictable
--- result on different machines.  The hash function for int4 simply returns
--- the sum of the values passed to it and the one for text returns the length
--- of the non-empty string value passed to it or 0.
-
-create or replace function part_hashint4_noop(value int4, seed int8)
-returns int8 as $$
-select value + seed;
-$$ language sql immutable;
-
-create operator class part_test_int4_ops
-for type int4
-using hash as
-operator 1 =,
-function 2 part_hashint4_noop(int4, int8);
-
-create or replace function part_hashtext_length(value text, seed int8)
-RETURNS int8 AS $$
-select length(coalesce(value, ''))::int8
-$$ language sql immutable;
-
-create operator class part_test_text_ops
-for type text
-using hash as
-operator 1 =,
-function 2 part_hashtext_length(text, int8);
-
 create table hash_parted (
 	a int
 ) partition by hash (a part_test_int4_ops);
diff --git a/src/test/regress/sql/lock.sql b/src/test/regress/sql/lock.sql
index 05bdb8ad4c..b867e0f994 100644
--- a/src/test/regress/sql/lock.sql
+++ b/src/test/regress/sql/lock.sql
@@ -2,6 +2,12 @@
 -- Test the LOCK statement
 --
 
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
 -- Setup
 CREATE SCHEMA lock_schema1;
 SET search_path = lock_schema1;
@@ -136,4 +142,10 @@ DROP ROLE regress_rol_lock1;
 
 -- atomic ops tests
 RESET search_path;
+
+CREATE FUNCTION test_atomic_ops()
+    RETURNS bool
+    AS :'regresslib'
+    LANGUAGE C;
+
 SELECT test_atomic_ops();
diff --git a/src/test/regress/sql/misc.sql b/src/test/regress/sql/misc.sql
index a1e2f779ba..8bf63bca3d 100644
--- a/src/test/regress/sql/misc.sql
+++ b/src/test/regress/sql/misc.sql
@@ -2,9 +2,23 @@
 -- MISC
 --
 
--- directory paths are passed to us in environment variables
+-- directory paths and dlsuffix are passed to us in environment variables
 \getenv abs_srcdir PG_ABS_SRCDIR
 \getenv abs_builddir PG_ABS_BUILDDIR
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
+CREATE FUNCTION overpaid(emp)
+   RETURNS bool
+   AS :'regresslib'
+   LANGUAGE C STRICT;
+
+CREATE FUNCTION reverse_name(name)
+   RETURNS name
+   AS :'regresslib'
+   LANGUAGE C STRICT;
 
 --
 -- BTREE
@@ -179,6 +193,62 @@ SELECT class, aa, a FROM a_star*;
 --
 -- postquel functions
 --
+
+CREATE FUNCTION hobbies(person)
+   RETURNS setof hobbies_r
+   AS 'select * from hobbies_r where person = $1.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION hobby_construct(text, text)
+   RETURNS hobbies_r
+   AS 'select $1 as name, $2 as hobby'
+   LANGUAGE SQL;
+
+CREATE FUNCTION hobby_construct_named(name text, hobby text)
+   RETURNS hobbies_r
+   AS 'select name, hobby'
+   LANGUAGE SQL;
+
+CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
+   RETURNS hobbies_r.person%TYPE
+   AS 'select person from hobbies_r where name = $1'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment(hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = $1.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = equipment_named.hobby.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_1a(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_1a.hobby.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_1b(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_1c(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = hobby.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_2a(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_2a.hobby'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_2b(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby'
+   LANGUAGE SQL;
+
 --
 -- mike does post_hacking,
 -- joe and sally play basketball, and
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index cfaba456e1..3db3f8bade 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -1,3 +1,9 @@
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
 --
 -- num_nulls()
 --
@@ -34,6 +40,11 @@ SELECT num_nulls();
 -- canonicalize_path()
 --
 
+CREATE FUNCTION test_canonicalize_path(text)
+   RETURNS text
+   AS :'regresslib'
+   LANGUAGE C STRICT IMMUTABLE;
+
 SELECT test_canonicalize_path('/');
 SELECT test_canonicalize_path('/./abc/def/');
 SELECT test_canonicalize_path('/./../abc/def');
@@ -156,6 +167,11 @@ SELECT * FROM tenk1 a JOIN tenk1 b ON a.unique1 = b.unique1
 WHERE my_int_eq(a.unique2, 42);
 
 -- With support function that knows it's int4eq, we get a different plan
+CREATE FUNCTION test_support_func(internal)
+    RETURNS internal
+    AS :'regresslib', 'test_support_func'
+    LANGUAGE C STRICT;
+
 ALTER FUNCTION my_int_eq(int, int) SUPPORT test_support_func;
 
 EXPLAIN (COSTS OFF)
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index a2d411d0da..1a10f67f19 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -1,9 +1,8 @@
 -- Tests for range data types.
 
-create type textrange as range (subtype=text, collation="C");
-
 --
 -- test input parser
+-- (type textrange was already made in test_setup.sql)
 --
 
 -- negative tests; should fail
@@ -424,13 +423,12 @@ set timezone to default;
 
 --
 -- Test user-defined range of floats
+-- (type float8range was already made in test_setup.sql)
 --
 
 --should fail
-create type float8range as range (subtype=float8, subtype_diff=float4mi);
+create type bogus_float8range as range (subtype=float8, subtype_diff=float4mi);
 
---should succeed
-create type float8range as range (subtype=float8, subtype_diff=float8mi);
 select '[123.001, 5.e9)'::float8range @> 888.882::float8;
 create table float8range_test(f8r float8range, i int);
 insert into float8range_test values(float8range(-100.00007, '1.111113e9'), 42);
diff --git a/src/test/regress/sql/test_setup.sql b/src/test/regress/sql/test_setup.sql
index bea7a7e265..61c953fb16 100644
--- a/src/test/regress/sql/test_setup.sql
+++ b/src/test/regress/sql/test_setup.sql
@@ -1,3 +1,67 @@
+--
+-- TEST_SETUP --- prepare environment expected by regression test scripts
+--
+
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv abs_srcdir PG_ABS_SRCDIR
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set regresslib :libdir '/regress' :dlsuffix
+
+--
 -- Postgres formerly made the public schema read/write by default,
 -- and most of the core regression tests still expect that.
+--
 GRANT ALL ON SCHEMA public TO public;
+
+--
+-- We must have some enum type in the database for opr_sanity and type_sanity.
+--
+
+create type stoplight as enum ('red', 'yellow', 'green');
+
+--
+-- Also create some non-built-in range types.
+--
+
+create type float8range as range (subtype = float8, subtype_diff = float8mi);
+
+create type textrange as range (subtype = text, collation = "C");
+
+--
+-- Create some C functions that will be used by various tests.
+--
+
+CREATE FUNCTION binary_coercible(oid, oid)
+    RETURNS bool
+    AS :'regresslib', 'binary_coercible'
+    LANGUAGE C STRICT STABLE PARALLEL SAFE;
+
+CREATE FUNCTION ttdummy ()
+    RETURNS trigger
+    AS :'regresslib'
+    LANGUAGE C;
+
+-- Use hand-rolled hash functions and operator classes to get predictable
+-- result on different machines.  The hash function for int4 simply returns
+-- the sum of the values passed to it and the one for text returns the length
+-- of the non-empty string value passed to it or 0.
+
+create function part_hashint4_noop(value int4, seed int8)
+    returns int8 as $$
+    select value + seed;
+    $$ language sql strict immutable parallel safe;
+
+create operator class part_test_int4_ops for type int4 using hash as
+    operator 1 =,
+    function 2 part_hashint4_noop(int4, int8);
+
+create function part_hashtext_length(value text, seed int8)
+    returns int8 as $$
+    select length(coalesce(value, ''))::int8
+    $$ language sql strict immutable parallel safe;
+
+create operator class part_test_text_ops for type text using hash as
+    operator 1 =,
+    function 2 part_hashtext_length(text, int8);
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 2657d127d5..4cc096265d 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -2,6 +2,39 @@
 -- TRIGGERS
 --
 
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+
+\set autoinclib :libdir '/autoinc' :dlsuffix
+\set refintlib :libdir '/refint' :dlsuffix
+\set regresslib :libdir '/regress' :dlsuffix
+
+CREATE FUNCTION autoinc ()
+	RETURNS trigger
+	AS :'autoinclib'
+	LANGUAGE C;
+
+CREATE FUNCTION check_primary_key ()
+	RETURNS trigger
+	AS :'refintlib'
+	LANGUAGE C;
+
+CREATE FUNCTION check_foreign_key ()
+	RETURNS trigger
+	AS :'refintlib'
+	LANGUAGE C;
+
+CREATE FUNCTION trigger_return_old ()
+        RETURNS trigger
+        AS :'regresslib'
+        LANGUAGE C;
+
+CREATE FUNCTION set_ttdummy (int4)
+        RETURNS int4
+        AS :'regresslib'
+        LANGUAGE C STRICT;
+
 create table pkeys (pkey1 int4 not null, pkey2 text not null);
 create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
 create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sql
index 8281076423..cdde47290f 100644
--- a/src/test/regress/sql/type_sanity.sql
+++ b/src/test/regress/sql/type_sanity.sql
@@ -553,29 +553,24 @@ CREATE TABLE tab_core_types AS SELECT
   'n'::information_schema.sql_identifier,
   'now'::information_schema.time_stamp,
   'YES'::information_schema.yes_or_no,
-  'venus'::planets,
-  'i16'::insenum,
   '(1,2)'::int4range, '{(1,2)}'::int4multirange,
   '(3,4)'::int8range, '{(3,4)}'::int8multirange,
-  '(1,2)'::float8range, '{(1,2)}'::float8multirange,
   '(3,4)'::numrange, '{(3,4)}'::nummultirange,
-  '(a,b)'::textrange, '{(a,b)}'::textmultirange,
-  '(12.34, 56.78)'::cashrange, '{(12.34, 56.78)}'::cashmultirange,
   '(2020-01-02, 2021-02-03)'::daterange,
   '{(2020-01-02, 2021-02-03)}'::datemultirange,
   '(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tsrange,
   '{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tsmultirange,
   '(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tstzrange,
-  '{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tstzmultirange,
-  arrayrange(ARRAY[1,2], ARRAY[2,1]),
-  arraymultirange(arrayrange(ARRAY[1,2], ARRAY[2,1]));
+  '{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tstzmultirange;
 
 -- Sanity check on the previous table, checking that all core types are
 -- included in this table.
-SELECT oid, typname, typtype, typelem, typarray, typarray
+SELECT oid, typname, typtype, typelem, typarray
   FROM pg_type t
-  WHERE typtype NOT IN ('p', 'c') AND
-    -- reg* types cannot be pg_upgraded, so discard them.
+  WHERE oid < 16384 AND
+    -- Exclude pseudotypes and composite types.
+    typtype NOT IN ('p', 'c') AND
+    -- These reg* types cannot be pg_upgraded, so discard them.
     oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper',
                      'regoperator', 'regconfig', 'regdictionary',
                      'regnamespace', 'regcollation']::regtype[]) AND