codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>FETCH</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-explain.html" title="EXPLAIN" /><link rel="next" href="sql-grant.html" title="GRANT" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">FETCH</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-explain.html" title="EXPLAIN">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-grant.html" title="GRANT">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-FETCH"><div class="titlepage"></div><a id="id-1.9.3.149.1" class="indexterm"></a><a id="id-1.9.3.149.2" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">FETCH</span></h2><p>FETCH — retrieve rows from a query using a cursor</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3FETCH [ <em class="replaceable"><code>direction</code></em> ] [ FROM | IN ] <em class="replaceable"><code>cursor_name</code></em>4 5<span class="phrase">where <em class="replaceable"><code>direction</code></em> can be one of:</span>6 7    NEXT8    PRIOR9    FIRST10    LAST11    ABSOLUTE <em class="replaceable"><code>count</code></em>12    RELATIVE <em class="replaceable"><code>count</code></em>13    <em class="replaceable"><code>count</code></em>14    ALL15    FORWARD16    FORWARD <em class="replaceable"><code>count</code></em>17    FORWARD ALL18    BACKWARD19    BACKWARD <em class="replaceable"><code>count</code></em>20    BACKWARD ALL21</pre></div><div class="refsect1" id="id-1.9.3.149.6"><h2>Description</h2><p>22   <code class="command">FETCH</code> retrieves rows using a previously-created cursor.23  </p><p>24   A cursor has an associated position, which is used by25   <code class="command">FETCH</code>.  The cursor position can be before the first row of the26   query result, on any particular row of the result, or after the last row27   of the result.  When created, a cursor is positioned before the first row.28   After fetching some rows, the cursor is positioned on the row most recently29   retrieved.  If <code class="command">FETCH</code> runs off the end of the available rows30   then the cursor is left positioned after the last row, or before the first31   row if fetching backward.  <code class="command">FETCH ALL</code> or <code class="command">FETCH BACKWARD32   ALL</code> will always leave the cursor positioned after the last row or before33   the first row.34  </p><p>35   The forms <code class="literal">NEXT</code>, <code class="literal">PRIOR</code>, <code class="literal">FIRST</code>,36   <code class="literal">LAST</code>, <code class="literal">ABSOLUTE</code>, <code class="literal">RELATIVE</code> fetch37   a single row after moving the cursor appropriately.  If there is no38   such row, an empty result is returned, and the cursor is left39   positioned before the first row or after the last row as40   appropriate.41  </p><p>42   The forms using <code class="literal">FORWARD</code> and <code class="literal">BACKWARD</code>43   retrieve the indicated number of rows moving in the forward or44   backward direction, leaving the cursor positioned on the45   last-returned row (or after/before all rows, if the <em class="replaceable"><code>count</code></em> exceeds the number of rows46   available).47  </p><p>48   <code class="literal">RELATIVE 0</code>, <code class="literal">FORWARD 0</code>, and49   <code class="literal">BACKWARD 0</code> all request fetching the current row without50   moving the cursor, that is, re-fetching the most recently fetched51   row.  This will succeed unless the cursor is positioned before the52   first row or after the last row; in which case, no row is returned.53  </p><div class="note"><h3 class="title">Note</h3><p>54    This page describes usage of cursors at the SQL command level.55    If you are trying to use cursors inside a <span class="application">PL/pgSQL</span>56    function, the rules are different —57    see <a class="xref" href="plpgsql-cursors.html#PLPGSQL-CURSOR-USING" title="43.7.3. Using Cursors">Section 43.7.3</a>.58   </p></div></div><div class="refsect1" id="id-1.9.3.149.7"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>direction</code></em></span></dt><dd><p><em class="replaceable"><code>direction</code></em> defines59      the fetch direction and number of rows to fetch.  It can be one60      of the following:61 62      </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">NEXT</code></span></dt><dd><p>63          Fetch the next row. This is the default if <em class="replaceable"><code>direction</code></em> is omitted.64         </p></dd><dt><span class="term"><code class="literal">PRIOR</code></span></dt><dd><p>65          Fetch the prior row.66         </p></dd><dt><span class="term"><code class="literal">FIRST</code></span></dt><dd><p>67          Fetch the first row of the query (same as <code class="literal">ABSOLUTE 1</code>).68         </p></dd><dt><span class="term"><code class="literal">LAST</code></span></dt><dd><p>69          Fetch the last row of the query (same as <code class="literal">ABSOLUTE -1</code>).70         </p></dd><dt><span class="term"><code class="literal">ABSOLUTE <em class="replaceable"><code>count</code></em></code></span></dt><dd><p>71          Fetch the <em class="replaceable"><code>count</code></em>'th row of the query,72          or the <code class="literal">abs(<em class="replaceable"><code>count</code></em>)</code>'th row from73          the end if <em class="replaceable"><code>count</code></em> is negative.  Position74          before first row or after last row if <em class="replaceable"><code>count</code></em> is out of range; in75          particular, <code class="literal">ABSOLUTE 0</code> positions before76          the first row.77         </p></dd><dt><span class="term"><code class="literal">RELATIVE <em class="replaceable"><code>count</code></em></code></span></dt><dd><p>78          Fetch the <em class="replaceable"><code>count</code></em>'th succeeding row, or79          the <code class="literal">abs(<em class="replaceable"><code>count</code></em>)</code>'th prior80          row if <em class="replaceable"><code>count</code></em> is81          negative.  <code class="literal">RELATIVE 0</code> re-fetches the82          current row, if any.83         </p></dd><dt><span class="term"><em class="replaceable"><code>count</code></em></span></dt><dd><p>84          Fetch the next <em class="replaceable"><code>count</code></em> rows (same as85          <code class="literal">FORWARD <em class="replaceable"><code>count</code></em></code>).86         </p></dd><dt><span class="term"><code class="literal">ALL</code></span></dt><dd><p>87          Fetch all remaining rows (same as <code class="literal">FORWARD ALL</code>).88         </p></dd><dt><span class="term"><code class="literal">FORWARD</code></span></dt><dd><p>89          Fetch the next row (same as <code class="literal">NEXT</code>).90         </p></dd><dt><span class="term"><code class="literal">FORWARD <em class="replaceable"><code>count</code></em></code></span></dt><dd><p>91          Fetch the next <em class="replaceable"><code>count</code></em> rows.92          <code class="literal">FORWARD 0</code> re-fetches the current row.93         </p></dd><dt><span class="term"><code class="literal">FORWARD ALL</code></span></dt><dd><p>94          Fetch all remaining rows.95         </p></dd><dt><span class="term"><code class="literal">BACKWARD</code></span></dt><dd><p>96          Fetch the prior row (same as <code class="literal">PRIOR</code>).97         </p></dd><dt><span class="term"><code class="literal">BACKWARD <em class="replaceable"><code>count</code></em></code></span></dt><dd><p>98          Fetch the prior <em class="replaceable"><code>count</code></em> rows (scanning99          backwards).  <code class="literal">BACKWARD 0</code> re-fetches the100          current row.101         </p></dd><dt><span class="term"><code class="literal">BACKWARD ALL</code></span></dt><dd><p>102          Fetch all prior rows (scanning backwards).103         </p></dd></dl></div></dd><dt><span class="term"><em class="replaceable"><code>count</code></em></span></dt><dd><p><em class="replaceable"><code>count</code></em> is a104      possibly-signed integer constant, determining the location or105      number of rows to fetch.  For <code class="literal">FORWARD</code> and106      <code class="literal">BACKWARD</code> cases, specifying a negative <em class="replaceable"><code>count</code></em> is equivalent to changing107      the sense of <code class="literal">FORWARD</code> and <code class="literal">BACKWARD</code>.108     </p></dd><dt><span class="term"><em class="replaceable"><code>cursor_name</code></em></span></dt><dd><p>109      An open cursor's name.110     </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.149.8"><h2>Outputs</h2><p>111   On successful completion, a <code class="command">FETCH</code> command returns a command112   tag of the form113</p><pre class="screen">114FETCH <em class="replaceable"><code>count</code></em>115</pre><p>116   The <em class="replaceable"><code>count</code></em> is the number117   of rows fetched (possibly zero).  Note that in118   <span class="application">psql</span>, the command tag will not actually be119   displayed, since <span class="application">psql</span> displays the fetched120   rows instead.121  </p></div><div class="refsect1" id="id-1.9.3.149.9"><h2>Notes</h2><p>122   The cursor should be declared with the <code class="literal">SCROLL</code>123   option if one intends to use any variants of <code class="command">FETCH</code>124   other than <code class="command">FETCH NEXT</code> or <code class="command">FETCH FORWARD</code> with125   a positive count.  For simple queries126   <span class="productname">PostgreSQL</span> will allow backwards fetch127   from cursors not declared with <code class="literal">SCROLL</code>, but this128   behavior is best not relied on. If the cursor is declared with129   <code class="literal">NO SCROLL</code>, no backward fetches are allowed.130  </p><p>131   <code class="literal">ABSOLUTE</code> fetches are not any faster than132   navigating to the desired row with a relative move: the underlying133   implementation must traverse all the intermediate rows anyway.134   Negative absolute fetches are even worse: the query must be read to135   the end to find the last row, and then traversed backward from136   there.  However, rewinding to the start of the query (as with137   <code class="literal">FETCH ABSOLUTE 0</code>) is fast.138  </p><p>139   <a class="link" href="sql-declare.html" title="DECLARE"><code class="command">DECLARE</code></a>140   is used to define a cursor.  Use141   <a class="link" href="sql-move.html" title="MOVE"><code class="command">MOVE</code></a>142   to change cursor position without retrieving data.143  </p></div><div class="refsect1" id="id-1.9.3.149.10"><h2>Examples</h2><p>144   The following example traverses a table using a cursor:145 146</p><pre class="programlisting">147BEGIN WORK;148 149-- Set up a cursor:150DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;151 152-- Fetch the first 5 rows in the cursor liahona:153FETCH FORWARD 5 FROM liahona;154 155 code  |          title          | did | date_prod  |   kind   |  len156-------+-------------------------+-----+------------+----------+-------157 BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44158 BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43159 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25160 P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08161 P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28162 163-- Fetch the previous row:164FETCH PRIOR FROM liahona;165 166 code  |  title  | did | date_prod  |  kind  |  len167-------+---------+-----+------------+--------+-------168 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08169 170-- Close the cursor and end the transaction:171CLOSE liahona;172COMMIT WORK;173</pre></div><div class="refsect1" id="id-1.9.3.149.11"><h2>Compatibility</h2><p>174   The SQL standard defines <code class="command">FETCH</code> for use in175   embedded SQL only.  The variant of <code class="command">FETCH</code>176   described here returns the data as if it were a177   <code class="command">SELECT</code> result rather than placing it in host178   variables.  Other than this point, <code class="command">FETCH</code> is179   fully upward-compatible with the SQL standard.180  </p><p>181   The <code class="command">FETCH</code> forms involving182   <code class="literal">FORWARD</code> and <code class="literal">BACKWARD</code>, as well183   as the forms <code class="literal">FETCH <em class="replaceable"><code>count</code></em></code> and <code class="literal">FETCH184   ALL</code>, in which <code class="literal">FORWARD</code> is implicit, are185   <span class="productname">PostgreSQL</span> extensions.186  </p><p>187   The SQL standard allows only <code class="literal">FROM</code> preceding the cursor188   name; the option to use <code class="literal">IN</code>, or to leave them out altogether, is189   an extension.190  </p></div><div class="refsect1" id="id-1.9.3.149.12"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-close.html" title="CLOSE"><span class="refentrytitle">CLOSE</span></a>, <a class="xref" href="sql-declare.html" title="DECLARE"><span class="refentrytitle">DECLARE</span></a>, <a class="xref" href="sql-move.html" title="MOVE"><span class="refentrytitle">MOVE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-explain.html" title="EXPLAIN">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-grant.html" title="GRANT">Next</a></td></tr><tr><td width="40%" align="left" valign="top">EXPLAIN </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> GRANT</td></tr></table></div></body></html>