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 不允许这种用法。

原始问题代码

-- 为图片内容添加标签
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 子句中，使用分步骤的方法：

修复后的代码

-- 为图片内容添加标签
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展开数组

技术解释

问题原因

集合返回函数限制: PostgreSQL 不允许在 CASE 表达式中直接使用 unnest() 等集合返回函数
语法限制: CASE 表达式期望每个分支返回单一值，而 unnest() 返回多行

解决思路

分离逻辑: 将数组构建逻辑和数组展开逻辑分离
CASE 返回数组: 在 CASE 中返回完整的数组，而不是展开后的元素
LATERAL JOIN: 使用 CROSS JOIN LATERAL unnest() 来展开数组

技术优势

✅ 符合 PostgreSQL 语法: 避免了在 CASE 中使用集合返回函数
✅ 逻辑清晰: 分步骤处理，易于理解和维护
✅ 性能良好: LATERAL JOIN 是高效的数组展开方式
✅ 可扩展: 容易添加更多的标签组合

执行流程

image_contents: 获取最近1小时创建的图片内容
image_with_row: 为每个图片内容添加行号
image_tags: 根据行号选择对应的标签数组
最终插入: 使用 LATERAL unnest 展开数组并插入标签

验证结果

✅ SQL 语法检查: 通过 PostgreSQL 语法验证
✅ 执行逻辑: 正确为两个图片内容分别添加对应标签
✅ 数据完整性: 保持原有的标签分配逻辑

文件状态

文件: h:\blews\akmon\video_system_database.sql
修复行数: 第 903-922 行
状态: ✅ 已修复，SQL 语法正确
更新时间: 2025-07-07

UNNEST 函数在 CASE 语句中的使用问题已成功修复，现在使用标准的 PostgreSQL LATERAL JOIN 语法。

4.0 KiB Raw Permalink Blame History Unescape Escape