Thread: Selecting user-defined CASTs
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
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_types 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.nspnamefor 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
Joe Abbate <jma@freedomcircle.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
On 09.08.2011 08:27, Tom Lane wrote: > 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. That will also exclude "system" casts that have been removed, and manually re-created later. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On 08/09/2011 01:27 AM, Tom Lane wrote: > 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. Intriguing alternative. The query is for my dbtoyaml utility which is sort of equivalent to pg_dump -s --format=y (yaml). So for now, I'll stick to emulating what dumpCast in pg_dump.c does. Joe