1 files changed, 345 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/DECLARE.7 b/doc/src/sgml/man7/DECLARE.7
new file mode 100644
index 0000000..24afb11
--- /dev/null
+++ b/doc/src/sgml/man7/DECLARE.7
@@ -0,0 +1,345 @@
+'\" t
+.\"     Title: DECLARE
+.\"    Author: The PostgreSQL Global Development Group
+.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
+.\"      Date: 2022
+.\"    Manual: PostgreSQL 14.5 Documentation
+.\"    Source: PostgreSQL 14.5
+.\"  Language: English
+.\"
+.TH "DECLARE" "7" "2022" "PostgreSQL 14.5" "PostgreSQL 14.5 Documentation"
+.\" -----------------------------------------------------------------
+.\" * Define some portability stuff
+.\" -----------------------------------------------------------------
+.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+.\" http://bugs.debian.org/507673
+.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
+.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+.ie \n(.g .ds Aq \(aq
+.el       .ds Aq '
+.\" -----------------------------------------------------------------
+.\" * set default formatting
+.\" -----------------------------------------------------------------
+.\" disable hyphenation
+.nh
+.\" disable justification (adjust text to left margin only)
+.ad l
+.\" -----------------------------------------------------------------
+.\" * MAIN CONTENT STARTS HERE *
+.\" -----------------------------------------------------------------
+.SH "NAME"
+DECLARE \- define a cursor
+.SH "SYNOPSIS"
+.sp
+.nf
+DECLARE \fIname\fR [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]
+    CURSOR [ { WITH | WITHOUT } HOLD ] FOR \fIquery\fR
+.fi
+.SH "DESCRIPTION"
+.PP
+\fBDECLARE\fR
+allows a user to create cursors, which can be used to retrieve a small number of rows at a time out of a larger query\&. After the cursor is created, rows are fetched from it using
+\fBFETCH\fR\&.
+.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\ \&43.7\&.
+.sp .5v
+.RE
+.SH "PARAMETERS"
+.PP
+\fIname\fR
+.RS 4
+The name of the cursor to be created\&.
+.RE
+.PP
+BINARY
+.RS 4
+Causes the cursor to return data in binary rather than in text format\&.
+.RE
+.PP
+ASENSITIVE
+.br
+INSENSITIVE
+.RS 4
+Cursor sensitivity determines whether changes to the data underlying the cursor, done in the same transaction, after the cursor has been declared, are visible in the cursor\&.
+INSENSITIVE
+means they are not visible,
+ASENSITIVE
+means the behavior is implementation\-dependent\&. A third behavior,
+SENSITIVE, meaning that such changes are visible in the cursor, is not available in
+PostgreSQL\&. In
+PostgreSQL, all cursors are insensitive; so these key words have no effect and are only accepted for compatibility with the SQL standard\&.
+.sp
+Specifying
+INSENSITIVE
+together with
+FOR UPDATE
+or
+FOR SHARE
+is an error\&.
+.RE
+.PP
+SCROLL
+.br
+NO SCROLL
+.RS 4
+SCROLL
+specifies that the cursor can be used to retrieve rows in a nonsequential fashion (e\&.g\&., backward)\&. Depending upon the complexity of the query\*(Aqs execution plan, specifying
+SCROLL
+might impose a performance penalty on the query\*(Aqs execution time\&.
+NO SCROLL
+specifies that the cursor cannot be used to retrieve rows in a nonsequential fashion\&. The default is to allow scrolling in some cases; this is not the same as specifying
+SCROLL\&. See
+Notes
+below for details\&.
+.RE
+.PP
+WITH HOLD
+.br
+WITHOUT HOLD
+.RS 4
+WITH HOLD
+specifies that the cursor can continue to be used after the transaction that created it successfully commits\&.
+WITHOUT HOLD
+specifies that the cursor cannot be used outside of the transaction that created it\&. If neither
+WITHOUT HOLD
+nor
+WITH HOLD
+is specified,
+WITHOUT HOLD
+is the default\&.
+.RE
+.PP
+\fIquery\fR
+.RS 4
+A
+\fBSELECT\fR
+or
+\fBVALUES\fR
+command which will provide the rows to be returned by the cursor\&.
+.RE
+.PP
+The key words
+ASENSITIVE,
+BINARY,
+INSENSITIVE, and
+SCROLL
+can appear in any order\&.
+.SH "NOTES"
+.PP
+Normal cursors return data in text format, the same as a
+\fBSELECT\fR
+would produce\&. The
+BINARY
+option specifies that the cursor should return data in binary format\&. This reduces conversion effort for both the server and client, at the cost of more programmer effort to deal with platform\-dependent binary data formats\&. As an example, if a query returns a value of one from an integer column, you would get a string of
+1
+with a default cursor, whereas with a binary cursor you would get a 4\-byte field containing the internal representation of the value (in big\-endian byte order)\&.
+.PP
+Binary cursors should be used carefully\&. Many applications, including
+psql, are not prepared to handle binary cursors and expect data to come back in the text format\&.
+.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
+When the client application uses the
+\(lqextended query\(rq
+protocol to issue a
+\fBFETCH\fR
+command, the Bind protocol message specifies whether data is to be retrieved in text or binary format\&. This choice overrides the way that the cursor is defined\&. The concept of a binary cursor as such is thus obsolete when using extended query protocol \(em any cursor can be treated as either text or binary\&.
+.sp .5v
+.RE
+.PP
+Unless
+WITH HOLD
+is specified, the cursor created by this command can only be used within the current transaction\&. Thus,
+\fBDECLARE\fR
+without
+WITH HOLD
+is useless outside a transaction block: the cursor would survive only to the completion of the statement\&. Therefore
+PostgreSQL
+reports an error if such a command is used outside a transaction block\&. Use
+\fBBEGIN\fR
+and
+\fBCOMMIT\fR
+(or
+\fBROLLBACK\fR) to define a transaction block\&.
+.PP
+If
+WITH HOLD
+is specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session\&. (But if the creating transaction is aborted, the cursor is removed\&.) A cursor created with
+WITH HOLD
+is closed when an explicit
+\fBCLOSE\fR
+command is issued on it, or the session ends\&. In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions\&.
+.PP
+WITH HOLD
+may not be specified when the query includes
+FOR UPDATE
+or
+FOR SHARE\&.
+.PP
+The
+SCROLL
+option should be specified when defining a cursor that will be used to fetch backwards\&. This is required by the SQL standard\&. However, for compatibility with earlier versions,
+PostgreSQL
+will allow backward fetches without
+SCROLL, if the cursor\*(Aqs query plan is simple enough that no extra overhead is needed to support it\&. However, application developers are advised not to rely on using backward fetches from a cursor that has not been created with
+SCROLL\&. If
+NO SCROLL
+is specified, then backward fetches are disallowed in any case\&.
+.PP
+Backward fetches are also disallowed when the query includes
+FOR UPDATE
+or
+FOR SHARE; therefore
+SCROLL
+may not be specified in this case\&.
+.if n \{\
+.sp
+.\}
+.RS 4
+.it 1 an-trap
+.nr an-no-space-flag 1
+.nr an-break-flag 1
+.br
+.ps +1
+\fBCaution\fR
+.ps -1
+.br
+.PP
+Scrollable cursors may give unexpected results if they invoke any volatile functions (see
+Section\ \&38.7)\&. When a previously fetched row is re\-fetched, the functions might be re\-executed, perhaps leading to results different from the first time\&. It\*(Aqs best to specify
+NO SCROLL
+for a query involving volatile functions\&. If that is not practical, one workaround is to declare the cursor
+SCROLL WITH HOLD
+and commit the transaction before reading any rows from it\&. This will force the entire output of the cursor to be materialized in temporary storage, so that volatile functions are executed exactly once for each row\&.
+.sp .5v
+.RE
+.PP
+If the cursor\*(Aqs query includes
+FOR UPDATE
+or
+FOR SHARE, then returned rows are locked at the time they are first fetched, in the same way as for a regular
+\fBSELECT\fR
+command with these options\&. In addition, the returned rows will be the most up\-to\-date versions\&.
+.if n \{\
+.sp
+.\}
+.RS 4
+.it 1 an-trap
+.nr an-no-space-flag 1
+.nr an-break-flag 1
+.br
+.ps +1
+\fBCaution\fR
+.ps -1
+.br
+.PP
+It is generally recommended to use
+FOR UPDATE
+if the cursor is intended to be used with
+\fBUPDATE \&.\&.\&. WHERE CURRENT OF\fR
+or
+\fBDELETE \&.\&.\&. WHERE CURRENT OF\fR\&. Using
+FOR UPDATE
+prevents other sessions from changing the rows between the time they are fetched and the time they are updated\&. Without
+FOR UPDATE, a subsequent
+WHERE CURRENT OF
+command will have no effect if the row was changed since the cursor was created\&.
+.PP
+Another reason to use
+FOR UPDATE
+is that without it, a subsequent
+WHERE CURRENT OF
+might fail if the cursor query does not meet the SQL standard\*(Aqs rules for being
+\(lqsimply updatable\(rq
+(in particular, the cursor must reference just one table and not use grouping or
+ORDER BY)\&. Cursors that are not simply updatable might work, or might not, depending on plan choice details; so in the worst case, an application might work in testing and then fail in production\&. If
+FOR UPDATE
+is specified, the cursor is guaranteed to be updatable\&.
+.PP
+The main reason not to use
+FOR UPDATE
+with
+WHERE CURRENT OF
+is if you need the cursor to be scrollable, or to be isolated from concurrent updates (that is, continue to show the old data)\&. If this is a requirement, pay close heed to the caveats shown above\&.
+.sp .5v
+.RE
+.PP
+The SQL standard only makes provisions for cursors in embedded
+SQL\&. The
+PostgreSQL
+server does not implement an
+\fBOPEN\fR
+statement for cursors; a cursor is considered to be open when it is declared\&. However,
+ECPG, the embedded SQL preprocessor for
+PostgreSQL, supports the standard SQL cursor conventions, including those involving
+\fBDECLARE\fR
+and
+\fBOPEN\fR
+statements\&.
+.PP
+You can see all available cursors by querying the
+pg_cursors
+system view\&.
+.SH "EXAMPLES"
+.PP
+To declare a cursor:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+DECLARE liahona CURSOR FOR SELECT * FROM films;
+.fi
+.if n \{\
+.RE
+.\}
+.sp
+See
+\fBFETCH\fR(7)
+for more examples of cursor usage\&.
+.SH "COMPATIBILITY"
+.PP
+The SQL standard allows cursors only in embedded
+SQL
+and in modules\&.
+PostgreSQL
+permits cursors to be used interactively\&.
+.PP
+According to the SQL standard, changes made to insensitive cursors by
+UPDATE \&.\&.\&. WHERE CURRENT OF
+and
+DELETE \&.\&.\&. WHERE CURRENT OF
+statements are visible in that same cursor\&.
+PostgreSQL
+treats these statements like all other data changing statements in that they are not visible in insensitive cursors\&.
+.PP
+Binary cursors are a
+PostgreSQL
+extension\&.
+.SH "SEE ALSO"
+\fBCLOSE\fR(7), \fBFETCH\fR(7), \fBMOVE\fR(7)