如何获取PostgreSQL游标的行数

Question

我使用WITH HOLD选项创建了一个光标，该选项允许将光标用于后续事务。

我想检索光标可以提取的行数。由于由保持的光标表示的行被复制到临时文件或内存区域中，我想知道是否有可能以简单的方式检索该数字，或者唯一的解决方案是提取所有记录来计数。

在这种情况下，MOVE FORWARD ALL FROM <cursor>语句返回MOVE x。其中x为数字移动了。结果是一个写入stdout的命令标记，我不知道如何在pgsql函数中检索该值。GET DIAGNOSTICS <var> := ROW_COUNT仅适用于FETCH而不适用于MOVE。

这是一个解决方案建议，你认为我可以如何改进？(是否可以使用MOVE而不是FETCH来检索x值？)

-- Function returning the number of rows available in the cursor
CREATE FUNCTION get_cursor_size(_cursor_name TEXT)
RETURNS TEXT AS
$func$
DECLARE
_n_rows int;
BEGIN
-- Set cursor at the end of records
EXECUTE format('FETCH FORWARD ALL FROM %I', _cursor_name);
-- Retrieve number of rows
GET DIAGNOSTICS _n_rows := ROW_COUNT;
-- Set cursor at the beginning
EXECUTE format('MOVE ABSOLUTE 0 FROM %I', _cursor_name);
RETURN _n_rows;
END
$func$ LANGUAGE plpgsql;

非常感谢您的帮助

Answer 1

我认为在PL/pgSQL中没有办法做到这一点。

作为一种变通方法，您可以定义光标，使其包含一个行号：

DECLARE c CURSOR WITH HOLD FOR
SELECT *, row_number() OVER ()
FROM (SELECT /* your original query */) AS s;

这只比原始查询稍微贵一点，而且它允许您将光标定位在具有MOVE的最后一行，并检索row_number，即总行数。

Answer 2

是的，可以使用MOVE而不是FETCH来计算光标中的记录，性能略有提高。事实证明，我们确实可以从MOVE游标语句中检索ROW_COUNT诊断。

GOTCHA:使用EXECUTE不会为MOVE更新GET DIAGNOSTICS，而为FETCH更新，并且这两个语句都不会更新FOUND变量。

PostgreSQL文档本身并没有明确规定这一点，但考虑到MOVE并没有产生任何实际结果，这可能是有道理的。

注意：以下示例不会像原始示例那样将光标位置重置回0，从而允许该函数与所有光标类型一起使用，尤其是NO SCROLL光标，后者将通过引发错误来拒绝向后移动。

用MOVE代替FETCH

我们一直在等待的PostgreSQL游标记录计数方法的圣杯。将函数修改为以refcursor作为参数，而直接执行MOVE游标语句，从而使ROW_COUNT可用。

-- Function returning the number of rows available in the cursor
CREATE FUNCTION get_cursor_size(_cursor refcursor)
RETURNS TEXT AS
$func$
DECLARE
_n_rows int;
BEGIN
-- Set cursor at the end of records
MOVE FORWARD ALL FROM _cursor;
-- Retrieve number of rows
GET DIAGNOSTICS _n_rows := ROW_COUNT;
RETURN _n_rows;
END
$func$ LANGUAGE plpgsql;

使用MOVE的替代方法

此处提供以供完成。

另一种方法是通过光标访问LOOP，直到FOUND返回false，但是这种方法甚至比问题中原始示例的FETCH ALL方法慢。

-- Increment the cursor position and count the rows
CREATE FUNCTION get_cursor_size(_cursor refcursor)
RETURNS TEXT AS
$func$
DECLARE
_n_rows int := 0;
begin
LOOP
-- Move cursor position
MOVE FORWARD 1 IN _cursor;
-- While not found
EXIT WHEN NOT FOUND;
-- Increment counter
_n_rows := _n_rows + 1;
END LOOP;
RETURN _n_rows;
END
$func$ LANGUAGE plpgsql;

增加步长确实可以提高性能，但结果将被四舍五入，因为如果光标已经移动，FOUND将报告成功。为了纠正这一点，我们可以查找ROW_COUNT，并按实际移动量递增。

-- Count the actual number of rows incremented
CREATE FUNCTION get_cursor_size(_cursor refcursor)
RETURNS TEXT AS
$func$
DECLARE
_n_rows int := 0;
_move_count int;
begin
LOOP
-- Move cursor position
MOVE FORWARD 100 IN _cursor;
-- Until not found
EXIT WHEN NOT FOUND;
-- Increment counter
GET DIAGNOSTICS _move_count := ROW_COUNT;
_n_rows := _n_rows + _move_count;
END LOOP;
RETURN _n_rows;
END
$func$ LANGUAGE plpgsql;

对于50000条记录，我不得不将步长增加到1000，然后才注意到FETCH ALL的任何改进，所以除非有其他值得同时做的事情，否则增量方法不太理想。

传递光标函数

我们可能希望将其用于光标生成功能，如：

-- Get a reference cursor
CREATE FUNCTION get_refcursor()
RETURNS refcursor AS
$func$
DECLARE
return_cursor refcursor;
BEGIN
OPEN return_cursor FOR SELECT 'One Record';
RETURN return_cursor;
END
$func$ LANGUAGE plpgsql;

我们可以通过使用OUT参数来简化这一点，而不是允许我们省略声明块和返回语句，请参阅创建函数文档。

-- Get a reference cursor
CREATE FUNCTION get_refcursor(OUT return_cursor refcursor)
RETURNS refcursor AS
$func$
BEGIN
OPEN return_cursor FOR SELECT 'One Record';
END
$func$ LANGUAGE plpgsql;

使用我们的一个get游标函数，我们现在可以通过将该函数作为参数传递来轻松地计算返回的游标记录的数量(仅为1)。

SELECT get_cursor_size(get_refcursor());

nJoy！