120 lines
4.0 KiB
Markdown
120 lines
4.0 KiB
Markdown
# UNNEST 函数 CASE 语句修复
|
||
|
||
## 问题描述
|
||
|
||
在执行模拟数据插入时遇到 PostgreSQL 错误：
|
||
```
|
||
set-returning functions are not allowed in CASE
|
||
LINE 166: WHEN rn = 2 THEN unnest(ARRAY['旅游', '图集', '云南', '风光', '摄影'])
|
||
HINT: You might be able to move the set-returning function into a LATERAL FROM item.
|
||
```
|
||
|
||
这个错误是因为在 CASE 语句中直接使用了 `unnest()` 集合返回函数，PostgreSQL 不允许这种用法。
|
||
|
||
## 原始问题代码
|
||
|
||
```sql
|
||
-- 为图片内容添加标签
|
||
WITH image_contents AS (
|
||
SELECT id FROM ak_contents
|
||
WHERE content_type = 'image'
|
||
AND created_at >= NOW() - INTERVAL '1 hour'
|
||
LIMIT 2
|
||
),
|
||
image_with_row AS (
|
||
SELECT id, ROW_NUMBER() OVER (ORDER BY id) as rn FROM image_contents
|
||
)
|
||
INSERT INTO ak_image_tags (content_id, tag_name, tag_type, confidence)
|
||
SELECT
|
||
id,
|
||
CASE
|
||
WHEN rn = 1 THEN unnest(ARRAY['风景', '西湖', '自然', '摄影']) -- ❌ 错误：CASE中使用unnest
|
||
WHEN rn = 2 THEN unnest(ARRAY['旅游', '图集', '云南', '风光', '摄影']) -- ❌ 错误：CASE中使用unnest
|
||
END,
|
||
'user',
|
||
NULL
|
||
FROM image_with_row;
|
||
```
|
||
|
||
## 解决方案
|
||
|
||
将 `unnest()` 函数移到 `LATERAL FROM` 子句中，使用分步骤的方法：
|
||
|
||
### 修复后的代码
|
||
|
||
```sql
|
||
-- 为图片内容添加标签
|
||
WITH image_contents AS (
|
||
SELECT id FROM ak_contents
|
||
WHERE content_type = 'image'
|
||
AND created_at >= NOW() - INTERVAL '1 hour'
|
||
LIMIT 2
|
||
),
|
||
image_with_row AS (
|
||
SELECT id, ROW_NUMBER() OVER (ORDER BY id) as rn FROM image_contents
|
||
),
|
||
image_tags AS (
|
||
SELECT
|
||
id,
|
||
CASE
|
||
WHEN rn = 1 THEN ARRAY['风景', '西湖', '自然', '摄影'] -- ✅ 正确：CASE中返回数组
|
||
WHEN rn = 2 THEN ARRAY['旅游', '图集', '云南', '风光', '摄影'] -- ✅ 正确：CASE中返回数组
|
||
END as tag_array
|
||
FROM image_with_row
|
||
)
|
||
INSERT INTO ak_image_tags (content_id, tag_name, tag_type, confidence)
|
||
SELECT
|
||
id,
|
||
tag_name,
|
||
'user',
|
||
NULL
|
||
FROM image_tags
|
||
CROSS JOIN LATERAL unnest(tag_array) AS tag_name; -- ✅ 正确：使用LATERAL FROM展开数组
|
||
```
|
||
|
||
## 技术解释
|
||
|
||
### 问题原因
|
||
1. **集合返回函数限制**: PostgreSQL 不允许在 CASE 表达式中直接使用 `unnest()` 等集合返回函数
|
||
2. **语法限制**: CASE 表达式期望每个分支返回单一值，而 `unnest()` 返回多行
|
||
|
||
### 解决思路
|
||
1. **分离逻辑**: 将数组构建逻辑和数组展开逻辑分离
|
||
2. **CASE 返回数组**: 在 CASE 中返回完整的数组，而不是展开后的元素
|
||
3. **LATERAL JOIN**: 使用 `CROSS JOIN LATERAL unnest()` 来展开数组
|
||
|
||
### 技术优势
|
||
- ✅ **符合 PostgreSQL 语法**: 避免了在 CASE 中使用集合返回函数
|
||
- ✅ **逻辑清晰**: 分步骤处理，易于理解和维护
|
||
- ✅ **性能良好**: LATERAL JOIN 是高效的数组展开方式
|
||
- ✅ **可扩展**: 容易添加更多的标签组合
|
||
|
||
## 执行流程
|
||
|
||
1. **image_contents**: 获取最近1小时创建的图片内容
|
||
2. **image_with_row**: 为每个图片内容添加行号
|
||
3. **image_tags**: 根据行号选择对应的标签数组
|
||
4. **最终插入**: 使用 LATERAL unnest 展开数组并插入标签
|
||
|
||
## 验证结果
|
||
|
||
- ✅ **SQL 语法检查**: 通过 PostgreSQL 语法验证
|
||
- ✅ **执行逻辑**: 正确为两个图片内容分别添加对应标签
|
||
- ✅ **数据完整性**: 保持原有的标签分配逻辑
|
||
|
||
## 相关资源
|
||
|
||
- [PostgreSQL LATERAL 文档](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL)
|
||
- [PostgreSQL unnest() 函数](https://www.postgresql.org/docs/current/functions-array.html)
|
||
- [CASE 表达式限制](https://www.postgresql.org/docs/current/functions-conditional.html)
|
||
|
||
## 文件状态
|
||
|
||
- **文件**: `h:\blews\akmon\video_system_database.sql`
|
||
- **修复行数**: 第 903-922 行
|
||
- **状态**: ✅ 已修复，SQL 语法正确
|
||
- **更新时间**: 2025-07-07
|
||
|
||
---
|
||
*UNNEST 函数在 CASE 语句中的使用问题已成功修复，现在使用标准的 PostgreSQL LATERAL JOIN 语法。*
|