Cursors

Run a query once, keep its rows, and read them a few at a time.

• Category: Cursors
• Keywords: cursor, CURSOR, DECLARE CURSOR, NEXT, ALL, paging, page by page, read in parts, big result, snapshot, FETCH NEXT, FETCH ALL, too many open cursors, is not open
• See also: Sessions and cursors over HTTP, The procedure language (local-cursors), Transactions, ORDER BY, LIMIT, OFFSET, 13. Functions, procedures and triggers

A cursor runs a SELECT once and keeps its rows. Then you read the rows a part at a time with FETCH, and end with CLOSE. Use it when a result is too big to read at once, for example to send a long trade log to an app page by page.

• The rows are a snapshot (a copy made at DECLARE): changes to the table after that do not change what FETCH returns.
• A cursor lives until CLOSE, or until the end of the text or session (see where a cursor lives).
• Over HTTP, POST /cursor does the same job (see cursors over HTTP).
• Inside a procedure, a cursor reads one row at a time into variables: see cursors in a body.

Example: The trade log of one day, 5 trades at a time (sample data: sample)

DECLARE trades CURSOR FOR
  SELECT transaction_id, symbol, buyer, seller, quantity, rate
  FROM trade_log WHERE date = '2026-09-10' ORDER BY transaction_id;
FETCH NEXT 5 FROM trades;
FETCH NEXT 5 FROM trades;
CLOSE trades;

Result:

DECLARE trades CURSOR FOR SELECT transaction_id, symbol, buyer, seller, quantity, rate FROM trade_lo

cursor 'trades' declared: 24 row(s)

FETCH NEXT 5 FROM trades

(5 rows)

5 row(s) fetched, 19 left

FETCH NEXT 5 FROM trades

(5 rows)

5 row(s) fetched, 14 left

CLOSE trades

cursor 'trades' closed

DECLARE CURSOR

Syntax:

DECLARE name CURSOR FOR SELECT ...

Runs the query at once and keeps all its rows under a name.

• Returns: cursor 'name' declared: N row(s).
• Notes:
  • The query runs once, now. Its rows stay in memory until the cursor is closed.
  • A name that is already open in the same text or session is an error: CLOSE it first.
  • DECLARE name TYPE (a variable) works only inside a procedure or trigger body.
  • At most 16 open cursors per database, and at most 10 million rows per cursor (see limits).
  • DECLARE ... CURSOR works inside BEGIN ... COMMIT, and then it sees the changes of the transaction.

Example: A snapshot: rows deleted after DECLARE are still fetched (sample data: sample)

DECLARE s CURSOR FOR SELECT symbol, sector FROM securities ORDER BY symbol;
DELETE FROM securities WHERE sector = 'Banking';
FETCH ALL FROM s;
SELECT count(*) AS securities_now FROM securities;

Result:

DECLARE s CURSOR FOR SELECT symbol, sector FROM securities ORDER BY symbol

cursor 's' declared: 5 row(s)

DELETE FROM securities WHERE sector = 'Banking'

2 row(s) deleted

FETCH ALL FROM s

(5 rows)

5 row(s) fetched, no more rows

SELECT count(*) AS securities_now FROM securities

(1 row)

Example: The same name twice (shows an error)

DECLARE c CURSOR FOR SELECT 1 AS x;
DECLARE c CURSOR FOR SELECT 2 AS x;

Result:

cursor 'c' declared: 1 row(s)

Error: cursor 'c' is already open; CLOSE it first

Example: Only a SELECT (shows an error)

DECLARE c CURSOR FOR SHOW TABLES;

Result:

Error: line 1, column 22: expected a query (SELECT ...) after CURSOR FOR but found 'SHOW'

FETCH

Syntax:

FETCH name
FETCH [FROM] name
FETCH NEXT [FROM] name
FETCH NEXT count FROM name
FETCH count FROM name
FETCH ALL FROM name

Returns the next rows of the cursor and moves past them.

• Returns: the rows (with the columns of the query), and the message N row(s) fetched, M left or N row(s) fetched, no more rows.
• Notes:
  • With a count or ALL, the word FROM is needed: FETCH NEXT 5 c is an error.
  • The count must be 1 or more.
  • After the last row, FETCH returns 0 rows (not an error). The cursor stays open until CLOSE.
  • There is no going back (PRIOR, ABSOLUTE): a cursor only moves forward.
  • FETCH ... INTO variables works only inside a procedure or trigger body.

Example: All forms of FETCH (sample data: sample)

DECLARE s CURSOR FOR SELECT symbol, sector FROM securities ORDER BY symbol;
FETCH s;
FETCH NEXT FROM s;
FETCH 2 FROM s;
FETCH ALL FROM s;
FETCH ALL FROM s;

Result:

DECLARE s CURSOR FOR SELECT symbol, sector FROM securities ORDER BY symbol

cursor 's' declared: 5 row(s)

FETCH NEXT 1 FROM s

(1 row)

1 row(s) fetched, 4 left

FETCH NEXT 1 FROM s

(1 row)

1 row(s) fetched, 3 left

FETCH NEXT 2 FROM s

(2 rows)

2 row(s) fetched, 1 left

FETCH ALL FROM s

(1 row)

1 row(s) fetched, no more rows

FETCH ALL FROM s

(0 rows)

0 row(s) fetched, no more rows

Example: Minute candles of CITY, one hour at a time (sample data: sample)

DECLARE m CURSOR FOR
  SELECT time, open, high, low, close, volume
  FROM ochl_minutes WHERE symbol = 'CITY' AND time IN '2026-09-10' ORDER BY time;
FETCH NEXT 60 FROM m;
FETCH NEXT 60 FROM m;
CLOSE m;

Result:

DECLARE m CURSOR FOR SELECT time, open, high, low, close, volume FROM ochl_minutes WHERE symbol = 'C

cursor 'm' declared: 240 row(s)

FETCH NEXT 60 FROM m

(first 5 of 60 rows)

60 row(s) fetched, 180 left

FETCH NEXT 60 FROM m

(first 5 of 60 rows)

60 row(s) fetched, 120 left

CLOSE m

cursor 'm' closed

Example: FROM is needed with a count (shows an error)

DECLARE c CURSOR FOR SELECT 1 AS x;
FETCH NEXT 5 c;

Result:

Error: line 2, column 14: expected FROM and the cursor name, as in FETCH NEXT 100 FROM c, but found 'c'

Example: A count of 0 (shows an error)

DECLARE c CURSOR FOR SELECT 1 AS x;
FETCH 0 FROM c;

Result:

Error: line 2, column 7: expected a row count of 1 or more but found '0'

CLOSE

Syntax:

CLOSE name

Closes the cursor and frees the memory of its rows.

• Returns: cursor 'name' closed.
• Notes: closing a cursor that is not open is an error. Cursors also close by themselves at the end of the text, the end of the session, and after 10 minutes without FETCH. Still, close them when you are done: only 16 can be open.

Example (sample data: sample)

DECLARE b CURSOR FOR SELECT id, name FROM brokers ORDER BY id;
FETCH NEXT 2 FROM b;
CLOSE b;

Result:

DECLARE b CURSOR FOR SELECT id, name FROM brokers ORDER BY id

cursor 'b' declared: 5 row(s)

FETCH NEXT 2 FROM b

(2 rows)

2 row(s) fetched, 3 left

CLOSE b

cursor 'b' closed

Example: A cursor that is not open (shows an error)

CLOSE b;

Result:

Error: cursor 'b' is not open

Where a cursor lives

Syntax:

kdb sql db "DECLARE ...; FETCH ...; CLOSE ..."     -- one text
kdb shell db                                        -- one session: many lines
POST /session, then X-KDB-Session on POST /exec     -- one HTTP session: many requests

A cursor belongs to the text or the session that declared it.

All of them also close after 10 minutes without FETCH. Another session cannot see your cursor, even with the same name.

• Notes:
  • When a statement of one text fails, the cursors of that text are closed too. In kdb shell and in an HTTP session, cursors stay open after an error.
  • ROLLBACK does not close cursors: their rows were already read at DECLARE.

Command line: Two kdb sql commands: the cursor of the first is gone (sample data: sample; shows an error)

kdb sql market "DECLARE b CURSOR FOR SELECT id, name FROM brokers ORDER BY id; FETCH NEXT 2 FROM b"
kdb sql market "FETCH NEXT 2 FROM b"

Output:

> kdb sql market "DECLARE b CURSOR FOR SELECT id, name FROM brokers ORDER BY id; FETCH NEXT 2 FROM b"

cursor 'b' declared: 5 row(s)

id | name               
---+--------------------
 1 | Keystone Securities
34 | Vision Securities  
(2 rows, 0.0 ms)
2 row(s) fetched, 3 left

> kdb sql market "FETCH NEXT 2 FROM b"

error: cursor 'b' is not open (DECLARE b CURSOR FOR SELECT ... first; an unused cursor closes after 10 minutes)

exit code 1

Command line: kdb shell: the cursor stays from one line to the next (sample data: sample)

kdb shell market
> DECLARE b CURSOR FOR SELECT id, name FROM brokers ORDER BY id;
> FETCH NEXT 2 FROM b;
> FETCH NEXT 2 FROM b;
> FETCH ALL FROM b;
> CLOSE b;
> .exit

Output:

KDB 0.2.0-dev shell on market
End statements with ';'. Type .help for commands, .exit to quit.
kdb> cursor 'b' declared: 5 row(s)
kdb> id | name               
---+--------------------
 1 | Keystone Securities
34 | Vision Securities  
(2 rows, 0.0 ms)
2 row(s) fetched, 3 left
kdb> id | name               
---+--------------------
45 | Imperial Securities
49 | Online Securities  
(2 rows, 0.0 ms)
2 row(s) fetched, 1 left
kdb> id | name                
---+---------------------
58 | Northgate Securities
(1 row, 0.0 ms)
1 row(s) fetched, no more rows
kdb> cursor 'b' closed
kdb>

HTTP: An HTTP session: DECLARE in one request, FETCH in the next (sample data: sample)

POST /session

###
POST /exec
X-KDB-Session: {{session}}

DECLARE b CURSOR FOR SELECT id, name FROM brokers ORDER BY id
###
POST /exec
X-KDB-Session: {{session}}

FETCH NEXT 3 FROM b
###
DELETE /session/{{session}}

Response:

POST /session

HTTP 200 (application/json)

{"session": "bc2b748660dfa76d2a145ccd793a280b", "idle_timeout_ms": 600000}

POST /exec

HTTP 200 (application/json)

{
  "query": "DECLARE b CURSOR FOR SELECT id, name FROM brokers ORDER BY id",
  "results": [
    {
      "statement": "DECLARE b CURSOR FOR SELECT id, name FROM brokers ORDER BY id",
      "columns": [],
      "dataset": [],
      "count": 0,
      "truncated": false,
      "rows_affected": 0,
      "message": "cursor 'b' declared: 5 row(s)",
      "timings": {"parse_us": 48, "execute_us": 295}
    }
  ],
  "columns": [],
  "dataset": [],
  "count": 0,
  "timings": {"parse_us": 48, "execute_us": 295}
}

POST /exec

HTTP 200 (application/json)

{
  "query": "FETCH NEXT 3 FROM b",
  "results": [
    {
      "statement": "FETCH NEXT 3 FROM b",
      "columns": [
        {"name": "id", "type": "INT"},
        {"name": "name", "type": "STRING"}
      ],
      "dataset": [
        [1, "Keystone Securities"],
        [34, "Vision Securities"],
        [45, "Imperial Securities"]
      ],
      "count": 3,
      "truncated": false,
      "rows_affected": 0,
      "message": "3 row(s) fetched, 2 left",
      "timings": {"parse_us": 20, "execute_us": 8}
    }
  ],
  "columns": [
    {"name": "id", "type": "INT"},
    {"name": "name", "type": "STRING"}
  ],
  "dataset": [
    [1, "Keystone Securities"],
    [34, "Vision Securities"],
    [45, "Imperial Securities"]
  ],
  "count": 3,
  "timings": {"parse_us": 20, "execute_us": 8}
}

DELETE /session/{{session}}

HTTP 200 (application/json)

{"session": "bc2b748660dfa76d2a145ccd793a280b", "closed": true}

Cursors over HTTP: POST /cursor

Syntax:

POST   /cursor                 body: one SELECT     -> {"cursor": "<id>", "columns": [...], "rows": N}
GET    /cursor/<id>?fetch=N                          -> {"cursor", "columns", "dataset", "count", "done"}
DELETE /cursor/<id>                                  -> {"cursor": "<id>", "closed": true}

An app does not need SQL cursor statements over HTTP. POST /cursor runs one SELECT and keeps the rows on the server; GET /cursor/<id>?fetch=N returns the next N rows; DELETE closes it. The full description is in HTTP sessions and cursors.

• Notes:
  • fetch is 1,000 when you do not give it. done is true when no rows are left.
  • The id is long and random, so another client cannot guess it. No session is needed.
  • Use the same database (db= or the header X-KDB-Database) for all requests of one cursor.
  • HTTP cursors count toward the same 16 open cursors of the database.
  • A closed or unknown cursor answers HTTP 404.

HTTP: Page through the trade log over HTTP (sample data: sample)

POST /cursor

SELECT transaction_id, symbol, quantity, rate FROM trade_log WHERE date = '2026-09-09' ORDER BY transaction_id
###
GET /cursor/{{cursor}}?fetch=3
###
GET /cursor/{{cursor}}?fetch=3
###
DELETE /cursor/{{cursor}}
###
GET /cursor/{{cursor}}?fetch=3

Response:

POST /cursor

HTTP 200 (application/json)

{
  "cursor": "d8c238e3eace8c6c",
  "columns": [
    {"name": "transaction_id", "type": "LONG"},
    {"name": "symbol", "type": "SYMBOL"},
    {"name": "quantity", "type": "LONG"},
    {"name": "rate", "type": "DOUBLE"}
  ],
  "rows": 8
}

GET /cursor/{{cursor}}?fetch=3

HTTP 200 (application/json)

{
  "cursor": "d8c238e3eace8c6c",
  "columns": [
    {"name": "transaction_id", "type": "LONG"},
    {"name": "symbol", "type": "SYMBOL"},
    {"name": "quantity", "type": "LONG"},
    {"name": "rate", "type": "DOUBLE"}
  ],
  "dataset": [
    [100001, "TELCO", 1000, 881],
    [100002, "CITY", 10, 515],
    [100003, "CITY", 500, 513]
  ],
  "count": 3,
  "done": false
}

GET /cursor/{{cursor}}?fetch=3

HTTP 200 (application/json)

{
  "cursor": "d8c238e3eace8c6c",
  "columns": [
    {"name": "transaction_id", "type": "LONG"},
    {"name": "symbol", "type": "SYMBOL"},
    {"name": "quantity", "type": "LONG"},
    {"name": "rate", "type": "DOUBLE"}
  ],
  "dataset": [
    [100004, "CITY", 500, 514.5],
    [100005, "CITY", 500, 517],
    [100006, "COAST", 200, 401]
  ],
  "count": 3,
  "done": false
}

DELETE /cursor/{{cursor}}

HTTP 200 (application/json)

{"cursor": "d8c238e3eace8c6c", "closed": true}

GET /cursor/{{cursor}}?fetch=3

HTTP 404 (application/json)

{"error": "cursor 'd8c238e3eace8c6c' does not exist (an unused cursor closes after 10 minutes)"}

Limits of cursors

Syntax:

DECLARE c17 CURSOR FOR SELECT ...   -- error when 16 cursors are open in the database

Example: The 17th open cursor (shows an error)

DECLARE c1 CURSOR FOR SELECT 1 AS x;
DECLARE c2 CURSOR FOR SELECT 2 AS x;
DECLARE c3 CURSOR FOR SELECT 3 AS x;
DECLARE c4 CURSOR FOR SELECT 4 AS x;
DECLARE c5 CURSOR FOR SELECT 5 AS x;
DECLARE c6 CURSOR FOR SELECT 6 AS x;
DECLARE c7 CURSOR FOR SELECT 7 AS x;
DECLARE c8 CURSOR FOR SELECT 8 AS x;
DECLARE c9 CURSOR FOR SELECT 9 AS x;
DECLARE c10 CURSOR FOR SELECT 10 AS x;
DECLARE c11 CURSOR FOR SELECT 11 AS x;
DECLARE c12 CURSOR FOR SELECT 12 AS x;
DECLARE c13 CURSOR FOR SELECT 13 AS x;
DECLARE c14 CURSOR FOR SELECT 14 AS x;
DECLARE c15 CURSOR FOR SELECT 15 AS x;
DECLARE c16 CURSOR FOR SELECT 16 AS x;
DECLARE c17 CURSOR FOR SELECT 17 AS x;

Result:

DECLARE c1 CURSOR FOR SELECT 1 AS x

cursor 'c1' declared: 1 row(s)

DECLARE c2 CURSOR FOR SELECT 2 AS x

cursor 'c2' declared: 1 row(s)

DECLARE c3 CURSOR FOR SELECT 3 AS x

cursor 'c3' declared: 1 row(s)

DECLARE c4 CURSOR FOR SELECT 4 AS x

cursor 'c4' declared: 1 row(s)

DECLARE c5 CURSOR FOR SELECT 5 AS x

cursor 'c5' declared: 1 row(s)

DECLARE c6 CURSOR FOR SELECT 6 AS x

cursor 'c6' declared: 1 row(s)

DECLARE c7 CURSOR FOR SELECT 7 AS x

cursor 'c7' declared: 1 row(s)

DECLARE c8 CURSOR FOR SELECT 8 AS x

cursor 'c8' declared: 1 row(s)

DECLARE c9 CURSOR FOR SELECT 9 AS x

cursor 'c9' declared: 1 row(s)

DECLARE c10 CURSOR FOR SELECT 10 AS x

cursor 'c10' declared: 1 row(s)

DECLARE c11 CURSOR FOR SELECT 11 AS x

cursor 'c11' declared: 1 row(s)

DECLARE c12 CURSOR FOR SELECT 12 AS x

cursor 'c12' declared: 1 row(s)

DECLARE c13 CURSOR FOR SELECT 13 AS x

cursor 'c13' declared: 1 row(s)

DECLARE c14 CURSOR FOR SELECT 14 AS x

cursor 'c14' declared: 1 row(s)

DECLARE c15 CURSOR FOR SELECT 15 AS x

cursor 'c15' declared: 1 row(s)

DECLARE c16 CURSOR FOR SELECT 16 AS x

cursor 'c16' declared: 1 row(s)

Error: too many open cursors (16); CLOSE the ones you do not need (an unused cursor also closes after 10 minutes)