~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

FETCH\&. The cursor position can be before the first row of the query result, on any particular row of the result, or after the last row of the result\&. When created, a cursor is positioned before the first row\&. After fetching some rows, the cursor is positioned on the row most recently retrieved\&. If

FETCH

runs off the end of the available rows then the cursor is left positioned after the last row, or before the first row if fetching backward\&.

FETCH ALL

FETCH BACKWARD ALL

will always leave the cursor positioned after the last row or before the first row\&.

.PP

The forms

NEXT,

PRIOR,

FIRST,

LAST,

ABSOLUTE,

RELATIVE

fetch a single row after moving the cursor appropriately\&. If there is no such row, an empty result is returned, and the cursor is left positioned before the first row or after the last row as appropriate\&.

.PP

The forms using

FORWARD

and

BACKWARD

retrieve the indicated number of rows moving in the forward or backward direction, leaving the cursor positioned on the last\-returned row (or after/before all rows, if the

\fIcount\fR

exceeds the number of rows available)\&.

.PP

RELATIVE 0,

FORWARD 0, and

BACKWARD 0

all request fetching the current row without moving the cursor, that is, re\-fetching the most recently fetched row\&. This will succeed unless the cursor is positioned before the first row or after the last row; in which case, no row is returned\&.

.if n \{\

.sp

.\}

.RS 4

.it 1 an-trap

.nr an-no-space-flag 1

.nr an-break-flag 1

.br

.ps +1

\fBNote\fR

.ps -1

.br

.PP

This page describes usage of cursors at the SQL command level\&. If you are trying to use cursors inside a

PL/pgSQL

function, the rules are different \(em see

Section 39.7, \(lqCursors\(rq, in the documentation\&.

.sp .5v

100

.RE

101

.SH "PARAMETERS"

102

.PP

103

\fIdirection\fR

104

.RS 4

105

\fIdirection\fR

106

defines the fetch direction and number of rows to fetch\&. It can be one of the following:

107

.PP

108

109

.RS 4

110

Fetch the next row\&. This is the default if

111

\fIdirection\fR

112

is omitted\&.

113

.RE

114

.PP

115

PRIOR

116

.RS 4

117

Fetch the prior row\&.

118

.RE

119

.PP

120

FIRST

121

.RS 4

122

Fetch the first row of the query (same as

123

ABSOLUTE 1)\&.

124

.RE

125

.PP

126

LAST

127

.RS 4

128

Fetch the last row of the query (same as

129

ABSOLUTE \-1)\&.

130

.RE

131

.PP

132

ABSOLUTE \fIcount\fR

133

.RS 4

134

Fetch the

135

\fIcount\fR\(aqth row of the query, or the

136

abs(\fIcount\fR)\(aqth row from the end if

137

\fIcount\fR

138

is negative\&. Position before first row or after last row if

139

\fIcount\fR

140

is out of range; in particular,

141

ABSOLUTE 0

142

positions before the first row\&.

143

.RE

144

.PP

145

RELATIVE \fIcount\fR

146

.RS 4

147

Fetch the

148

\fIcount\fR\(aqth succeeding row, or the

149

abs(\fIcount\fR)\(aqth prior row if

150

\fIcount\fR

151

is negative\&.

152

RELATIVE 0

153

re\-fetches the current row, if any\&.

154

.RE

155

.PP

156

\fIcount\fR

157

.RS 4

158

Fetch the next

159

\fIcount\fR

160

rows (same as

161

FORWARD \fIcount\fR)\&.

162

.RE

163

.PP

164

ALL

165

.RS 4

166

Fetch all remaining rows (same as

167

FORWARD ALL)\&.

168

.RE

169

.PP

170

FORWARD

171

.RS 4

172

Fetch the next row (same as

173

NEXT)\&.

174

.RE

175

.PP

176

FORWARD \fIcount\fR

177

.RS 4

178

Fetch the next

179

\fIcount\fR

180

rows\&.

181

FORWARD 0

182

re\-fetches the current row\&.

183

.RE

184

.PP

185

FORWARD ALL

186

.RS 4

187

Fetch all remaining rows\&.

188

.RE

189

.PP

190

BACKWARD

191

.RS 4

192

Fetch the prior row (same as

193

PRIOR)\&.

194

.RE

195

.PP

196

BACKWARD \fIcount\fR

197

.RS 4

198

Fetch the prior

199

\fIcount\fR

200

rows (scanning backwards)\&.

201

BACKWARD 0

202

re\-fetches the current row\&.

203

.RE

204

.PP

205

BACKWARD ALL

206

.RS 4

207

Fetch all prior rows (scanning backwards)\&.

208

.RE

209

.RE

210

.PP

211

\fIcount\fR

212

.RS 4

213

\fIcount\fR

214

is a possibly\-signed integer constant, determining the location or number of rows to fetch\&. For

215

FORWARD

216

and

217

BACKWARD

218

cases, specifying a negative

219

\fIcount\fR

220

is equivalent to changing the sense of

221

FORWARD

222

and

223

BACKWARD\&.

224

.RE

225

.PP

226

\fIcursor_name\fR

227

.RS 4

228

An open cursor\(aqs name\&.

229

.RE

230

.SH "OUTPUTS"

231

.PP

232

On successful completion, a

233

FETCH

234

command returns a command tag of the form

235

.sp

236

.if n \{\

237

.RS 4

238

.\}

239

.nf

240

FETCH \fIcount\fR

241

.fi

242

.if n \{\

243

.RE

244

.\}

245

.sp

246

The

247

\fIcount\fR

248

is the number of rows fetched (possibly zero)\&. Note that in

249

psql, the command tag will not actually be displayed, since

250

psql

251

displays the fetched rows instead\&.

252

.SH "NOTES"

253

.PP

254

The cursor should be declared with the

255

SCROLL

256

option if one intends to use any variants of

257

FETCH

258

other than

259

FETCH NEXT

260

261

FETCH FORWARD

262

with a positive count\&. For simple queries

263

PostgreSQL

264

will allow backwards fetch from cursors not declared with

265

SCROLL, but this behavior is best not relied on\&. If the cursor is declared with

266

NO SCROLL, no backward fetches are allowed\&.

267

.PP

268

ABSOLUTE

269

fetches are not any faster than navigating to the desired row with a relative move: the underlying implementation must traverse all the intermediate rows anyway\&. Negative absolute fetches are even worse: the query must be read to the end to find the last row, and then traversed backward from there\&. However, rewinding to the start of the query (as with

270

FETCH ABSOLUTE 0) is fast\&.

271

.PP

272

\fBDECLARE\fR(7)

273

is used to define a cursor\&. Use

274

\fBMOVE\fR(7)

275

to change cursor position without retrieving data\&.

276

.SH "EXAMPLES"

277

.PP

278

The following example traverses a table using a cursor:

279

.sp

280

.if n \{\

281

.RS 4

282

.\}

283

.nf

284

BEGIN WORK;

285

286

\-\- Set up a cursor:

287

DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;

288

289

\-\- Fetch the first 5 rows in the cursor liahona:

290

FETCH FORWARD 5 FROM liahona;

291

292

293

\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-

294

BL101 | The Third Man | 101 | 1949\-12\-23 | Drama | 01:44

295

BL102 | The African Queen | 101 | 1951\-08\-11 | Romantic | 01:43

296

JL201 | Une Femme est une Femme | 102 | 1961\-03\-12 | Romantic | 01:25

297

P_301 | Vertigo | 103 | 1958\-11\-14 | Action | 02:08

298

P_302 | Becket | 103 | 1964\-02\-03 | Drama | 02:28

299

300

\-\- Fetch the previous row:

301

FETCH PRIOR FROM liahona;

302

303

304

\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-+\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-

305

P_301 | Vertigo | 103 | 1958\-11\-14 | Action | 02:08

306

307

\-\- Close the cursor and end the transaction:

308

CLOSE liahona;

309

COMMIT WORK;

310

.fi

311

.if n \{\

312

.RE

313

.\}

314

.SH "COMPATIBILITY"

315

.PP

316

The SQL standard defines

317

FETCH

318

for use in embedded SQL only\&. The variant of

319

FETCH

320

described here returns the data as if it were a

321

SELECT

322

result rather than placing it in host variables\&. Other than this point,

323

FETCH

324

is fully upward\-compatible with the SQL standard\&.

325

.PP

326

The

327

FETCH

328

forms involving

329

FORWARD

330

and

331

BACKWARD, as well as the forms

332

FETCH \fIcount\fR

333

and

334

FETCH ALL, in which

335

FORWARD

336

is implicit, are

337

PostgreSQL

338

extensions\&.

339

.PP

340

The SQL standard allows only

341

FROM

342

preceding the cursor name; the option to use

343

IN, or to leave them out altogether, is an extension\&.

344

.SH "SEE ALSO"

345

\fBCLOSE\fR(7), \fBDECLARE\fR(7), \fBMOVE\fR(7)

Older »