DECLARE¶
Create a cursor.
Synopsis¶
DECLARE name [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
Where name is an arbitrary name for the cursor and query is a
SELECT statement.
Description¶
A cursor is used to retrieve a small number of rows at a time from a query with a larger result set. After creating a cursor, rows are fetched using FETCH.
Declared cursors are visible in the pg_catalog.pg_cursors table.
Clauses¶
WITH | WITHOUT HOLD¶
Defaults to WITHOUT HOLD, causing a cursor to be bound to the life-time
of a transaction. Using WITHOUT HOLD without active transaction (Started
with BEGIN) is an error.
WITH HOLD changes the life-time of a cursor to that of the connection.
Committing a transaction closes all cursors created with WITHOUT HOLD.
Closing a connection closes all cursors created within that connection.
Note
CrateDB doesn’t support full transactions. A transaction cannot be rolled
back and any write operations within a BEGIN clause may become visible
to other statements before committing the transaction.
[ ASENSITIVE | INSENSITIVE ]¶
This clause has no effect in CrateDB Cursors in CrateDB are always insensitive.
[ NO ] SCROLL¶
NO SCROLL (the default) specifies that the cursor can only be used to move
forward.
SCROLL allows using a cursor for backward movement but also adds memory
overhead.