fetch.sgml 8.88 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14
<REFENTRY ID="SQL-FETCH">
<REFMETA>
<REFENTRYTITLE>
FETCH
</REFENTRYTITLE>
<REFMISCINFO>SQL - Language Statements</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>
FETCH
</REFNAME>
<REFPURPOSE>
Gets rows using a cursor
</REFPURPOSE>
15
</refnamediv>
16 17 18 19 20
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1998-09-01</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
21 22
FETCH [ <REPLACEABLE CLASS="PARAMETER">selector</REPLACEABLE> ] [ <REPLACEABLE CLASS="PARAMETER">count</REPLACEABLE> ] 
    { IN | FROM } <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE>
23 24
FETCH [ RELATIVE ] [ { [ <REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE> | ALL | NEXT | PRIOR ] } ]
    FROM ] <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE>
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
</SYNOPSIS>

<REFSECT2 ID="R2-SQL-FETCH-1">
<REFSECT2INFO>
<DATE>1998-09-01</DATE>
</REFSECT2INFO>
<TITLE>
Inputs
</TITLE>
<PARA>

<VARIABLELIST>
<VARLISTENTRY>
<TERM>
<REPLACEABLE CLASS="PARAMETER">selector</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
43 44
<REPLACEABLE CLASS="PARAMETER">selector</REPLACEABLE>
defines the fetch direction. It can be one
45
         the following:
46 47 48 49
</para>
</listitem>
</varlistentry>
</variablelist>
50 51 52 53 54 55 56
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
FORWARD
</TERM>
<LISTITEM>
<PARA>
57 58
fetch next row(s). This is the default
 if <REPLACEABLE CLASS="PARAMETER">selector</REPLACEABLE> is omitted.
59 60 61
</para>
</listitem>
</varlistentry>
62 63 64 65 66 67 68
<VARLISTENTRY>
<TERM>
BACKWARD
</TERM>
<LISTITEM>
<PARA>
fetch previous row(s).
69 70 71
</para>
</listitem>
</varlistentry>
72 73 74 75 76 77 78
<VARLISTENTRY>
<TERM>
RELATIVE
</TERM>
<LISTITEM>
<PARA>
Noise word for SQL92 compatibility.
79 80 81
</para>
</listitem>
</varlistentry>
82

83

84 85 86 87 88 89 90 91
<VARLISTENTRY>
<TERM>
<REPLACEABLE CLASS="PARAMETER">count</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
<REPLACEABLE CLASS="PARAMETER">count</REPLACEABLE>
determines how many rows to fetch. It can be one of the following:
92 93 94 95
</para>
</listitem>
</varlistentry>

96

97 98 99 100 101 102
<VARLISTENTRY>
<TERM>
<REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
103 104 105
A signed integer that specify how many rows to fetch.
Note that a negative integer is equivalent to changing the sense of
FORWARD and BACKWARD.
106 107 108
</para>
</listitem>
</varlistentry>
109 110 111 112 113 114 115 116

<VARLISTENTRY>
<TERM>
ALL
</TERM>
<LISTITEM>
<PARA>
Retrieve all remaining rows.
117 118 119
</para>
</listitem>
</varlistentry>
120

121 122 123 124 125 126 127
<VARLISTENTRY>
<TERM>
NEXT
</TERM>
<LISTITEM>
<PARA>
Equivalent to specifying a count of <command>1</command>.
128 129 130
</para>
</listitem>
</varlistentry>
131 132 133 134 135 136 137 138

<VARLISTENTRY>
<TERM>
PRIOR
</TERM>
<LISTITEM>
<PARA>
Equivalent to specifying a count of <command>-1</command>.
139 140 141
</para>
</listitem>
</varlistentry>
142

143 144 145 146 147 148 149
<VARLISTENTRY>
<TERM>
<REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
An open cursor's name.
150 151 152
</para>
</listitem>
</varlistentry>
153
</variablelist>
154
</para>
155 156 157 158 159 160 161 162 163 164 165 166
</REFSECT2>

<REFSECT2 ID="R2-SQL-FETCH-2">
<REFSECT2INFO>
<DATE>1998-04-15</DATE>
</REFSECT2INFO>
<TITLE>
Outputs
</TITLE>
<PARA>
FETCH returns the results of the query defined by the specified cursor.
The following messages will be returned if the query fails:
167
</para>
168 169 170 171 172 173 174 175

<VARIABLELIST>
<VARLISTENTRY>
<TERM>
NOTICE:  PerformPortalFetch: portal "<REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE>" not found
</TERM>
<LISTITEM>
<PARA>
176 177
If <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE>
 is not previously declared.
178
The cursor must be declared within a transaction block.
179 180 181
</para>
</listitem>
</varlistentry>
182

183 184 185 186 187 188 189 190
<VARLISTENTRY>
<TERM>
NOTICE:  FETCH/ABSOLUTE not supported, using RELATIVE
</TERM>
<LISTITEM>
<PARA>
<productname>Postgres</productname> does not support absolute
positioning of cursors.
191 192 193
</para>
</listitem>
</varlistentry>
194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212

<VARLISTENTRY>
<TERM>
ERROR:  FETCH/RELATIVE at current position is not supported
</TERM>
<LISTITEM>
<PARA>
<acronym>SQL92</acronym> allows one to repetatively retrieve the cursor
at its "current position" using the syntax
<programlisting>
FETCH RELATIVE 0 FROM <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE>
</programlisting>

<productname>Postgres</productname> does not currently support
this notion; in fact the value zero is reserved to indicate that
all rows should be retrieved and is equivalent to specifying the ALL keyword.
If the RELATIVE keyword has been used, the <productname>Postgres</productname> 
assumes that the user intended <acronym>SQL92</acronym> behavior
and returns this error message.
213 214 215
</para>
</listitem>
</varlistentry>
216

217 218 219 220 221 222 223 224 225 226 227 228 229 230
</variablelist>

</REFSECT2>
</REFSYNOPSISDIV>

<REFSECT1 ID="R1-SQL-FETCH-1">
<REFSECT1INFO>
<DATE>1998-04-15</DATE>
</REFSECT1INFO>
<TITLE>
Description
</TITLE>
<PARA>
   FETCH allows a user to retrieve rows using a cursor.
231 232
   The number of rows retrieved is specified by
 <REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE>.
233
   If the number of rows remaining in the cursor is less
234 235
   than <REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE>,
 then only those available are fetched.
236 237
   Substituting the keyword ALL in place of a number will
   cause all remaining rows in the cursor to be retrieved.
238 239 240 241 242 243 244 245 246
   Instances may be fetched in both FORWARD and BACKWARD
   directions. The default direction is FORWARD.

<tip>
<para>
Negative numbers are now allowed to be specified for the
row count. A negative number is equivalent to reversing
the sense of the FORWARD and BACKWARD keywords. For example,
<command>FORWARD -1</command> is the same as <command>BACKWARD 1</command>.
247
</para>
248
</tip>
249
</para>
250 251 252 253 254 255
<para>
Note that the FORWARD and BACKWARD keywords are
 <productname>Postgres</productname> extensions.
The <acronym>SQL92</acronym> syntax is also supported, specified
in the second form of the command. See below for details
on compatibility issues.
256
</para>
257 258 259
<para>
   Once all rows are fetched, every other fetch access returns
   no rows.
260
</para>
261 262 263 264 265

<para>
   Updating data in a cursor is not supported by 
<productname>Postgres</productname>,
   because mapping cursor updates back to base tables is
266 267 268
not generally possible, as is also the case with VIEW updates.
 Consequently,
   users must issue explicit UPDATE commands to replace data.
269
</para>
270 271 272 273

<para>
   Cursors may only be used inside of transactions because
   the data that they store spans multiple user queries.
274
</para>
275 276 277 278 279 280 281 282 283 284 285 286 287

<REFSECT2 ID="R2-SQL-FETCH-3">
<REFSECT2INFO>
<DATE>1998-04-15</DATE>
</REFSECT2INFO>
<TITLE>
Notes
</TITLE>
<PARA>
   Refer to MOVE statements to change cursor position.
   Refer to DECLARE statements to declare a cursor.
   Refer to BEGIN WORK, COMMIT WORK, ROLLBACK WORK statements
     for further information about transactions.
288
</para>
289
</REFSECT2>
290
</refsect1>
291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329

<REFSECT1 ID="R1-SQL-FETCH-2">
<TITLE>
Usage
</TITLE>
<PARA>
<ProgramListing>
   --set up and use a cursor:
   --
   BEGIN WORK;
     DECLARE liahona CURSOR
        FOR SELECT * FROM films;

   --Fetch first 5 rows in the cursor liahona:
   --
     FETCH FORWARD 5 IN liahona;

     code |title                  |did| date_prod|kind      |len
     -----+-----------------------+---+----------+----------+------
     BL101|The Third Man          |101|1949-12-23|Drama     | 01:44
     BL102|The African Queen      |101|1951-08-11|Romantic  | 01:43
     JL201|Une Femme est une Femme|102|1961-03-12|Romantic  | 01:25
     P_301|Vertigo                |103|1958-11-14|Action    | 02:08
     P_302|Becket                 |103|1964-02-03|Drama     | 02:28
 

   --Fetch previous row:
   --
     FETCH BACKWARD 1 IN liahona;

     code |title                  |did| date_prod|kind      |len
     -----+-----------------------+---+----------+----------+------
     P_301|Vertigo                |103|1958-11-14|Action    | 02:08

   -- close the cursor and commit work:
   --
     CLOSE liahona;
   COMMIT WORK;
</ProgramListing>
330
</para>        
331 332 333 334 335 336 337
</REFSECT1>

<REFSECT1 ID="R1-SQL-FETCH-3">
<TITLE>
Compatibility
</TITLE>
<PARA>
338 339 340
The non-embedded use of cursors is a <productname>Postgres</productname>
extension. The syntax and usage of cursors is being compared
against the embedded form of cursors defined in <acronym>SQL92</acronym>.
341 342 343 344 345 346 347 348 349 350
</PARA>

<REFSECT2 ID="R2-SQL-FETCH-4">
<REFSECT2INFO>
<DATE>1998-09-01</DATE>
</REFSECT2INFO>
<TITLE>
SQL92
</TITLE>
<PARA>
351
<acronym>SQL92</acronym> allows absolute positioning of the cursor for
352
FETCH, and allows placing the results into explicit variables.
353 354

<synopsis>
355 356
FETCH ABSOLUTE <REPLACEABLE CLASS="PARAMETER">#</REPLACEABLE>
    FROM <REPLACEABLE CLASS="PARAMETER">cursor</REPLACEABLE>
357 358 359 360 361 362
    INTO :<REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> [, ...]
</synopsis>

<VARIABLELIST>
<VARLISTENTRY>
<TERM>
363
ABSOLUTE
364 365 366
</TERM>
<LISTITEM>
<PARA>
367 368 369
The cursor should be positioned to the specified absolute
row number. All row numbers in <productname>Postgres</productname>
are relative numbers so this capability is not supported.
370 371 372
</para>
</listitem>
</varlistentry>
373 374 375 376 377 378 379
<VARLISTENTRY>
<TERM>
:<REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
Target host variable(s).
380 381 382
</para>
</listitem>
</varlistentry>
383
</variablelist>
384 385 386
</para>
</refsect2>
</refsect1>
387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407
</REFENTRY>

<!--
<REPLACEABLE CLASS="PARAMETER">
</REPLACEABLE>
<ReturnValue></ReturnValue>
<PARA>
</PARA>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>&bull;
</TERM>
<LISTITEM>
<PARA>
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
<PARA>
</PARA>
-->