Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to create a new composite type using already existing composite types

how to create a new composite type using already existing composite types

From
Iain Barnett
Date:
Hi,

Is there a way to create a new composite type using already existing composite type?

For example,

CREATE TABLE inventory_item (   name            text,   supplier_id     integer REFERENCES suppliers,   price           numeric CHECK (price > 0)
);

produces the following type automatically
CREATE TYPE inventory_item AS (   name            text,   supplier_id     integer,   price           numeric
);

but I'd also like to have a type with an extra column for certain functions

CREATE TYPE inventory_item2 AS (   name            text,   supplier_id     integer,   price           numeric,
size	integer
);

but it would be handy if I could reuse inventory_item instead of having to retype the whole lot. I can't work out or find the right syntax, can anyone show me how? Any help would be much appreciated.

Regards
Iain

Re: how to create a new composite type using already existing composite types

From
Jeff Davis
Date:
On Wed, 2010-02-10 at 03:46 +0000, Iain Barnett wrote:

> CREATE TYPE inventory_item2 AS (
>     name            text,
>     supplier_id     integer,
>     price           numeric,
> size    integer
> );
>
>
> but it would be handy if I could reuse inventory_item instead of
> having to retype the whole lot. I can't work out or find the right
> syntax, can anyone show me how? Any help would be much appreciated.

What about:

  CREATE TYPE inventory_item2 AS (
    ii inventory_item,
    size integer
  );

or:

  CREATE TABLE inventory_item2 (
    LIKE inventory_item,
    size integer
  );

and that will automatically create the type.

Regards,
    Jeff Davis

Re: how to create a new composite type using already existing composite types

From
Merlin Moncure
Date:
On Sat, Feb 13, 2010 at 7:24 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> On Wed, 2010-02-10 at 03:46 +0000, Iain Barnett wrote:
>
>> CREATE TYPE inventory_item2 AS (
>>     name            text,
>>     supplier_id     integer,
>>     price           numeric,
>> size  integer
>> );
>>
>>
>> but it would be handy if I could reuse inventory_item instead of
>> having to retype the whole lot. I can't work out or find the right
>> syntax, can anyone show me how? Any help would be much appreciated.
>
> What about:
>
>  CREATE TYPE inventory_item2 AS (
>    ii inventory_item,
>    size integer
>  );
>
> or:
>
>  CREATE TABLE inventory_item2 (
>    LIKE inventory_item,
>    size integer
>  );

Pretty neat.  I wasn't aware you could mix LIKE with explicit field
declarations.

Those expressions produce different results...the first creates a type
within a type (nested) and the second creates a new unnested type
based on the fields of the original type.  The first expression
creates a dependency while the second does not.  This flexibility is
only possible if you create types with the 'create table' statement.

merlin