在plpgsql中获取游标

Question

在阅读了这个链接http://microjet.ath.cx/WebWiki/ResultPaginationWithPostgresql.html之后，我决定使用游标进行分页。但是，我似乎不知道如何在plpgsql中获取结果。

这是我的职责

CREATE OR REPLACE FUNCTION get_pagination_custom_word_moderation(_moderation_id bigint, _is_black boolean, _index integer, _max_result integer)
    RETURNS TABLE(word_id bigint,
        word character varying,
        is_num_rlpcm boolean,
        is_word_bund boolean,
        note text,
        create_time timestamp without time zone,
        last_update timestamp without time zone) AS
$BODY$
DECLARE custom_word_moderation_cursor CURSOR FOR
    SELECT
        word_id,
        word,
        is_num_rlpcm,
        is_word_bund,
        note,
        create_time,
        last_update
    FROM
        custom_word_moderation
    WHERE
        moderation_id=_moderation_id
    AND is_black=_is_black;
BEGIN

MOVE ABSOLUTE _index FROM custom_word_moderation_cursor;
RETURN QUERY FETCH _max_result FROM custom_word_moderation_cursor;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

错误是：

ERROR:  syntax error at or near "$1"
LINE 1:  FETCH  $1  FROM  $2 
                ^
QUERY:   FETCH  $1  FROM  $2 
CONTEXT:  SQL statement in PL/PgSQL function "get_pagination_custom_word_moderation" near line 18

********** Error **********

ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "get_pagination_custom_word_moderation" near line 18

我认为问题在于如何返回获取结果表单游标。

Answer 1

你想要做的事情没有实现。游标将以这样的方式返回，以便客户端可以根据自己的意愿获取行。尤其是对于重大的结果。为此，您将使用RETURNS refcursor定义一个函数。

您可以让它与FOR LOOP和显式分配的OUT变量，但这是棘手的结合RETURNS TABLE .您还必须使用 the cursor，因为在plpgsql的上下文中，DECLARE的含义与 for cursors的关键字不同。你就得FETCH .. INTO ..了

相反，使用一个简单的等价物而不带游标：

CREATE OR REPLACE FUNCTION get_pagination_custom_word_moderation(
                         _moderation_id bigint, _is_black boolean
                       , _index integer, _max_result integer)
    RETURNS TABLE(word_id bigint,
        word varchar,
        is_num_rlpcm boolean,
        is_word_bund boolean,
        note text,
        create_time timestamp,
        last_update timestamp) AS
$func$
BEGIN
   RETURN QUERY
   SELECT word_id
         ,word
         ,is_num_rlpcm
         ,is_word_bund
         ,note
         ,create_time
         ,last_update
   FROM   custom_word_moderation
   WHERE  moderation_id = _moderation_id
   AND    is_black = _is_black
   OFFSET _index
   LIMIT  _max_result;
END
$func$ LANGUAGE plpgsql;

或者更简单地使用SQL函数：

CREATE OR REPLACE FUNCTION get_pagination_custom_word_moderation(
                         _moderation_id bigint, _is_black boolean
                       , _index integer, _max_result integer)
    RETURNS TABLE(word_id bigint,
        word varchar,
        is_num_rlpcm boolean,
        is_word_bund boolean,
        note text,
        create_time timestamp,
        last_update timestamp) AS
$func$
   SELECT word_id
         ,word
         ,is_num_rlpcm
         ,is_word_bund
         ,note
         ,create_time
         ,last_update
   FROM   custom_word_moderation
   WHERE  moderation_id = $1
   AND    is_black = $2
   OFFSET $3
   LIMIT  $4;
$func$ LANGUAGE sql;

我在函数体中使用了$n表示法，因为在版本9.2之前，在SQL中不能通过名称引用参数。

如果实际上希望返回表的所有列，则可以进一步简化：

CREATE OR REPLACE FUNCTION get_pagination_custom_word_moderation(
                         _moderation_id bigint, _is_black boolean
                       , _index integer, _max_result integer)
    RETURNS SETOF custom_word_moderation AS
$func$
   SELECT *
   FROM   custom_word_moderation
   WHERE  moderation_id = $1
   AND    is_black = $2
   OFFSET $3
   LIMIT  $4;
$func$ LANGUAGE sql;