aboutsummaryrefslogtreecommitdiff
path: root/src/bin/psql/describe.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/bin/psql/describe.c')
-rw-r--r--src/bin/psql/describe.c761
1 files changed, 485 insertions, 276 deletions
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 6af2b06a8b5..b0b4f9d48d4 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -34,55 +34,60 @@
* takes an optional regexp to match specific aggregates by name
*/
bool
-describeAggregates(const char *name, PsqlSettings *pset)
+describeAggregates(const char *name, PsqlSettings *pset, bool verbose, bool desc)
{
- char descbuf[384 + 2 * REGEXP_CUTOFF]; /* observe/adjust this
- * if you change the
- * query */
+ char buf[384 + 2 * REGEXP_CUTOFF];
PGresult *res;
- bool description = GetVariableBool(pset->vars, "description");
printQueryOpt myopt = pset->popt;
- descbuf[0] = '\0';
-
/*
* There are two kinds of aggregates: ones that work on particular
* types ones that work on all
*/
- strcat(descbuf,
+ strcpy(buf,
"SELECT a.aggname AS \"Name\", t.typname AS \"Type\"");
- if (description)
- strcat(descbuf,
- ",\n obj_description(a.oid) as \"Description\"");
- strcat(descbuf,
- "\nFROM pg_aggregate a, pg_type t\n"
- "WHERE a.aggbasetype = t.oid\n");
+ if (verbose)
+ strcat(buf, " ,u.usename as \"Owner\"");
+ if (desc)
+ strcat(buf, ",\n obj_description(a.oid) as \"Description\"");
+ strcat(buf, !verbose ?
+ ("\nFROM pg_aggregate a, pg_type t\n"
+ "WHERE a.aggbasetype = t.oid\n") :
+ ("\nFROM pg_aggregate a, pg_type t, pg_user u\n"
+ "WHERE a.aggbasetype = t.oid AND a.aggowner = u.usesysid\n")
+ );
+
if (name)
{
- strcat(descbuf, " AND a.aggname ~* '^");
- strncat(descbuf, name, REGEXP_CUTOFF);
- strcat(descbuf, "'\n");
+ strcat(buf, " AND a.aggname ~* '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
- strcat(descbuf,
+ strcat(buf,
"UNION\n"
"SELECT a.aggname AS \"Name\", '(all types)' as \"Type\"");
- if (description)
- strcat(descbuf,
+ if (verbose)
+ strcat(buf, " ,u.usename as \"Owner\"");
+ if (desc)
+ strcat(buf,
",\n obj_description(a.oid) as \"Description\"");
- strcat(descbuf,
- "\nFROM pg_aggregate a\n"
- "WHERE a.aggbasetype = 0\n");
+ strcat(buf, !verbose ?
+ ("\nFROM pg_aggregate a\n"
+ "WHERE a.aggbasetype = 0\n") :
+ ("\nFROM pg_aggregate a, pg_user u\n"
+ "WHERE a.aggbasetype = 0 AND a.aggowner = u.usesysid\n")
+ );
if (name)
{
- strcat(descbuf, " AND a.aggname ~* '^");
- strncat(descbuf, name, REGEXP_CUTOFF);
- strcat(descbuf, "'\n");
+ strcat(buf, " AND a.aggname ~* '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
- strcat(descbuf, "ORDER BY \"Name\", \"Type\"");
+ strcat(buf, "ORDER BY \"Name\", \"Type\"");
- res = PSQLexec(pset, descbuf);
+ res = PSQLexec(pset, buf);
if (!res)
return false;
@@ -98,12 +103,12 @@ describeAggregates(const char *name, PsqlSettings *pset)
/* \df
- * takes an optional regexp to narrow down the function name
+ * Takes an optional regexp to narrow down the function name
*/
bool
-describeFunctions(const char *name, PsqlSettings *pset)
+describeFunctions(const char *name, PsqlSettings *pset, bool verbose, bool desc)
{
- char descbuf[384 + REGEXP_CUTOFF];
+ char buf[384 + REGEXP_CUTOFF];
PGresult *res;
printQueryOpt myopt = pset->popt;
@@ -111,23 +116,33 @@ describeFunctions(const char *name, PsqlSettings *pset)
* we skip in/out funcs by excluding functions that take some
* arguments, but have no types defined for those arguments
*/
- descbuf[0] = '\0';
-
- strcat(descbuf, "SELECT t.typname as \"Result\", p.proname as \"Function\",\n"
+ strcpy(buf,
+ "SELECT t.typname as \"Result\", p.proname as \"Function\",\n"
" oid8types(p.proargtypes) as \"Arguments\"");
- if (GetVariableBool(pset->vars, "description"))
- strcat(descbuf, "\n, obj_description(p.oid) as \"Description\"");
- strcat(descbuf, "\nFROM pg_proc p, pg_type t\n"
- "WHERE p.prorettype = t.oid and (pronargs = 0 or oid8types(p.proargtypes) != '')\n");
+ if (verbose)
+ strcat(buf, ",\n u.usename as \"Owner\", l.lanname as \"Language\", p.prosrc as \"Source\"");
+ if (desc)
+ strcat(buf, ",\n obj_description(p.oid) as \"Description\"");
+
+ if (!verbose)
+ strcat(buf,
+ "\nFROM pg_proc p, pg_type t\n"
+ "WHERE p.prorettype = t.oid and (pronargs = 0 or oid8types(p.proargtypes) != '')\n");
+ else
+ strcat(buf,
+ "\nFROM pg_proc p, pg_type t, pg_language l, pg_user u\n"
+ "WHERE p.prorettype = t.oid AND p.prolang = l.oid AND p.proowner = u.usesysid\n"
+ " AND (pronargs = 0 or oid8types(p.proargtypes) != '')\n");
+
if (name)
{
- strcat(descbuf, " AND p.proname ~* '^");
- strncat(descbuf, name, REGEXP_CUTOFF);
- strcat(descbuf, "'\n");
+ strcat(buf, " AND p.proname ~* '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
- strcat(descbuf, "ORDER BY \"Function\", \"Result\", \"Arguments\"");
+ strcat(buf, "ORDER BY \"Function\", \"Result\", \"Arguments\"");
- res = PSQLexec(pset, descbuf);
+ res = PSQLexec(pset, buf);
if (!res)
return false;
@@ -144,33 +159,44 @@ describeFunctions(const char *name, PsqlSettings *pset)
/*
- * describeTypes
- *
- * for \dT
+ * \dT
+ * describe types
*/
bool
-describeTypes(const char *name, PsqlSettings *pset)
+describeTypes(const char *name, PsqlSettings *pset, bool verbose, bool desc)
{
- char descbuf[256 + REGEXP_CUTOFF];
+ char buf[256 + REGEXP_CUTOFF];
PGresult *res;
printQueryOpt myopt = pset->popt;
- descbuf[0] = '\0';
- strcat(descbuf, "SELECT typname AS \"Type\"");
- if (GetVariableBool(pset->vars, "description"))
- strcat(descbuf, ", obj_description(oid) as \"Description\"");
- strcat(descbuf, "\nFROM pg_type\n"
- "WHERE typrelid = 0 AND typname !~ '^_.*'\n");
+ strcpy(buf, "SELECT t.typname AS \"Type\"");
+ if (verbose)
+ strcat(buf,
+ ",\n (CASE WHEN t.typlen=-1 THEN 'var'::text ELSE t.typlen::text END) as \"Length\""
+ ",\n u.usename as \"Owner\""
+ );
+ if (desc)
+ strcat(buf, ",\n obj_description(t.oid) as \"Description\"");
+ /*
+ * do not include array types (start with underscore),
+ * do not include user relations (typrelid!=0)
+ */
+ strcat(buf, !verbose ?
+ ("\nFROM pg_type t\n"
+ "WHERE t.typrelid = 0 AND t.typname !~ '^_.*'\n") :
+ ("\nFROM pg_type t, pg_user u\n"
+ "WHERE t.typrelid = 0 AND t.typname !~ '^_.*' AND t.typowner = u.usesysid\n")
+ );
if (name)
{
- strcat(descbuf, " AND typname ~* '^");
- strncat(descbuf, name, REGEXP_CUTOFF);
- strcat(descbuf, "' ");
+ strcat(buf, " AND t.typname ~* '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "' ");
}
- strcat(descbuf, "ORDER BY typname;");
+ strcat(buf, "ORDER BY t.typname;");
- res = PSQLexec(pset, descbuf);
+ res = PSQLexec(pset, buf);
if (!res)
return false;
@@ -187,28 +213,28 @@ describeTypes(const char *name, PsqlSettings *pset)
/* \do
- * NOTE: The (optional) argument here is _not_ a regexp since with all the
- * funny chars floating around that would probably confuse people. It's an
- * exact match string.
*/
bool
-describeOperators(const char *name, PsqlSettings *pset)
+describeOperators(const char *name, PsqlSettings *pset, bool verbose, bool desc)
{
- char descbuf[1536 + 3 * 32]; /* 32 is max length for operator
- * name */
+ char buf[1536 + 3 * REGEXP_CUTOFF];
PGresult *res;
- bool description = GetVariableBool(pset->vars, "description");
printQueryOpt myopt = pset->popt;
- descbuf[0] = '\0';
+ /* Not used right now. Maybe later. */
+ (void)verbose;
- strcat(descbuf, "SELECT o.oprname AS \"Op\",\n"
+ /* FIXME: Use outer joins here when ready */
+
+ strcpy(buf,
+ "SELECT o.oprname AS \"Op\",\n"
" t1.typname AS \"Left arg\",\n"
" t2.typname AS \"Right arg\",\n"
" t0.typname AS \"Result\"");
- if (description)
- strcat(descbuf, ",\n obj_description(p.oid) as \"Description\"");
- strcat(descbuf, "\nFROM pg_proc p, pg_type t0,\n"
+ if (desc)
+ strcat(buf, ",\n obj_description(p.oid) as \"Description\"");
+ strcat(buf,
+ "\nFROM pg_proc p, pg_type t0,\n"
" pg_type t1, pg_type t2,\n"
" pg_operator o\n"
"WHERE p.prorettype = t0.oid AND\n"
@@ -218,51 +244,51 @@ describeOperators(const char *name, PsqlSettings *pset)
" o.oprright = t2.oid\n");
if (name)
{
- strcat(descbuf, " AND o.oprname = '");
- strncat(descbuf, name, 32);
- strcat(descbuf, "'\n");
+ strcat(buf, " AND o.oprname ~ '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
- strcat(descbuf, "\nUNION\n\n"
+ strcat(buf, "\nUNION\n\n"
"SELECT o.oprname as \"Op\",\n"
" ''::name AS \"Left arg\",\n"
" t1.typname AS \"Right arg\",\n"
" t0.typname AS \"Result\"");
- if (description)
- strcat(descbuf, ",\n obj_description(p.oid) as \"Description\"");
- strcat(descbuf, "\nFROM pg_operator o, pg_proc p, pg_type t0, pg_type t1\n"
+ if (desc)
+ strcat(buf, ",\n obj_description(p.oid) as \"Description\"");
+ strcat(buf, "\nFROM pg_operator o, pg_proc p, pg_type t0, pg_type t1\n"
"WHERE RegprocToOid(o.oprcode) = p.oid AND\n"
" o.oprresult = t0.oid AND\n"
" o.oprkind = 'l' AND\n"
" o.oprright = t1.oid\n");
if (name)
{
- strcat(descbuf, "AND o.oprname = '");
- strncat(descbuf, name, 32);
- strcat(descbuf, "'\n");
+ strcat(buf, "AND o.oprname ~ '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
- strcat(descbuf, "\nUNION\n\n"
+ strcat(buf, "\nUNION\n\n"
"SELECT o.oprname as \"Op\",\n"
" t1.typname AS \"Left arg\",\n"
" ''::name AS \"Right arg\",\n"
" t0.typname AS \"Result\"");
- if (description)
- strcat(descbuf, ",\n obj_description(p.oid) as \"Description\"");
- strcat(descbuf, "\nFROM pg_operator o, pg_proc p, pg_type t0, pg_type t1\n"
+ if (desc)
+ strcat(buf, ",\n obj_description(p.oid) as \"Description\"");
+ strcat(buf, "\nFROM pg_operator o, pg_proc p, pg_type t0, pg_type t1\n"
"WHERE RegprocToOid(o.oprcode) = p.oid AND\n"
" o.oprresult = t0.oid AND\n"
" o.oprkind = 'r' AND\n"
" o.oprleft = t1.oid\n");
if (name)
{
- strcat(descbuf, "AND o.oprname = '");
- strncat(descbuf, name, 32);
- strcat(descbuf, "'\n");
+ strcat(buf, "AND o.oprname ~ '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
- strcat(descbuf, "\nORDER BY \"Op\", \"Left arg\", \"Right arg\", \"Result\"");
+ strcat(buf, "\nORDER BY \"Op\", \"Left arg\", \"Right arg\", \"Result\"");
- res = PSQLexec(pset, descbuf);
+ res = PSQLexec(pset, buf);
if (!res)
return false;
@@ -283,26 +309,26 @@ describeOperators(const char *name, PsqlSettings *pset)
* for \l, \list, and -l switch
*/
bool
-listAllDbs(PsqlSettings *pset)
+listAllDbs(PsqlSettings *pset, bool desc)
{
PGresult *res;
- char descbuf[256];
+ char buf[256];
printQueryOpt myopt = pset->popt;
- descbuf[0] = '\0';
- strcat(descbuf, "SELECT pg_database.datname as \"Database\",\n"
+ strcpy(buf,
+ "SELECT pg_database.datname as \"Database\",\n"
" pg_user.usename as \"Owner\""
#ifdef MULTIBYTE
",\n pg_database.encoding as \"Encoding\""
#endif
);
- if (GetVariableBool(pset->vars, "description"))
- strcat(descbuf, ",\n obj_description(pg_database.oid) as \"Description\"\n");
- strcat(descbuf, "FROM pg_database, pg_user\n"
+ if (desc)
+ strcat(buf, ",\n obj_description(pg_database.oid) as \"Description\"\n");
+ strcat(buf, "FROM pg_database, pg_user\n"
"WHERE pg_database.datdba = pg_user.usesysid\n"
"ORDER BY \"Database\"");
- res = PSQLexec(pset, descbuf);
+ res = PSQLexec(pset, buf);
if (!res)
return false;
@@ -337,7 +363,7 @@ permissionsList(const char *name, PsqlSettings *pset)
" relname !~ '^pg_'\n");
if (name)
{
- strcat(descbuf, " AND rename ~ '^");
+ strcat(descbuf, " AND rename ~ '");
strncat(descbuf, name, REGEXP_CUTOFF);
strcat(descbuf, "'\n");
}
@@ -389,7 +415,7 @@ objectDescription(const char *object, PsqlSettings *pset)
"WHERE a.oid = d.objoid\n");
if (object)
{
- strcat(descbuf, " AND a.aggname ~* '^");
+ strcat(descbuf, " AND a.aggname ~* '");
strncat(descbuf, object, REGEXP_CUTOFF);
strcat(descbuf, "'\n");
}
@@ -401,7 +427,7 @@ objectDescription(const char *object, PsqlSettings *pset)
"WHERE p.oid = d.objoid AND (p.pronargs = 0 or oid8types(p.proargtypes) != '')\n");
if (object)
{
- strcat(descbuf, " AND p.proname ~* '^");
+ strcat(descbuf, " AND p.proname ~* '");
strncat(descbuf, object, REGEXP_CUTOFF);
strcat(descbuf, "'\n");
}
@@ -410,11 +436,11 @@ objectDescription(const char *object, PsqlSettings *pset)
strcat(descbuf, "\nUNION ALL\n\n");
strcat(descbuf, "SELECT DISTINCT o.oprname as \"Name\", 'operator'::text as \"What\", d.description as \"Description\"\n"
"FROM pg_operator o, pg_description d\n"
- // must get comment via associated function
+ /* must get comment via associated function */
"WHERE RegprocToOid(o.oprcode) = d.objoid\n");
if (object)
{
- strcat(descbuf, " AND o.oprname = '");
+ strcat(descbuf, " AND o.oprname ~ '");
strncat(descbuf, object, REGEXP_CUTOFF);
strcat(descbuf, "'\n");
}
@@ -426,7 +452,7 @@ objectDescription(const char *object, PsqlSettings *pset)
"WHERE t.oid = d.objoid\n");
if (object)
{
- strcat(descbuf, " AND t.typname ~* '^");
+ strcat(descbuf, " AND t.typname ~* '");
strncat(descbuf, object, REGEXP_CUTOFF);
strcat(descbuf, "'\n");
}
@@ -438,7 +464,7 @@ objectDescription(const char *object, PsqlSettings *pset)
"WHERE c.oid = d.objoid\n");
if (object)
{
- strcat(descbuf, " AND c.relname ~* '^");
+ strcat(descbuf, " AND c.relname ~* '");
strncat(descbuf, object, REGEXP_CUTOFF);
strcat(descbuf, "'\n");
}
@@ -450,7 +476,7 @@ objectDescription(const char *object, PsqlSettings *pset)
"WHERE r.oid = d.objoid AND r.rulename !~ '^_RET'\n");
if (object)
{
- strcat(descbuf, " AND r.rulename ~* '^");
+ strcat(descbuf, " AND r.rulename ~* '");
strncat(descbuf, object, REGEXP_CUTOFF);
strcat(descbuf, "'\n");
}
@@ -462,7 +488,7 @@ objectDescription(const char *object, PsqlSettings *pset)
"WHERE t.oid = d.objoid\n");
if (object)
{
- strcat(descbuf, " AND t.tgname ~* '^");
+ strcat(descbuf, " AND t.tgname ~* '");
strncat(descbuf, object, REGEXP_CUTOFF);
strcat(descbuf, "'\n");
}
@@ -510,89 +536,122 @@ xmalloc(size_t size)
bool
-describeTableDetails(const char *name, PsqlSettings *pset)
+describeTableDetails(const char *name, PsqlSettings *pset, bool desc)
{
- char descbuf[512 + NAMEDATALEN];
- PGresult *res = NULL,
- *res2 = NULL,
- *res3 = NULL;
+ char buf[512 + 8 * NAMEDATALEN];
+ PGresult *res = NULL;
printTableOpt myopt = pset->popt.topt;
- bool description = GetVariableBool(pset->vars, "description");
int i;
- const char *view_def = NULL;
+ 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;
- cols = 3 + (description ? 1 : 0);
-
- headers[0] = "Attribute";
- headers[1] = "Type";
- headers[2] = "Info";
- if (description)
- {
- headers[3] = "Description";
- headers[4] = NULL;
- }
- else
- headers[3] = NULL;
+ /* 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 */
- strcpy(descbuf, "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum");
- if (description)
- strcat(descbuf, ", obj_description(a.oid)");
- strcat(descbuf, "\nFROM pg_class c, pg_attribute a, pg_type t\n"
- "WHERE c.relname = '");
- strncat(descbuf, name, NAMEDATALEN);
- strcat(descbuf, "'\n AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid\n"
- "ORDER BY a.attnum");
-
- res = PSQLexec(pset, descbuf);
- if (!res)
- return false;
+ sprintf(buf,
+ "SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules\n"
+ "FROM pg_class WHERE relname='%s'",
+ name);
+ res = PSQLexec(pset, buf);
+ if (!res)
+ return false;
/* Did we get anything? */
if (PQntuples(res) == 0)
{
if (!GetVariableBool(pset->vars, "quiet"))
- fprintf(stdout, "Did not find any class named \"%s\".\n", name);
+ fprintf(stdout, "Did not find any relation named \"%s\".\n", name);
PQclear(res);
return false;
}
- /* Check if table is a view */
- strcpy(descbuf, "SELECT definition FROM pg_views WHERE viewname = '");
- strncat(descbuf, name, NAMEDATALEN);
- strcat(descbuf, "'");
- res2 = PSQLexec(pset, descbuf);
- if (!res2)
- 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);
- if (PQntuples(res2) > 0)
- view_def = PQgetvalue(res2, 0, 0);
+ headers[0] = "Attribute";
+ headers[1] = "Type";
+ cols = 2;
+ if (tableinfo.relkind == 'r' || tableinfo.relkind == 's')
+ {
+ cols++;
+ headers[cols-1] = "Extra";
+ }
- /* Generate table cells to be printed */
- cells = calloc(PQntuples(res) * cols + 1, sizeof(*cells));
- if (!cells)
+ if (desc)
{
- perror("calloc");
- exit(EXIT_FAILURE);
+ cols++;
+ headers[cols-1] = "Description";
}
+ headers[cols] = NULL;
+
+
+ /* Get column info */
+ strcpy(buf, "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum");
+ if (desc)
+ strcat(buf, ", obj_description(a.oid)");
+ strcat(buf, "\nFROM pg_class c, pg_attribute a, pg_type t\n"
+ "WHERE c.relname = '");
+ strncat(buf, name, NAMEDATALEN);
+ strcat(buf, "'\n AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid\n"
+ "ORDER BY a.attnum");
+
+ res = PSQLexec(pset, 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(pset, 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++)
{
int4 attypmod = atoi(PQgetvalue(res, i, 3));
const char *attype = PQgetvalue(res, i, 1);
/* Name */
- cells[i * cols + 0] = (char*)PQgetvalue(res, i, 0); /* don't free this afterwards */
-
+ cells[i * cols + 0] = (char *)PQgetvalue(res, i, 0); /* don't free this afterwards */
+
/* Type */
+ /* (convert some internal type names to "readable") */
cells[i * cols + 1] = xmalloc(NAMEDATALEN + 16);
if (strcmp(attype, "bpchar") == 0)
sprintf(cells[i * cols + 1], "char(%d)", attypmod != -1 ? attypmod - VARHDRSZ : 0);
@@ -606,85 +665,235 @@ describeTableDetails(const char *name, PsqlSettings *pset)
else
strcpy(cells[i * cols + 1], attype);
- /* Info */
- cells[i * cols + 2] = xmalloc(128 + 128); /* I'm cutting off the
- * 'default' string at 128 */
- cells[i * cols + 2][0] = '\0';
- if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
- strcat(cells[i * cols + 2], "not null");
- if (strcmp(PQgetvalue(res, i, 5), "t") == 0)
- {
- /* handle "default" here */
- strcpy(descbuf, "SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c\n"
- "WHERE c.relname = '");
- strncat(descbuf, name, NAMEDATALEN);
- strcat(descbuf, "' AND c.oid = d.adrelid AND d.adnum = ");
- strcat(descbuf, PQgetvalue(res, i, 6));
-
- res3 = PSQLexec(pset, descbuf);
- if (!res)
- return false;
- if (cells[i * cols + 2][0])
- strcat(cells[i * cols + 2], " ");
- strcat(cells[i * cols + 2], "default ");
- strcat(cells[i * cols + 2], PQgetvalue(res3, 0, 0));
- }
+
+ /* Extra: not null and default */
+ /* (I'm cutting off the 'default' string at 128) */
+ if (tableinfo.relkind == 'r' || tableinfo.relkind == 's')
+ {
+ cells[i * cols + 2] = xmalloc(128 + 128);
+ cells[i * cols + 2][0] = '\0';
+ if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
+ strcat(cells[i * cols + 2], "not null");
+
+ /* handle "default" here */
+ if (strcmp(PQgetvalue(res, i, 5), "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, 6));
+
+ result = PSQLexec(pset, 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 (description)
- cells[i * cols + 3] = (char*)PQgetvalue(res, i, 7);
+ if (desc)
+ cells[i * cols + cols-1] = (char*)PQgetvalue(res, i, 7);
}
/* Make title */
- title = xmalloc(10 + strlen(name));
- if (view_def)
- sprintf(title, "View \"%s\"", name);
- else
- sprintf(title, "Table \"%s\"", name);
+ title = xmalloc(20 + strlen(name));
+ switch (tableinfo.relkind) {
+ case 'r':
+ if (view_def)
+ sprintf(title, "View \"%s\"", name);
+ else
+ sprintf(title, "Table \"%s\"", name);
+ break;
+ case 'S':
+ sprintf(title, "Sequence \"%s\"", name);
+ break;
+ case 'i':
+ sprintf(title, "Index \"%s\"", name);
+ break;
+ case 's':
+ sprintf(title, "System table \"%s\"", name);
+ break;
+ default:
+ sprintf(title, "?%c?", tableinfo.relkind);
+ }
/* Make footers */
- if (view_def)
+ /* Information about the index */
+ if (tableinfo.relkind == 'i')
+ {
+ PGresult * result;
+
+ sprintf(buf, "SELECT i.indisunique, i.indisprimary, a.amname\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(pset, buf);
+ if (!result)
+ error = true;
+ else
+ {
+ footers = xmalloc(2 * sizeof(*footers));
+ footers[0] = xmalloc(NAMEDATALEN + 32);
+ sprintf(footers[0], "%s%s",
+ strcmp(PQgetvalue(result, 0, 0), "t")==0 ? "unique " : "",
+ PQgetvalue(result, 0, 2)
+ );
+ if (strcmp(PQgetvalue(result, 0, 1), "t")==0)
+ strcat(footers[0], " (primary key)");
+ footers[1] = NULL;
+ }
+ }
+ /* Information about the view */
+ else if (tableinfo.relkind == 'r' && view_def)
{
footers = xmalloc(2 * sizeof(*footers));
footers[0] = xmalloc(20 + strlen(view_def));
sprintf(footers[0], "View definition: %s", view_def);
footers[1] = NULL;
}
- else
+
+ /* Information about the table */
+ else if (tableinfo.relkind == 'r')
{
- /* display indices */
- strcpy(descbuf, "SELECT c2.relname\n"
- "FROM pg_class c, pg_class c2, pg_index i\n"
- "WHERE c.relname = '");
- strncat(descbuf, name, NAMEDATALEN);
- strcat(descbuf, "' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
- "ORDER BY c2.relname");
- res3 = PSQLexec(pset, descbuf);
- if (!res3)
- return false;
-
- if (PQntuples(res3) > 0)
- {
- footers = xmalloc((PQntuples(res3) + 1) * sizeof(*footers));
-
- for (i = 0; i < PQntuples(res3); i++)
- {
- footers[i] = xmalloc(10 + NAMEDATALEN);
- if (PQntuples(res3) == 1)
- sprintf(footers[i], "Index: %s", PQgetvalue(res3, i, 0));
- else if (i == 0)
- sprintf(footers[i], "Indices: %s", PQgetvalue(res3, i, 0));
- else
- sprintf(footers[i], " %s", PQgetvalue(res3, i, 0));
- }
-
- footers[i] = NULL;
+ PGresult *result1=NULL, *result2=NULL, *result3=NULL, *result4=NULL;
+ int index_count=0, constr_count=0, rule_count=0, trigger_count=0;
+ int count_footers=0;
+
+ /* 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",
+ name);
+ result1 = PSQLexec(pset, buf);
+ if (!result1)
+ error = true;
+ else
+ index_count = PQntuples(result1);
+ }
+
+ /* count table (and column) constraints */
+ if (!error && tableinfo.checks)
+ {
+ sprintf(buf, "SELECT rcsrc\n"
+ "FROM pg_relcheck r, pg_class c\n"
+ "WHERE c.relname='%s' AND c.oid = r.rcrelid",
+ name);
+ result2 = PSQLexec(pset, 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(pset, buf);
+ if (!result3)
+ error = true;
+ else
+ rule_count = PQntuples(result3);
+ }
+
+ /* count triggers */
+ if (!error && tableinfo.hasrules)
+ {
+ 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(pset, buf);
+ if (!result4)
+ error = true;
+ else
+ trigger_count = PQntuples(result4);
+ }
+
+ footers = xmalloc((index_count + constr_count + rule_count + trigger_count + 1) * sizeof(*footers));
+
+ /* 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",
+ constr_count==1 ? "Constraint:" : (i==0 ? "Constraints:" : " "),
+ PQgetvalue(result2, i, 0)
+ );
+ footers[count_footers++] = xstrdup(buf);
+ }
+
+ /* print rules */
+ for (i = 0; i < rule_count; i++)
+ {
+ sprintf(buf, "%s %s",
+ rule_count==1 ? "Rule:" : (i==0 ? "Rules:" : " "),
+ 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++)
+ {
+ sprintf(buf, "%s %s",
+ trigger_count==1 ? "Trigger:" : (i==0 ? "Triggers:" : " "),
+ 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);
+ }
- myopt.tuples_only = false;
- printTable(title, headers, (const char**)cells, (const char**)footers, "llll", &myopt, pset->queryFout);
+ if (!error) {
+ myopt.tuples_only = false;
+ printTable(title, headers, (const char**)cells, (const char**)footers, "llll", &myopt, pset->queryFout);
+ }
/* clean up */
free(title);
@@ -692,7 +901,8 @@ describeTableDetails(const char *name, PsqlSettings *pset)
for (i = 0; i < PQntuples(res); i++)
{
free(cells[i * cols + 1]);
- free(cells[i * cols + 2]);
+ if (tableinfo.relkind == 'r' || tableinfo.relkind == 's')
+ free(cells[i * cols + 2]);
}
free(cells);
@@ -701,10 +911,8 @@ describeTableDetails(const char *name, PsqlSettings *pset)
free(footers);
PQclear(res);
- PQclear(res2);
- PQclear(res3);
- return true;
+ return !error;
}
@@ -719,11 +927,11 @@ describeTableDetails(const char *name, PsqlSettings *pset)
* i - indices
* v - views
* s - sequences
- * S - systems tables (~'^pg_')
+ * S - systems tables (~ '^pg_')
* (any order of the above is fine)
*/
bool
-listTables(const char *infotype, const char *name, PsqlSettings *pset)
+listTables(const char *infotype, const char *name, PsqlSettings *pset, bool desc)
{
bool showTables = strchr(infotype, 't') != NULL;
bool showIndices = strchr(infotype, 'i') != NULL;
@@ -731,120 +939,121 @@ listTables(const char *infotype, const char *name, PsqlSettings *pset)
bool showSeq = strchr(infotype, 's') != NULL;
bool showSystem = strchr(infotype, 'S') != NULL;
- bool description = GetVariableBool(pset->vars, "description");
-
- char descbuf[1536 + 4 * REGEXP_CUTOFF];
+ char buf[1536 + 4 * REGEXP_CUTOFF];
PGresult *res;
printQueryOpt myopt = pset->popt;
- descbuf[0] = '\0';
+ buf[0] = '\0';
/* tables */
if (showTables)
{
- strcat(descbuf, "SELECT u.usename as \"Owner\", c.relname as \"Name\", 'table'::text as \"Type\"");
- if (description)
- strcat(descbuf, ", obj_description(c.oid) as \"Description\"");
- strcat(descbuf, "\nFROM pg_class c, pg_user u\n"
+ strcat(buf, "SELECT c.relname as \"Name\", 'table'::text as \"Type\", u.usename as \"Owner\"");
+ if (desc)
+ strcat(buf, ", obj_description(c.oid) as \"Description\"");
+ strcat(buf, "\nFROM pg_class c, pg_user u\n"
"WHERE c.relowner = u.usesysid AND c.relkind = 'r'\n"
" AND not exists (select 1 from pg_views where viewname = c.relname)\n");
- strcat(descbuf, showSystem ? " AND c.relname ~ '^pg_'\n" : " AND c.relname !~ '^pg_'\n");
+ strcat(buf, showSystem ? " AND c.relname ~ '^pg_'\n" : " AND c.relname !~ '^pg_'\n");
if (name)
{
- strcat(descbuf, " AND c.relname ~ '^");
- strncat(descbuf, name, REGEXP_CUTOFF);
- strcat(descbuf, "'\n");
+ strcat(buf, " AND c.relname ~ '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
}
/* views */
if (showViews)
{
- if (descbuf[0])
- strcat(descbuf, "\nUNION\n\n");
+ if (buf[0])
+ strcat(buf, "\nUNION\n\n");
- strcat(descbuf, "SELECT u.usename as \"Owner\", c.relname as \"Name\", 'view'::text as \"Type\"");
- if (description)
- strcat(descbuf, ", obj_description(c.oid) as \"Description\"");
- strcat(descbuf, "\nFROM pg_class c, pg_user u\n"
+ strcat(buf, "SELECT c.relname as \"Name\", 'view'::text as \"Type\", u.usename as \"Owner\"");
+ if (desc)
+ strcat(buf, ", obj_description(c.oid) as \"Description\"");
+ strcat(buf, "\nFROM pg_class c, pg_user u\n"
"WHERE c.relowner = u.usesysid AND c.relkind = 'r'\n"
" AND exists (select 1 from pg_views where viewname = c.relname)\n");
- strcat(descbuf, showSystem ? " AND c.relname ~ '^pg_'\n" : " AND c.relname !~ '^pg_'\n");
+ strcat(buf, showSystem ? " AND c.relname ~ '^pg_'\n" : " AND c.relname !~ '^pg_'\n");
if (name)
{
- strcat(descbuf, " AND c.relname ~ '^");
- strncat(descbuf, name, REGEXP_CUTOFF);
- strcat(descbuf, "'\n");
+ strcat(buf, " AND c.relname ~ '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
}
/* indices, sequences */
if (showIndices || showSeq)
{
- if (descbuf[0])
- strcat(descbuf, "\nUNION\n\n");
-
- strcat(descbuf, "SELECT u.usename as \"Owner\", c.relname as \"Name\",\n"
- " (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END) as \"Type\"");
- if (description)
- strcat(descbuf, ", obj_description(c.oid) as \"Description\"");
- strcat(descbuf, "\nFROM pg_class c, pg_user u\n"
+ if (buf[0])
+ strcat(buf, "\nUNION\n\n");
+
+ strcat(buf,
+ "SELECT c.relname as \"Name\",\n"
+ " (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END) as \"Type\",\n"
+ " u.usename as \"Owner\""
+ );
+ if (desc)
+ strcat(buf, ", obj_description(c.oid) as \"Description\"");
+ strcat(buf, "\nFROM pg_class c, pg_user u\n"
"WHERE c.relowner = u.usesysid AND relkind in (");
if (showIndices && showSeq)
- strcat(descbuf, "'i', 'S'");
+ strcat(buf, "'i', 'S'");
else if (showIndices)
- strcat(descbuf, "'i'");
+ strcat(buf, "'i'");
else
- strcat(descbuf, "'S'");
- strcat(descbuf, ")\n");
+ strcat(buf, "'S'");
+ strcat(buf, ")\n");
/* ignore large-obj indices */
if (showIndices)
- strcat(descbuf, " AND (c.relkind != 'i' OR c.relname !~ '^xinx')\n");
+ strcat(buf, " AND (c.relkind != 'i' OR c.relname !~ '^xinx')\n");
- strcat(descbuf, showSystem ? " AND c.relname ~ '^pg_'\n" : " AND c.relname !~ '^pg_'\n");
+ strcat(buf, showSystem ? " AND c.relname ~ '^pg_'\n" : " AND c.relname !~ '^pg_'\n");
if (name)
{
- strcat(descbuf, " AND c.relname ~ '^");
- strncat(descbuf, name, REGEXP_CUTOFF);
- strcat(descbuf, "'\n");
+ strcat(buf, " AND c.relname ~ '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
}
/* real system catalogue tables */
if (showSystem && showTables)
{
- if (descbuf[0])
- strcat(descbuf, "\nUNION\n\n");
+ if (buf[0])
+ strcat(buf, "\nUNION\n\n");
- strcat(descbuf, "SELECT u.usename as \"Owner\", c.relname as \"Name\", 'system'::text as \"Type\"");
- if (description)
- strcat(descbuf, ", obj_description(c.oid) as \"Description\"");
- strcat(descbuf, "\nFROM pg_class c, pg_user u\n"
+ strcat(buf, "SELECT c.relname as \"Name\", 'system'::text as \"Type\", u.usename as \"Owner\"");
+ if (desc)
+ strcat(buf, ", obj_description(c.oid) as \"Description\"");
+ strcat(buf, "\nFROM pg_class c, pg_user u\n"
"WHERE c.relowner = u.usesysid AND c.relkind = 's'\n");
if (name)
{
- strcat(descbuf, " AND c.relname ~ '^");
- strncat(descbuf, name, REGEXP_CUTOFF);
- strcat(descbuf, "'\n");
+ strcat(buf, " AND c.relname ~ '");
+ strncat(buf, name, REGEXP_CUTOFF);
+ strcat(buf, "'\n");
}
}
- strcat(descbuf, "\nORDER BY \"Name\"");
+ strcat(buf, "\nORDER BY \"Name\"");
- res = PSQLexec(pset, descbuf);
+ res = PSQLexec(pset, buf);
if (!res)
return false;
if (PQntuples(res) == 0)
- fprintf(pset->queryFout, "No matching classes found.\n");
+ fprintf(pset->queryFout, "No matching relations found.\n");
else
{
myopt.topt.tuples_only = false;
myopt.nullPrint = NULL;
- myopt.title = "List of classes";
+ myopt.title = "List of relations";
printQuery(res, &myopt, pset->queryFout);
}
@@ -854,4 +1063,4 @@ listTables(const char *infotype, const char *name, PsqlSettings *pset)
}
-/* the end */
+/* end of file */
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 02:59:33 +0000