Enhanced index details using \d in psql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've revised my patch to use the pg_get_indexdef function, as
suggested by Tom Lane. When doing a \d in psql, it shows not
only the name of the index, but which columns it uses,
what type of index it is (btree, hash, etc.), whether it
is unique or a primary key, and (thanks to the pg_get_indexdef
magic) information about functional and partial indexes.
I also made the ORDER BY stick the primary keys and unique
indexes at the top of the list, then alphabetasize the rest,
which I think is a good trade off for having ruined the
previous "separation" of the 3 types of indexes.

Here is some sample output:

CREATE TABLE PIZZA (
  slicename VARCHAR(10) PRIMARY KEY,
  crust     VARCHAR(12),
  topping   INTEGER
);
CREATE INDEX banana ON pizza (crust);
CREATE UNIQUE INDEX watermelon ON pizza (topping);
CREATE INDEX apple ON pizza USING hash (topping) WHERE (topping > 99);
CREATE INDEX peach ON pizza (lower(crust));

bakery=# \d pizza

                 Table "pizza"
  Column   |         Type          | Modifiers
- -----------+-----------------------+-----------
 slicename | character varying(10) | not null
 crust     | character varying(12) |
 topping   | integer               |
Indexes: pizza_pkey primary key btree (slicename),
         watermelon unique btree (topping),
         apple hash (topping) WHERE (topping > 99),
         banana btree (crust),
         peach btree (lower(crust))


Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200111081007

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBO+qfuLybkGcUlkrIEQKFNgCg0ycYPt5DMvXI8EH/CH2mc8ZOJYwAoK1x
RDPvYrxuU55Sp1UIYgTWCRdD
=VBAa
-----END PGP SIGNATURE-----

*** ./src/bin/psql/describe.c.orig    Mon Nov  5 15:43:13 2001
--- ./src/bin/psql/describe.c    Thu Nov  8 09:22:49 2001
***************
*** 654,660 ****
          PGresult   *result;

          sprintf(buf, "SELECT i.indisunique, i.indisprimary, a.amname,\n"
!                 "       pg_get_expr(i.indpred, i.indrelid) as indpred\n"
                  "FROM pg_index i, pg_class c, pg_am a\n"
                  "WHERE i.indexrelid = c.oid AND c.relname = '%s' AND c.relam = a.oid",
                  name);
--- 654,660 ----
          PGresult   *result;

          sprintf(buf, "SELECT i.indisunique, i.indisprimary, a.amname,\n"
!                 "i.indpred\n"
                  "FROM pg_index i, pg_class c, pg_am a\n"
                  "WHERE i.indexrelid = c.oid AND c.relname = '%s' AND c.relam = a.oid",
                  name);
***************
*** 707,718 ****
          PGresult   *result1 = NULL,
                     *result2 = NULL,
                     *result3 = NULL,
!                    *result4 = NULL,
!                    *result5 = NULL,
!                    *result6 = NULL;
          int            index_count = 0,
-                     primary_count = 0,
-                     unique_count = 0,
                      constr_count = 0,
                      rule_count = 0,
                      trigger_count = 0;
--- 707,714 ----
          PGresult   *result1 = NULL,
                     *result2 = NULL,
                     *result3 = NULL,
!                    *result4 = NULL;
          int            index_count = 0,
                      constr_count = 0,
                      rule_count = 0,
                      trigger_count = 0;
***************
*** 721,730 ****
          /* count indexes */
          if (!error && tableinfo.hasindex)
          {
!             sprintf(buf, "SELECT c2.relname\n"
                      "FROM pg_class c, pg_class c2, pg_index i\n"
                      "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
!                     "AND NOT i.indisunique ORDER BY c2.relname",
                      name);
              result1 = PSQLexec(buf);
              if (!result1)
--- 717,726 ----
          /* count indexes */
          if (!error && tableinfo.hasindex)
          {
!             sprintf(buf, "SELECT c2.relname, pg_get_indexdef(i.indexrelid), i.indisprimary, i.indisunique\n"
                      "FROM pg_class c, pg_class c2, pg_index i\n"
                      "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
!                     "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
                      name);
              result1 = PSQLexec(buf);
              if (!result1)
***************
*** 733,768 ****
                  index_count = PQntuples(result1);
          }

-         /* count primary keys */
-         if (!error && tableinfo.hasindex)
-         {
-             sprintf(buf, "SELECT c2.relname\n"
-                     "FROM pg_class c, pg_class c2, pg_index i\n"
-                     "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
-               "AND i.indisprimary AND i.indisunique ORDER BY c2.relname",
-                     name);
-             result5 = PSQLexec(buf);
-             if (!result5)
-                 error = true;
-             else
-                 primary_count = PQntuples(result5);
-         }
-
-         /* count unique constraints */
-         if (!error && tableinfo.hasindex)
-         {
-             sprintf(buf, "SELECT c2.relname\n"
-                     "FROM pg_class c, pg_class c2, pg_index i\n"
-                     "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
-                     "AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname",
-                     name);
-             result6 = PSQLexec(buf);
-             if (!result6)
-                 error = true;
-             else
-                 unique_count = PQntuples(result6);
-         }
-
          /* count table (and column) constraints */
          if (!error && tableinfo.checks)
          {
--- 729,734 ----
***************
*** 807,860 ****
                  trigger_count = PQntuples(result4);
          }

!         footers = xmalloc((index_count + primary_count + unique_count +
!                            constr_count + rule_count + trigger_count + 1)
                            * sizeof(*footers));

          /* print indexes */
          for (i = 0; i < index_count; i++)
          {
              char       *s = _("Indexes");
!
              if (i == 0)
                  snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result1, i, 0));
              else
                  snprintf(buf, sizeof(buf), "%*s  %s", (int) strlen(s), "", PQgetvalue(result1, i, 0));
!             if (i < index_count - 1)
!                 strcat(buf, ",");
!
!             footers[count_footers++] = xstrdup(buf);
!         }

!         /* print primary keys */
!         for (i = 0; i < primary_count; i++)
!         {
!             char       *s = _("Primary key");

!             if (i == 0)
!                 snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result5, i, 0));
!             else
!                 snprintf(buf, sizeof(buf), "%*s  %s", (int) strlen(s), "", PQgetvalue(result5, i, 0));
!             if (i < primary_count - 1)
                  strcat(buf, ",");

              footers[count_footers++] = xstrdup(buf);
          }

-         /* print unique constraints */
-         for (i = 0; i < unique_count; i++)
-         {
-             char       *s = _("Unique keys");
-
-             if (i == 0)
-                 snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result6, i, 0));
-             else
-                 snprintf(buf, sizeof(buf), "%*s  %s", (int) strlen(s), "", PQgetvalue(result6, i, 0));
-             if (i < unique_count - 1)
-                 strcat(buf, ",");
-
-             footers[count_footers++] = xstrdup(buf);
-         }

          /* print constraints */
          for (i = 0; i < constr_count; i++)
--- 773,809 ----
                  trigger_count = PQntuples(result4);
          }

!         footers = xmalloc((index_count + constr_count + rule_count + trigger_count + 1)
                            * sizeof(*footers));

          /* print indexes */
          for (i = 0; i < index_count; i++)
          {
              char       *s = _("Indexes");
!
              if (i == 0)
                  snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result1, i, 0));
              else
                  snprintf(buf, sizeof(buf), "%*s  %s", (int) strlen(s), "", PQgetvalue(result1, i, 0));
!             /* Label primary key or unique (but not both) */
!             if (strcmp(PQgetvalue(result1,i,2),"t") == 0) {
!                 strcat(buf, _(" primary key"));
!             }
!             else if (strcmp(PQgetvalue(result1,i,3),"t") == 0) {
!                 strcat(buf, _(" unique"));
!             }

!             /* Everything after "USING" is echoed verbatim */
!             if (strstr(PQgetvalue(result1,i,1),"USING ")) {
!                 strcat(buf, strstr(strstr(PQgetvalue(result1,i,1),"USING")," "));
!             }

!             if (i < index_count - 1)
                  strcat(buf, ",");

              footers[count_footers++] = xstrdup(buf);
          }


          /* print constraints */
          for (i = 0; i < constr_count; i++)
***************
*** 907,914 ****
          PQclear(result2);
          PQclear(result3);
          PQclear(result4);
-         PQclear(result5);
-         PQclear(result6);
      }

      if (!error)
--- 856,861 ----