[HACKERS] patch: Allow \dd to show constraint comments

[Josh Kupershmidt]

Hi all,

Attached is a simple patch addressing the TODO item "Allow \dd to show
constraint comments". If you have comments on various constraints
(column, foreign key, primary key, unique, exclusion), they should
show up via \dd now.

Some example SQL is attached to create two tables with a variety of
constraints and constraint comments. With the patch, \dd should then
produce something like this:

                            Object descriptions
 Schema |         Name         |   Object   |         Description
--------+----------------------+------------+------------------------------
 public | bar_c_excl           | constraint | exclusion constraint comment
 public | bar_pkey             | constraint | two column pkey comment
 public | bar_uname_check      | constraint | constraint for bar
 public | bar_uname_fkey       | constraint | fkey comment
 public | uname_check_not_null | constraint | not null comment
 public | uname_cons           | constraint | sanity check for uname
 public | uname_uniq_cons      | constraint | unique constraint comment
(7 rows)

whereas without the patch, you should see nothing.

Josh

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index ac351d3..20dfd1d 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** testdb=>
*** 991,997 ****
          objects.
          <quote>Object</quote> covers aggregates, functions, operators,
          types, relations (tables, views, indexes, sequences), large
!         objects, rules, and triggers. For example:
  <programlisting>
  =&gt; <userinput>\dd version</userinput>
                       Object descriptions
--- 991,997 ----
          objects.
          <quote>Object</quote> covers aggregates, functions, operators,
          types, relations (tables, views, indexes, sequences), large
!         objects, rules, triggers, and constraints. For example:
  <programlisting>
  =&gt; <userinput>\dd version</userinput>
                       Object descriptions
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e01fb7b..6da97e7 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** objectDescription(const char *pattern, b
*** 998,1003 ****
--- 998,1026 ----
  						  "n.nspname", "t.tgname", NULL,
  						  "pg_catalog.pg_table_is_visible(c.oid)");
  
+ 	/* Constraint descriptions */
+ 	appendPQExpBuffer(&buf,
+ 					  "UNION ALL\n"
+ 					  "  SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
+ 					  "  n.nspname as nspname,\n"
+ 					  "  CAST(pgc.conname AS pg_catalog.text) as name,"
+ 					  "  CAST('%s' AS pg_catalog.text) as object\n"
+ 					  "  FROM pg_catalog.pg_constraint pgc\n"
+ 					  "    JOIN pg_catalog.pg_class c "
+ 					  "ON c.oid = pgc.conrelid\n"
+ 					  "    LEFT JOIN pg_catalog.pg_namespace n "
+ 					  "    ON n.oid = c.relnamespace\n",
+ 					  gettext_noop("constraint"));
+ 
+ 	if (!showSystem && !pattern)
+ 		appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
+ 						  "      AND n.nspname <> 'information_schema'\n");
+ 
+ 	/* XXX not sure what to do about visibility rule here? */
+ 	processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
+ 						  "n.nspname", "pgc.conname", NULL,
+ 						  "pg_catalog.pg_table_is_visible(c.oid)");
+ 
  	appendPQExpBuffer(&buf,
  					  ") AS tt\n"
  					  "  JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");

CREATE TABLE mytable (
    uname text PRIMARY KEY,
    CONSTRAINT uname_cons CHECK ((uname <> 'badname'::text))
);

COMMENT ON CONSTRAINT uname_cons ON mytable IS 'sanity check for uname';

CREATE TABLE bar (
    uname text NOT NULL,
    another_uname text NOT NULL,
    c circle
    CONSTRAINT bar_uname_check CHECK ((uname <> 'invalid'::text)),
    CONSTRAINT uname_check_not_null CHECK ((uname IS NOT NULL)),
    CONSTRAINT bar_pkey PRIMARY KEY (uname, another_uname),
    CONSTRAINT uname_uniq_cons UNIQUE (uname),
    CONSTRAINT bar_uname_fkey FOREIGN KEY (uname) REFERENCES mytable(uname),
    EXCLUDE USING gist (c WITH &&)
);

COMMENT ON CONSTRAINT bar_uname_check ON bar IS 'constraint for bar';
COMMENT ON CONSTRAINT uname_check_not_null ON bar IS 'not null comment';
COMMENT ON CONSTRAINT bar_pkey ON bar IS 'two column pkey comment';
COMMENT ON CONSTRAINT uname_uniq_cons ON bar IS 'unique constraint comment';
COMMENT ON CONSTRAINT bar_uname_fkey ON bar IS 'fkey comment';
COMMENT ON CONSTRAINT bar_c_excl ON bar IS 'exclusion constraint comment';


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers