Thread: TIME column manipulation/comparison hangups

TIME column manipulation/comparison hangups

From
Bill Moran
Date:
I'm trying to test the time in a time column to see if it's the same
minute as the current time.  I wouldn't have thought this would be
difficult:

WHERE TO_CHAR(now(), 'HH24MI') = TO_CHAR(time_column, 'HH24MI')

Doesn't work, though:
ERROR:  function to_char(time with time zone, unknown) does not exist

So, I tried to force it:
WHERE TO_CHAR(now(), 'HH24MI') = TO_CHAR(time_column::TIMESTAMP WITH TIME ZONE, 'HH24MI')

Which led to an invalid cast.  I also tried using date_trunc() with no
success.  It seems as if EXTRACT() will work, but it sure feels hacky
to do:

(extract(hours from now()) = extract(hours from time_column)
 AND
 (extract(minutes from now()) = extract(minutes from time_column)

Am I missing something obvious?  Anyone have any better methods for
doing this?  I'm working on 8.3.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Re: TIME column manipulation/comparison hangups

From
Raymond O'Donnell
Date:
On 03/11/2008 19:01, Bill Moran wrote:

> It seems as if EXTRACT() will work, but it sure feels hacky
> to do:
>
> (extract(hours from now()) = extract(hours from time_column)
>  AND
>  (extract(minutes from now()) = extract(minutes from time_column)

I'd have thought that this was the correct way to do it. Anyway, you
could encapsulate this in a function to make re-use easier (the
following hasn't been tested):

create function is_same_minute(time with time zone, time with time zone)
returns bool
as
$$
  select
    (extract(hours from $1) = extract(hours from $2))
    and
    (extract(minutes from $1) = extract(minutes from $2));
$$
language sql;

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: TIME column manipulation/comparison hangups

From
"Scott Marlowe"
Date:
On Mon, Nov 3, 2008 at 12:01 PM, Bill Moran
<wmoran@collaborativefusion.com> wrote:
>
> I'm trying to test the time in a time column to see if it's the same
> minute as the current time.  I wouldn't have thought this would be
> difficult:
>
> WHERE TO_CHAR(now(), 'HH24MI') = TO_CHAR(time_column, 'HH24MI')

Use date_trunc

where date_trunc('minute',timefield)=date_trunc('minute',now());

I might have the args backwards.

Re: TIME column manipulation/comparison hangups

From
Bill Moran
Date:
In response to "Scott Marlowe" <scott.marlowe@gmail.com>:

> On Mon, Nov 3, 2008 at 12:01 PM, Bill Moran
> <wmoran@collaborativefusion.com> wrote:
> >
> > I'm trying to test the time in a time column to see if it's the same
> > minute as the current time.  I wouldn't have thought this would be
> > difficult:
> >
> > WHERE TO_CHAR(now(), 'HH24MI') = TO_CHAR(time_column, 'HH24MI')
>
> Use date_trunc
>
> where date_trunc('minute',timefield)=date_trunc('minute',now());
>
> I might have the args backwards.

Hunh ...

# select date_trunc('minute','13:45:15'::time);
     date_trunc
--------------------
 @ 13 hours 45 mins
(1 row)

# select date_trunc('minute','13:45:15'::time with time zone);
ERROR:  function date_trunc(unknown, time with time zone) does not exist
LINE 1: select date_trunc('minute','13:45:15'::time with time zone);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

# select date_trunc('minute',('13:45:15'::time with time zone)::time);
     date_trunc
--------------------
 @ 13 hours 45 mins
(1 row)

Curiouser and curiouser ...

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Re: TIME column manipulation/comparison hangups

From
"Scott Marlowe"
Date:
On Mon, Nov 3, 2008 at 12:25 PM, Bill Moran
<wmoran@collaborativefusion.com> wrote:
> In response to "Scott Marlowe" <scott.marlowe@gmail.com>:
>
>> On Mon, Nov 3, 2008 at 12:01 PM, Bill Moran
>> <wmoran@collaborativefusion.com> wrote:
>> >
>> > I'm trying to test the time in a time column to see if it's the same
>> > minute as the current time.  I wouldn't have thought this would be
>> > difficult:
>> >
>> > WHERE TO_CHAR(now(), 'HH24MI') = TO_CHAR(time_column, 'HH24MI')
>>
>> Use date_trunc
>>
>> where date_trunc('minute',timefield)=date_trunc('minute',now());
>>
>> I might have the args backwards.
>
> Hunh ...
>
> # select date_trunc('minute','13:45:15'::time);
>     date_trunc
> --------------------
>  @ 13 hours 45 mins
> (1 row)
>
> # select date_trunc('minute','13:45:15'::time with time zone);
> ERROR:  function date_trunc(unknown, time with time zone) does not exist
> LINE 1: select date_trunc('minute','13:45:15'::time with time zone);
>               ^
> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
>
> # select date_trunc('minute',('13:45:15'::time with time zone)::time);
>     date_trunc
> --------------------
>  @ 13 hours 45 mins
> (1 row)
>
> Curiouser and curiouser ...

Ahhh, not timestamps, but times...  You might have to add the time to
some date to run it through date_trunc.

Re: TIME column manipulation/comparison hangups

From
"Scott Marlowe"
Date:
On Mon, Nov 3, 2008 at 12:31 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Nov 3, 2008 at 12:25 PM, Bill Moran
> <wmoran@collaborativefusion.com> wrote:
>> In response to "Scott Marlowe" <scott.marlowe@gmail.com>:
>>
>>> On Mon, Nov 3, 2008 at 12:01 PM, Bill Moran
>>> <wmoran@collaborativefusion.com> wrote:
>>> >
>>> > I'm trying to test the time in a time column to see if it's the same
>>> > minute as the current time.  I wouldn't have thought this would be
>>> > difficult:
>>> >
>>> > WHERE TO_CHAR(now(), 'HH24MI') = TO_CHAR(time_column, 'HH24MI')
>>>
>>> Use date_trunc
>>>
>>> where date_trunc('minute',timefield)=date_trunc('minute',now());
>>>
>>> I might have the args backwards.
>>
>> Hunh ...
>>
>> # select date_trunc('minute','13:45:15'::time);
>>     date_trunc
>> --------------------
>>  @ 13 hours 45 mins
>> (1 row)
>>
>> # select date_trunc('minute','13:45:15'::time with time zone);
>> ERROR:  function date_trunc(unknown, time with time zone) does not exist
>> LINE 1: select date_trunc('minute','13:45:15'::time with time zone);
>>               ^
>> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
>>
>> # select date_trunc('minute',('13:45:15'::time with time zone)::time);
>>     date_trunc
>> --------------------
>>  @ 13 hours 45 mins
>> (1 row)
>>
>> Curiouser and curiouser ...
>
> Ahhh, not timestamps, but times...  You might have to add the time to
> some date to run it through date_trunc.

Actually, the more I look at this the more I think extract / date_part
might be your best answer.

In response to "Scott Marlowe" <scott.marlowe@gmail.com>:

> On Mon, Nov 3, 2008 at 12:25 PM, Bill Moran
> <wmoran@collaborativefusion.com> wrote:
> > In response to "Scott Marlowe" <scott.marlowe@gmail.com>:
> >
> >> On Mon, Nov 3, 2008 at 12:01 PM, Bill Moran
> >> <wmoran@collaborativefusion.com> wrote:
> >> >
> >> > I'm trying to test the time in a time column to see if it's the same
> >> > minute as the current time.  I wouldn't have thought this would be
> >> > difficult:
> >> >
> >> > WHERE TO_CHAR(now(), 'HH24MI') = TO_CHAR(time_column, 'HH24MI')
> >>
> >> Use date_trunc
> >>
> >> where date_trunc('minute',timefield)=date_trunc('minute',now());
> >>
> >> I might have the args backwards.
> >
> > Hunh ...
> >
> > # select date_trunc('minute','13:45:15'::time);
> >     date_trunc
> > --------------------
> >  @ 13 hours 45 mins
> > (1 row)
> >
> > # select date_trunc('minute','13:45:15'::time with time zone);
> > ERROR:  function date_trunc(unknown, time with time zone) does not exist
> > LINE 1: select date_trunc('minute','13:45:15'::time with time zone);
> >               ^
> > HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
> >
> > # select date_trunc('minute',('13:45:15'::time with time zone)::time);
> >     date_trunc
> > --------------------
> >  @ 13 hours 45 mins
> > (1 row)
> >
> > Curiouser and curiouser ...
>
> Ahhh, not timestamps, but times...  You might have to add the time to
> some date to run it through date_trunc.

Not quite.  As shown in the examples, date_trunc() works fine on
TIME WITHOUT TIME ZONE, but there's no such function for TIME WITH
TIME ZONE.

Is that an oversight, or does the timezone add some ambiguity that
date_trunc() can't handle?

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Bill Moran <wmoran@collaborativefusion.com> writes:
> Not quite.  As shown in the examples, date_trunc() works fine on
> TIME WITHOUT TIME ZONE, but there's no such function for TIME WITH
> TIME ZONE.

Well, actually there's no date_trunc for time either:

regression=# \df date_trunc
                                     List of functions
   Schema   |    Name    |      Result data type       |        Argument data types
------------+------------+-----------------------------+-----------------------------------
 pg_catalog | date_trunc | interval                    | text, interval
 pg_catalog | date_trunc | timestamp with time zone    | text, timestamp with time zone
 pg_catalog | date_trunc | timestamp without time zone | text, timestamp without time zone
(3 rows)

However, the interval version of the function can capture the time case
because there's an implicit cast from time to interval:

regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource =
'time'::regtype;
       casttarget       | castcontext |                castfunc
------------------------+-------------+----------------------------------------
 interval               | i           | "interval"(time without time zone)
 time with time zone    | i           | timetz(time without time zone)
 time without time zone | i           | "time"(time without time zone,integer)
(3 rows)

There's no implicit cast from timetz to interval, which I suppose is
because it would be an information-losing transform.

            regards, tom lane

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

From
Alvaro Herrera
Date:
Tom Lane escribió:

> However, the interval version of the function can capture the time case
> because there's an implicit cast from time to interval:
>
> regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource =
'time'::regtype;
>        casttarget       | castcontext |                castfunc
> ------------------------+-------------+----------------------------------------
>  interval               | i           | "interval"(time without time zone)
>  time with time zone    | i           | timetz(time without time zone)
>  time without time zone | i           | "time"(time without time zone,integer)
> (3 rows)

BTW it very much looks like we should have a pg_casts view that displays
these things in a human-readable manner (like the above except with
castcontext expanded)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane escribi�:
>> regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource =
'time'::regtype;

> BTW it very much looks like we should have a pg_casts view that displays
> these things in a human-readable manner (like the above except with
> castcontext expanded)

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?

            regards, tom lane

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

From
Alvaro Herrera
Date:
Tom Lane escribió:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Tom Lane escribi�:
> >> regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource =
'time'::regtype;
>
> > BTW it very much looks like we should have a pg_casts view that displays
> > these things in a human-readable manner (like the above except with
> > castcontext expanded)
>
> 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.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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"));

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

From
Peter Eisentraut
Date:
Alvaro Herrera wrote:
> Tom Lane escribió:
>
>> However, the interval version of the function can capture the time case
>> because there's an implicit cast from time to interval:
>>
>> regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource =
'time'::regtype;
>>        casttarget       | castcontext |                castfunc
>> ------------------------+-------------+----------------------------------------
>>  interval               | i           | "interval"(time without time zone)
>>  time with time zone    | i           | timetz(time without time zone)
>>  time without time zone | i           | "time"(time without time zone,integer)
>> (3 rows)
>
> BTW it very much looks like we should have a pg_casts view that displays
> these things in a human-readable manner (like the above except with
> castcontext expanded)

Could we change the data types of the pg_cast table to regprocedure and
regtype instead?


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

From
Peter Eisentraut
Date:
Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Tom Lane escribi�:
>>> regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource =
'time'::regtype;
>
>> BTW it very much looks like we should have a pg_casts view that displays
>> these things in a human-readable manner (like the above except with
>> castcontext expanded)
>
> 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?

I'd say it could be a pattern for both source and target.  Often times I
am interested in casts in either direction.

Peter Eisentraut <peter_e@gmx.net> writes:
> Could we change the data types of the pg_cast table to regprocedure and
> regtype instead?

Back when we first introduced the reg-foo types, there was some
discussion of changing all relevant catalog columns to those types,
but the idea crashed and burned for reasons I don't recall right
at the moment.

            regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> Maybe we should just agree that its argument is a pattern for the
>> castsource type's name?

> I'd say it could be a pattern for both source and target.  Often times I
> am interested in casts in either direction.

Well, it makes the query markedly uglier, but I suppose we aren't too
concerned about the performance of \dC.  New proposed patch attached.

            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    5 Nov 2008 17:41:12 -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 or target 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    5 Nov 2008 17:41:12 -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,2137 ----
                        "       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 ts\n"
!                       "     ON c.castsource = ts.oid\n"
!                       "     LEFT JOIN pg_catalog.pg_namespace ns\n"
!                       "     ON ns.oid = ts.typnamespace\n"
!                       "     LEFT JOIN pg_catalog.pg_type tt\n"
!                       "     ON c.casttarget = tt.oid\n"
!                       "     LEFT JOIN pg_catalog.pg_namespace nt\n"
!                       "     ON nt.oid = tt.typnamespace\n"
!                       "WHERE (true",
                        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 either
+      * castsource or casttarget
+      */
+     processSQLNamePattern(pset.db, &buf, pattern, true, false,
+                           "ns.nspname", "ts.typname",
+                           "pg_catalog.format_type(ts.oid, NULL)",
+                           "pg_catalog.pg_type_is_visible(ts.oid)");
+
+     appendPQExpBuffer(&buf, ") OR (true");
+
+     processSQLNamePattern(pset.db, &buf, pattern, true, false,
+                           "nt.nspname", "tt.typname",
+                           "pg_catalog.format_type(tt.oid, NULL)",
+                           "pg_catalog.pg_type_is_visible(tt.oid)");
+
+     appendPQExpBuffer(&buf, ")\nORDER 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    5 Nov 2008 17:41:12 -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"));