我有两个表，用户和组：
users(user_id BIGSERIAL, email TEXT UNIQUE NOT NULL, state TEXT NOT NULL)
groups(group_id BIGSERIAL, name TEXT, members []BIGINT)
从上面可以看到，users 表包含一个用户记录，其中每条记录由电子邮件地址和自动生成的user_id字段唯一标识。groups表包含一个members数组，其中包含一堆引用用户表中user_id的 BIGINT。
现在我需要一个查询，当给定两个传入参数时，email和一个group_id：
- 检查
users表是否已包含具有该电子邮件地址的记录 - 如果没有行包含该电子邮件地址，则
INSERT新记录 SELECT行的user_id(新创建或已存在)- 追加在上述步骤中获得的
user_id，并将其追加到组表的members字段中，其中group_id与传入参数匹配。成员字段可能已经包含user_id在这种情况下，无需执行任何操作(IOW，groups表中每一行的members字段不应有重复值)。
对于 1、2 和 3，我编写了以下查询：
WITH new_row AS (
INSERT INTO users (email, state)
SELECT 'abc@abc.abcd', 'Referred'
WHERE NOT EXISTS (SELECT * FROM users WHERE email = 'abc@abc.abcd')
RETURNING *
)
SELECT user_id FROM new_row
UNION
SELECT user_id FROM users WHERE email='abc@abc.abcd';
对于 4，我编写了以下查询：
UPDATE groups SET members = (
SELECT ARRAY(
SELECT DISTINCT unnest(members || array[RANDOM_USER_ID]::bigint[]) ) )
WHERE group_id = INCOMING_GROUP_ID
RETURNING group_id, members;
其中，第二个查询中的RANDOM_USER_ID应替换为第一个查询中获取的user_id。
现在，将这两个查询原子地组合为单个查询的方法是什么，其中第一个查询的user_id通过管道传输到第二个查询？我不想创建事务并独立运行两个单独的查询，并将user_id字段保存在临时位置(在我的服务器程序中)。我希望所有这些都在单个查询中完成。可能吗？
如果重要的话，我将在RDS/Google CloudSQL上使用Postgresql 9.6.4(或更高版本)。
从我所看到的情况来看，我认为假设每个查询将是一个user_id是公平的，因此例如您可以使用另一个 CTE(为清楚起见)和子查询：
WITH usr AS (
WITH new_row AS (
INSERT INTO users (email, state)
SELECT 'abc@abc.abcd', 'Referred'
WHERE NOT EXISTS (SELECT * FROM users WHERE email = 'abc@abc.abcd')
RETURNING *
)
SELECT user_id FROM new_row
UNION
SELECT user_id FROM users WHERE email='abc@abc.abcd'
)
UPDATE groups SET members = (
SELECT ARRAY(
SELECT DISTINCT unnest(members || array[(SELECT user_id FROM usr)]::bigint[]) ) )
WHERE group_id = INCOMING_GROUP_ID
RETURNING group_id, members;
替代方法是使用 UPSERT：
CREATE UNIQUE INDEX ON users(email);
WITH usr AS (
INSERT INTO users (email, state)
SELECT 'abc@abc.abcd', 'Referred'
ON CONFLICT(email)
DO UPDATE SET email = users.email --this does empty UPDATE so that RETURNING gives us a row
RETURNING *
)
UPDATE groups SET members = (
SELECT ARRAY(
SELECT DISTINCT unnest(members || array[(SELECT user_id FROM usr)]::bigint[]) ) )
WHERE group_id = INCOMING_GROUP_ID
RETURNING group_id, members;