PostgreSQL CREATE TYPE using multiple relations?

Question

I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax.

It appears to me that this type does not allow to compose a type out of relations/tables. In other words, I can do this:

CREATE TYPE compfoo AS (f1 int, f2 text);

but I can't do

CREATE TYPE compfoo AS (f1 relation, f2 relation);

I was wondering if my understanding is correct, and if not, how one could achieve the latter?

I want to indicate that I don't have any particular application for it. I would actually be interested in starting a discussion on an application! I am just trying to expand my understanding of this feature which is often touted as a very powerful feature in the extensible DBMS world.

Answer 1

The old version with support is 9.4 today and i can achieve your case, and then in the recent version too.

for example:

create table a ( i int, j int);
create table b ( k text, l text);
CREATE TYPE compfoo AS (f1 a, f2 b);
create table c (m compfoo );
insert into c values ( row(row(1,2),row('hello','world'))   );
select * from c;

result:
("(1,2)","(hello,world)")

you can check this test in https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=4499c5d52e876979d81fb3f2537c2439

check your version