Derived type in PostgreSQL Derived type in PostgreSQL sql sql

Derived type in PostgreSQL

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