According to my reading of the PostgreSQL documentation on ALTER TYPE I should be able to do the following:

CREATE TYPE compfoo AS OBJECT (f1 int, f2 text);
CREATE TYPE compbar AS OBJECT (f1 int, f2 compfoo);
ALTER TYPE compbar DROP ATTRIBUTE f2;
ALTER TYPE compbar ADD ATTRIBUTE f2 varying(1000);
ALTER TYPE compbar ADD ATTRIBUTE f3 compfoo;

However, when I try it, I get the following error:

ERROR: ALTER TYPE ADD/DROP COLUMN statement not supported for object types

What am I missing here? Is there a server configuration that will allow me to do this? Is it because the type in question is a composite OBJECT type, as opposed to a composite of 'basic' types?

UPDATE 2015-02-10 I've updated the title, and the samples to more closely resemble the problem at hand. In short, within Postgres Plus Advanced Server, there's additional syntax for creating types, in the form CREATE TYPE foo AS OBJECT <--- that syntax is the root of the issue. You cannot alter attributes when the composite type was created AS OBJECT.

1

There are 1 best solutions below

0
On
CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE combbar AS (f1 int, f2 compfoo);
ALTER TYPE compbar DROP ATTRIBUTE f2;
---------------^ maybe you should write combbar?

To make it clear you are defining a type named "combbar" and then you try to alter a type with the name "compbar".

UPDATE

Besides that typo, the SQL seems to run fine.