Re: Showing index details with \d on psql - Mailing list pgsql-patches
| From | Greg Sabino Mullane |
|---|---|
| Subject | Re: Showing index details with \d on psql |
| Date | |
| Msg-id | 200110131543.LAA32183@smtp6.mindspring.com Whole thread Raw |
| In response to | Showing index details with \d on psql ("Greg Sabino Mullane" <greg@turnstep.com>) |
| Responses |
Re: Showing index details with \d on psql
(Peter Eisentraut <peter_e@gmx.net>)
|
| List | pgsql-patches |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
..
> Notice the use of the _() macro. Also, looks like we have a
> PRIMARY KEY section in the code now.
New patch applied. I think I was able to wrap my brain around
the new _() macro and use of snprintf this early in the weekend.
:) I removed the separate counts of primary key and unique keys,
and simply added labels to the index list, since that's what
they are anyway.
One thing for someone to look into (I think it's related to the
backend/utils code, which I am not familiar with) is that any
describing of indexes in psql generates an error about
not finding the function 'pg_get_expr'
Sample output from this patch:
Table "b1"
Column | Type | Modifiers
- --------+--------------------------+--------------------------
post | integer | *
thread | smallint | not null *
reply | smallint | not null *
stat | character varying(1) | not null default 'N' * *
uid | integer | not null
ctime | timestamp with time zone | default 'now'
atime | timestamp with time zone |
Indexes abc (post, reply, stat)
def (stat)
foo_pkey [PRIMARY_KEY] (thread)
The code also makes allowances for the table pg_class, just in
case anyone does a \d on it (one of its indexes refers to column
"-2")
Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110131131
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iQA/AwUBO8hhcrybkGcUlkrIEQJtNACggHKbcz6+MXGkR34T/Woe8JyyP8EAoLty
ZxuvaVQQ3H34HYfNWsrDUOwr
=f2Oy
-----END PGP SIGNATURE-----
*** ./src/bin/psql/describe.c.orig Sat Oct 13 10:13:10 2001
--- ./src/bin/psql/describe.c Sat Oct 13 11:17:27 2001
***************
*** 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, i.indkey, 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 c2.relname",
name);
result1 = PSQLexec(buf);
if (!result1)
***************
*** 733,774 ****
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)
{
sprintf(buf, "SELECT rcsrc, rcname\n"
"FROM pg_relcheck r, pg_class c\n"
! "WHERE c.relname='%s' AND c.oid = r.rcrelid",
name);
result2 = PSQLexec(buf);
if (!result2)
--- 729,741 ----
index_count = PQntuples(result1);
}
/* count table (and column) constraints */
if (!error && tableinfo.checks)
{
sprintf(buf, "SELECT rcsrc, rcname\n"
"FROM pg_relcheck r, pg_class c\n"
! "WHERE c.relname='%s' AND c.oid = r.rcrelid\n"
! "ORDER BY rcname",
name);
result2 = PSQLexec(buf);
if (!result2)
***************
*** 783,789 ****
sprintf(buf,
"SELECT r.rulename\n"
"FROM pg_rewrite r, pg_class c\n"
! "WHERE c.relname='%s' AND c.oid = r.ev_class",
name);
result3 = PSQLexec(buf);
if (!result3)
--- 750,757 ----
sprintf(buf,
"SELECT r.rulename\n"
"FROM pg_rewrite r, pg_class c\n"
! "WHERE c.relname='%s' AND c.oid = r.ev_class\n"
! "ORDER BY r.rulename",
name);
result3 = PSQLexec(buf);
if (!result3)
***************
*** 798,804 ****
sprintf(buf,
"SELECT t.tgname\n"
"FROM pg_trigger t, pg_class c\n"
! "WHERE c.relname='%s' AND c.oid = t.tgrelid",
name);
result4 = PSQLexec(buf);
if (!result4)
--- 766,773 ----
sprintf(buf,
"SELECT t.tgname\n"
"FROM pg_trigger t, pg_class c\n"
! "WHERE c.relname='%s' AND c.oid = t.tgrelid\n"
! "ORDER BY t.tgname",
name);
result4 = PSQLexec(buf);
if (!result4)
***************
*** 807,858 ****
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);
}
--- 776,816 ----
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");
! snprintf(buf, sizeof(buf), "%*s %s%s%s",
! (int)strlen(s),
! i == 0 ? _("Indexes") : "",
! PQgetvalue(result1, i, 0),
! strcmp(PQgetvalue(result1, i, 2), "t") == 0 ? _(" [PRIMARY KEY]") : "",
! strcmp(PQgetvalue(result1, i, 3), "t") == 0 ? _(" [UNIQUE]") : "");
!
! char indexchar[5]; /* Should be plenty */
! int indexnumber=0;
! char * indexlist = PQgetvalue(result1, i, 1);
! int j,found;
! for (j=0,found=0;j<=strlen(indexlist); j++) {
! if (indexlist[j] == 0 || indexlist[j] == 32) {
! indexnumber = atoi(indexchar);
! if (indexnumber>0) /* pg_class has a -2! */
! {
! strcat(cells[(indexnumber-1) * cols + 2],
! cells[(indexnumber-1) * cols +2][0] ? " *" : "*");
! strcat(buf, ++found==1 ? " (" : ", ");
! strcat(buf, cells[(indexnumber-1) * cols]);
! }
! indexchar[0] = '\0';
! }
! else { strcat(indexchar,&indexlist[j]); }
! }
! if (found) /* must cover for pg_class again */
! strcat(buf, ")");
footers[count_footers++] = xstrdup(buf);
}
***************
*** 907,914 ****
PQclear(result2);
PQclear(result3);
PQclear(result4);
- PQclear(result5);
- PQclear(result6);
}
if (!error)
--- 865,870 ----
pgsql-patches by date: