Hi,
This patch finishes off the work that I did with making view
definitions use pretty printing.
It does:
* Pretty check constraints
* Pretty index predicates
* Pretty rule definitions
* Uppercases PRIMARY KEY and UNIQUE to be consistent with CHECK and
FOREIGN KEY
* View rules are improved to match table rules:
View "public.v"
Column | Type | Modifiers
----------+---------+-----------
?column? | integer |
View definition:
SELECT 1;
Rules:
r1 AS
ON INSERT TO v DO INSTEAD NOTHING
r2 AS
ON INSERT TO v DO INSTEAD NOTHING
Chris
Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.85
diff -c -r1.85 describe.c
*** describe.c 7 Sep 2003 03:43:53 -0000 1.85
--- describe.c 29 Sep 2003 04:24:56 -0000
***************
*** 857,863 ****
printfPQExpBuffer(&buf,
"SELECT i.indisunique, i.indisprimary, a.amname, c2.relname,\n"
! " pg_catalog.pg_get_expr(i.indpred,
i.indrelid)\n"
"FROM pg_catalog.pg_index i,
pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
"WHERE i.indexrelid = c.oid AND
c.oid = '%s' AND c.relam = a.oid\n"
"AND i.indrelid = c2.oid",
--- 857,863 ----
printfPQExpBuffer(&buf,
"SELECT i.indisunique, i.indisprimary, a.amname, c2.relname,\n"
! " pg_catalog.pg_get_expr(i.indpred,
i.indrelid, true)\n"
"FROM pg_catalog.pg_index i,
pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
"WHERE i.indexrelid = c.oid AND
c.oid = '%s' AND c.relam = a.oid\n"
"AND i.indrelid = c2.oid",
***************
*** 880,888 ****
char *indpred = PQgetvalue(result, 0, 4);
if (strcmp(indisprimary, "t") == 0)
! printfPQExpBuffer(&tmpbuf, _("primary key, "));
else if (strcmp(indisunique, "t") == 0)
! printfPQExpBuffer(&tmpbuf, _("unique, "));
else
resetPQExpBuffer(&tmpbuf);
appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
--- 880,888 ----
char *indpred = PQgetvalue(result, 0, 4);
if (strcmp(indisprimary, "t") == 0)
! printfPQExpBuffer(&tmpbuf, _("PRIMARY KEY, "));
else if (strcmp(indisunique, "t") == 0)
! printfPQExpBuffer(&tmpbuf, _("UNIQUE, "));
else
resetPQExpBuffer(&tmpbuf);
appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
***************
*** 892,898 ****
schemaname, indtable);
if (strlen(indpred))
! appendPQExpBuffer(&tmpbuf, ", predicate %s", indpred);
footers = xmalloczero(2 * sizeof(*footers));
footers[0] = xstrdup(tmpbuf.data);
--- 892,898 ----
schemaname, indtable);
if (strlen(indpred))
! appendPQExpBuffer(&tmpbuf, ", predicate (%s)",
indpred);
footers = xmalloczero(2 * sizeof(*footers));
footers[0] = xstrdup(tmpbuf.data);
***************
*** 911,917 ****
if (tableinfo.hasrules)
{
printfPQExpBuffer(&buf,
! "SELECT r.rulename\n"
"FROM pg_catalog.pg_rewrite
r\n"
"WHERE r.ev_class = '%s' AND r.rulename !=
'_RETURN'",
oid);
--- 911,917 ----
if (tableinfo.hasrules)
{
printfPQExpBuffer(&buf,
! "SELECT r.rulename,
trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
"FROM pg_catalog.pg_rewrite
r\n"
"WHERE r.ev_class = '%s' AND r.rulename !=
'_RETURN'",
oid);
***************
*** 923,949 ****
}
/* Footer information about a view */
! footers = xmalloczero((rule_count + 2) * sizeof(*footers));
footers[count_footers] = xmalloc(64 + strlen(view_def));
snprintf(footers[count_footers], 64 + strlen(view_def),
_("View definition:\n%s"), view_def);
count_footers++;
/* print rules */
! for (i = 0; i < rule_count; i++)
{
! char *s = _("Rules");
! if (i == 0)
! printfPQExpBuffer(&buf, "%s: %s", s,
PQgetvalue(result, i, 0));
! else
! printfPQExpBuffer(&buf, "%*s %s", (int) strlen(s),
"", PQgetvalue(result, i, 0));
! if (i < rule_count - 1)
! appendPQExpBuffer(&buf, ",");
! footers[count_footers++] = xstrdup(buf.data);
}
- PQclear(result);
footers[count_footers] = NULL;
--- 923,953 ----
}
/* Footer information about a view */
! footers = xmalloczero((rule_count + 3) * sizeof(*footers));
footers[count_footers] = xmalloc(64 + strlen(view_def));
snprintf(footers[count_footers], 64 + strlen(view_def),
_("View definition:\n%s"), view_def);
count_footers++;
/* print rules */
! if (rule_count > 0)
{
! printfPQExpBuffer(&buf, _("Rules:"));
! footers[count_footers++] = xstrdup(buf.data);
! for (i = 0; i < rule_count; i++)
! {
! const char *ruledef;
! /* Everything after "CREATE RULE" is echoed verbatim */
! ruledef = PQgetvalue(result, i, 1);
! ruledef += 12;
! printfPQExpBuffer(&buf, " %s", ruledef);
!
! footers[count_footers++] = xstrdup(buf.data);
! }
! PQclear(result);
}
footers[count_footers] = NULL;
***************
*** 970,976 ****
{
printfPQExpBuffer(&buf,
"SELECT c2.relname, i.indisprimary,
i.indisunique, "
!
"pg_catalog.pg_get_indexdef(i.indexrelid)\n"
"FROM pg_catalog.pg_class c,
pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
"WHERE c.oid = '%s' AND
c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
"ORDER BY i.indisprimary
DESC, i.indisunique DESC, c2.relname",
--- 974,980 ----
{
printfPQExpBuffer(&buf,
"SELECT c2.relname, i.indisprimary,
i.indisunique, "
!
"pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)\n"
"FROM pg_catalog.pg_class c,
pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
"WHERE c.oid = '%s' AND
c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
"ORDER BY i.indisprimary
DESC, i.indisunique DESC, c2.relname",
***************
*** 986,992 ****
if (tableinfo.checks)
{
printfPQExpBuffer(&buf,
! "SELECT consrc, conname\n"
"FROM
pg_catalog.pg_constraint r\n"
"WHERE r.conrelid = '%s' AND
r.contype = 'c'",
oid);
--- 990,996 ----
if (tableinfo.checks)
{
printfPQExpBuffer(&buf,
! "SELECT
pg_catalog.pg_get_constraintdef(oid, true) AS consrc, conname\n"
"FROM
pg_catalog.pg_constraint r\n"
"WHERE r.conrelid = '%s' AND
r.contype = 'c'",
oid);
***************
*** 1004,1010 ****
if (tableinfo.hasrules)
{
printfPQExpBuffer(&buf,
! "SELECT r.rulename,
trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid))\n"
"FROM pg_catalog.pg_rewrite
r\n"
"WHERE r.ev_class = '%s'",
oid);
--- 1008,1014 ----
if (tableinfo.hasrules)
{
printfPQExpBuffer(&buf,
! "SELECT r.rulename,
trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
"FROM pg_catalog.pg_rewrite
r\n"
"WHERE r.ev_class = '%s'",
oid);
***************
*** 1049,1055 ****
{
printfPQExpBuffer(&buf,
"SELECT conname,\n"
! " pg_catalog.pg_get_constraintdef(oid) as
condef\n"
"FROM
pg_catalog.pg_constraint r\n"
"WHERE r.conrelid = '%s' AND
r.contype = 'f'",
oid);
--- 1053,1059 ----
{
printfPQExpBuffer(&buf,
"SELECT conname,\n"
! " pg_catalog.pg_get_constraintdef(oid, true)
as condef\n"
"FROM
pg_catalog.pg_constraint r\n"
"WHERE r.conrelid = '%s' AND
r.contype = 'f'",
oid);
***************
*** 1095,1103 ****
/* Label as primary key or unique (but not both) */
appendPQExpBuffer(&buf,
strcmp(PQgetvalue(result1,
i, 1), "t") == 0
! ? _(" primary key,")
:
(strcmp(PQgetvalue(result1,
i, 2), "t") == 0
! ? _(" unique,")
: ""));
/* Everything after "USING" is echoed verbatim */
--- 1099,1107 ----
/* Label as primary key or unique (but not both) */
appendPQExpBuffer(&buf,
strcmp(PQgetvalue(result1,
i, 1), "t") == 0
! ? _(" PRIMARY KEY,")
:
(strcmp(PQgetvalue(result1,
i, 2), "t") == 0
! ? _(" UNIQUE,")
: ""));
/* Everything after "USING" is echoed verbatim */
***************
*** 1119,1125 ****
footers[count_footers++] = xstrdup(buf.data);
for (i = 0; i < check_count; i++)
{
! printfPQExpBuffer(&buf, _(" \"%s\" CHECK %s"),
PQgetvalue(result2,
i, 1),
PQgetvalue(result2,
i, 0));
--- 1123,1129 ----
footers[count_footers++] = xstrdup(buf.data);
for (i = 0; i < check_count; i++)
{
! printfPQExpBuffer(&buf, _(" \"%s\" %s"),
PQgetvalue(result2,
i, 1),
PQgetvalue(result2,
i, 0));
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
