Derived type in PostgreSQL
In PostgreSQL, there is no direct type inheritance, but you have a few options:
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);
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;
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 manuallyselect 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)
You may use table inheritance for this, as any table implicitly defines a type. Quoted from CREATE TABLE:
CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema.
Your example, with tables:
create table mytype ( f1 int, --many other fields... fn varchar(10));create table mytype_extended( fx int) inherits (mytype);
When describing the table with psql:
# \d mytype_extended Table "public.mytype_extended" Column | Type | Modifiers --------+-----------------------+----------- f1 | integer | fn | character varying(10) | fx | integer | Inherits: mytype
Now let's add a column to the base table and check that the inherited table gets it too:
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