Including a set of rows in a view column

Design: A main table where each entry in it can have zero of more of a set of options “checked”. It seems to me that it would be easier to maintain (adding/removing options) if the options were part of a separate table and a mapping was made between the main table and an options table.

Goal: A view that contains the information from the main table, as well as all options to which that row has been mapped. However the latter information exists in the view, it should be possible to extract the option’s ID and its description easily.

The implementation below is specific to PostgreSQL, but any paradigm that works across databases is of interest.

The select statement that does what I want is:

WITH tmp AS (
        SELECT
            tmap.MainID AS MainID,
            array_agg(temp_options) AS options
        FROM tstng.tmap
        INNER JOIN (SELECT id, description FROM tstng.toptions ORDER BY description ASC) AS temp_options
            ON tmap.OptionID = temp_options.id
        GROUP BY tmap.MainID
    )
SELECT tmain.id, tmain.contentcolumns, tmp.options
FROM tstng.tmain
INNER JOIN tmp
    ON tmain.id = tmp.MainID;

However, attempting to create a view from this select statement generates an error: column "options" has pseudo-type record[]

The solution that I’ve found is to cast the array of options (record[]) to a text array (text[][]); however, I’m interested in knowing if there is a better solution out there. For reference, the create instruction:

CREATE OR REPLACE VIEW tstng.vsolution AS
    WITH tmp AS (
            SELECT
                tmap.MainID AS MainID,
                array_agg(temp_options) AS options
            FROM tstng.tmap
            INNER JOIN (SELECT id, description FROM tstng.toptions ORDER BY description ASC) AS temp_options
                ON tmap.OptionID = temp_options.id
            GROUP BY tmap.Main开发者_如何转开发ID
        )
    SELECT tmain.id, tmain.contentcolumns, CAST(tmp.options AS text[][])
    FROM tstng.tmain
    INNER JOIN tmp
        ON tmain.id = tmp.MainID;

Finally, the DDL in case my description has been unclear:

CREATE TABLE tstng.tmap (
        mainid INTEGER NOT NULL,
        optionid INTEGER NOT NULL
    );

CREATE TABLE tstng.toptions (
        id INTEGER NOT NULL,
        description text NOT NULL,
        unwanted_column text
    );

CREATE TABLE tstng.tmain (
        id INTEGER NOT NULL,
        contentcolumns text
    );

ALTER TABLE tstng.tmain ADD CONSTRAINT main_pkey PRIMARY KEY (id);
ALTER TABLE tstng.toptions ADD CONSTRAINT toptions_pkey PRIMARY KEY (id);
ALTER TABLE tstng.tmap ADD CONSTRAINT tmap_pkey PRIMARY KEY (mainid, optionid);
ALTER TABLE tstng.tmap ADD CONSTRAINT tmap_optionid_fkey FOREIGN KEY (optionid)
    REFERENCES tstng.toptions (id);
ALTER TABLE tstng.tmap ADD CONSTRAINT tmap_mainid_fkey FOREIGN KEY (mainid)
    REFERENCES tstng.tmain (id);

You could create composite type e.g. temp_options_type with:

DROP TYPE IF EXISTS temp_options_type;
CREATE TYPE temp_options_type AS (id integer, description text);

After that just cast temp_options to that type within array_agg, so it returns temp_options_type[] instead of record[]:

DROP VIEW IF EXISTS tstng.vsolution;
CREATE OR REPLACE VIEW tstng.vsolution AS
    WITH tmp AS
    (
        SELECT
            tmap.MainID AS MainID,
            array_agg(CAST(temp_options AS temp_options_type)) AS options
        FROM
            tstng.tmap INNER JOIN
            (
                SELECT id, description
                FROM tstng.toptions
                ORDER BY description
            ) temp_options
            ON tmap.OptionID = temp_options.id
        GROUP BY tmap.MainID
    )
    SELECT tmain.id, tmain.contentcolumns, tmp.options
    FROM tstng.tmain
    INNER JOIN tmp ON tmain.id = tmp.MainID;

Example result:

TABLE tstng.vsolution;
 id | contentcolumns |        options        
----+----------------+-----------------------
  1 | aaa            | {"(1,xxx)","(2,yyy)"}
  2 | bbb            | {"(3,zzz)"}
  3 | ccc            | {"(1,xxx)"}
(3 rows)