Derived type in PostgreSQL

Question

Is it possible to create a "derived type" from a type? Like extends in Java.

For instance I need these types:

create type mytype as (
    f1 int,
    --many other fields...
    fn varchar(10)
);

create type mytype_extended as (
    f1 int,
    --many other fields...
    fn varchar(10),

    fx int --one field more
);

You can see that this is redundant. If in the future I'll change mytype, I'll need to change mytype_extended too.

I tried this:

create type mytype as (
    f1 int,
    --many other fields...
    fn varchar(10)
);

create type mytype_extended as (
    mt mytype,

    fx int --one field more
);

but this leads mytype_extended to have just 2 fields, mt (a complex type, I think) and fx, instead of f1, f2... fn, fx.

Is there a way to accomplish this?

Answer 1

In PostgreSQL, there is no direct type inheritance, but you have a few options:

1. Table inheritance

You can create inherited tables to create inherited types (PostgreSQL will always create a composite type for every table, with the same name):

create table supertable (
  foo   int,
  bar   text
);

create table subtable (
  baz   int
) inherits (supertable);

2. Construct views using each other

Because views are (in reality) tables (with rules), a type is created for each of them too:

create view superview
  as select null::int  foo,
            null::text bar;

create view subview
  as select superview.*,
            null::int  baz
     from   superview;

3. Type composition

This is what, you've tried. You have more control with this one in general:

create type supertype as (
  foo   int,
  bar   text
);

create type subtype as (
  super supertype,
  baz   int
);

-- resolve composition manually
select get_foo(v),        -- this will call get_foo(subtype)
       get_foo((v).super) -- this will call get_foo(supertype)
from   (values (((1, '2'), 3)::subtype)) v(v);

+1 True type inheritance?

PostgreSQL's documentation explicitly says, that table inheritance is not the standard's type inheritance:

SQL:1999 and later define a type inheritance feature, which differs in many respects from the features described here.

Nevertheless, inherited table's auto-created types really work like true inherited types (they can be used, where the super type can be used):

-- if there is a get_foo(supertable) function,
-- but there is no get_foo(subtable) function:

select get_foo((1, '2')::supertable);  -- will call get_foo(supertable)
select get_foo((1, '2', 3)::subtable); -- will also call get_foo(supertable)

SQLFiddle