使用Returning子句之外的数据为新INSERT返回INSERT的标识

Question

我使用的是PostgreSQL 12.3。

类似于已经提出的许多问题，比如这个问题，但我需要使用RETURNING子句中不存在的另一个表中的数据。

对于一个人为的示例，考虑三个表：customers、products和sales，以及需要在销售点创建客户，并且需要使用users和productsid更新销售表的场景。

CREATE TABLE public.customers (
id SERIAL PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL
) 
CREATE TABLE public.products (
id SERIAL PRIMARY KEY,
product TEXT NOT NULL
)
CREATE TABLE public.sales (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
product_id INTEGER NOT NULL REFERENCES products(id)
) 

INSERT INTO customers (first_name, last_name) VALUES ('Bob', 'Smith');
INSERT INTO customers (first_name, last_name) VALUES ('Jane', 'Doe');
INSERT INTO products (product) VALUES ('widget 1');
INSERT INTO products (product) VALUES ('widget 2');
INSERT INTO products (product) VALUES ('widget 3');
INSERT INTO sales (customer_id, product_id) VALUES (1, 1);
INSERT INTO sales (customer_id, product_id) VALUES (2, 1);
INSERT INTO sales (customer_id, product_id) VALUES (2, 2);

如果我只需要客户id，以下不会是问题：

WITH new_customer_and_new_sale AS (
INSERT INTO customers (first_name, last_name) VALUES ('John', 'Doe') RETURNING id
)   
INSERT INTO sales (customer_id) 
SELECT id FROM new_user_and_new_sale

由于sales具有返回子句中找不到的约束，因此上述方法显然不起作用。我曾尝试连接表以获取额外的数据，但一直未能使其发挥作用。

请忽略表结构中的任何小问题，因为我正在处理的数据有数百列和许多外键。我试图把这个问题浓缩成最简单的形式，冒着看起来做作的风险。

Answer 1

您的示例失败，因为您没有提供product_id的值，这是必需的。

你可以直接指定这样一个：

WITH inserted_customer AS (
INSERT INTO customers (first_name, last_name) VALUES ('acacaca', 'Doe') RETURNING id
)
INSERT INTO sales (customer_id, product_id) 
SELECT inserted_customer.id, 2 FROM inserted_customer;

如果你想从现有产品中获得产品ID，你可以使用子查询(如果你在做更复杂的事情，可以使用CTE(。

WITH inserted_customer AS (
INSERT INTO customers (first_name, last_name) VALUES ('acacaca', 'Doe') RETURNING id
)
INSERT INTO sales (customer_id, product_id) 
SELECT inserted_customer.id, (SELECT id FROM products ORDER BY id DESC LIMIT 1) FROM inserted_customer;

如果你想在飞行中插入客户和产品，你可以做两个CTE：

WITH inserted_customer AS (
INSERT INTO customers (first_name, last_name) VALUES ('acacaca', 'Doe') RETURNING id
),
inserted_product AS (
INSERT INTO products (product) VALUES ('my product') RETURNING id
)   
INSERT INTO sales (customer_id, product_id) 
SELECT inserted_customer.id, inserted_product.id
FROM inserted_customer, inserted_product;

希望这能有所帮助！