sql - upsert 函数中不存在错误的地方

Question

我在为 postgresql 编写强大的 upsert 函数方面得到了很多帮助。我正在尝试将值插入到包含 3 列的表中，其中两列是外键，一列是文本。

我有一个非常相似的函数，效果很好，只是没有像这个函数那样的子查询。我收到一个错误

"ERROR:  syntax error at or near "WHERE"
LINE 24:   WHERE NOT EXISTS (SELECT 1 FROM sel)"

函数:

create or replace function upsert_dish_cluster_to_network(n_id INT, c_id INT, amsover TEXT)
RETURNS SETOF dish_cluster_to_network AS 
$func$
BEGIN

LOOP
    BEGIN

    RETURN QUERY
    WITH sel AS (
        SELECT dctn.id
        FROM   dish_cluster_to_network dctn
        WHERE  dctn.network_id = (SELECT id FROM unv_network WHERE amscode = n_id)
        AND    dctn.cluster_id = (SELECT id FROM dish_cluster_network WHERE axsyscode = c_id)
        FOR    SHARE
        ),
         ins AS (
        INSERT INTO dish_cluster_to_network (network_id, cluster_id, amsname_override)
        VALUES (
            (SELECT id FROM unv_network WHERE amscode = n_id),
            (SELECT id from dish_cluster_network WHERE axsyscode = c_id),
            amsover
            )
        WHERE NOT EXISTS (SELECT 1 FROM sel)
        RETURNING *
        ),
         upd AS (
        UPDATE dish_cluster_to_network dctn
        SET    network_id       = (SELECT id from unv_network WHERE amscode = n_id),
               cluster_id       = (SELECT id from dish_cluster_network WHERE axsyscode = c_id),
               amsname_override = amsover
        FROM   sel
        WHERE  sel.id = dctn.id
        AND    (      dctn.network_id    IS DISTINCT FROM (SELECT id FROM unv_network WHERE amscode = n_id)
            OR    dctn.cluster_id    IS DISTINCT FROM (SELECT id FROM dish_cluster_network WHERE axsyscode = c_id)
            )
        RETURNING dctn.*
        )
    SELECT * FROM ins
    UNION ALL
    SELECT * FROM upd;

    RETURN;

    EXCEPTION WHEN UNIQUE_VIOLATION THEN
        RAISE NOTICE 'Something Bad Happened';
    END;
END LOOP;

END
$func$ LANGUAGE plpgsql;

我不明白为什么我没有 WHERE NOT EXISTS 行。如果我注释掉该行，该函数总是插入新条目并且从不更新。

Answer 1

插入命令没有where 子句。因此，将 values 替换为 select

ins AS (
    INSERT INTO dish_cluster_to_network (
        network_id, cluster_id, amsname_override
    )
    select
       (SELECT id FROM unv_network WHERE amscode = n_id),
       (SELECT id from dish_cluster_network WHERE axsyscode = c_id),
       amsover
    WHERE NOT EXISTS (SELECT 1 FROM sel)
    RETURNING *
),