Re: [GENERAL] psql's "\d" and CLUSTER

Igor Shevchenko wrote:
> Hi,
>
> Are there any plans on adding CLUSTER-related information to "\d tablename" ?

The attached patch displays cluster information for \d "table" and \di
"index":

    test=> \d test2
         Table "public.test2"
     Column |  Type   | Modifiers
    --------+---------+-----------
     y      | integer | not null
    Indexes:
        "test2_pkey" PRIMARY KEY, btree (y) CLUSTER

    test=> \d test2_pkey
    Index "public.test2_pkey"
     Column |  Type
    --------+---------
     y      | integer
    PRIMARY KEY, btree, for table "public.test2", CLUSTER

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.95
diff -c -c -r1.95 describe.c
*** src/bin/psql/describe.c    22 Mar 2004 03:38:24 -0000    1.95
--- src/bin/psql/describe.c    31 Mar 2004 04:31:36 -0000
***************
*** 831,837 ****
          PGresult   *result;

          printfPQExpBuffer(&buf,
!           "SELECT i.indisunique, i.indisprimary, a.amname, c2.relname,\n"
                        "  pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
                            "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_ama\n" 
                            "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
--- 831,837 ----
          PGresult   *result;

          printfPQExpBuffer(&buf,
!           "SELECT i.indisunique, i.indisprimary, i.indisclustered, a.amname, c2.relname,\n"
                        "  pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
                            "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_ama\n" 
                            "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
***************
*** 850,858 ****
          {
              char       *indisunique = PQgetvalue(result, 0, 0);
              char       *indisprimary = PQgetvalue(result, 0, 1);
!             char       *indamname = PQgetvalue(result, 0, 2);
!             char       *indtable = PQgetvalue(result, 0, 3);
!             char       *indpred = PQgetvalue(result, 0, 4);

              if (strcmp(indisprimary, "t") == 0)
                  printfPQExpBuffer(&tmpbuf, _("PRIMARY KEY, "));
--- 850,859 ----
          {
              char       *indisunique = PQgetvalue(result, 0, 0);
              char       *indisprimary = PQgetvalue(result, 0, 1);
!             char       *indisclustered = PQgetvalue(result, 0, 2);
!             char       *indamname = PQgetvalue(result, 0, 3);
!             char       *indtable = PQgetvalue(result, 0, 4);
!             char       *indpred = PQgetvalue(result, 0, 5);

              if (strcmp(indisprimary, "t") == 0)
                  printfPQExpBuffer(&tmpbuf, _("PRIMARY KEY, "));
***************
*** 869,874 ****
--- 870,878 ----
              if (strlen(indpred))
                  appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);

+             if (strcmp(indisclustered, "t") == 0)
+                 appendPQExpBuffer(&tmpbuf, _(", CLUSTER"));
+
              footers = pg_malloc_zero(2 * sizeof(*footers));
              footers[0] = pg_strdup(tmpbuf.data);
              footers[1] = NULL;
***************
*** 948,954 ****
          if (tableinfo.hasindex)
          {
              printfPQExpBuffer(&buf,
!                      "SELECT c2.relname, i.indisprimary, i.indisunique, "
                                "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)\n"
                                "FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
                                "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
--- 952,958 ----
          if (tableinfo.hasindex)
          {
              printfPQExpBuffer(&buf,
!                      "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, "
                                "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)\n"
                                "FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
                                "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
***************
*** 1080,1093 ****
                               (strcmp(PQgetvalue(result1, i, 2), "t") == 0
                                ? _(" UNIQUE,")
                                : ""));
-
                  /* Everything after "USING" is echoed verbatim */
!                 indexdef = PQgetvalue(result1, i, 3);
                  usingpos = strstr(indexdef, " USING ");
                  if (usingpos)
                      indexdef = usingpos + 7;

                  appendPQExpBuffer(&buf, " %s", indexdef);

                  footers[count_footers++] = pg_strdup(buf.data);
              }
--- 1084,1099 ----
                               (strcmp(PQgetvalue(result1, i, 2), "t") == 0
                                ? _(" UNIQUE,")
                                : ""));
                  /* Everything after "USING" is echoed verbatim */
!                 indexdef = PQgetvalue(result1, i, 4);
                  usingpos = strstr(indexdef, " USING ");
                  if (usingpos)
                      indexdef = usingpos + 7;

                  appendPQExpBuffer(&buf, " %s", indexdef);
+
+                 if (strcmp(PQgetvalue(result1, i, 3), "t") == 0)
+                     appendPQExpBuffer(&buf, _(" CLUSTER"));

                  footers[count_footers++] = pg_strdup(buf.data);
              }