Hi,
I'm trying to query the catalogs to select only the user-defined CASTs
(my test db only has one such CAST). Looking at pg_dump.c, I've come up
with the following so far:
SELECT castsource::regtype AS source, casttarget::regtype AS target, castfunc::regprocedure AS
function, castcontext AS context, castmethod AS method, description FROM pg_cast c LEFT JOIN
pg_descriptiond ON (c.oid = d.objoid AND d.objsubid = 0) WHERE (castfunc != 0 AND
substring(castfunc::regproc::textfor 3) != 'pg_') ORDER BY castsource, casttarget;
This excludes the vast majority of internal casts (172 of them) where
castfunc is 0 or castfunc::regproc causes castfunc to show up with
"pg_catalog." prepended to the function name. However, this still pulls
19 other rows, as shown in the excerpt output below (after setting
search_path to pg_catalog):
source | target | function | context | method | description
-------------------+---------------+-------------------------------------+---------+--------+-------------------------bigint
| regproc | oid(bigint)| i | f |bigint | oid | oid(bigint)| i | f
|
...
smallint | boolean | public.int2_bool(smallint)
| e | f | Test comment for cast 1integer | boolean | bool(integer)| e | f |
...interval | reltime | reltime(interval)| a | f |bit varying | bit varying |
varbit(bitvarying,integer,boolean)
| i | f |
(20 rows)
The smallint AS boolean CAST is mine and is the only one I want to retrieve.
It seems the only way out is to do something like a 9-way join between
pg_cast, pg_type, pg_proc and pg_namespace to test the source, target
and function namespaces much as dumpCast() does in pg_dump.c. Before I
go that route, I'd thought I'd check with -hackers to see if there's a
simpler way.
Regards,
Joe