Re: pg_casts view (was Re: date_trun() with timezones? (was Re: [GENERAL] TIME column ...))
Peter Eisentraut <[EMAIL PROTECTED]> 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 - 1.211
--- doc/src/sgml/ref/psql-ref.sgml 5 Nov 2008 17:41:12 -
***
*** 894,903
! \dC
Lists all available type casts.
--- 894,906
! \dC [ pattern ]
Lists all available type casts.
+ If pattern
+ is specified, only casts whose source or target types match the
+ pattern are listed.
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 - 1.186
--- src/bin/psql/describe.c 5 Nov 2008 17:41:12 -
***
*** 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,
+
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: [GENERAL] TIME column ...))
Peter Eisentraut <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: [GENERAL] TIME column ...))
Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: [GENERAL] TIME column ...))
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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: [GENERAL] TIME column ...))
Alvaro Herrera <[EMAIL PROTECTED]> 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 - 1.211
--- doc/src/sgml/ref/psql-ref.sgml 4 Nov 2008 22:44:08 -
***
*** 894,903
! \dC
Lists all available type casts.
--- 894,906
! \dC [ pattern ]
Lists all available type casts.
+ If pattern
+ is specified, only casts whose source types match the pattern are
+ listed.
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 - 1.186
--- src/bin/psql/describe.c 4 Nov 2008 22:44:08 -
***
*** 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
+* ca
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: [GENERAL] TIME column ...))
Tom Lane escribió: > Alvaro Herrera <[EMAIL PROTECTED]> 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: pg_casts view (was Re: date_trun() with timezones? (was Re: [GENERAL] TIME column ...))
Alvaro Herrera <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
pg_casts view (was Re: date_trun() with timezones? (was Re: [GENERAL] TIME column ...))
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
