cursor

http://blog.163.com/digoal@126/blog/static/163877040201111694355822/

http://blog.163.com/digoal@126/blog/static/1638770402012520105855757/

游标使用注意点：

http://www.postgresql.org/docs/9.3/static/sql-declare.html

http://www.postgresql.org/docs/9.3/static/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS

分页：http://blog.163.com/digoal@126/blog/static/163877040201111694355822/

使用分页的话，如果使用limit...offset..则会每次都要计算时长执行select语句，

如果使用move cursor的话，则不需要每次都执行select。会省去不少时间。

参数解释:

http://www.postgresql.org/docs/9.3/static/sql-declare.html

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query

BINARY：默认是text，不是二进制

INSENSITIVE：（不敏感）默认是INSENSITIVE，只有在对游标的SQL使用

for update/for share的时候才会自动将其改为SENSITIVE（敏感）。

SENSITIVE即就是会及时反映出外部DML操作对记录的更改。

INSENSITIVE不能跟for update一起使用，会出错。

for update会锁住相应已经获取到的行，如果另一个session更新相应行，则会等待这个锁。

如果想使用cursor进行更新数据则游标最好使用for update

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.)

SCROLL：默认是SCROLL。for update之后会将其改为NO SCROLL。

WITH HOLD：定义之后在SESSION中有效。WITHOUT HOLD定以后在transaction中有效。

FETCH操作cursor：

http://www.postgresql.org/docs/9.3/static/sql-fetch.html

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

move操作cursor：

http://www.postgresql.org/docs/9.3/static/sql-move.html

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

DML操作cursor：

begin;
declare c cursor for select * from aa for update;
update aa set b='xxxxxxxx' where current of c;
end;

函数返回row

http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html

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)