Thread: Showing index details with \d on psql
-----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@turnstep.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 */
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@turnstep.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@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > 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. Agreed - it's probably more intuitive for someone to ask "is this column in an index?" and "what does this index cover?" rather than "which index is this column in?" Attached patch is against 7.1.2. As a side benfit, it does multiple stars for columns that belong to multiple indices, which is a nice feature to be able to spot right away. Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200110122041 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iQA/AwUBO8eqrbybkGcUlkrIEQI+BwCeMkkc2UfyJs+vaJNWS9BFSdZInvAAnjIF vRuRPuHJWmQ8dqxorK72wLgM =VdAI -----END PGP SIGNATURE----- *** ./src/bin/psql/describe.c.orig Wed Mar 21 23:00:19 2001 --- ./src/bin/psql/describe.c Fri Oct 12 22:27:25 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, 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", *************** *** 810,826 **** /* 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 */ for (i = 0; i < constr_count; i++) { sprintf(buf, "%s %s", --- 810,847 ---- /* print indices */ for (i = 0; i < index_count; i++) { ! char *indexname, *indexlist; ! indexname = PQgetvalue(result1, i, 0); ! sprintf(buf, "%s %s%s%s", ! index_count == 1 ? "*Index: " : (i == 0 ? "*Indices:" : " "), ! indexname, ! strcmp(PQgetvalue(result1, i, 2), "t") == 0 ? " [PRIMARY KEY]" : "", ! strcmp(PQgetvalue(result1, i, 3), "t") == 0 ? " [UNIQUE]" : "" ); ! ! /* strtokx is overkill here */ ! char indexchar[5]; /* Should be plenty */ ! int indexnumber=0; ! 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]); } ! } ! strcat(buf, ")"); footers[count_footers++] = xstrdup(buf); } ! ! /* print contraints */ for (i = 0; i < constr_count; i++) { sprintf(buf, "%s %s",
> > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > 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. > > Agreed - it's probably more intuitive for someone to ask > "is this column in an index?" and "what does this index cover?" > rather than "which index is this column in?" Attached patch is > against 7.1.2. As a side benfit, it does multiple stars for > columns that belong to multiple indices, which is a nice feature > to be able to spot right away. Looks very good. I have attached the describe.c file from current CVS. Can I ask you to submit a patch against this file? Seems there have been some coding style improvements since 7.2 and I would like your patch to match. Notice the use of the _() macro. Also, looks like we have a PRIMARY KEY section in the code now. Please figure out how your patch mixes in with that. Also, can you send over sample output from the new patch. Thanks. I am uncertain if I can get this into 7.2 because we are so close to beta. This is shaping up into a nice psql addition. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 /* * psql - the PostgreSQL interactive terminal * * Copyright 2000 by PostgreSQL Global Development Group * * $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.40 2001/10/06 14:41:17 momjian Exp $ */ #include "postgres_fe.h" #include "describe.h" #include "libpq-fe.h" #include "common.h" #include "settings.h" #include "print.h" #include "variables.h" #define _(x) gettext((x)) /*---------------- * Handlers for various slash commands displaying some sort of list * of things in the database. * * If you add something here, try to format the query to look nice in -E output. *---------------- */ /* the maximal size of regular expression we'll accept here */ /* (it is safe to just change this here) */ #define REGEXP_CUTOFF (10 * NAMEDATALEN) /* \da * takes an optional regexp to match specific aggregates by name */ bool describeAggregates(const char *name) { char buf[384 + REGEXP_CUTOFF]; PGresult *res; printQueryOpt myopt = pset.popt; /* * There are two kinds of aggregates: ones that work on particular * types ones that work on all */ snprintf(buf, sizeof(buf), "SELECT a.aggname AS \"%s\",\n" " CASE a.aggbasetype\n" " WHEN 0 THEN CAST('%s' AS text)\n" " ELSE format_type(a.aggbasetype, NULL)\n" " END AS \"%s\",\n" " obj_description(a.oid, 'pg_aggregate') as \"%s\"\n" "FROM pg_aggregate a\n", _("Name"), _("(all types)"), _("Data type"), _("Description") ); if (name) { strcat(buf, "WHERE a.aggname ~ '^"); strncat(buf, name, REGEXP_CUTOFF); strcat(buf, "'\n"); } strcat(buf, "ORDER BY 1, 2;"); res = PSQLexec(buf); if (!res) return false; myopt.nullPrint = NULL; myopt.title = _("List of aggregate functions"); printQuery(res, &myopt, pset.queryFout); PQclear(res); return true; } /* \df * Takes an optional regexp to narrow down the function name */ bool describeFunctions(const char *name, bool verbose) { char buf[384 + REGEXP_CUTOFF]; PGresult *res; printQueryOpt myopt = pset.popt; /* * we skip in/out funcs by excluding functions that take some * arguments, but have no types defined for those arguments */ snprintf(buf, sizeof(buf), "SELECT format_type(p.prorettype, NULL) as \"%s\",\n" " p.proname as \"%s\",\n" " oidvectortypes(p.proargtypes) as \"%s\"", _("Result data type"), _("Name"), _("Argument data types") ); if (verbose) snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), ",\n u.usename as \"%s\",\n" " l.lanname as \"%s\",\n" " p.prosrc as \"%s\",\n" " obj_description(p.oid, 'pg_proc') as \"%s\"", _("Owner"), _("Language"), _("Source code"), _("Description") ); if (!verbose) strcat(buf, "\nFROM pg_proc p\n" "WHERE p.prorettype <> 0 AND (pronargs = 0 OR oidvectortypes(p.proargtypes) <> '')\n"); else strcat(buf, "\nFROM pg_proc p, pg_language l, pg_user u\n" "WHERE p.prolang = l.oid AND p.proowner = u.usesysid\n" " AND p.prorettype <> 0 AND (pronargs = 0 OR oidvectortypes(p.proargtypes) <> '')\n"); if (name) { strcat(buf, " AND p.proname ~ '^"); strncat(buf, name, REGEXP_CUTOFF); strcat(buf, "'\n"); } strcat(buf, "ORDER BY 2, 1, 3;"); res = PSQLexec(buf); if (!res) return false; myopt.nullPrint = NULL; myopt.title = _("List of functions"); printQuery(res, &myopt, pset.queryFout); PQclear(res); return true; } /* * \dT * describe types */ bool describeTypes(const char *name, bool verbose) { char buf[384 + 2 * REGEXP_CUTOFF]; PGresult *res; printQueryOpt myopt = pset.popt; snprintf(buf, sizeof(buf), "SELECT format_type(t.oid, NULL) AS \"%s\",\n", _("Name") ); if (verbose) snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), " t.typname AS \"%s\",\n" " CASE WHEN t.typlen = -1\n" " THEN CAST('var' AS text)\n" " ELSE CAST(t.typlen AS text)\n" " END AS \"%s\",\n", _("Internal name"), _("Size") ); snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), " obj_description(t.oid, 'pg_type') as \"%s\"\n", _("Description") ); /* * do not include array types (start with underscore), do not include * user relations (typrelid!=0) */ strcat(buf, "FROM pg_type t\nWHERE t.typrelid = 0 AND t.typname !~ '^_.*'\n"); if (name) { /* accept either internal or external type name */ strcat(buf, " AND (format_type(t.oid, NULL) ~ '^"); strncat(buf, name, REGEXP_CUTOFF); strcat(buf, "' OR t.typname ~ '^"); strncat(buf, name, REGEXP_CUTOFF); strcat(buf, "')\n"); } strcat(buf, "ORDER BY 1;"); res = PSQLexec(buf); if (!res) return false; myopt.nullPrint = NULL; myopt.title = _("List of data types"); printQuery(res, &myopt, pset.queryFout); PQclear(res); return true; } /* \do */ bool describeOperators(const char *name) { char buf[384 + REGEXP_CUTOFF]; PGresult *res; printQueryOpt myopt = pset.popt; snprintf(buf, sizeof(buf), "SELECT o.oprname AS \"%s\",\n" " CASE WHEN o.oprkind='l' THEN NULL ELSE format_type(o.oprleft, NULL) END AS \"%s\",\n" " CASE WHEN o.oprkind='r' THEN NULL ELSE format_type(o.oprright, NULL) END AS \"%s\",\n" " format_type(p.prorettype, NULL) AS \"%s\",\n" " obj_description(p.oid, 'pg_proc') as \"%s\"\n" "FROM pg_proc p, pg_operator o\n" "WHERE RegprocToOid(o.oprcode) = p.oid\n", _("Name"), _("Left arg type"), _("Right arg type"), _("Result type"), _("Description") ); if (name) { strcat(buf, " AND o.oprname = '"); strncat(buf, name, REGEXP_CUTOFF); strcat(buf, "'\n"); } strcat(buf, "ORDER BY 1, 2, 3, 4;"); res = PSQLexec(buf); if (!res) return false; myopt.nullPrint = NULL; myopt.title = _("List of operators"); printQuery(res, &myopt, pset.queryFout); PQclear(res); return true; } /* * listAllDbs * * for \l, \list, and -l switch */ bool listAllDbs(bool desc) { PGresult *res; char buf[1024]; printQueryOpt myopt = pset.popt; snprintf(buf, sizeof(buf), "SELECT d.datname as \"%s\",\n" " u.usename as \"%s\"", _("Name"), _("Owner")); #ifdef MULTIBYTE snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), ",\n pg_encoding_to_char(d.encoding) as \"%s\"", _("Encoding")); #endif if (desc) snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), ",\n obj_description(d.oid, 'pg_database') as \"%s\"", _("Description")); strcat(buf, "\nFROM pg_database d LEFT JOIN pg_user u ON d.datdba = u.usesysid\n" "ORDER BY 1;"); res = PSQLexec(buf); if (!res) return false; myopt.nullPrint = NULL; myopt.title = _("List of databases"); printQuery(res, &myopt, pset.queryFout); PQclear(res); return true; } /* * List Tables Grant/Revoke Permissions * \z (now also \dp -- perhaps more mnemonic) */ bool permissionsList(const char *name) { char buf[256 + REGEXP_CUTOFF]; PGresult *res; printQueryOpt myopt = pset.popt; /* Currently, we ignore indexes since they have no meaningful rights */ snprintf(buf, sizeof(buf), "SELECT relname as \"%s\",\n" " relacl as \"%s\"\n" "FROM pg_class\n" "WHERE relkind in ('r', 'v', 'S') AND\n" " relname NOT LIKE 'pg$_%%' ESCAPE '$'\n", _("Table"), _("Access privileges")); if (name) { strcat(buf, " AND relname ~ '^"); strncat(buf, name, REGEXP_CUTOFF); strcat(buf, "'\n"); } strcat(buf, "ORDER BY 1;"); res = PSQLexec(buf); if (!res) return false; myopt.nullPrint = NULL; sprintf(buf, _("Access privileges for database \"%s\""), PQdb(pset.db)); myopt.title = buf; printQuery(res, &myopt, pset.queryFout); PQclear(res); return true; } /* * Get object comments * * \dd [foo] * * Note: This only lists things that actually have a description. For complete * lists of things, there are other \d? commands. */ bool objectDescription(const char *object) { char descbuf[2048 + REGEXP_CUTOFF]; PGresult *res; printQueryOpt myopt = pset.popt; snprintf(descbuf, sizeof(descbuf), "SELECT DISTINCT tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n" "FROM (\n" /* Aggregate descriptions */ " SELECT a.oid as oid, a.tableoid as tableoid,\n" " CAST(a.aggname AS text) as name, CAST('%s' AS text) as object\n" " FROM pg_aggregate a\n" /* Function descriptions (except in/outs for datatypes) */ "UNION ALL\n" " SELECT p.oid as oid, p.tableoid as tableoid,\n" " CAST(p.proname AS text) as name, CAST('%s' AS text) as object\n" " FROM pg_proc p\n" " WHERE p.pronargs = 0 or oidvectortypes(p.proargtypes) <> ''\n" /* Operator descriptions (must get comment via associated function) */ "UNION ALL\n" " SELECT RegprocToOid(o.oprcode) as oid,\n" " (SELECT oid FROM pg_class WHERE relname = 'pg_proc') as tableoid,\n" " CAST(o.oprname AS text) as name, CAST('%s' AS text) as object\n" " FROM pg_operator o\n" /* Type description */ "UNION ALL\n" " SELECT t.oid as oid, t.tableoid as tableoid,\n" " format_type(t.oid, NULL) as name, CAST('%s' AS text) as object\n" " FROM pg_type t\n" /* Relation (tables, views, indexes, sequences) descriptions */ "UNION ALL\n" " SELECT c.oid as oid, c.tableoid as tableoid,\n" " CAST(c.relname AS text) as name,\n" " CAST(\n" " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END" " AS text) as object\n" " FROM pg_class c\n" /* Rule description (ignore rules for views) */ "UNION ALL\n" " SELECT r.oid as oid, r.tableoid as tableoid,\n" " CAST(r.rulename AS text) as name, CAST('%s' AS text) as object\n" " FROM pg_rewrite r\n" " WHERE r.rulename !~ '^_RET'\n" /* Trigger description */ "UNION ALL\n" " SELECT t.oid as oid, t.tableoid as tableoid,\n" " CAST(t.tgname AS text) as name, CAST('%s' AS text) as object\n" " FROM pg_trigger t\n" ") AS tt,\n" "pg_description d\n" "WHERE tt.oid = d.objoid and tt.tableoid = d.classoid and d.objsubid = 0\n", _("Name"), _("Object"), _("Description"), _("aggregate"), _("function"), _("operator"), _("data type"), _("table"), _("view"), _("index"), _("sequence"), _("rule"), _("trigger") ); if (object) { strcat(descbuf, " AND tt.name ~ '^"); strncat(descbuf, object, REGEXP_CUTOFF); strcat(descbuf, "'\n"); } strcat(descbuf, "ORDER BY 1;"); res = PSQLexec(descbuf); if (!res) return false; myopt.nullPrint = NULL; myopt.title = _("Object descriptions"); printQuery(res, &myopt, pset.queryFout); PQclear(res); return true; } /* * describeTableDetails (for \d) * * Unfortunately, the information presented here is so complicated that it cannot * be done in a single query. So we have to assemble the printed table by hand * and pass it to the underlying printTable() function. * */ static void * xmalloc(size_t size) { void *tmp; tmp = malloc(size); if (!tmp) { psql_error("out of memory\n"); exit(EXIT_FAILURE); } return tmp; } bool describeTableDetails(const char *name, bool desc) { char buf[512 + INDEX_MAX_KEYS * NAMEDATALEN]; PGresult *res = NULL; printTableOpt myopt = pset.popt.topt; int i; const char *view_def = NULL; const char *headers[5]; char **cells = NULL; char *title = NULL; char **footers = NULL; char **ptr; unsigned int cols; struct { bool hasindex; char relkind; int16 checks; int16 triggers; bool hasrules; } tableinfo; bool error = false; /* truncate table name */ if (strlen(name) > NAMEDATALEN) { char *my_name = xmalloc(NAMEDATALEN + 1); strncpy(my_name, name, NAMEDATALEN); my_name[NAMEDATALEN] = '\0'; name = my_name; } /* Get general table info */ sprintf(buf, "SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules\n" "FROM pg_class WHERE relname='%s'", name); res = PSQLexec(buf); if (!res) return false; /* Did we get anything? */ if (PQntuples(res) == 0) { if (!QUIET()) fprintf(stderr, _("Did not find any relation named \"%s\".\n"), name); PQclear(res); return false; } /* FIXME: check for null pointers here? */ tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 0), "t") == 0; tableinfo.relkind = *(PQgetvalue(res, 0, 1)); tableinfo.checks = atoi(PQgetvalue(res, 0, 2)); tableinfo.triggers = atoi(PQgetvalue(res, 0, 3)); tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), "t") == 0; PQclear(res); headers[0] = _("Column"); headers[1] = _("Type"); cols = 2; if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v') { cols++; headers[cols - 1] = _("Modifiers"); } if (desc) { cols++; headers[cols - 1] = _("Description"); } headers[cols] = NULL; /* Get column info */ strcpy(buf, "SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum"); if (desc) strcat(buf, ", col_description(a.attrelid, a.attnum)"); strcat(buf, "\nFROM pg_class c, pg_attribute a\n" "WHERE c.relname = '"); strncat(buf, name, NAMEDATALEN); strcat(buf, "'\n AND a.attnum > 0 AND a.attrelid = c.oid\n" "ORDER BY a.attnum"); res = PSQLexec(buf); if (!res) return false; /* Check if table is a view */ if (tableinfo.hasrules) { PGresult *result; sprintf(buf, "SELECT definition FROM pg_views WHERE viewname = '%s'", name); result = PSQLexec(buf); if (!result) { PQclear(res); PQclear(result); return false; } if (PQntuples(result) > 0) view_def = xstrdup(PQgetvalue(result, 0, 0)); PQclear(result); } /* Generate table cells to be printed */ cells = xmalloc((PQntuples(res) * cols + 1) * sizeof(*cells)); cells[PQntuples(res) * cols] = NULL; /* end of list */ for (i = 0; i < PQntuples(res); i++) { /* Name */ cells[i * cols + 0] = PQgetvalue(res, i, 0); /* don't free this * afterwards */ /* Type */ cells[i * cols + 1] = PQgetvalue(res, i, 1); /* don't free this * either */ /* Extra: not null and default */ /* (I'm cutting off the 'default' string at 128) */ if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v') { cells[i * cols + 2] = xmalloc(128 + 128); cells[i * cols + 2][0] = '\0'; if (strcmp(PQgetvalue(res, i, 2), "t") == 0) strcat(cells[i * cols + 2], "not null"); /* handle "default" here */ if (strcmp(PQgetvalue(res, i, 3), "t") == 0) { PGresult *result; sprintf(buf, "SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c\n" "WHERE c.relname = '%s' AND c.oid = d.adrelid AND d.adnum = %s", name, PQgetvalue(res, i, 4)); result = PSQLexec(buf); if (!result) error = true; else { if (cells[i * cols + 2][0]) strcat(cells[i * cols + 2], " "); strcat(cells[i * cols + 2], "default "); strcat(cells[i * cols + 2], PQgetvalue(result, 0, 0)); PQclear(result); } } } if (error) break; /* Description */ if (desc) cells[i * cols + cols - 1] = PQgetvalue(res, i, 5); } /* Make title */ title = xmalloc(32 + NAMEDATALEN); switch (tableinfo.relkind) { case 'r': snprintf(title, 32 + NAMEDATALEN, _("Table \"%s\""), name); break; case 'v': snprintf(title, 32 + NAMEDATALEN, _("View \"%s\""), name); break; case 'S': snprintf(title, 32 + NAMEDATALEN, _("Sequence \"%s\""), name); break; case 'i': snprintf(title, 32 + NAMEDATALEN, _("Index \"%s\""), name); break; case 's': snprintf(title, 32 + NAMEDATALEN, _("Special relation \"%s\""), name); break; case 't': snprintf(title, 32 + NAMEDATALEN, _("TOAST table \"%s\""), name); break; default: snprintf(title, 32 + NAMEDATALEN, _("?%c? \"%s\""), tableinfo.relkind, name); break; } /* Make footers */ if (tableinfo.relkind == 'i') { /* Footer information about an index */ 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); result = PSQLexec(buf); if (!result || PQntuples(result) != 1) error = true; else { char *indisunique = PQgetvalue(result, 0, 0); char *indisprimary = PQgetvalue(result, 0, 1); char *indamname = PQgetvalue(result, 0, 2); char *indpred = PQgetvalue(result, 0, 3); footers = xmalloc(3 * sizeof(*footers)); /* XXX This construction is poorly internationalized. */ footers[0] = xmalloc(NAMEDATALEN + 128); snprintf(footers[0], NAMEDATALEN + 128, "%s%s", strcmp(indisunique, "t") == 0 ? _("unique ") : "", indamname); if (strcmp(indisprimary, "t") == 0) snprintf(footers[0] + strlen(footers[0]), NAMEDATALEN + 128 - strlen(footers[0]), _(" (primary key)")); if (strlen(indpred) > 0) { footers[1] = xmalloc(64 + strlen(indpred)); snprintf(footers[1], 64 + strlen(indpred), _("Index predicate: %s"), indpred); footers[2] = NULL; } else footers[1] = NULL; } PQclear(result); } else if (view_def) { /* Footer information about a view */ footers = xmalloc(2 * sizeof(*footers)); footers[0] = xmalloc(64 + strlen(view_def)); snprintf(footers[0], 64 + strlen(view_def), _("View definition: %s"), view_def); footers[1] = NULL; } else if (tableinfo.relkind == 'r') { /* Footer information about a table */ 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; int count_footers = 0; /* 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) error = true; else 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) error = true; else constr_count = PQntuples(result2); } /* count rules */ if (!error && tableinfo.hasrules) { 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) error = true; else rule_count = PQntuples(result3); } /* count triggers */ if (!error && tableinfo.triggers) { 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) error = true; else 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++) { char *s = _("Check constraints"); if (i == 0) snprintf(buf, sizeof(buf), _("%s: \"%s\" %s"), s, PQgetvalue(result2, i, 1), PQgetvalue(result2, i, 0)); else snprintf(buf, sizeof(buf), _("%*s \"%s\" %s"), (int)strlen(s), "", PQgetvalue(result2, i, 1), PQgetvalue(result2, i, 0)); footers[count_footers++] = xstrdup(buf); } /* print rules */ for (i = 0; i < rule_count; i++) { char *s = _("Rules"); if (i == 0) snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result3, i, 0)); else snprintf(buf, sizeof(buf), "%*s %s", (int)strlen(s), "", PQgetvalue(result3, i, 0)); if (i < rule_count - 1) strcat(buf, ","); footers[count_footers++] = xstrdup(buf); } /* print triggers */ for (i = 0; i < trigger_count; i++) { char *s = _("Triggers"); if (i == 0) snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result4, i, 0)); else snprintf(buf, sizeof(buf), "%*s %s", (int)strlen(s), "", PQgetvalue(result4, i, 0)); if (i < trigger_count - 1) strcat(buf, ","); footers[count_footers++] = xstrdup(buf); } /* end of list marker */ footers[count_footers] = NULL; PQclear(result1); PQclear(result2); PQclear(result3); PQclear(result4); PQclear(result5); PQclear(result6); } if (!error) printTable(title, headers, (const char **) cells, (const char **) footers, "llll", &myopt, pset.queryFout); /* clean up */ free(title); for (i = 0; i < PQntuples(res); i++) { if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v') free(cells[i * cols + 2]); } free(cells); for (ptr = footers; footers && *ptr; ptr++) free(*ptr); free(footers); PQclear(res); return !error; } /* * \du [user] * * Describes users, possibly based on a simplistic prefix search on the * argument. */ bool describeUsers (const char *name) { char buf[384 + REGEXP_CUTOFF]; PGresult *res; printQueryOpt myopt = pset.popt; snprintf(buf, sizeof(buf), "SELECT u.usename AS \"%s\",\n" " u.usesysid AS \"%s\",\n" " CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('%s' AS text)\n" " WHEN u.usesuper THEN CAST('%s' AS text)\n" " WHEN u.usecreatedb THEN CAST('%s' AS text)\n" " ELSE CAST('' AS text)\n" " END AS \"%s\"\n" "FROM pg_user u\n", _("User name"), _("User ID"), _("superuser, create database"), _("superuser"), _("create database"), _("Attributes") ); if (name) { strcat(buf, "WHERE u.usename ~ '^"); strncat(buf, name, REGEXP_CUTOFF); strcat(buf, "'\n"); } strcat(buf, "ORDER BY 1;"); res = PSQLexec(buf); if (!res) return false; myopt.nullPrint = NULL; myopt.title = _("List of database users"); printQuery(res, &myopt, pset.queryFout); PQclear(res); return true; } /* * listTables() * * handler for \d, \dt, etc. * * The infotype is an array of characters, specifying what info is desired: * t - tables * i - indexes * v - views * s - sequences * S - systems tables (~ '^pg_') * (any order of the above is fine) * * Note: For some reason it always happens to people that their tables have owners * that are no longer in pg_user; consequently they wouldn't show up here. The code * tries to fix this the painful way, hopefully outer joins will be done sometime. */ bool listTables(const char *infotype, const char *name, bool desc) { bool showTables = strchr(infotype, 't') != NULL; bool showIndexes = strchr(infotype, 'i') != NULL; bool showViews = strchr(infotype, 'v') != NULL; bool showSeq = strchr(infotype, 's') != NULL; bool showSystem = strchr(infotype, 'S') != NULL; char buf[3072 + 8 * REGEXP_CUTOFF]; PGresult *res; printQueryOpt myopt = pset.popt; if (showSystem && !(showSeq || showIndexes || showViews || showTables)) showTables = showViews = showSeq = true; buf[0] = '\0'; snprintf(buf, sizeof(buf), "SELECT c.relname as \"%s\",\n" " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's' THEN'%s' END as \"%s\",\n" " u.usename as \"%s\"", _("Name"), _("table"), _("view"), _("index"), _("sequence"), _("special"), _("Type"), _("Owner")); if (desc) snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), ",\n obj_description(c.oid, 'pg_class') as \"%s\"", _("Description")); strcat(buf, "\nFROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid\n" "WHERE c.relkind IN ("); if (showTables) strcat(buf, "'r',"); if (showViews) strcat(buf, "'v',"); if (showIndexes) strcat(buf, "'i',"); if (showSeq) strcat(buf, "'S',"); if (showSystem && showTables) strcat(buf, "'s',"); strcat(buf, "''"); /* dummy */ strcat(buf, ")\n"); if (showSystem) strcat(buf, " AND c.relname ~ '^pg_'\n"); else strcat(buf, " AND c.relname !~ '^pg_'\n"); if (name) { strcat(buf, " AND c.relname ~ '^"); strncat(buf, name, REGEXP_CUTOFF); strcat(buf, "'\n"); } strcat(buf, "ORDER BY 1;"); res = PSQLexec(buf); if (!res) return false; if (PQntuples(res) == 0 && !QUIET()) { if (name) fprintf(pset.queryFout, _("No matching relations found.\n")); else fprintf(pset.queryFout, _("No relations found.\n")); } else { myopt.nullPrint = NULL; myopt.title = _("List of relations"); printQuery(res, &myopt, pset.queryFout); } PQclear(res); return true; }
-----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 ----
Greg Sabino Mullane writes: > 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) Please use "(primary key)" and add a colon after "Indexes". I don't like the '*' things. They look ugly and convey little real information. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Please use "(primary key)" and add a colon after "Indexes".
Done and done. I capitalized and used brackets to make it
stand out more from the index contents, but it is now changed
back.
> I don't like the '*' things. They look ugly and convey little
> real information.
They convey "this column is indexed" and also indicate in how many
indexes it appears.
Anyway, revised patch attached.
Revised sample output:
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)
Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110131806
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iQA/AwUBO8i8YrybkGcUlkrIEQI1ngCcCtzjcBB8hMrBsAh+wbSsExJCpUYAoP4E
FWCa51FCDi1BP8zTgdujnDlu
=4P3T
-----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 ? s : "",
! 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 ----
>> I don't like the '*' things. They look ugly and convey little
>> real information.
> They convey "this column is indexed" and also indicate in how many
> indexes it appears.
I tend to agree with Peter on that part ... the asterisks add more
clutter than information. I also think that they could lead to
ambiguity; for example, it's not obvious that the * is not part of
the default clause where there's a default.
I have a large number of problems with this part of your patch:
char *s = _("Indexes:");
! snprintf(buf, sizeof(buf), "%*s %s%s%s",
! (int)strlen(s),
! i == 0 ? s : "",
! 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);
Gripe #1: declarations after the start of a block are a C++-ism. They
are not legal in ANSI C.
Gripe #2: what is indexchar[], why is it being used without
initialization, and what is your justification for thinking 5 is enough
space?
Gripe #3: "32" is not a portable spelling of "' '".
Gripe #4: looks to me like it will fail when indexes are on columns
numbered 10 or above, because the loop will do strcat() multiple times.
Gripe #5: doing the wrong thing on indexes that mention system columns
(negative column numbers) isn't acceptable.
You are really doing things quite the hard way here anyhow, since
pg_get_indexdef would produce the info you want without so much work,
and with less dependency in psql on backend catalog details. I'd
suggest pulling the pg_get_indexdef result instead of indkey in the
SELECT, and then just print the part after USING.
BTW, "primary key" implies "unique", so I think it's not necessary to
print both annotations for a primary key.
regards, tom lane
Greg Sabino Mullane writes:
> > I don't like the '*' things. They look ugly and convey little
> > real information.
>
> They convey "this column is indexed" and also indicate in how many
> indexes it appears.
I understood that, but a new user would not without a footnote. And
obviously, '*' is just an arbitrary character -- what would we use next to
say "this column is part of a table constraint"? I think this part is
very useful as it stands:
> Indexes: abc (post, reply, stat)
> def (stat)
> foo_pkey (primary key) (thread)
Hmm, shouldn't that be "foo_pkey(thread) (primary key)". I understand
what you meant about the brackets, but brackets aren't typically used in
writing and look artificial. Maybe something like
foo_pkey (thread) -- primary key
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter Eisentraut <peter_e@gmx.net> writes:
> Hmm, shouldn't that be "foo_pkey(thread) (primary key)". I understand
> what you meant about the brackets, but brackets aren't typically used in
> writing and look artificial. Maybe something like
> foo_pkey (thread) -- primary key
Or
primary key: foo_pkey (thread)
unique: foo_serial_key (serialcol)
Note my advice nearby for Greg to make use of pg_get_indexdef, which
returns a CREATE INDEX command string for the index. I have an ulterior
motive here, which is that pg_get_indexdef will deliver useful
information about functional and partial indexes, and I think \d ought
to show that stuff.
In the case of a partial index, anything tacked onto the end of the line
is likely to be confused with the partial index condition. Consider
these examples, all obtained from the regression database with
select pg_get_indexdef(oid) from pg_class where relkind='i';
CREATE INDEX onek2_u2_prtl ON onek2 USING btree (unique2) WHERE (stringu1 < 'B'::name)
CREATE INDEX rect2ind ON fast_emp4000 USING rtree (home_base bigbox_ops)
CREATE UNIQUE INDEX func_index_index ON func_index_heap USING btree (textcat(f1, f2))
I suggest that \d should produce entries like this:
Indexes: onek2_u2_prtl btree (unique2) WHERE (stringu1 < 'B'::name)
Indexes: rect2ind rtree (home_base bigbox_ops)
Indexes: unique: func_index_index btree (textcat(f1, f2))
or possibly this would read better:
Indexes: func_index_index unique btree (textcat(f1, f2))
(s/unique/primary key/ where appropriate).
regards, tom lane
> You are really doing things quite the hard way here anyhow, since > pg_get_indexdef would produce the info you want without so much work, > and with less dependency in psql on backend catalog details. I'd > suggest pulling the pg_get_indexdef result instead of indkey in the > SELECT, and then just print the part after USING. Quick question - where are all these handy 'pg_get_indexdef' and friends functions documented??? Chris
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > > Quick question - where are all these handy 'pg_get_indexdef' and friends > > functions documented??? > > They ain't :-(. Feel free to contribute some documentation patches... When did they appear? Are they 7.2-devel only, or are they hidden in 7.1.3 as well? I ask because I'm a committer on the phpPgAdmin (WebPg) project and I've never heard of them! They seem like they'd be helpful in replacing some of our 20 line sql statements! Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > Quick question - where are all these handy 'pg_get_indexdef' and friends > functions documented??? >> >> They ain't :-(. Feel free to contribute some documentation patches... > When did they appear? Are they 7.2-devel only, or are they hidden in 7.1.3 > as well? pg_get_indexdef goes back to 6.4, according to the cvs logs at http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ruleutils.c regards, tom lane
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Quick question - where are all these handy 'pg_get_indexdef' and friends
> functions documented???
They ain't :-(. Feel free to contribute some documentation patches...
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I understood that, but a new user would not without a footnote. And > obviously, '*' is just an arbitrary character... Hey, the asterick was Bruce's idea! :) Consider it dropped. > foo_pkey (thread) -- primary key I'll put that and the other suggestions by Tom Lane into my next patch, when I have time for it. Please don't let my possible acquirement of free time in the future dissuade others from knocking the patch out. Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200110171426 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iQA/AwUBO88e+bybkGcUlkrIEQKfLgCfRhobqXuWMwBhqSjl+5p391IYxvEAnjLh KGaka5SSVF87brjN6joSy7us =jv4S -----END PGP SIGNATURE-----
Peter Eisentraut <peter_e@gmx.net> writes:
> Christopher Kings-Lynne writes:
>> Quick question - where are all these handy 'pg_get_indexdef' and friends
>> functions documented???
> I think they aren't documented because we'd rather not have them exposed
> to users, since they might change whenever it seems convenient. Whether
> this is a good strategy is another question.
My take is that they aren't documented because Jan didn't bother to
document them :-(. I think we're much better off encouraging
applications to use these functions rather than burrowing around
in the system catalogs for themselves, which seems the only alternative.
regards, tom lane
Christopher Kings-Lynne writes: > Quick question - where are all these handy 'pg_get_indexdef' and friends > functions documented??? I think they aren't documented because we'd rather not have them exposed to users, since they might change whenever it seems convenient. Whether this is a good strategy is another question. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Greg, do you have an updated version of the patch you would like applied to 7.3? --------------------------------------------------------------------------- Greg Sabino Mullane wrote: > my next patch, when I have time for it. Please don't let my > possible acquirement of free time in the future dissuade > others from knocking the patch out. > > Greg Sabino Mullane > greg@turnstep.com > PGP Key: 0x14964AC8 200110171426 -- End of PGP signed section, PGP failed! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Patch applied. Thanks. --------------------------------------------------------------------------- Greg Sabino Mullane wrote: [ There is text before PGP section. ] > -- Start of PGP signed section. > > > Greg, do you have an updated version of the patch you would like > > applied to 7.3 > > Yes, I think this one should work: > > http://www.gtsm.com/postgres/showindex2.patch > > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 200202221455 -- End of PGP signed section. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026