在WITH子句中链接sql查询以最终执行更新

Question

我正试图用一组对象填充测试db，以便进行非常特定的集成测试，而且我很难将来自其中一个查询的返回id用作后续对象字段的值。

-- create a user with an account with salesforce integration
WITH p AS (
        INSERT INTO person (email, confirmed, first_name, last_name) VALUES
        ('test3@example.com', TRUE, 'Salesforce', 'Guy')
        RETURNING id
     ),
     ac AS (
        INSERT INTO account (subscription_type, subscription_expires_on, salesforce_integration) VALUES (0, '2024-10-10', TRUE)
        RETURNING id
     ),
     am AS (
        INSERT INTO account_member (person_id, account_id, admin) VALUES
        ((SELECT p.id FROM p), (SELECT ac.id FROM ac), TRUE)
        RETURNING account_id, person_id
     ),
     la AS (
        -- create a salesforce linked_account
        INSERT INTO linked_account (person_id, provider_id, access_token) VALUES
        ((SELECT p.id FROM p), 'salesforce', '00DC00000016x37!AQIAQIt5EpCIgTFl9hg2qF9Ed6vzLJmTg9Nrd.uxvVva5WaxzMChn4sBBgV6KXiICCBoJgcFYbrTqpFtFJwpd.B7fe5kG9_z')
        RETURNING id
    ),
     v AS (
        -- create a video and take
        INSERT INTO video (person_id, account_id) VALUES
        ((SELECT p.id FROM p), (SELECT ac.id FROM ac))
        RETURNING id
    ),
     t AS (
        INSERT INTO take (video_id, key, duration, state, thumbnail_selected) VALUES
        ((SELECT v.id FROM v), 'g1/g1546ad07eff44c397e356be7c4bea49/g1546ad07eff44c397e356be7c4bea49', 35, 2, 1)
        RETURNING id
    )
    -- update video with selected take
    UPDATE video SET selected_take_id = (SELECT id FROM take WHERE take.video_id=video.id);

我遇到的问题是，当测试运行时，它声明我创建的视频(v)没有selected_take_id集，这意味着底部的“用选中的采取更新视频”查询实际上不起作用。

需要注意的是，在运行此脚本时，此db并不是空的，在此之前还有另外两个类似的种子文件运行，因此至少有2个视频、2个镜头等已经存储在DB中。如果有人知道如何使这个工作，而不必提供静态id作为值的外键，这将节省我一吨时间。

谢谢!

Answer 1

看起来，您希望UPDATE (“主查询”)找到一个插入在同一个SQL语句中的行，一个在语句开头不存在的行。

如果是这样的话，就不能像的数据修改语句中所描述的那样工作了：

WITH中的子语句彼此并行执行，并与主查询并行执行。因此，当使用WITH的数据修改语句时，指定的更新实际发生的顺序是不可预测的。所有语句都以相同的快照执行(参见第13章)，因此它们不能“看到”对方对目标表的影响。这减轻了行更新的实际顺序的不可预测性的影响，并意味着返回数据是在不同的子语句和主查询之间通信更改的唯一方法。

您可能希望使用DO块，而不是使用pl/pgsql中以过程形式列出的几个查询。