PostgreSQL使用WITH子句多表INSERT后如何返回adapter的name值

解决方法 #

你可以把第二次插入user_adapter的操作也封装为WITH子句中的一个CTE，最后直接从已返回name字段的AdapterIns中查询结果即可，调整后的可运行SQL如下：

WITH 
AdapterName AS (
    SELECT CONCAT('s', LTRIM(name, 's')::INTEGER + 1) AS new_name
    FROM adapter
    WHERE adapter.name LIKE 's%'
    ORDER BY id DESC
    LIMIT 1
),
AdapterIns AS (
    INSERT INTO adapter (create_date, name, tag) 
    SELECT now(), new_name, new_name 
    FROM AdapterName 
    RETURNING id, name
),
UserAdapterIns AS (
    INSERT INTO user_adapter (create_date, user_id, adapter_id)
    SELECT now(), 1, AdapterIns.id
    FROM AdapterIns
    RETURNING adapter_id
)
SELECT name FROM AdapterIns;

说明 #

• 你之前的语法报错是因为PostgreSQL不允许将包含INSERT/UPDATE/DELETE等数据修改操作的CTE嵌套在子查询内，所有修改类CTE必须放在最外层的WITH块中。
• 你已经在AdapterIns CTE中通过RETURNING id,name拿到了刚插入的adapter的name值，不需要再额外关联原adapter表做二次查询。

内容的提问来源于stack exchange，提问作者proze