October 15th, 2009 by depesz | Tags: , , , , , , | No comments »
Did it help? If yes - maybe you can help me?

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.

Leave a comment