Re: Overhaul of type attributes modification - Mailing list pgadmin-hackers

On 8 July 2011 15:19, Thom Brown <thom@linux.com> wrote:
> Hi all,
>
> I noticed that if you add, delete, rename or change the type,
> collation or precision of a composite type attribute, it deletes all
> of them then adds them all back in.  Obviously attribute additions,
> deletions and modifications may only occur for types under PostgreSQL
> 9.1, but I thought it a bit extreme to actually drop all types if
> you're adding a new one in, or just removing one.  Also, if you rename
> a type, it drops all attributes again then re-adds them, just to have
> an attribute with a different name.
>
> So I've attached a patch which "does the right thing".  Take the
> example of the following type:
>
> CREATE TYPE bark AS
>   (one text,
>    two text,
>    three text,
>    four text,
>    five text);
>
> Say we wanted to remove "two", change the type of three to uuid,
> rename four to forty and add an extra text type of six.
>
> Normally we'd just get:
>
> ALTER TYPE bark DROP ATTRIBUTE one;
> ALTER TYPE bark DROP ATTRIBUTE two;
> ALTER TYPE bark DROP ATTRIBUTE three;
> ALTER TYPE bark DROP ATTRIBUTE four;
> ALTER TYPE bark DROP ATTRIBUTE five;
> ALTER TYPE bark ADD ATTRIBUTE one text;
> ALTER TYPE bark ADD ATTRIBUTE three text;
> ALTER TYPE bark ADD ATTRIBUTE forty uuid;
> ALTER TYPE bark ADD ATTRIBUTE five text;
> ALTER TYPE bark ADD ATTRIBUTE six uuid;
>
> With these changes we'd now get:
>
> ALTER TYPE bark DROP ATTRIBUTE two;
> ALTER TYPE bark RENAME ATTRIBUTE four TO forty;
> ALTER TYPE bark ADD ATTRIBUTE six text;
>
> .. except now those are also nicely indented to be more readable for
> types with long schemas/type names.
>
> e.g.
> ALTER TYPE long_schema_name.quite_a_long_table_name
>  ADD ATTRIBUTE "suspiciously long attribute name"?
>
> It also fixes a bug whereby if you have a precision specified, the
> word COLLATE mysteriously appears after the type whether or not you
> have a collation assigned because the collation condition was based on
> the precision being present for some reason.  And if you actually
> assigned a collation for a valid data type, it wouldn't appear at all,
> so that's fixed too.

Actually I noticed that the example I gave of what would occur before
the patch highlights another bug.  Notice that forty and six have the
type of uuid, and three doesn't?  Three should have been the one with
uuid, and forty and sixty should have been text.  That's not my typo
as that's come from PgAdmin 1.14 beta 2.  So it was actually assigning
the wrong data types in those case too.  So the patch fixes those
problems too.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company