在"带递归"中使用其他表格中的信息

Question

我有几个表:

• 产品(包含一个或多个类别)
• 货架(意味着某些父/super_categories)
• 将某种产品放在货架上的平面图

我一直在尝试创建一个函数，给定一个程序，如果产品的类别都不属于货架的类别层次结构，则会引发错误，但我在多个' with '中遇到了一些麻烦，并且不断受到语法错误的打击。我能做得更好吗?

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;

(cat_id是唯一的，但我认为，因为我们正在与表工作，我必须使用"IN cat_id"而不是"=cat_id")

相关表格如下:

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)
);

Answer 1

我不清楚您是否需要一个函数或触发器，但在开始之前，我建议您首先开发一个可行的选择查询来验证您的递归逻辑是否合理。例如

SET @ivm_manuf = '1';
SET @ivm_serial_number = '2';
SET @shelf_nr = '3';
/* start using "with recursive" */
WITH RECURSIVE shelf_categories (lvl, child_category, super_category)
AS (
SELECT
1 as lvl
, child_category
, super_category
FROM has_other
WHERE super_category IN (
SELECT category_name
FROM shelf
WHERE ivm_manuf = @ivm_manuf
AND ivm_serial_number = @ivm_serial_number
AND shelf_nr = @shelf_nr
)

UNION ALL

SELECT
lvl + 1 as lvl
, has_other.child_category
, has_other.super_category
FROM shelf_categories
INNER JOIN has_other ON shelf_categories.child_category = has_other.super_category
WHERE lvl < 20 /* include some way to "escape" from the recursion */
)
SELECT *
FROM shelf_categories

1. 更改参数数据类型和值以适应您的数据
2. 使用with recursive开始with子句，即使您定义的第一个CTE不需要递归，参见本文档中的示例。
3. 在递归CTE中，在第二次选择中，根据需要在连接其他表的from子句中使用该CTE的名称
4. 包含从递归
转义的方法
5. 一旦选择查询成功运行，将其转换为触发器或函数