Re: Selecting user-defined CASTs

Joe Abbate <jma(at)freedomcircle(dot)com> writes:
> I'm trying to query the catalogs to select only the user-defined CASTs

This is rather difficult to do, actually, because pg_cast stores
neither an owner nor a schema for casts, which eliminates all of the
principled ways in which you might decide that a cast belongs to "the
system" or "the user".

An easy but unprincipled way to do it is

select ... from pg_cast c where c.oid >= 16384;

What that really does is eliminate the casts that were installed during
initdb, which are at least a subset of the "system" ones, and might be
all of them depending on what you feel a "system" cast is. The main
shortcoming of it is that there's no very good way to eliminate casts
installed by extensions, should you want to not consider those "user"
casts.

Another approach is to check pg_depend. A cast installed by initdb will
match a "pin" entry in pg_depend (refclassid = pg_cast, refobjid =
cast's OID, deptype = 'p'). You're still out of luck for distinguishing
extension members in existing releases, but in 9.1 and up it'll be
possible to identify casts belonging to extensions from pg_depend
entries.

regards, tom lane