Re: ALTER TYPE ADD SEND AND RECEIVE

On 11. Dezember 2014 at 00:08:52, Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)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