Обсуждение: ALTER TYPE ADD SEND AND RECEIVE
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?
Manuel
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