Derived type in PostgreSQL
Asked Answered
B

2

11

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?

Behah answered 3/2, 2015 at 8:50 Comment(0)
H
8

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

Hermit answered 3/2, 2015 at 10:13 Comment(0)
H
2

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
Hedrick answered 3/2, 2015 at 9:48 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.