Postgresql函数锁定表，更新值并返回行

Question

对于我的Hi/Lo实现，我需要一个函数来获取独占表锁、更新值并选择一行。我想出了以下代码：

CREATE OR REPLACE FUNCTION bb_next_hi(tbl varchar(35))  
RETURNS setof record AS
$$ 
    LOCK TABLE hilo IN ACCESS EXCLUSIVE MODE;
    OPEN ref FOR SELECT hi as "Hi", lo as "Lo", "table" as "Table" FROM hilo WHERE "table" = $1;
    UPDATE hilo SET hi = hi + 1 WHERE "table" = $1
    RETURN ref;

$$ LANGUAGE plpgsql;

但是，在调用该函数时，它返回的不是一行，而是一列，内容类似于“未命名门户3”。我想我应该迭代返回的ref（但如何）？

我可以使用的另一种方法是使用UPDATE RETURNING语句，但是我不确定在这种情况下是否会出现竞争情况。任何帮助都将不胜感激。谢谢

Answer 1

您正在使用一个< code>cursor变量(您没有声明该变量，并且还缺少一些其他PL/pgSQL必需的元素),并且您返回了该变量。光标引用实际上被称为“入口”,这解释了您的输出。更好的方法是:

CREATE OR REPLACE FUNCTION bb_next_hi(tbl varchar(35))  
RETURNS TABLE ("Hi" int, "Lo" int, "Table" varchar(35)) AS $$
BEGIN
    LOCK TABLE hilo IN ACCESS EXCLUSIVE MODE;
    RETURN QUERY SELECT hi, lo, $1 FROM hilo WHERE "table" = $1;
    UPDATE hilo SET hi = hi + 1 WHERE "table" = $1;
    RETURN;
END;
$$ LANGUAGE plpgsql STRICT;

但你真的需要桌子上的独占锁吗？获得一致的表快照的侵入性要小得多。

Answer 2

对我来说，这似乎简单得多：

CREATE OR REPLACE FUNCTION bb_next_hi(tbl varchar(35))  
RETURNS setof record AS
$$ 
BEGIN
    LOCK TABLE hilo IN ACCESS EXCLUSIVE MODE;
    RETURN QUERY UPDATE hilo SET hi = hi + 1 WHERE "table" = $1
        RETURNING hi as "Hi", lo as "Lo", "table" as "Table";
END;
$$ LANGUAGE plpgsql;

我通常也定义< code >返回表(col1 varchar，col2 varchar...)而不是< code >返回记录集。