sql - 在 View 列中包含一组行

Question

设计:一个主表，其中每个条目都可以“检查”一组选项中的零个或多个选项。在我看来，如果选项是单独表的一部分并且在主表和选项表之间进行映射，则维护(添加/删除选项)会更容易。

目标:包含来自主表的信息以及该行已映射到的所有选项的 View 。无论 View 中是否存在后一种信息，都应该可以轻松提取选项的 ID 及其描述。

下面的实现是特定于 PostgreSQL 的，但任何适用于跨数据库的范例都是令人感兴趣的。

执行我想要的操作的 select 语句是:

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;

但是，尝试从此选择语句创建 View 会产生错误:“选项”列具有伪类型记录[]

我找到的解决方案是将选项数组 (record[]) 转换为文本数组 (text[][])；但是，我很想知道是否有更好的解决方案。作为引用，创建指令:

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.MainID
        )
    SELECT tmain.id, tmain.contentcolumns, CAST(tmp.options AS text[][])
    FROM tstng.tmain
    INNER JOIN tmp
        ON tmain.id = tmp.MainID;

最后，DDL 以防我的描述不清楚:

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);

Answer 1

您可以创建复合类型，例如temp_options_type 具有:

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

之后只需将 temp_options 转换为 array_agg 中的该类型，因此它返回 temp_options_type[] 而不是 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;

示例结果:

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