On 11. Dezember 2014 at 00:08:52, Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Manuel Kniep writes:
> > I have a custom type and want to add the yet missing SEND and RECEIVE functions
> > is there anyway to alter the type definition without dropping and recreating it?
>
> There's no supported way to do that. As an unsupported way, you could
> consider a manual UPDATE on the type's pg_type row.
I also thought about this but I guess I have to INSERT the dependency in pg_depend too.
assuming the receiver function is in the same namespace as the type I’d do something like this:
UPDATE pg_catalog.pg_type t
SET typreceive = ‘my_recv', typsend = ‘my_send'
WHERE t.typname = ‘my_type’;
INSERT INTO pg_catalog.pg_depend (classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype)
SELECT c.oid AS classid, p.oid as objid, 0 AS objsubid, refc.oid AS refclassid, t.oid AS refobjid, 0 AS refobjsubid,
'n'AS deptype
FROM pg_catalog.pg_class c, pg_catalog.pg_proc p, pg_catalog.pg_class refc, pg_catalog.pg_type t
WHERE c.relname='pg_proc' AND p.proname IN(‘my_recv', ‘my_send') AND refc.relname = 'pg_type' AND t.typname =‘my_type'
AND p.pronamespace = t.typnamespace
AND c.relnamespace = refc.relnamespace
AND c.relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog’);
Are there any other pg_catalog tables that might need informations?
thanks
Manuel