--- title: "DECLARE" url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/dml_declare/" --- # DECLARE Use the DECLARE statement to create a cursor. (There's no notion of "opening" a cursor in top-level SQL.) YSQL currently supports only fetching rows from a cursor consecutively in the forward direction. See the subsection [Beware Issue #6514](/stable/api/ysql/cursors/#beware-issue-6514 "Beware Issue #6514") in the generic section [Cursors](/stable/api/ysql/cursors/ "Cursors"). ## Synopsis Use the `DECLARE` statement to create a *cursor*. See the generic section [Cursors](/stable/api/ysql/cursors/ "Cursors"). The `DECLARE` statement is used jointly with the [`MOVE`](/stable/api/ysql/the-sql-language/statements/dml_move "MOVE"), [`FETCH`](/stable/api/ysql/the-sql-language/statements/dml_fetch "FETCH"), and [`CLOSE`](/stable/api/ysql/the-sql-language/statements/dml_close "CLOSE") statements. The term *cursor* is a SQL keyword that's used in the `DECLARE` statement (but in no other SQL statement). There's also a PL/pgSQL API for explicit cursor management. ## Syntax - [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1) - [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1) ```output.ebnf declare ::= DECLARE cursor_name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR subquery ``` #### declare DECLARE[cursor\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#cursor-name)BINARYINSENSITIVENOSCROLLCURSORWITHWITHOUTHOLDFOR[subquery](/stable/api/ysql/syntax_resources/grammar_diagrams#subquery) ## Semantics `DECLARE` creates a *cursor*. (There's no notion of so-called "opening" a *cursor* in top-level SQL.) A *cursor's* duration is limited to the duration of the session that declares it. Notice the critical maximum lifetime difference between a *holdable*, and a *non-holdable*, *cursor*. See the section [Transactional behavior — holdable and non-holdable cursors](/stable/api/ysql/cursors/#transactional-behavior-holdable-and-non-holdable-cursors "Transactional behavior — holdable and non-holdable cursors"). (The [`CLOSE`](/stable/api/ysql/the-sql-language/statements/dml_close "CLOSE") statement drops a *cursor* so that you can shorten its lifetime if you want to—typically in order to save resources.) The *pg\_cursors* catalog view lists all the currently existing *cursors* in the current session. ### *name* A *cursor* is identified only by an unqualified name and is visible only in the session that declares it. This determines the uniqueness scope for its name. (The name of a *cursor* is like that of a *prepared statement* in this respect.) ### *BINARY* Specifies that the *cursor* returns data in binary rather than in text format. Usually, a *cursor* is specified to return data in text format, the same as a `SELECT` statement would produce. This binary format reduces conversion effort for both the server and client at the cost of more programmer effort to deal with platform-dependent binary data formats. For example, if a query returns a value of *one* from an integer column, you would get the string value *1* with the default choice. But with a binary *cursor* you would get a 4-byte field containing the internal representation of the value (in big-endian byte order). Use binary cursors carefully. Many applications, including *ysqlsh* (and *psql*) are not prepared to handle binary cursors and expect data to come back in the text format. BINARY cursors and the 'extended' query protocol When the client application uses the *“extended"* query protocol to issue a `FETCH` statement, the bind protocol message specifies whether data is to be retrieved in text or binary format. This choice overrides the way that the *cursor* is defined. It therefore isn't useful to declare a *cursor* explicitly as `BINARY` because *any* *cursor* can be treated as either text or binary when you use the extended query protocol. ### *INSENSITIVE* This indicates that data retrieved from the *cursor* should be unaffected by updates to the table(s) underlying the *cursor* that occur after the *cursor* has been declared. In PostgreSQL, and therefore in YSQL, this is the *only* behavior. This key word therefore has no effect and is accepted only for compatibility with the SQL standard. ### *SCROLL and NO SCROLL* `SCROLL` specifies that you can use the flavors of `FETCH` and `MOVE` to access the current row and rows in the *cursor's* result set that lie *before* the current row (i.e. including and before the row that `FETCH RELATIVE 0` accesses). In simple cases, the execution plan is intrinsically reversible: it allows backwards fetching just as easily as it allows forwards fetching. But not all execution plans are reversible; and when a plan is not reversible, specifying `SCROLL` implies creating a cache of the rows that the *cursor's* *subquery* defines at the moment that the first `MOVE` or `FETCH` statement is executed (or on demand as new rows are accessed). This implies both a performance cost and a resource consumption cost. `NO SCROLL` specifies that the *cursor* cannot be used to retrieve the current row or rows that lie before it. When you specify neither `SCROLL` nor `NO SCROLL`, then allow scrolling is allowed in only *some* cases—and this is therefore different from specifying `SCROLL` explicitly. Always specify either SCROLL or NO SCROLL explicitly See the [tip](/stable/api/ysql/cursors/#specify-no-scroll-or-scroll-explicitly "tip") in the subsection [Scrollable cursors](/stable/api/ysql/cursors/#scrollable-cursors "Scrollable cursors") on the dedicated [Cursors](/stable/api/ysql/cursors/ "Cursors") page. Choose the mode that you want explicitly to honor the requirements that you must meet. Notice that while [Issue #6514](https://github.com/yugabyte/yugabyte-db/issues/6514 "Issue #6514") remains open, your only viable choice is `NO SCROLL`. ### *WITHOUT HOLD and WITH HOLD* `WITHOUT HOLD` specifies that the *cursor* cannot be used after the transaction that created it ends (even if it ends with a successful *commit*). `WITH HOLD` specifies that the *cursor* can continue to be used after the transaction that created it successfully commits. (Of course, it vanishes if the transaction that created it rolls back.) Specifying neither `WITHOUT HOLD` nor `WITH HOLD` is the same as specifying `WITHOUT HOLD`. ## Simple example ```plpgsql drop table if exists t cascade; create table t(k, v) as select g.val, g.val*100 from generate_series(1, 22) as g(val); start transaction; declare cur scroll cursor without hold for select k, v from t where (k <> all (array[1, 3, 5, 7, 11, 13, 17, 19])) order by k; select statement, is_holdable::text, is_scrollable::text from pg_cursors where name = 'cur' and not is_binary and creation_time < (transaction_timestamp() + make_interval(secs=>0.05)); fetch all from cur; close cur; rollback; ``` This is the result from *"select... from pg\_cursors..."*: ```output statement | is_holdable | is_scrollable --------------------------------------------------------+-------------+--------------- declare cur scroll cursor without hold for +| false | true select k, v +| | from t +| | where (k <> all (array[1, 3, 5, 7, 11, 13, 17, 19]))+| | order by k; | | ``` And this is the result from `FETCH ALL`: ```output k | v ----+------ 2 | 200 4 | 400 6 | 600 8 | 800 9 | 900 10 | 1000 12 | 1200 14 | 1400 15 | 1500 16 | 1600 18 | 1800 20 | 2000 21 | 2100 22 | 2200 ``` ## See also - [MOVE](/stable/api/ysql/the-sql-language/statements/dml_move "MOVE") - [FETCH](/stable/api/ysql/the-sql-language/statements/dml_fetch "FETCH") - [CLOSE](/stable/api/ysql/the-sql-language/statements/dml_close "CLOSE")