http://blog.163.com/digoal@126/blog/static/163877040201111694355822/
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
If the cursor's query includes FOR UPDATE or FOR SHARE, then returned rows are locked at the time they are first fetched, in the same way as for a regular SELECT command with these options. In addition, the returned rows will be the most up-to-date versions; therefore these options provide the equivalent of what the SQL standard calls a "sensitive cursor". (Specifying INSENSITIVE together with FOR UPDATE or FOR SHARE is an error.)
FETCH [ direction [ FROM | IN ] ] cursor_name where direction can be empty or one of: NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count count ALL FORWARD FORWARD count FORWARD ALL BACKWARD BACKWARD count BACKWARD ALL
DML操作cursor:MOVE [ direction [ FROM | IN ] ] cursor_name where direction can be empty or one of: NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count count ALL FORWARD FORWARD count FORWARD ALL BACKWARD BACKWARD count BACKWARD ALL
begin; declare c cursor for select * from aa for update; update aa set b='xxxxxxxx' where current of c; end;
digoal=# CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); CREATE TABLE digoal=# INSERT INTO foo VALUES (1, 2, 'three'); INSERT 0 1 digoal=# INSERT INTO foo VALUES (4, 5, 'six'); INSERT 0 1 digoal=# CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS digoal-# $BODY$ digoal$# DECLARE digoal$# r foo%rowtype; digoal$# BEGIN digoal$# FOR r IN digoal$# SELECT * FROM foo WHERE fooid > 0 digoal$# LOOP digoal$# -- can do some processing here digoal$# RETURN NEXT r; -- return current row of SELECT digoal$# END LOOP; digoal$# RETURN; digoal$# END digoal$# $BODY$ digoal-# LANGUAGE plpgsql; CREATE FUNCTION digoal=# select * from aa where a in (select fooid from get_all_foo()); a | b ---+------------------- 4 | 0.949729613494128 1 | mm (2 rows)