Selecting user-defined CASTs

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

Responses

Browse pgsql-hackers by date

  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