Derived Type in PostgreSQL

Question

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

For example, 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 change mytype , I will also need to change mytype_extended .

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 to the fact that mytype_extended has only 2 fields, mt (complex type, I think) and fx instead of f1, f2... fn, fx .

Is there any way to do this?

Answer 1

There is no direct type inheritance in PostgreSQL, but you have several options:

1. Inheritance of tables

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

 create table supertable ( foo int, bar text ); create table subtable ( baz int ) inherits (supertable); 

2. Build views with each other

Since views are (in fact) tables (with rules ), a type is created for each of them:

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

3. Type of composition

This is what you have tried. You have more control with this overall:

 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 Inheritance True type?

The PostgreSQL documentation explicitly says that table inheritance is not standard type inheritance:

SQL: 1999 and later define a type inheritance function that is very different from the functions described here.

However, inherited automatically generated table types do work as true inherited types (you can use them where you can use a super type):

 -- 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

Answer 2

You can use table inheritance to do this, since any table implicitly defines a type. Quoted from CREATE TABLE :

CREATE TABLE also automatically creates a data type that represents a composite type that matches one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema.

An example with tables:

 create table mytype ( f1 int, --many other fields... fn varchar(10) ); create table mytype_extended( fx int ) inherits (mytype); 

When describing a table with psql:

  # \ d mytype_extended

        Table "public.mytype_extended"
  Column |  Type |  Modifiers 
 -------- + ----------------------- + -----------
  f1 |  integer | 
  fn |  character varying (10) | 
  fx |  integer | 
 Inherits: mytype

Now add the column to the base table and make sure the legacy table gets it:

 alter table mytype add other_column int; 

  # \ d mytype_extended
           Table "public.mytype_extended"
     Column |  Type |  Modifiers 
 -------------- + ----------------------- + -----------
  f1 |  integer | 
  fn |  character varying (10) | 
  fx |  integer | 
  other_column |  integer | 
 Inherits: mytype