Waiting for 8.5 – MOVE {FORWARD,BACKWARD} X

On 29th of September (I know, there is a backlog – I'll work on it, I promise), Tom Lane committed another patch from Pavel Stehule:

Allow MOVE FORWARD n, MOVE BACKWARD n, MOVE FORWARD ALL, MOVE BACKWARD ALL
in plpgsql.  Clean up a couple of corner cases in the MOVE/FETCH syntax.
 
Pavel Stehule

Description is pretty obvious, but let's try to use it in some example:

CREATE OR REPLACE FUNCTION test() RETURNS void AS $$
DECLARE
    test_cursor REFCURSOR;
    temprec     RECORD;
BEGIN
    OPEN test_cursor SCROLL FOR
        SELECT oid, ROW_NUMBER() OVER ( ORDER BY oid ), *
        FROM pg_class
        WHERE relkind = 'r'
        ORDER BY oid;
 
    FETCH test_cursor INTO temprec;
    RAISE NOTICE 'Table #% is % with oid %.', temprec.row_number, temprec.oid::regclass, temprec.oid;
 
    MOVE FORWARD 5 IN test_cursor;
    FETCH test_cursor INTO temprec;
    RAISE NOTICE 'Table #% is % with oid %.', temprec.row_number, temprec.oid::regclass, temprec.oid;
 
    MOVE FORWARD ALL IN test_cursor;
    FETCH test_cursor INTO temprec;
    RAISE NOTICE 'Table #% is % with oid %.', temprec.row_number, temprec.oid::regclass, temprec.oid;
 
    MOVE BACKWARD 2 IN test_cursor;
    FETCH test_cursor INTO temprec;
    RAISE NOTICE 'Table #% is % with oid %.', temprec.row_number, temprec.oid::regclass, temprec.oid;
 
    MOVE BACKWARD 5 IN test_cursor;
    FETCH test_cursor INTO temprec;
    RAISE NOTICE 'Table #% is % with oid %.', temprec.row_number, temprec.oid::regclass, temprec.oid;
 
    close test_cursor;
END;
$$ LANGUAGE plpgsql;

Output:

psql:z.sql:36: NOTICE:  Table #1 is pg_default_acl with oid 826.
psql:z.sql:36: NOTICE:  Table #7 is pg_proc with oid 1255.
psql:z.sql:36: NOTICE:  Table #<NULL> is <NULL> with oid <NULL>.
psql:z.sql:36: NOTICE:  Table #56 is xy with oid 16735.
psql:z.sql:36: NOTICE:  Table #52 is audit_pg_stat_all_indexes with oid 16711.

As you can see after MOVE FORWARD ALL cursor is technically after returnset.

Also, please note thet I used MOVE BACKWARD 2 – it is important, as fetch always moves the cursor 1 row in direction of fetch (usually forward).

I could have avoided it by using FETCH BACKWARD, but it's not that important.

Generally – it's nice to have the ability to quickly move to end (and/or) beginning of cursor in PL/pgSQL, and to jump over it.