为什么子查询函数不插入新行

Question

我需要一个函数来插入行，因为一列的(seriano(默认值应该与PKid相同。

我已经定义了表格：

CREATE SEQUENCE some_table_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
CREATE TABLE some_table
(
id bigint NOT NULL DEFAULT nextval('some_table_id_seq'::regclass),
itemid integer NOT NULL,
serialno bigint,
CONSTRAINT stockitem_pkey PRIMARY KEY (id),
CONSTRAINT stockitem_serialno_key UNIQUE (serialno)
);

和插入行数的功能：

CREATE OR REPLACE FUNCTION insert_item(itemid int, count int DEFAULT 1) RETURNS SETOF bigint AS
$func$
DECLARE 
ids bigint[] DEFAULT '{}';
id bigint;
BEGIN
FOR counter IN 1..count LOOP
id :=  NEXTVAL( 'some_table_id_seq' );
INSERT INTO some_table (id, itemid, serialno) VALUES (id, itemid, id);
ids := array_append(ids, id);
END LOOP;
RETURN QUERY SELECT unnest(ids); 
END
$func$
LANGUAGE plpgsql;

使用它插入效果很好：

$ select insert_item(123, 10);
insert_item
-------------
1
2
3
4
5
6
7
8
9
10
(10 rows)
$ select * from some_table;
id | itemid | serialno 
----+--------+----------
1 |    123 |        1
2 |    123 |        2
3 |    123 |        3
4 |    123 |        4
5 |    123 |        5
6 |    123 |        6
7 |    123 |        7
8 |    123 |        8
9 |    123 |        9
10 |    123 |       10
(10 rows)

但如果我想使用函数insert_item作为子查询，它似乎已经不起作用了：

$ select id, itemid from some_table where id in (select insert_item(123, 10));
id | itemid 
----+--------
(0 rows)

我创建了一个哑函数insert_dumb来测试子查询：

CREATE OR REPLACE FUNCTION insert_dumb(itemid int, count int DEFAULT 1) RETURNS SETOF bigint AS
$func$
DECLARE 
ids bigint[] DEFAULT '{}';
BEGIN
FOR counter IN 1..count LOOP
ids := array_append(ids, counter::bigint);
END LOOP;
RETURN QUERY SELECT unnest(ids); 
END
$func$
LANGUAGE plpgsql;

这在子查询中起作用，正如预期的那样：

$ select id, itemid from some_table where id in (select insert_dumb(123, 10));
id | itemid 
----+--------
1 |    123
2 |    123
3 |    123
4 |    123
5 |    123
6 |    123
7 |    123
8 |    123
9 |    123
10 |    123
(10 rows)

为什么insert_item函数在作为子查询调用时不插入新行？我尝试将raise notice添加到循环中，它按预期运行，每次都喊出新的id(并增加序列(，但没有向表中添加新行。

我把所有的设置都作为小提琴

我在Ubuntu上使用Postgres 11。

编辑

当然，我说出了我真正的理由，这是有回报的…

我需要返回ids的insert_item函数，所以我可以在update-语句中使用它，比如：

update some_table set some_text = 'x' where id in (select insert_item(123, 10);)

除了为什么的问题：可以理解的是，我不能得到任何id作为回报(因为它们共享同一个快照(，但该函数运行所有需要的INSERT，而不会影响表。这些行在下一个查询中不应该可用吗？

Answer 1

问题是子查询和周围查询共享相同的快照，也就是说，它们看到的数据库状态相同。因此，外部查询无法看到内部查询插入的行。

参见文档(在WITH的上下文中解释了这一点，尽管它也适用于此处(：

WITH中的子语句与主查询同时执行。因此，当在WITH中使用数据修改语句时，指定的更新实际发生的顺序是不可预测的。所有语句都使用相同的快照执行(请参阅第13章(，因此它们无法"看到"彼此对目标表的影响。

此外，您的方法还有第二个问题：如果对语句运行EXPLAIN (ANALYZE)，您会发现根本没有执行子查询！由于表是空的，因此没有id，并且不需要运行子查询来计算(空的(结果。

您必须在两个不同的语句中运行它。或者，更好的做法是用不同的方式：更新刚刚插入的行是不必要的浪费。

Answer 2

Laurenz解释了可见性问题，但如果您重写函数以返回实际的表，而不仅仅是ID ，则根本不需要子查询

CREATE OR REPLACE FUNCTION insert_item(itemid int, count int DEFAULT 1) 
RETURNS setof some_table
AS
$func$
INSERT INTO some_table (id, itemid, serialno) 
select NEXTVAL( 'some_table_id_seq' ), itemid, currval('some_table_id_seq')
from generate_series(1,count)
returning *;
$func$
LANGUAGE sql;

然后你可以这样使用它：

select id, itemid 
from insert_item(123, 10);

然后得到完整的插入行。

在线示例