如何在PostgreSQL中通过SQL视图将复杂数组字段平铺展示？-SQL-PHP中文网

视图中不能直接使用UNNEST，因其为集合返回函数，会破坏视图结构稳定性；必须通过LATERAL JOIN或子查询包装，如LEFT JOIN LATERAL UNNEST(...) WITH ORDINALITY AS s(skill, pos) ON true，才能在视图中安全展开数组并保留原记录。

如何在postgresql中通过sql视图将复杂数组字段平铺展示？

视图里不能直接用 UNNEST 生成多行结果 —— 这是硬性限制，不是写法问题。想平铺数组字段，必须用 LATERAL 或子查询包装，否则建视图会报错。

为什么视图里写 `UNNEST` 直接报错？

PostgreSQL 视图定义要求返回结果集结构固定：列名、类型、数量都不能动态变化。UNNEST 本身不带 LATERAL 时属于“集合返回函数”，在视图顶层使用会被拒绝，错误信息通常是：ERROR: set-returning functions are not allowed in VIEW definitions。

常见误写（会失败）：

CREATE VIEW user_skills_flat AS
SELECT id, name, UNNEST(skills) AS skill
FROM employee;

正确路径只有一条：把 UNNEST 放进 LATERAL 或子查询里，让它成为 JOIN 的一部分。

用 `LATERAL` 在视图中安全展开数组

LATERAL 允许右侧表达式引用左侧表字段，且 PostgreSQL 明确认可它在视图中使用。这是最干净、最推荐的方式。

空数组或 NULL 字段默认被丢弃 —— 如果要保留原记录，必须改用 LEFT JOIN LATERAL
顺序不保证，如需原始位置索引，加 WITH ORDINALITY，例如 UNNEST(skills) WITH ORDINALITY AS t(skill, pos)
字段名必须显式别名，否则视图列名会是 unnest，不易读

示例（保留原记录）：

CREATE VIEW user_skills_flat AS
SELECT e.id, e.name, s.skill, s.pos
FROM employee e
LEFT JOIN LATERAL UNNEST(e.skills) WITH ORDINALITY AS s(skill, pos) ON true;

用子查询替代 `LATERAL` 的适用场景

某些旧版本 PostgreSQL（LATERAL，或你正在适配不兼容的 ORM，这时可用子查询兜底。

子查询必须关联外层主键，否则变成笛卡尔积
性能略差于 LATERAL，尤其当数组很长时，子查询可能重复执行
无法用 WITH ORDINALITY，位置信息只能靠 array_position() 模拟（不稳定，重复值会出错）

示例（兼容旧版）：

PostgreSQL 18.4 ubuntu

PostgreSQL 18.4 官方 Ubuntu 安装包现已发布，这是目前最新的稳定版本。推荐通过官方 APT 仓库安装：先执行 sudo apt update 更新索引，再运行 sudo apt install postgresql-18 即可完成部署。新版本引入了异步 I/O 子系统，在顺序扫描与 VACUUM 场景下性能提升显著，同时支持 UUID v7 原生生成函数与虚拟生成列。

下载

CREATE VIEW user_skills_flat AS
SELECT e.id, e.name, s.skill
FROM employee e,
LATERAL (
  SELECT unnest(e.skills) AS skill
) s;

注意：这里仍用了 LATERAL —— 真正的子查询写法是：

CREATE VIEW user_skills_flat AS
SELECT e.id, e.name, s.skill
FROM employee e
JOIN (
  SELECT id, unnest(skills) AS skill
  FROM employee
) s ON s.id = e.id;

但这种写法会放大中间结果集，且无法处理 skills 为 NULL 的情况（UNNEST(NULL) 不产出行，JOIN 后该记录消失）。

多维数组平铺要分两层展开

如果字段是 TEXT[][]（比如 tags TEXT[][]），UNNEST 一次只能展开一层，直接用会报错：cannot unnest array of arrays。

第一层展开得到子数组，第二层再对每个子数组 UNNEST
必须嵌套 LATERAL，外层 LATERAL 产生子数组，内层 LATERAL 展开它
二维索引信息（如 [i][j]）无法自动还原，需靠 generate_subscripts 手动构造，复杂度陡增

示例（二维展开）：

CREATE VIEW user_tags_flat AS
SELECT e.id, e.name, t1.tag_group, t2.tag
FROM employee e,
LATERAL UNNEST(e.tags) AS t1(tag_group),
LATERAL UNNEST(t1.tag_group) AS t2(tag);

这里 t1.tag_group 是一维数组，t2.tag 是标量字符串 —— 但原始二维坐标（第几组第几个）已丢失。

真正麻烦的不是语法，而是语义：一旦平铺，就脱离了原数组的结构上下文。如果业务依赖位置、嵌套关系或空数组占位，视图输出就不可逆 —— 这点比怎么写更重要。