Improve ordering for \dAo and \dAp psql commands

This commit changes ORDER BY clause for \dAo and \dAp psql commands in
the following way.
 * Operators for the same types are grouped together.
 * Same-class operators and procedures are listed before cross-class operators
   and procedures.

Modification of ORDER BY clause for \dAp required removing DISTINCT clause,
which doesn't seem to affect anything.

Discussion: https://postgr.es/m/20200511210856.GA18368%40alvherre.pgsql
Author: Alvaro Herrera revised by me
Reviewed-by: Alexander Korotkov, Nikita Glukhov

3 files changed, 49 insertions, 25 deletions

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 9b526e40cdd..920d5580554 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6288,7 +6288,11 @@ listOpFamilyOperators(const char *access_method_pattern,
 		processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
 							  "nsf.nspname", "of.opfname", NULL, NULL);
 
-	appendPQExpBufferStr(&buf, "ORDER BY 1, 2, o.amopstrategy, 3;");
+	appendPQExpBufferStr(&buf, "ORDER BY 1, 2,\n"
+						 "  o.amoplefttype = o.amoprighttype DESC,\n"
+						 "  pg_catalog.format_type(o.amoplefttype, NULL),\n"
+						 "  pg_catalog.format_type(o.amoprighttype, NULL),\n"
+						 "  o.amopstrategy;");
 
 	res = PSQLexec(buf.data);
 	termPQExpBuffer(&buf);
@@ -6327,7 +6331,7 @@ listOpFamilyProcedures(const char *access_method_pattern,
 	initPQExpBuffer(&buf);
 
 	printfPQExpBuffer(&buf,
-					  "SELECT DISTINCT\n"
+					  "SELECT\n"
 					  "  am.amname AS \"%s\",\n"
 					  "  CASE\n"
 					  "    WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
@@ -6360,8 +6364,9 @@ listOpFamilyProcedures(const char *access_method_pattern,
 		processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
 							  "ns.nspname", "of.opfname", NULL, NULL);
 
-	appendPQExpBufferStr(&buf,
-						 "ORDER BY 1, 2, 3, 4, 5;");
+	appendPQExpBufferStr(&buf, "ORDER BY 1, 2,\n"
+						 "  ap.amproclefttype = ap.amprocrighttype DESC,\n"
+						 "  3, 4, 5;");
 
 	res = PSQLexec(buf.data);
 	termPQExpBuffer(&buf);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 0b990fd814a..6e6fcb2db64 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4952,16 +4952,31 @@ List of access methods
  btree | integer_ops     | smallint, integer, bigint
 (1 row)
 
-\dAo brin uuid_minmax_ops
-  List of operators of operator families
-  AM  |  Opfamily Name  |    Operator     
-------+-----------------+-----------------
- brin | uuid_minmax_ops | < (uuid, uuid)
- brin | uuid_minmax_ops | <= (uuid, uuid)
- brin | uuid_minmax_ops | = (uuid, uuid)
- brin | uuid_minmax_ops | >= (uuid, uuid)
- brin | uuid_minmax_ops | > (uuid, uuid)
-(5 rows)
+\dAo+ btree float_ops
+                                List of operators of operator families
+  AM   | Opfamily Name |                Operator                 | Strategy | Purpose | Sort opfamily 
+-------+---------------+-----------------------------------------+----------+---------+---------------
+ btree | float_ops     | < (double precision, double precision)  |        1 | search  | 
+ btree | float_ops     | <= (double precision, double precision) |        2 | search  | 
+ btree | float_ops     | = (double precision, double precision)  |        3 | search  | 
+ btree | float_ops     | >= (double precision, double precision) |        4 | search  | 
+ btree | float_ops     | > (double precision, double precision)  |        5 | search  | 
+ btree | float_ops     | < (real, real)                          |        1 | search  | 
+ btree | float_ops     | <= (real, real)                         |        2 | search  | 
+ btree | float_ops     | = (real, real)                          |        3 | search  | 
+ btree | float_ops     | >= (real, real)                         |        4 | search  | 
+ btree | float_ops     | > (real, real)                          |        5 | search  | 
+ btree | float_ops     | < (double precision, real)              |        1 | search  | 
+ btree | float_ops     | <= (double precision, real)             |        2 | search  | 
+ btree | float_ops     | = (double precision, real)              |        3 | search  | 
+ btree | float_ops     | >= (double precision, real)             |        4 | search  | 
+ btree | float_ops     | > (double precision, real)              |        5 | search  | 
+ btree | float_ops     | < (real, double precision)              |        1 | search  | 
+ btree | float_ops     | <= (real, double precision)             |        2 | search  | 
+ btree | float_ops     | = (real, double precision)              |        3 | search  | 
+ btree | float_ops     | >= (real, double precision)             |        4 | search  | 
+ btree | float_ops     | > (real, double precision)              |        5 | search  | 
+(20 rows)
 
 \dAo * pg_catalog.jsonb_path_ops
    List of operators of operator families
@@ -4972,15 +4987,19 @@ List of access methods
  gin | jsonb_path_ops | @@ (jsonb, jsonpath)
 (3 rows)
 
-\dAp brin uuid_minmax_ops
-                          List of procedures of operator families
-  AM  | Operator family | Left arg type | Right arg type | Number |       Proc name        
-------+-----------------+---------------+----------------+--------+------------------------
- brin | uuid_minmax_ops | uuid          | uuid           |      1 | brin_minmax_opcinfo
- brin | uuid_minmax_ops | uuid          | uuid           |      2 | brin_minmax_add_value
- brin | uuid_minmax_ops | uuid          | uuid           |      3 | brin_minmax_consistent
- brin | uuid_minmax_ops | uuid          | uuid           |      4 | brin_minmax_union
-(4 rows)
+\dAp btree float_ops
+                           List of procedures of operator families
+  AM   | Operator family |  Left arg type   |  Right arg type  | Number |      Proc name      
+-------+-----------------+------------------+------------------+--------+---------------------
+ btree | float_ops       | double precision | double precision |      1 | btfloat8cmp
+ btree | float_ops       | double precision | double precision |      2 | btfloat8sortsupport
+ btree | float_ops       | double precision | double precision |      3 | in_range
+ btree | float_ops       | real             | real             |      1 | btfloat4cmp
+ btree | float_ops       | real             | real             |      2 | btfloat4sortsupport
+ btree | float_ops       | double precision | real             |      1 | btfloat84cmp
+ btree | float_ops       | real             | double precision |      1 | btfloat48cmp
+ btree | float_ops       | real             | double precision |      3 | in_range
+(8 rows)
 
 \dAp * pg_catalog.uuid_ops
                         List of procedures of operator families
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index d462c357bd5..bd10aec6d67 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1203,7 +1203,7 @@ drop role regress_partitioning_role;
 \dAc brin pg*.oid*
 \dAf spgist
 \dAf btree int4
-\dAo brin uuid_minmax_ops
+\dAo+ btree float_ops
 \dAo * pg_catalog.jsonb_path_ops
-\dAp brin uuid_minmax_ops
+\dAp btree float_ops
 \dAp * pg_catalog.uuid_ops