PostgreSQL递归WITH跨表引用编写校验触发器语法报错问题

涉及的基础表设计 #

• products：商品表，单个商品可归属一个或多个分类
• shelves：货架表，货架对应指定的父级/超类分类
• planogram：货架陈列图表，记录指定商品在货架上的摆放规则

需求与问题描述 #

需要编写触发器函数，在写入planogram记录时做校验：如果该商品的所有分类都不属于对应货架的分类层级体系，则抛出错误。编写多段WITH子句时，递归WITH语句附近持续触发语法错误。
附加疑问：cat_id返回的分类名是唯一的，是否因为操作的是表结果集，必须使用IN cat_id而不能用=cat_id？

原错误实现代码 #

CREATE OR REPLACE FUNCTION chk_insert_planogram()
RETURNS TRIGGER AS
$$
BEGIN
    WITH cat_id AS (
        SELECT category_name
        FROM shelf
        WHERE ivm_manuf = NEW.ivm_manuf
        AND ivm_serial_number = NEW.ivm_serial_number
        AND shelf_nr = NEW.shelf_nr
    )
    , product_categories AS (
        SELECT category_name
        FROM has_category
        WHERE product_ean = NEW.product_ean
    )
    , RECURSIVE shelf_categories(child_category,super_category) AS (
    SELECT child_category,super_category
        FROM has_other 
        WHERE super_category IN cat_id
        UNION ALL
    SELECT others.child_category, others.super_category
        FROM has_other others
        INNER JOIN shelf_categories
        ON others.super_category = shelf_categories.child_category
    )
    IF NOT EXISTS (
        SELECT category_name 
        FROM product_categories
        WHERE category_name IN cat_id
        OR category_name IN (
            SELECT child_category
            FROM shelf_categories
        )
    )
    THEN
        RAISE EXCEPTION 'Products categories not present in selected shelf';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

关联表结构 #

create table planogram (
    product_ean char(13) not null,
    shelf_nr numeric(16,0) not null,
    ivm_serial_number numeric(5,0) not null,
    ivm_manuf varchar(255) not null,
    faces_seen numeric(16, 0) not null,
    units numeric(3, 0) not null,
    loc numeric(3, 0) not null,
    constraint pk_planogram primary key(product_ean, shelf_nr, ivm_serial_number, ivm_manuf),
    constraint fk_planogram_product foreign key(product_ean) references product(product_ean),
    constraint fk_planogram_shelf foreign key(shelf_nr, ivm_serial_number, ivm_manuf) references shelf(shelf_nr, ivm_serial_number, ivm_manuf)
);

create table shelf (
    shelf_nr numeric(16,0) not null,
    ivm_serial_number numeric(5,0) not null,
    ivm_manuf varchar(255) not null,
    shelf_height numeric(5, 2) not null,
    category_name varchar(255) not null,
    constraint pk_shelf primary key(shelf_nr, ivm_serial_number, ivm_manuf),
    constraint fk_shelf_ivm foreign key(ivm_serial_number, ivm_manuf) references ivm(ivm_serial_number, ivm_manuf),
    constraint fk_shelf_category foreign key(category_name) references category(category_name)
);

create table super_category (
    category_name varchar(255) not null,
    constraint pk_super_category primary key(category_name),
    constraint fk_super_category_category foreign key(category_name) references category(category_name)
);

create table has_other (
    super_category varchar(255) not null,
    child_category varchar(255) not null,
    constraint pk_has_other primary key(child_category),
    constraint fk_has_other_super_category foreign key(super_category) references super_category(category_name),
    constraint fk_has_other_category foreign key(child_category) references category(category_name)
);

create table has_category (
    product_ean char(13) not null,
    category_name varchar(255) not null,
    constraint pk_has_category primary key(product_ean, category_name),
    constraint fk_has_category_product foreign key(product_ean) references product(product_ean),
    constraint fk_has_category_category foreign key(category_name) references category(category_name)
);

错误原因说明 #

1. PL/pgSQL语法规则不匹配：WITH子句（CTE）不能独立悬空写在PL/pgSQL的BEGIN块中，必须依附于一个完整的查询/DML语句，原代码写完CTE直接接IF NOT EXISTS属于语法非法。
2. RECURSIVE关键字位置错误：包含递归CTE的多CTE语句中，RECURSIVE关键字必须放在第一个CTE的定义前，不能放在中间CTE的位置，这是PostgreSQL的硬性语法要求。
3. CTE使用方式错误：CTE是行结果集，哪怕只返回单行单值，也不能直接跟在IN后面写CTE名，必须接子查询，例如IN (SELECT * FROM cat_id)。
4. 递归逻辑缺失：原递归CTE的初始集合没有包含货架本身绑定的根分类，只查询了子分类关系，会导致商品分类等于货架根分类时出现误判。

修正后的实现代码 #

CREATE OR REPLACE FUNCTION chk_insert_planogram()
RETURNS TRIGGER AS
$$
DECLARE
    is_valid boolean;
BEGIN
    WITH RECURSIVE
    -- 第一步：查询当前货架绑定的根分类
    shelf_root AS (
        SELECT category_name AS valid_cat
        FROM shelf
        WHERE ivm_manuf = NEW.ivm_manuf
          AND ivm_serial_number = NEW.ivm_serial_number
          AND shelf_nr = NEW.shelf_nr
    ),
    -- 第二步：递归获取该根分类下所有层级的子分类
    shelf_all_cats AS (
        -- 递归起点：根分类本身
        SELECT valid_cat FROM shelf_root
        UNION ALL
        -- 递归遍历所有下级子分类
        SELECT ho.child_category
        FROM has_other ho
        INNER JOIN shelf_all_cats sac
        ON ho.super_category = sac.valid_cat
    )
    -- 校验商品是否有任意一个分类属于货架分类体系
    SELECT EXISTS (
        SELECT 1
        FROM has_category hc
        WHERE hc.product_ean = NEW.product_ean
          AND hc.category_name IN (SELECT valid_cat FROM shelf_all_cats)
    ) INTO is_valid;

    IF NOT is_valid THEN
        RAISE EXCEPTION 'Products categories not present in selected shelf';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

疑问解答 #

关于IN cat_id和=cat_id的选择：
CTE本质是临时结果集，不是标量变量，哪怕确定只返回唯一值，也不能直接跟在IN或者=后面使用。如果要利用单值的特性，可以先把根分类值查询存入一个varchar类型的标量变量，后续既可以用=做等值判断，也省掉了子查询开销，性能更好。

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