1DECLARE(7)                       SQL Commands                       DECLARE(7)
2
3
4

NAME

6       DECLARE - define a cursor
7
8

SYNOPSIS

10       DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
11           CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
12
13

DESCRIPTION

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
19              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
23

PARAMETERS

25       name   The name of the cursor to be created.
26
27       BINARY Causes  the  cursor to return data in binary rather than in text
28              format.
29
30       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
37       SCROLL
38
39       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
49       WITH HOLD
50
51       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
58       query  A  SELECT  [select(7)]  or VALUES [values(7)] command which will
59              provide the rows to be returned by the cursor.
60
61       The key words BINARY, INSENSITIVE, and SCROLL can appear in any order.
62

NOTES

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
74       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
78              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
86
87       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
95       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
104       WITH  HOLD  may  not be specified when the query includes FOR UPDATE or
105       FOR SHARE.
106
107       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
116       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
119              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
129
130       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
138              Caution:
139
140              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
148              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
157              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
163
164       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
171       You can see all available cursors by  querying  the  pg_cursors  system
172       view.
173

EXAMPLES

175       To declare a cursor:
176
177       DECLARE liahona CURSOR FOR SELECT * FROM films;
178
179       See FETCH [fetch(7)] for more examples of cursor usage.
180

COMPATIBILITY

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
188       The  SQL  standard  allows cursors only in embedded SQL and in modules.
189       PostgreSQL permits cursors to be used interactively.
190
191       Binary cursors are a PostgreSQL extension.
192

SEE ALSO

194       CLOSE [close(7)], FETCH [fetch(7)], MOVE [move(7)]
195
196
197
198SQL - Language Statements         2014-02-17                        DECLARE(7)
Impressum