From: | Joe Abbate <jma(at)freedomcircle(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Selecting user-defined CASTs |
Date: | 2011-08-08 22:31:49 |
Message-ID: | 4E4063D5.1070002@freedomcircle.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Abbate | 2011-08-09 00:02:30 | Re: Selecting user-defined CASTs |
Previous Message | Josh Kupershmidt | 2011-08-08 22:10:55 | Re: psql: display of object comments |