PostgreSQL中作为子查询调用的插入函数为何无法插入新行？

核心原因说明 #

1. 为什么insert_item子查询返回空，但insert_dumb正常 #

PostgreSQL执行单条SELECT语句时，会在语句启动瞬间获取一致性读快照，这条语句的所有读操作都只能看到快照生成时已经提交的数据，两类函数的差异就在这里：

• insert_dumb返回的是1~10的固定数值，这些id是你之前已经插入的行的主键，查询快照里已经存在这些数据，所以外层查询可以匹配到对应行，返回结果
• insert_item在子查询里执行时，生成的是全新的id，这些id对应的行是快照生成后才插入的，对外层查询的读操作不可见，所以自然没有匹配项，返回0行

2. 为什么你误以为插入没有生效 #

插入操作实际已经执行成功了，你观察到的序列递增、raise notice正常输出就是证明。你看不到新行的原因和事务隔离级别有关：

• 如果你的事务隔离级别是可重复读/可序列化：同一个事务内的所有查询共用事务启动时的快照，只要你不提交事务，事务内的所有查询都看不到本次插入的新行，提交后后续查询就可以看到
• 如果你的事务隔离级别是读已提交：单条语句执行完成后，下一条语句会获取新的快照，你执行完带插入的SELECT后，再单独执行SELECT * FROM some_table就能看到新插入的行

需求实现方案 #

你要实现「插入行后直接更新这些行」的需求，不要把插入函数放在WHERE条件的子查询里，改用可写CTE的写法即可，既符合PostgreSQL的执行逻辑，性能也更好：

WITH inserted_ids AS (
  -- 先执行插入，拿到新生成的id
  SELECT insert_item(123, 10) AS id
)
-- 用新生成的id做更新
UPDATE some_table 
SET some_text = 'x' 
WHERE id IN (SELECT id FROM inserted_ids);

另外你当前的insert_item用循环插入性能较差，count值大的时候会有明显性能瓶颈，可以改成基于集合的批量插入写法，无需循环：

CREATE OR REPLACE FUNCTION insert_item(itemid int, count int DEFAULT 1) RETURNS SETOF bigint AS
$func$
BEGIN
  RETURN QUERY 
  INSERT INTO some_table (itemid, serialno)
  SELECT itemid, nextval('some_table_id_seq')
  FROM generate_series(1, count)
  RETURNING id;
END
$func$
LANGUAGE plpgsql VOLATILE;

内容的提问来源于stack exchange，提问作者w.k