Sql 如何在插入时 Select 带有子查询的单个元素并使用给定的返回 ID 更新同一个表

demo:db<>fiddle

您可以使用由WITH个子句组成的链:

WITH select_part AS (       -- 1
    SELECT next_id
    FROM tableList
    WHERE unique_id = 4
),
insert_part AS (            -- 2
    INSERT INTO tableList ("next_id", "randomCol1")
        SELECT next_id, 'my new string'
        FROM select_part
    RETURNING unique_id
)
UPDATE tableList t          -- 3
SET next_id = i.unique_id
FROM (
    SELECT * 
    FROM insert_part
) i
WHERE t.unique_id = 4

1. SELECT第unique_id = 4行中的next_id值
2. 在INSERT语句中使用此值并返回新生成的id.
3. 使用新id将原始行设置为UPDATE.

然而，您可能会考虑使用单独的函数来实现这一点.

demo:db<>fiddle

CREATE OR REPLACE FUNCTION insert_value (
    _unique_id int, 
    _my_string text
) RETURNS void
AS $$
DECLARE 
    _next_val int;
    _new_id int;
BEGIN
    SELECT
        next_id
    FROM tableList
    WHERE unique_id = _unique_id
    INTO _next_val;
        
    INSERT INTO tableList("next_id", "randomCol1")
    VALUES (_next_val, _my_string)
    RETURNING unique_id
    INTO _new_id;
    
    UPDATE tableList t
    SET next_id = _new_id
    WHERE unique_id = _unique_id;
END;
$$ LANGUAGE plpgsql

请使用以下选项进行调用:

SELECT insert_value(4, 'my_string');