Re: Showing index details with \d on psql

I like the idea of this but the format, as you say, could use
improvement. Can I recommend having a "*" next to the columns involved
in the index and have the column names listed next to the index names?
That would look better and be clearer, I think. Perhaps:

Table "mytable"
Attribute | Type | Modifier

- -----------+-----------------------+-----------------------------
post | integer | not null *
thread | smallint | *
reply | smallint | not null *
subject | character varying(60) | default 'foo' *
*Indices: 1. mytable_foobar (post) (PRIMARY KEY)
2. alphabet (subject, thread)
3. badname (thread)

---------------------------------------------------------------------------

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> While hacking around with psql, I came up with the idea of
> identifying the columns in a table as to which index they
> are in (and conversly, showing which columns an index
> contains). I find this useful because a normal listing that
> only tells you the name of the index is not very helpful
> and usually needs a separate \d index_name entry. The only
> concern is how to do it:
> as a separate "index" column?
> appending the name of the index to the Description column?
> numbering the indexes and using the number to save space?
> (The latter is used in the enclosed patch and
> example below). Which columns the index affects follows the
> name, and it also tells you if an index is a primary key. Here
> is some sample output:
>
> data=> \d mytable
> Table "mytable"
>
> Table "mytable"
> Attribute | Type | Modifier
>
> - -----------+-----------------------+-----------------------------
> post | integer | not null (index #1)
> thread | smallint | (index #2) (index #3)
> reply | smallint | not null
> subject | character varying(60) | default 'foo' (index #2)
> Indices: 1. mytable_foobar (1) (PRIMARY KEY)
> 2. alphabet (4 2)
> 3. badname (2)
>
>
> The numbers at the end of the index names are ugly, but it does
> show you instantly the composition and order of the index. I
> think once you get used to it, it can be very valuable and
> save on calls to \d index_name. My big concern is the size that
> each "(index #x)" takes up, but having them separate does make
> them stand out more, and in most cases, columns will not belong
> to a lot of indices.
>
> The attached (rough) patch is against 7.1.2. Feedback
> welcome, as always. :)
>
> Greg Sabino Mullane
> greg(at)turnstep(dot)com
> PGP Key: 0x14964AC8 200110062052
>
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iQA/AwUBO7+rPrybkGcUlkrIEQKz7gCcCyPUDAGGwMbwPa09Rc2pqMbD0cYAn1qY
> Yw6/kJdux/vwdN4waU5rdPmH
> =/PN6
> -----END PGP SIGNATURE-----

> *** ./src/bin/psql/describe.c.orig Wed Mar 21 23:00:19 2001
> --- ./src/bin/psql/describe.c Sat Oct 6 20:46:45 2001
> ***************
> *** 748,754 ****
> /* count indices */
> 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"
> "ORDER BY c2.relname",
> --- 748,754 ----
> /* count indices */
> if (!error && tableinfo.hasindex)
> {
> ! sprintf(buf, "SELECT c2.relname, i.indkey, i.indisprimary\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 c2.relname",
> ***************
> *** 810,823 ****
> /* print indices */
> for (i = 0; i < index_count; i++)
> {
> ! sprintf(buf, "%s %s",
> ! index_count == 1 ? "Index:" : (i == 0 ? "Indices:" : " "),
> ! PQgetvalue(result1, i, 0)
> );
> - if (i < index_count - 1)
> - strcat(buf, ",");
>
> footers[count_footers++] = xstrdup(buf);
> }
>
> /* print contraints */
> --- 810,845 ----
> /* print indices */
> for (i = 0; i < index_count; i++)
> {
> ! char *indexname, *indexlist;
> ! indexname = PQgetvalue(result1, i, 0);
> ! indexlist = PQgetvalue(result1, i, 1);
> ! sprintf(buf, "%s %3d. %s (%s)%s",
> ! index_count == 1 ? "Index:" : (i == 0 ? "Indices:" : " "),i+1,
> ! indexname,indexlist,
> ! strcmp(PQgetvalue(result1, i, 2), "t") == 0 ? " (PRIMARY KEY)" : ""
> );
>
> footers[count_footers++] = xstrdup(buf);
> +
> + /* strtokx is overkill here */
> + int j;
> + char dummy[6]; /* Should be plenty */
> + char showindex[10+31];
> + int bar=0;
> + for (j=0; j<=strlen(indexlist); j++) {
> + if (indexlist[j]==0 || indexlist[j]==32) {
> + bar = atoi(dummy);
> + if (bar>0) /* pg_class has a -2! */
> + {
> + sprintf(showindex, "(index #%d)", i+1);
> + if (cells[(bar-1) * cols + 2][0])
> + strcat(cells[(bar-1) * cols + 2], " ");
> + strcat(cells[(bar-1) * cols + 2], showindex);
> + }
> + dummy[0] = '\0';
> + }
> + else { strcat(dummy,&indexlist[j]); }
> + }
> }
>
> /* print contraints */

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026