Thread: TIME column manipulation/comparison hangups
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
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
------------------------------------------------------------------
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.
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
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.
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.
date_trun() with timezones? (was 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: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
Re: date_trun() with timezones? (was Re: TIME column manipulation/comparison hangups)
From
Tom Lane
Date:
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
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: TIME column ...))
From
Tom Lane
Date:
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.
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: TIME column ...))
From
Tom Lane
Date:
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.
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: TIME column ...))
From
Tom Lane
Date:
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
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: TIME column ...))
From
Tom Lane
Date:
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"));