1DECLARE(7) SQL Commands DECLARE(7)
2
6 DECLARE - define a cursor
7
10 DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
11 CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
12
15 DECLARE allows a user to create cursors, which can be used to retrieve
16 a small number of rows at a time out of a larger query. After the cur‐
17 sor is created, rows are fetched from it using FETCH [fetch(7)].
18 Note: This page describes usage of cursors at the SQL command
20 level. If you are trying to use cursors inside a PL/pgSQL func‐
21 tion, the rules are different — see in the documentation.
22
25 name The name of the cursor to be created.
26 BINARY Causes the cursor to return data in binary rather than in text
28 format.
29 INSENSITIVE
31 Indicates that data retrieved from the cursor should be unaf‐
32 fected by updates to the table(s) underlying the cursor that
33 occur after the cursor is created. In PostgreSQL, this is the
34 default behavior; so this key word has no effect and is only
35 accepted for compatibility with the SQL standard.
36 SCROLL
38 NO SCROLL
40 SCROLL specifies that the cursor can be used to retrieve rows in
41 a nonsequential fashion (e.g., backward). Depending upon the
42 complexity of the query's execution plan, specifying SCROLL
43 might impose a performance penalty on the query's execution
44 time. NO SCROLL specifies that the cursor cannot be used to
45 retrieve rows in a nonsequential fashion. The default is to
46 allow scrolling in some cases; this is not the same as specify‐
47 ing SCROLL. See Notes [declare(7)] for details.
48 WITH HOLD
50 WITHOUT HOLD
52 WITH HOLD specifies that the cursor can continue to be used
53 after the transaction that created it successfully commits.
54 WITHOUT HOLD specifies that the cursor cannot be used outside of
55 the transaction that created it. If neither WITHOUT HOLD nor
56 WITH HOLD is specified, WITHOUT HOLD is the default.
57 query A SELECT [select(7)] or VALUES [values(7)] command which will
59 provide the rows to be returned by the cursor.
60 The key words BINARY, INSENSITIVE, and SCROLL can appear in any order.
62
64 Normal cursors return data in text format, the same as a SELECT would
65 produce. The BINARY option specifies that the cursor should return data
66 in binary format. This reduces conversion effort for both the server
67 and client, at the cost of more programmer effort to deal with plat‐
68 form-dependent binary data formats. As an example, if a query returns
69 a value of one from an integer column, you would get a string of 1 with
70 a default cursor, whereas with a binary cursor you would get a 4-byte
71 field containing the internal representation of the value (in big-
72 endian byte order).
73 Binary cursors should be used carefully. Many applications, including
75 psql, are not prepared to handle binary cursors and expect data to come
76 back in the text format.
77 Note: When the client application uses the ``extended query''
79 protocol to issue a FETCH command, the Bind protocol message
80 specifies whether data is to be retrieved in text or binary for‐
81 mat. This choice overrides the way that the cursor is defined.
82 The concept of a binary cursor as such is thus obsolete when
83 using extended query protocol — any cursor can be treated as
84 either text or binary.
85 Unless WITH HOLD is specified, the cursor created by this command can
88 only be used within the current transaction. Thus, DECLARE without WITH
89 HOLD is useless outside a transaction block: the cursor would survive
90 only to the completion of the statement. Therefore PostgreSQL reports
91 an error if such a command is used outside a transaction block. Use
92 BEGIN [begin(7)] and COMMIT [commit(7)] (or ROLLBACK [rollback(7)]) to
93 define a transaction block.
94 If WITH HOLD is specified and the transaction that created the cursor
96 successfully commits, the cursor can continue to be accessed by subse‐
97 quent transactions in the same session. (But if the creating transac‐
98 tion is aborted, the cursor is removed.) A cursor created with WITH
99 HOLD is closed when an explicit CLOSE command is issued on it, or the
100 session ends. In the current implementation, the rows represented by a
101 held cursor are copied into a temporary file or memory area so that
102 they remain available for subsequent transactions.
103 WITH HOLD may not be specified when the query includes FOR UPDATE or
105 FOR SHARE.
106 The SCROLL option should be specified when defining a cursor that will
108 be used to fetch backwards. This is required by the SQL standard. How‐
109 ever, for compatibility with earlier versions, PostgreSQL will allow
110 backward fetches without SCROLL, if the cursor's query plan is simple
111 enough that no extra overhead is needed to support it. However, appli‐
112 cation developers are advised not to rely on using backward fetches
113 from a cursor that has not been created with SCROLL. If NO SCROLL is
114 specified, then backward fetches are disallowed in any case.
115 Backward fetches are also disallowed when the query includes FOR UPDATE
117 or FOR SHARE; therefore SCROLL may not be specified in this case.
118 Caution: Scrollable and WITH HOLD cursors may give unexpected
120 results if they invoke any volatile functions (see in the docu‐
121 mentation). When a previously fetched row is re-fetched, the
122 functions might be re-executed, perhaps leading to results dif‐
123 ferent from the first time. One workaround for such cases is to
124 declare the cursor WITH HOLD and commit the transaction before
125 reading any rows from it. This will force the entire output of
126 the cursor to be materialized in temporary storage, so that
127 volatile functions are executed exactly once for each row.
128 If the cursor's query includes FOR UPDATE or FOR SHARE, then returned
131 rows are locked at the time they are first fetched, in the same way as
132 for a regular SELECT [select(7)] command with these options. In addi‐
133 tion, the returned rows will be the most up-to-date versions; therefore
134 these options provide the equivalent of what the SQL standard calls a
135 ``sensitive cursor''. (Specifying INSENSITIVE together with FOR UPDATE
136 or FOR SHARE is an error.)
137 Caution:
139 It is generally recommended to use FOR UPDATE if the cursor is
141 intended to be used with UPDATE ... WHERE CURRENT OF or DELETE
142 ... WHERE CURRENT OF. Using FOR UPDATE prevents other sessions
143 from changing the rows between the time they are fetched and the
144 time they are updated. Without FOR UPDATE, a subsequent WHERE
145 CURRENT OF command will have no effect if the row was changed
146 since the cursor was created.
147 Another reason to use FOR UPDATE is that without it, a subse‐
149 quent WHERE CURRENT OF might fail if the cursor query does not
150 meet the SQL standard's rules for being ``simply updatable'' (in
151 particular, the cursor must reference just one table and not use
152 grouping or ORDER BY). Cursors that are not simply updatable
153 might work, or might not, depending on plan choice details; so
154 in the worst case, an application might work in testing and then
155 fail in production.
156 The main reason not to use FOR UPDATE with WHERE CURRENT OF is
158 if you need the cursor to be scrollable, or to be insensitive to
159 the subsequent updates (that is, continue to show the old data).
160 If this is a requirement, pay close heed to the caveats shown
161 above.
162 The SQL standard only makes provisions for cursors in embedded SQL. The
165 PostgreSQL server does not implement an OPEN statement for cursors; a
166 cursor is considered to be open when it is declared. However, ECPG,
167 the embedded SQL preprocessor for PostgreSQL, supports the standard SQL
168 cursor conventions, including those involving DECLARE and OPEN state‐
169 ments.
170 You can see all available cursors by querying the pg_cursors system
172 view.
173
175 To declare a cursor:
176 DECLARE liahona CURSOR FOR SELECT * FROM films;
178 See FETCH [fetch(7)] for more examples of cursor usage.
180
182 The SQL standard says that it is implementation-dependent whether cur‐
183 sors are sensitive to concurrent updates of the underlying data by
184 default. In PostgreSQL, cursors are insensitive by default, and can be
185 made sensitive by specifying FOR UPDATE. Other products may work dif‐
186 ferently.
187 The SQL standard allows cursors only in embedded SQL and in modules.
189 PostgreSQL permits cursors to be used interactively.
190 Binary cursors are a PostgreSQL extension.
192
194 CLOSE [close(7)], FETCH [fetch(7)], MOVE [move(7)]
195SQL - Language Statements 2014-02-17 DECLARE(7)