如何创建无聚合视图，将同IPN的制造子记录合并至单行？

背景 #

我是一名电气工程师，正在开发企业内部零件库数据库及浏览工具，复刻Cadence的CIS后端与CIP前端，但要兼容OrCAD、Altium、KiCAD等多CAD软件。目前项目接近完成，但在创建CAD软件可读的数据库视图时遇到了问题。

数据库表结构 #

现有6张表，分工存储零件不同维度的数据：

核心问题 #

CAD软件通过ODBC读取数据时，无法处理分散在多张表中的数据，因此需要按零件类别创建视图（每个零件库对应一个视图），将关联后的数据展示在单行中。当前用LEFT JOIN拉取数据时，manufacturing表的每条子记录都会生成新行，需要将同一IPN下的所有manufacturing子记录合并到单行。

示例表结构与数据 #

main表（简化版） #

manufacturing表（简化版） #

期望的视图格式 #

表重建SQL代码 #

CREATE TABLE components.main (
    id integer NOT NULL,
    ipn components.citext DEFAULT to_char(nextval('components.main_ipn_seq'::regclass), '"PRT-"fm00000000'::text) NOT NULL,
    part_class integer NOT NULL,
    part_value components.citext NOT NULL,
    description components.citext NOT NULL,
    spec_doc components.citext,
    sch_symbol_kicad components.citext,
    sch_symbol_altium components.citext,
    pwb_footprint_kicad components.citext,
    pwb_footprint_altium components.citext,
    step_file components.citext,
    lead_frm_reqd boolean DEFAULT false NOT NULL,
    lead_finish components.citext,
    datasheet components.citext,
    last_modified_user components.citext DEFAULT USER NOT NULL,
    last_modified_timestamp timestamp with time zone DEFAULT now() NOT NULL,
    nrnd boolean DEFAULT false NOT NULL,
    bom_exclusion integer DEFAULT 0 NOT NULL,
    pwb_exclusion integer DEFAULT 0 NOT NULL,
    package components.citext
);

ALTER TABLE components.main ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME components.main_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE SEQUENCE components.main_ipn_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE components.manufacturing (
    id integer NOT NULL,
    ipn components.citext NOT NULL,
    mfr components.citext NOT NULL,
    mpn components.citext NOT NULL,
    cage components.citext
);

ALTER TABLE components.manufacturing ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME components.manufacturing_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

已尝试的解决方案及性能问题 #

我通过嵌套视图实现了所需的视图格式，但性能较慢（200条数据耗时100-200ms），希望得到性能优化的建议。代码如下：

CREATE OR REPLACE VIEW components.manf_pair_index
AS
SELECT manufacturing.id,
    manufacturing.ipn,
    manufacturing.mfr,
    manufacturing.mpn,
    row_number() OVER (PARTITION BY manufacturing.ipn ORDER BY manufacturing.id, manufacturing.ipn) AS pair_num
    FROM manufacturing;

CREATE OR REPLACE VIEW components.manf_flat
AS
SELECT manf_pair_index.ipn,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 1) AS mfr_1,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 1) AS mpn_1,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 2) AS mfr_2,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 2) AS mpn_2,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 3) AS mfr_3,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 3) AS mpn_3,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 4) AS mfr_4,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 4) AS mpn_4,
    max(manf_pair_index.mfr) FILTER (WHERE manf_pair_index.pair_num = 5) AS mfr_5,
    max(manf_pair_index.mpn) FILTER (WHERE manf_pair_index.pair_num = 5) AS mpn_5
    FROM manf_pair_index
    GROUP BY manf_pair_index.ipn;

CREATE OR REPLACE VIEW components.resistors
AS
SELECT main.ipn,
    main.nrnd,
    main.part_class,
    main.part_value,
    main.description,
    main.package,
    main.spec_doc,
    main.lead_finish,
    main.lead_frm_reqd,
    manf_flat.mfr_1,
    manf_flat.mpn_1,
    manf_flat.mfr_2,
    manf_flat.mpn_2,
    manf_flat.mfr_3,
    manf_flat.mpn_3,
    manf_flat.mfr_4,
    manf_flat.mpn_4,
    manf_flat.mfr_5,
    manf_flat.mpn_5,
    compatibility.comperable_ipn,
    main.sch_symbol_kicad,
    main.sch_symbol_altium,
    main.pwb_footprint_kicad,
    main.pwb_footprint_altium,
    main.step_file,
    main.bom_exclusion,
    main.pwb_exclusion
    FROM main
        LEFT JOIN manf_flat ON main.ipn::text = manf_flat.ipn::text
        LEFT JOIN compatibility ON main.ipn::text = compatibility.ipn::text
    WHERE main.part_class > 28 AND main.part_class < 45
    ORDER BY main.ipn;

注：之前研究过数据透视和交叉表函数，但现有示例均针对数值数据聚合，且以单个列作为透视类别，无法适配mfr+mpn的组合需求。

内容的提问来源于stack exchange，提问作者jde0503