我试图弄清楚是否有一种方法可以使嵌套 CTE 适用于这种特殊情况。
考虑以下基于实际应用程序的(高度人为的)场景:有一个员工 ID 的单列表。然后是一个包含所有详细信息的员工属性表。(单个 col 表背后的主要原因通常是理所当然地需要在知道实际员工的任何详细信息之前批量创建和分配新员工 ID。)
现在到手头的任务,我们正在插入新员工的详细信息(即姓名),但首先我们需要检查是否已经存在具有该姓名的员工。如果是,我们将简单地返回 id,如果不是,我们将创建一个新的员工记录,然后插入详细信息,最后返回新创建的 id。
要重新创建此测试场景:
CREATE TABLE public.employee (
id text DEFAULT gen_random_uuid(),
PRIMARY KEY (id)
);
CREATE TABLE public.employee_details (
employee_id text,
name text,
PRIMARY KEY (employee_id),
FOREIGN KEY (employee_id) REFERENCES public.employee(id)
);
我试图敲定的查询如下所示。
with
e as
(select name, employee_id from employee_details where name = 'jack bauer'),
i as (insert into employee_details (name, employee_id)
select 'jack bauer',
(with a as (insert into employee values(default) RETURNING id) select a.id from a)
where not exists (select 1 from e) returning name, employee_id)
select employee_id, name from e
union all
select employee_id, name from i;
如果我用已经创建的 id 替换嵌套的 CTE(单独执行嵌套的 CTE),它可以工作(但可能导致创建多余的 id)。with e as (..), i as (..), a as (..) select .. where not exists...
也可以简单地将嵌套的CTE移动到顶层(所以整个事情看起来像做到这一点“内联” - 所以只有在not exists
子句返回 true时才会创建新的 id 。
我不断收到错误:
包含数据修改语句的 WITH 子句必须位于顶层。
我想问题在于嵌套的 CTE 返回一个“列”,而如果它获得一个“值”,则整个查询将起作用(当一个简单地复制文本值而不是 CTE 时,它会起作用)。我确实在这个问题上遇到了一些相关的讨论,提到了一个自 9.3 以来已修复的明显错误。我不知道这是否与我在这里的麻烦有关。引用链接的讨论:
解析分析代码似乎认为 WITH 只能附加到集合操作树内的顶层或叶级 SELECT;但语法遵循 SQL 标准,没有这样的说法
我正在使用 Postgres 10.3。
出于这个问题的目的,我假设
employee_details.name
被定义为UNIQUE
. 否则,整个操作将毫无意义。您不能像您尝试的那样嵌套数据修改 CTE(因为您已经发现了困难的方法) - 而且您不需要。此查询将实现您的目标:
核心功能是
INSERT
没有目标列和空的SELECT
.SELECT
Postgres用默认值填充所有未列出的列。这样我们就可以用有条件的替换VALUES (default)
无条件的INSERT
。i1
如果未找到给定名称,CTE仅插入一行。手册:
这是标准的 Postgres 特定扩展:
如果返回一行,则最终 CTE
i2
仅插入一行。i1
瞧。这取决于对同一表的并发写入负载下的竞争条件。如果你需要排除这种情况,你需要做更多的事情。有关的:
如果没有第二个表中条件 INSERT 的复杂性,这将归结为SELECT 或 INSERT的常见情况:
在旁边
我强烈建议使用数据类型
uuid
来存储 UUID。