sql - postgresql 试图调用更新返回多行

Question

此示例语句在 postgresql 9.1 及更高版本中运行良好。不幸的是，它在旧版本中不起作用，原因是更新不支持 with 语句。如何将 RETURNING 放入数组中？我正在使用 plpgsql 编写函数。

begin

create table testing(
i serial,
t text);

insert into testing
(t)
values (null),(null),(null);

with abc AS (
update testing
set t = null
returning i )
select array_agg(i)
from abc;

rollback

感谢您的帮助!

Answer 1

不幸的是，您不能在 9.1 之前的版本上执行此操作，因为可写公用表表达式 (A.K.A wCTE) 仅在 9.1 中可用(请参阅 PostgreSQL feature matrix 上的“可写公用表表达式”)。

我能看到的唯一解决方案是在您的应用程序上执行此操作或使用函数，例如:

CREATE OR REPLACE FUNCTION test()
RETURNS int[]
VOLATILE
LANGUAGE plpgsql AS $$
DECLARE
    ret int[];
    aux int;
BEGIN
    ret := '{}';
    FOR aux IN
        UPDATE testing
        SET t = null
        RETURNING i
    LOOP
        ret := ret || aux;
    END LOOP;
    RETURN ret;
END;
$$;

无论如何，如果您使用的是旧版本，我建议您尽快升级到较新的版本(现在是 9.3)。 8.4 版本即将失去支持，9.0 将在明年失去支持(参见 versioning policy )。

编辑:

为了给那些使用 PostgreSQL 9.1+ 的人留下引用，上面的内容可以用 wCTE 完成，如下所示:

WITH updt AS (
    UPDATE testing
    SET t = null
    RETURNING i
)
SELECT array_agg(i) FROM updt;