{
    "content": [
        {
            "type": "text",
            "text": "# DECLARE (info)\n\n## NAME\n\nDECLARE - define a cursor\n\n## SYNOPSIS\n\nDECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]\nCURSOR [ { WITH | WITHOUT } HOLD ] FOR query\n\n## DESCRIPTION\n\nDECLARE allows a user to create cursors, which can be used to retrieve\na small number of rows at a time out of a larger query. After the\ncursor is created, rows are fetched from it using FETCH.\n\n## Sections\n\n- **NAME**\n- **SYNOPSIS**\n- **DESCRIPTION**\n- **PARAMETERS**\n- **NOTES**\n- **EXAMPLES**\n- **COMPATIBILITY**\n- **SEE ALSO**\n\nUse structuredContent.sections for detailed options, examples, and full documentation.\n"
        }
    ],
    "structuredContent": {
        "command": "DECLARE",
        "section": "",
        "mode": "info",
        "summary": "DECLARE - define a cursor",
        "synopsis": "DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]\nCURSOR [ { WITH | WITHOUT } HOLD ] FOR query",
        "tldr_summary": null,
        "tldr_examples": [],
        "tldr_source": null,
        "flags": [],
        "examples": [
            "To declare a cursor:",
            "DECLARE liahona CURSOR FOR SELECT * FROM films;",
            "See FETCH(7) for more examples of cursor usage."
        ],
        "see_also": [
            {
                "name": "CLOSE",
                "section": "7",
                "url": "https://www.chedong.com/phpMan.php/man/CLOSE/7/json"
            },
            {
                "name": "FETCH",
                "section": "7",
                "url": "https://www.chedong.com/phpMan.php/man/FETCH/7/json"
            },
            {
                "name": "MOVE",
                "section": "7",
                "url": "https://www.chedong.com/phpMan.php/man/MOVE/7/json"
            }
        ],
        "section_outline": [
            {
                "name": "NAME",
                "lines": 2,
                "subsections": []
            },
            {
                "name": "SYNOPSIS",
                "lines": 3,
                "subsections": []
            },
            {
                "name": "DESCRIPTION",
                "lines": 9,
                "subsections": []
            },
            {
                "name": "PARAMETERS",
                "lines": 48,
                "subsections": []
            },
            {
                "name": "NOTES",
                "lines": 104,
                "subsections": []
            },
            {
                "name": "EXAMPLES",
                "lines": 6,
                "subsections": []
            },
            {
                "name": "COMPATIBILITY",
                "lines": 11,
                "subsections": []
            },
            {
                "name": "SEE ALSO",
                "lines": 3,
                "subsections": []
            }
        ],
        "sections": {
            "NAME": {
                "content": "DECLARE - define a cursor\n",
                "subsections": []
            },
            "SYNOPSIS": {
                "content": "DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]\nCURSOR [ { WITH | WITHOUT } HOLD ] FOR query\n",
                "subsections": []
            },
            "DESCRIPTION": {
                "content": "DECLARE allows a user to create cursors, which can be used to retrieve\na small number of rows at a time out of a larger query. After the\ncursor is created, rows are fetched from it using FETCH.\n\nNote\nThis page describes usage of cursors at the SQL command level. If\nyou are trying to use cursors inside a PL/pgSQL function, the rules\nare different -- see Section 43.7.\n",
                "subsections": []
            },
            "PARAMETERS": {
                "content": "name\nThe name of the cursor to be created.\n\nBINARY\nCauses the cursor to return data in binary rather than in text\nformat.\n\nASENSITIVE\nINSENSITIVE\nCursor sensitivity determines whether changes to the data\nunderlying the cursor, done in the same transaction, after the\ncursor has been declared, are visible in the cursor.  INSENSITIVE\nmeans they are not visible, ASENSITIVE means the behavior is\nimplementation-dependent. A third behavior, SENSITIVE, meaning that\nsuch changes are visible in the cursor, is not available in\nPostgreSQL. In PostgreSQL, all cursors are insensitive; so these\nkey words have no effect and are only accepted for compatibility\nwith the SQL standard.\n\nSpecifying INSENSITIVE together with FOR UPDATE or FOR SHARE is an\nerror.\n\nSCROLL\nNO SCROLL\nSCROLL specifies that the cursor can be used to retrieve rows in a\nnonsequential fashion (e.g., backward). Depending upon the\ncomplexity of the query's execution plan, specifying SCROLL might\nimpose a performance penalty on the query's execution time.  NO\nSCROLL specifies that the cursor cannot be used to retrieve rows in\na nonsequential fashion. The default is to allow scrolling in some\ncases; this is not the same as specifying SCROLL. See Notes below\nfor details.\n\nWITH HOLD\nWITHOUT HOLD\nWITH HOLD specifies that the cursor can continue to be used after\nthe transaction that created it successfully commits.  WITHOUT HOLD\nspecifies that the cursor cannot be used outside of the transaction\nthat created it. If neither WITHOUT HOLD nor WITH HOLD is\nspecified, WITHOUT HOLD is the default.\n\nquery\nA SELECT or VALUES command which will provide the rows to be\nreturned by the cursor.\n\nThe key words ASENSITIVE, BINARY, INSENSITIVE, and SCROLL can appear in\nany order.\n",
                "subsections": []
            },
            "NOTES": {
                "content": "Normal cursors return data in text format, the same as a SELECT would\nproduce. The BINARY option specifies that the cursor should return data\nin binary format. This reduces conversion effort for both the server\nand client, at the cost of more programmer effort to deal with\nplatform-dependent binary data formats. As an example, if a query\nreturns a value of one from an integer column, you would get a string\nof 1 with a default cursor, whereas with a binary cursor you would get\na 4-byte field containing the internal representation of the value (in\nbig-endian byte order).\n\nBinary cursors should be used carefully. Many applications, including\npsql, are not prepared to handle binary cursors and expect data to come\nback in the text format.\n\nNote\nWhen the client application uses the \"extended query\" protocol to\nissue a FETCH command, the Bind protocol message specifies whether\ndata is to be retrieved in text or binary format. This choice\noverrides the way that the cursor is defined. The concept of a\nbinary cursor as such is thus obsolete when using extended query\nprotocol -- any cursor can be treated as either text or binary.\n\nUnless WITH HOLD is specified, the cursor created by this command can\nonly be used within the current transaction. Thus, DECLARE without WITH\nHOLD is useless outside a transaction block: the cursor would survive\nonly to the completion of the statement. Therefore PostgreSQL reports\nan error if such a command is used outside a transaction block. Use\nBEGIN and COMMIT (or ROLLBACK) to define a transaction block.\n\nIf WITH HOLD is specified and the transaction that created the cursor\nsuccessfully commits, the cursor can continue to be accessed by\nsubsequent transactions in the same session. (But if the creating\ntransaction is aborted, the cursor is removed.) A cursor created with\nWITH HOLD is closed when an explicit CLOSE command is issued on it, or\nthe session ends. In the current implementation, the rows represented\nby a held cursor are copied into a temporary file or memory area so\nthat they remain available for subsequent transactions.\n\nWITH HOLD may not be specified when the query includes FOR UPDATE or\nFOR SHARE.\n\nThe SCROLL option should be specified when defining a cursor that will\nbe used to fetch backwards. This is required by the SQL standard.\nHowever, for compatibility with earlier versions, PostgreSQL will allow\nbackward fetches without SCROLL, if the cursor's query plan is simple\nenough that no extra overhead is needed to support it. However,\napplication developers are advised not to rely on using backward\nfetches from a cursor that has not been created with SCROLL. If NO\nSCROLL is specified, then backward fetches are disallowed in any case.\n\nBackward fetches are also disallowed when the query includes FOR UPDATE\nor FOR SHARE; therefore SCROLL may not be specified in this case.\n\nCaution\nScrollable cursors may give unexpected results if they invoke any\nvolatile functions (see Section 38.7). When a previously fetched\nrow is re-fetched, the functions might be re-executed, perhaps\nleading to results different from the first time. It's best to\nspecify NO SCROLL for a query involving volatile functions. If that\nis not practical, one workaround is to declare the cursor SCROLL\nWITH HOLD and commit the transaction before reading any rows from\nit. This will force the entire output of the cursor to be\nmaterialized in temporary storage, so that volatile functions are\nexecuted exactly once for each row.\n\nIf the cursor's query includes FOR UPDATE or FOR SHARE, then returned\nrows are locked at the time they are first fetched, in the same way as\nfor a regular SELECT command with these options. In addition, the\nreturned rows will be the most up-to-date versions.\n\nCaution\nIt is generally recommended to use FOR UPDATE if the cursor is\nintended to be used with UPDATE ... WHERE CURRENT OF or DELETE ...\nWHERE CURRENT OF. Using FOR UPDATE prevents other sessions from\nchanging the rows between the time they are fetched and the time\nthey are updated. Without FOR UPDATE, a subsequent WHERE CURRENT OF\ncommand will have no effect if the row was changed since the cursor\nwas created.\n\nAnother reason to use FOR UPDATE is that without it, a subsequent\nWHERE CURRENT OF might fail if the cursor query does not meet the\nSQL standard's rules for being \"simply updatable\" (in particular,\nthe cursor must reference just one table and not use grouping or\nORDER BY). Cursors that are not simply updatable might work, or\nmight not, depending on plan choice details; so in the worst case,\nan application might work in testing and then fail in production.\nIf FOR UPDATE is specified, the cursor is guaranteed to be\nupdatable.\n\nThe main reason not to use FOR UPDATE with WHERE CURRENT OF is if\nyou need the cursor to be scrollable, or to be isolated from\nconcurrent updates (that is, continue to show the old data). If\nthis is a requirement, pay close heed to the caveats shown above.\n\nThe SQL standard only makes provisions for cursors in embedded SQL. The\nPostgreSQL server does not implement an OPEN statement for cursors; a\ncursor is considered to be open when it is declared. However, ECPG, the\nembedded SQL preprocessor for PostgreSQL, supports the standard SQL\ncursor conventions, including those involving DECLARE and OPEN\nstatements.\n\nYou can see all available cursors by querying the pgcursors system\nview.\n",
                "subsections": []
            },
            "EXAMPLES": {
                "content": "To declare a cursor:\n\nDECLARE liahona CURSOR FOR SELECT * FROM films;\n\nSee FETCH(7) for more examples of cursor usage.\n",
                "subsections": []
            },
            "COMPATIBILITY": {
                "content": "The SQL standard allows cursors only in embedded SQL and in modules.\nPostgreSQL permits cursors to be used interactively.\n\nAccording to the SQL standard, changes made to insensitive cursors by\nUPDATE ... WHERE CURRENT OF and DELETE ... WHERE CURRENT OF statements\nare visible in that same cursor.  PostgreSQL treats these statements\nlike all other data changing statements in that they are not visible in\ninsensitive cursors.\n\nBinary cursors are a PostgreSQL extension.\n",
                "subsections": []
            },
            "SEE ALSO": {
                "content": "CLOSE(7), FETCH(7), MOVE(7)\n\nPostgreSQL 14.23                     2026                           DECLARE(7)",
                "subsections": []
            }
        }
    }
}