PostgreSQL中使用伪类型创建物化视图的解决方案咨询

PostgreSQL禁止record这类伪类型作为物化视图、普通表的列类型，你第一个示例报错的核心原因是子查询生成的结构是匿名record，没有在数据库中注册为明确的命名类型，不符合列类型要求。
你给出的可正常执行的写法，本质是利用了PostgreSQL为每个表自动创建同名复合类型的特性：直接写表名作为查询列时，列类型就是对应表的命名复合类型，不属于伪类型，因此可以正常存储。要实现你预期的查询效果，只需要给emp_addr对应的结构注册明确的合法复合类型即可，不需要做特殊的黑魔法规避。

方案1：自定义复合类型（100%匹配预期查询效果） #

这个方案可以完全支持你列出的两种查询语法，步骤如下：

1. 先定义和子查询字段结构完全匹配的命名复合类型，字段类型请替换为你实际业务中对应字段的真实类型：

CREATE TYPE emp_addr_type AS (
    id integer,
    first_name text,
    last_name text,
    city text,
    street text,
    housenumber text
);

2. 创建物化视图时，将子查询返回的字段按顺序拼接为行，显式转换为上面定义的复合类型即可：

CREATE MATERIALIZED VIEW data AS 
SELECT
    ROW(
        emp_addr.id,
        emp_addr.first_name,
        emp_addr.last_name,
        emp_addr.city,
        emp_addr.street,
        emp_addr.housenumber
    )::emp_addr_type AS emp_addr,
    salaries
FROM (
    SELECT employees.id as id, first_name, last_name, city, street, housenumber 
    FROM employees 
    INNER JOIN addresses
    ON employees.id = addresses.employee_id
) emp_addr
INNER JOIN salaries
ON emp_addr.id = salaries.employee_id;

创建完成后，你需要的两类查询都可以正常执行：

• 直接查询复合列：SELECT emp_addr, salaries FROM data;
• 展开复合列所有字段：SELECT (emp_addr).*, (salaries).* FROM data;
  同时也支持单独提取复合列内的指定字段，比如SELECT (emp_addr).city, (salaries).amount FROM data;。

方案2：基于现有表类型嵌套（无需自定义类型） #

如果你不需要emp_addr是平级的字段结构，也可以直接用已有的表复合类型嵌套生成行类型，不需要额外创建自定义类型：

CREATE MATERIALIZED VIEW data AS 
SELECT
    ROW(employees, addresses) AS emp_addr,
    salaries
FROM employees 
INNER JOIN addresses
ON employees.id = addresses.employee_id
INNER JOIN salaries
ON employees.id = salaries.employee_id;

这种写法的emp_addr是嵌套复合结构，外层行包含employees、addresses两个表复合类型字段，同样支持SELECT emp_addr FROM data和SELECT (emp_addr).* FROM data的语法，只是展开后得到的是两个表类型字段，不是平级的id、姓名、地址字段，适合不需要平级字段的场景。

• 不要尝试直接将匿名record强转为record类型，本质依然是伪类型，创建物化视图时还是会报错，必须使用数据库中已注册的、结构明确的命名复合类型。
• 如果后续需要修改自定义复合类型的字段结构，需要先删除依赖该类型的物化视图，执行ALTER TYPE调整结构后再重建物化视图。
• 复合类型列不影响物化视图的刷新、索引创建规则，和普通标量列的运维逻辑完全一致。

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