PostgreSQL CREATE TYPE using multiple relations

composite-typesdatabase-theorydatatypespostgresql

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.

Best Answer

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

Related Solutions

PostgreSQL – Is ‘internallength’ Useful in Custom Type Definition?

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc.

What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the manual once more.

To optimize disk space and performance I would rather avoid using composite types due to big row overhead. Details depend on the details of your requirements.

PostgreSQL – Return Type of JOINing Stored Procedure

No, I am afraid that's not possible. I have been wishing this was possible myself on several occasions. Either you have a registered row (composite) type that matches the return type or you have to list columns individually.

CREATE or REPLACE FUNCTION select_join()
  RETURNS TABLE (col1 int, col2 date, ...) AS ...

There is a somewhat awkward workaround with returning a nested composite type, but it's not exactly the same. See:

Easy way to have return type be SETOF table plus additional fields?

Or you create a composite type:

CREATE TYPE foo AS (col1 int, col2 date, ...);

CREATE or REPLACE FUNCTION select_join()
  RETURNS SETOF foo AS ...

A VIEW also registers its row type automatically. For the simple test case you present, you might just use a view instead of the function to begin with (like @a_horse commented):

CREATE OR REPLACE VIEW my_view AS 
SELECT t1.*, t2.name
FROM t1 JOIN t2 ON t1.t2_id = t2.id;

Or you create the view for the purpose of registering the row type conveniently. You might add LIMIT 0 to document that the view is not meant to return rows, but that's optional.

CREATE or REPLACE FUNCTION select_join()
  RETURNS SETOF my_view AS ...

Be aware that this introduces a dependency (like with any other view) and Postgres will prevent dropping the table and some other manipulations, unless you drop depending views first.

Best Answer

Related Solutions

PostgreSQL – Is ‘internallength’ Useful in Custom Type Definition?

PostgreSQL – Return Type of JOINing Stored Procedure

Related Question