Обсуждение: ALTER TYPE ADD SEND AND RECEIVE

Manuel Kniep <m.kniep@web.de> 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.

            regards, tom lane

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

Manuel Kniep <manuel@adjust.com> writes:
> On 11. Dezember 2014 at 00:08:52, Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> 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.

Personally, I wouldn't bother with that.  Good practice would be to have
any such type packaged as an extension; so if you add the new functions
to the extension (which is supported) there's no need to be terribly
tense about the intra-extension dependencies.  But even if you didn't
do that, the worst consequence of dropping the functions would be
"cache lookup failed" errors.

            regards, tom lane