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_description d
ON (c.oid = d.objoid AND d.objsubid = 0)
WHERE (castfunc != 0
AND substring(castfunc::regproc::text for 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 1
integer | boolean | bool(integer)
| e | f |
...
interval | reltime | reltime(interval)
| a | f |
bit varying | bit varying | varbit(bit varying,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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
