定义一个游标
postgres=# begin;BEGINpostgres=# DECLARE tdsql_pg_cur SCROLL CURSOR FOR SELECT * from tdsql_pg ORDER BY id;DECLARE CURSOR
游标需要放在一个事务中使用。
提取下一行数据
postgres=# DECLARE tdsql_pg_cur SCROLL CURSOR FOR SELECT * from tdsql_pg ORDER BY id;DECLARE CURSORpostgres=# FETCH NEXT from tdsql_pg_cur ;id | nickname----+-------------1 | hello tdsql_pg(1 row)postgres=# FETCH NEXT from tdsql_pg_cur ;id | nickname----+-----------2 | tdsql_pg好(1 row)
提取前一行数据
postgres=# FETCH PRIOR from tdsql_pg_cur ;id | nickname----+-------------1 | hello tdsql_pg(1 row)postgres=# FETCH PRIOR from tdsql_pg_cur ;id | nickname----+----------(0 rows)
提取最后一行
postgres=# FETCH LAST from tdsql_pg_cur ;id | nickname----+------------5 | tdsql_pg swap(1 row)
提取第一行
postgres=# FETCH FIRST from tdsql_pg_cur ;id | nickname----+-------------1 | hello tdsql_pg(1 row)
提取该查询的第 x 行
postgres=# FETCH ABSOLUTE 2 from tdsql_pg_cur ;id | nickname----+-----------2 | tdsql_pg好(1 row)postgres=# FETCH ABSOLUTE -1 from tdsql_pg_cur ;id | nickname----+------------5 | tdsql_pg swap(1 row)postgres=# FETCH ABSOLUTE -2 from tdsql_pg_cur ;id | nickname----+---------------4 | tdsql_pg default(1 row)
x 为负数时则从尾部向上提。
提取当前位置后的第 x 行
postgres=# FETCH ABSOLUTE 1 from tdsql_pg_cur ;id | nickname----+-------------1 | hello tdsql_pg(1 row)postgres=# FETCH RELATIVE 2 from tdsql_pg_cur ;id | nickname----+-----------3 | tdsql_pg好(1 row)postgres=# FETCH RELATIVE 2 from tdsql_pg_cur ;id | nickname----+------------5 | tdsql_pg swap(1 row)
每提取一次数据，游标的位置都是会前行。
向前提取 x 行数据
postgres=# FETCH FORWARD 2 from tdsql_pg_cur ;id | nickname----+-------------1 | hello tdsql_pg2 | tdsql_pg好(2 rows)postgres=# FETCH FORWARD 2 from tdsql_pg_cur ;id | nickname----+---------------3 | tdsql_pg好4 | tdsql_pg default(2 rows)
向前提取剩下的所有数据
postgres=# FETCH FORWARD 2 from tdsql_pg_cur ;id | nickname----+-------------1 | hello tdsql_pg2 | tdsql_pg好(2 rows)postgres=# FETCH FORWARD all from tdsql_pg_cur ;id | nickname----+---------------3 | tdsql_pg好4 | tdsql_pg default5 | tdsql_pg swap(3 rows)
向后提取 x 行数据
postgres=# FETCH BACKWARD 2 from tdsql_pg_cur ;id | nickname----+---------------5 | tdsql_pg swap4 | tdsql_pg default(2 rows)
向后提取剩下的所有数据
postgres=# FETCH BACKWARD all from tdsql_pg_cur ;id | nickname----+-------------3 | tdsql_pg好2 | tdsql_pg好1 | hello tdsql_pg(3 rows)
