1 files changed, 86 insertions, 0 deletions
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index 7e4010618d..63a24c7b53 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -28,6 +28,92 @@ FROM pg_proc as p1
 WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
     p1.pronargs < 0 OR p1.pronargs > 9;
 
+-- Look for conflicting proc definitions (same names and input datatypes).
+
+SELECT p1.oid, p1.proname, p2.oid, p2.proname
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.proname = p2.proname AND
+    p1.pronargs = p2.pronargs AND
+    p1.proargtypes = p2.proargtypes;
+
+-- Considering only built-in procs (prolang = 11), look for multiple uses
+-- of the same internal function (ie, matching prosrc fields).  It's OK to
+-- have several entries with different pronames for the same internal function,
+-- but conflicts in the number of arguments and other critical items should
+-- be complained of.
+
+SELECT p1.oid, p1.proname, p2.oid, p2.proname
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.proisinh != p2.proisinh OR
+     p1.proistrusted != p2.proistrusted OR
+     p1.proiscachable != p2.proiscachable OR
+     p1.pronargs != p2.pronargs OR
+     p1.proretset != p2.proretset);
+
+-- Look for uses of different type OIDs in the argument/result type fields
+-- for different aliases of the same built-in function.
+-- This indicates that the types are being presumed to be binary-equivalent.
+-- That's not wrong, necessarily, but we make lists of all the types being
+-- so treated.  Note that the expected output of this part of the test will
+-- need to be modified whenever new pairs of types are made binary-equivalent!
+
+SELECT DISTINCT p1.prorettype, p2.prorettype
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.prorettype < p2.prorettype);
+
+SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.proargtypes[0] < p2.proargtypes[0]);
+
+SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.proargtypes[1] < p2.proargtypes[1]);
+
+SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.proargtypes[2] < p2.proargtypes[2]);
+
+SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.proargtypes[3] < p2.proargtypes[3]);
+
+SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.proargtypes[4] < p2.proargtypes[4]);
+
+SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.proargtypes[5] < p2.proargtypes[5]);
+
+SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.proargtypes[6] < p2.proargtypes[6]);
+
+SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
+FROM pg_proc AS p1, pg_proc AS p2
+WHERE p1.oid != p2.oid AND
+    p1.prosrc = p2.prosrc AND p1.prolang = 11 AND p2.prolang = 11 AND
+    (p1.proargtypes[7] < p2.proargtypes[7]);
+
 -- **************** pg_operator ****************
 
 -- Look for illegal values in pg_operator fields.