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.

  • 1
    what do you mean? can you give an example? – Jasen Jul 6 '19 at 23:16
  • "CREATE TYPE compfoo AS (f1 relation, f2 relation);" For some reason it feels like you are looking for Inheritance instead – Raymond Nijland Oct 21 '19 at 19:00
  • .. also it feels like you are looking for MULTISET (ANSI/ISO SQL 2003) – Raymond Nijland Oct 21 '19 at 19:07

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;


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

check your version

  • I'm talking about passing a table or relation type instead of a table. Sorry if unclear. – Zeruno Jul 6 '19 at 19:45
  • In PostgreSQL a table automatically becomes a data type – Anthony Sotolongo Jul 7 '19 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.