Re: Selecting user-defined CASTs

On 08/08/2011 06:31 PM, Joe Abbate wrote:
> 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.

Well, for my specific example (both source and target are pg_catalog
types and the function is in the public schema), the following query
does the trick:

SELECT castsource::regtype AS source,
casttarget::regtype AS target,
castfunc::regprocedure AS function,
castcontext AS context, castmethod AS method,
description
FROM pg_cast c
JOIN pg_type s ON (castsource = s.oid)
JOIN pg_namespace sn ON (s.typnamespace = sn.oid)
JOIN pg_type t ON (casttarget = t.oid)
JOIN pg_namespace tn ON (t.typnamespace = tn.oid)
LEFT JOIN pg_proc p ON (castfunc = p.oid)
LEFT JOIN pg_namespace pn ON (p.pronamespace = pn.oid)
LEFT JOIN pg_description d
ON (c.oid = d.objoid AND d.objsubid = 0)
WHERE (substring(sn.nspname for 3) = 'pg_'
AND substring(tn.nspname for 3) = 'pg_'
AND castfunc != 0 AND substring(pn.nspname for 3) != 'pg_')
ORDER BY castsource, casttarget;

I realize that for the general case, the WHERE clause has to be expanded
(and may look much, much uglier). Nevertheless, if somebody has some
simplifications, I'd be glad to hear them.

Joe