Re: pg_casts view (was Re: date_trun() with timezones? (was Re: TIME column ...))

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane escribió:
>> There already is a \dC command in psql, which has nice enough output
>> format but doesn't provide any way to select a subset of the table.
>> Maybe we should just agree that its argument is a pattern for the
>> castsource type's name?

> Yeah, that sounds good enough ... I seem to recall having used
> casttarget as condition a couple of times, but I think it's a strange
> enough case that it is OK to just modify the query when that's needed;
> normal usage would seem to be what you propose.

Here's a draft patch for this.  One possible objection is that the
default behavior changes subtly: only casts whose source types are
visible in the search path will be shown by default.  In practice
I doubt that will make any difference, so I didn't bother to try to
avoid it --- we could special-case no pattern but I think it'd look
like a wart before long.

Comments?

            regards, tom lane

Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.211
diff -c -r1.211 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml    6 Sep 2008 20:18:08 -0000    1.211
--- doc/src/sgml/ref/psql-ref.sgml    4 Nov 2008 22:44:08 -0000
***************
*** 894,903 ****


        <varlistentry>
!         <term><literal>\dC</literal></term>
          <listitem>
          <para>
          Lists all available type casts.
          </para>
          </listitem>
        </varlistentry>
--- 894,906 ----


        <varlistentry>
!         <term><literal>\dC [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
          <listitem>
          <para>
          Lists all available type casts.
+         If <replaceable class="parameter">pattern</replaceable>
+         is specified, only casts whose source types match the pattern are
+         listed.
          </para>
          </listitem>
        </varlistentry>
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.186
diff -c -r1.186 describe.c
*** src/bin/psql/describe.c    3 Nov 2008 19:08:56 -0000    1.186
--- src/bin/psql/describe.c    4 Nov 2008 22:44:08 -0000
***************
*** 2082,2091 ****

      initPQExpBuffer(&buf);
      /*
!      * We need left join here for binary casts.  Also note that we don't
!      * attempt to localize '(binary coercible)', because there's too much
!      * risk of gettext translating a function name that happens to match
!      * some string in the PO database.
       */
      printfPQExpBuffer(&buf,
                 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
--- 2082,2091 ----

      initPQExpBuffer(&buf);
      /*
!      * We need a left join to pg_proc for binary casts; the others are just
!      * paranoia.  Also note that we don't attempt to localize '(binary
!      * coercible)', because there's too much risk of gettext translating a
!      * function name that happens to match some string in the PO database.
       */
      printfPQExpBuffer(&buf,
                 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
***************
*** 2099,2111 ****
                        "       END as \"%s\"\n"
                   "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
                        "     ON c.castfunc = p.oid\n"
!                       "ORDER BY 1, 2",
                        gettext_noop("Source type"),
                        gettext_noop("Target type"),
                        gettext_noop("Function"),
                        gettext_noop("no"), gettext_noop("in assignment"), gettext_noop("yes"),
                        gettext_noop("Implicit?"));

      res = PSQLexec(buf.data, false);
      termPQExpBuffer(&buf);
      if (!res)
--- 2099,2125 ----
                        "       END as \"%s\"\n"
                   "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
                        "     ON c.castfunc = p.oid\n"
!                       "     LEFT JOIN pg_catalog.pg_type t\n"
!                       "     ON c.castsource = t.oid\n"
!                       "     LEFT JOIN pg_catalog.pg_namespace n\n"
!                       "     ON n.oid = t.typnamespace\n",
                        gettext_noop("Source type"),
                        gettext_noop("Target type"),
                        gettext_noop("Function"),
                        gettext_noop("no"), gettext_noop("in assignment"), gettext_noop("yes"),
                        gettext_noop("Implicit?"));

+     /*
+      * Match name pattern against either internal or external name of the
+      * castsource type
+      */
+     processSQLNamePattern(pset.db, &buf, pattern, false, false,
+                           "n.nspname", "t.typname",
+                           "pg_catalog.format_type(t.oid, NULL)",
+                           "pg_catalog.pg_type_is_visible(t.oid)");
+
+     appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
+
      res = PSQLexec(buf.data, false);
      termPQExpBuffer(&buf);
      if (!res)
Index: src/bin/psql/help.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/help.c,v
retrieving revision 1.130
diff -c -r1.130 help.c
*** src/bin/psql/help.c    29 Aug 2008 15:52:07 -0000    1.130
--- src/bin/psql/help.c    4 Nov 2008 22:44:08 -0000
***************
*** 200,206 ****
      fprintf(output, _("  \\da [PATTERN]  list aggregate functions\n"));
      fprintf(output, _("  \\db [PATTERN]  list tablespaces (add \"+\" for more detail)\n"));
      fprintf(output, _("  \\dc [PATTERN]  list conversions\n"));
!     fprintf(output, _("  \\dC            list casts\n"));
      fprintf(output, _("  \\dd [PATTERN]  show comment for object\n"));
      fprintf(output, _("  \\dD [PATTERN]  list domains\n"));
      fprintf(output, _("  \\df [PATTERN]  list functions (add \"+\" for more detail)\n"));
--- 200,206 ----
      fprintf(output, _("  \\da [PATTERN]  list aggregate functions\n"));
      fprintf(output, _("  \\db [PATTERN]  list tablespaces (add \"+\" for more detail)\n"));
      fprintf(output, _("  \\dc [PATTERN]  list conversions\n"));
!     fprintf(output, _("  \\dC [PATTERN]  list casts\n"));
      fprintf(output, _("  \\dd [PATTERN]  show comment for object\n"));
      fprintf(output, _("  \\dD [PATTERN]  list domains\n"));
      fprintf(output, _("  \\df [PATTERN]  list functions (add \"+\" for more detail)\n"));