[SOLVED] Creating a TYPE with a SETOF field of another TYPE

Creating a TYPE with a SETOF field of another TYPE

I have some trouble trying to create a TYPE that contains a field which is a SETOF another TYPE:

DROP TYPE IF EXISTS subType CASCADE;
CREATE TYPE subType AS (
    filename varchar,
    image bytea
);

DROP TYPE IF EXISTS superType CASCADE;
CREATE TYPE superType AS (
    sort int,
    label varchar,
    photos SETOF subType,
);

This doesn't work though. Postgres gives an error:

ERROR:  column "photos" cannot be declared SETOF
CONTEXT:  SQL statement "CREATE TYPE superType AS ( sort int, label varchar, photos SETOF subType )"
PL/pgSQL function "update_database_script" line 2684 at SQL statement

I need to return this structure, although returning only the image data (type bytea) and not returning the file name of the image in subType would be acceptable. It should support multiple photos though.

Can somebody shed a light on how to accomplish this?

Solution

PostgreSQL doesn't support nested sets (and keyword SETOF in this context) yet. You can use a arrays instead:

postgres=# CREATE TYPE xx AS (a int, b int);
CREATE TYPE

postgres=# CREATE TYPE yy AS (a int, b xx[]);
CREATE TYPE
postgres=# SELECT '(10,20)'::xx;
   xx    
---------
 (10,20)
(1 row)

postgres=# select (10, ARRAY[(10,20),(30,40)]::xx[])::yy;
               row                
----------------------------------
 (10,"{""(10,20)"",""(30,40)""}")
(1 row)

Returning some nested structures is a usually complication - almost all drivers doesn't map this structure to adequate target structure and you should to manually parser returned string. Usually the best format for returned data is scalar, vector or table.