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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general