<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.40 2007/01/31 23:26:03 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.41 2007/06/11 01:16:21 tgl Exp $

PostgreSQL documentation

-->

<synopsis>

DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]

CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>

[ FOR { READ ONLY | UPDATE [ OF <replaceable class="parameter">column</replaceable> [, ...] ] } ]

</synopsis>

</refsynopsisdiv>

<para>

<command>DECLARE</command> 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. Cursors can

return data either in text or in binary format using

a small number of rows at a time out of a larger query.

After the cursor is created, rows are fetched from it using

<xref linkend="sql-fetch" endterm="sql-fetch-title">.

</para>

<para>

Normal cursors return data in text format, the same as a

<command>SELECT</> would produce. Since data is stored natively in

binary format, the system must do a conversion to produce the text

format. Once the information comes back in text form, the client

application might need to convert it to a binary format to manipulate

it. In addition, data in the text format is often larger in size

than in the binary format. Binary cursors return the data in a

binary representation that might be more easily manipulated.

Nevertheless, if you intend to display the data as text anyway,

retrieving it in text form will

save you some effort on the client side.

</para>

<para>

As an example, if a query returns a value of one from an integer column,

you would get a string of <literal>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).

</para>

<para>

Binary cursors should be used carefully. Many applications,

including <application>psql</application>, are not prepared to

handle binary cursors and expect data to come back in the text

format.

</para>

<note>

<para>

When the client application uses the <quote>extended query</> protocol

to issue a <command>FETCH</> 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 &mdash; any cursor can be treated as either text or binary.

</para>

</note>

</refsect1>

<refsect1>

<listitem>

<para>

Indicates that data retrieved from the cursor should be

unaffected by updates to thetables underlying the cursorwhile

the cursorexists. In <productname>PostgreSQL</productname>,

all cursors are insensitive;this key word currently has no

effect and ispresent for compatibility with the SQL standard.

unaffected by updates to thetable(s) underlying the cursorthat occur

afterthe cursoris created. In <productname>PostgreSQL</productname>,

this is the default behavior; sothis key word has no

effect and isonly accepted for compatibility with the SQL standard.

</para>

</listitem>

</varlistentry>

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>FOR READ ONLY</literal></term>

<term><literal>FOR UPDATE</literal></term>

<listitem>

<para>

<literal>FOR READ ONLY</literal> indicates that the cursor will

be used in a read-only mode. <literal>FOR UPDATE</literal>

indicates that the cursor will be used to update tables. Since

cursor updates are not currently supported in

<productname>PostgreSQL</productname>, specifying <literal>FOR

UPDATE</literal> will cause an error message and specifying

<literal>FOR READ ONLY</literal> has no effect.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">column</replaceable></term>

<listitem>

<para>

Column(s) to be updated by the cursor. Since cursor updates are

not currently supported in

<productname>PostgreSQL</productname>, the <literal>FOR

UPDATE</literal> clause provokes an error message.

</para>

</listitem>

</varlistentry>

</variablelist>

<para>

<refsect1 id="sql-declare-notes">

<title id="sql-declare-notes-title">Notes</title>

<para>

Normal cursors return data in text format, the same as a

<command>SELECT</> would produce. The <literal>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 <literal>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).

</para>

<para>

Binary cursors should be used carefully. Many applications,

including <application>psql</application>, are not prepared to

handle binary cursors and expect data to come back in the text

format.

</para>

<note>

<para>

When the client application uses the <quote>extended query</> protocol

to issue a <command>FETCH</> 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 &mdash; any cursor can be treated as either text or binary.

</para>

</note>

<para>

Unless <literal>WITH HOLD</literal> is specified, the cursor

created by this command can only be used within the current

transactions.

</para>

<para>

<literal>WITH HOLD</literal> may not be specified when the query

includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>.

</para>

<para>

The <literal>SCROLL</> option should be specified when defining a

cursor that will be used to fetch backwards. This is required by

specified, then backward fetches are disallowed in any case.

</para>

<para>

If the cursor's query includes <literal>FOR UPDATE</> or <literal>FOR

SHARE</>, then returned rows are locked at the time they are first

fetched, in the same way as for a regular

<xref linkend="sql-select" endterm="sql-select-title"> command with

these options.

In addition, the returned rows will be the most up-to-date versions;

therefore these options provide the equivalent of what the SQL standard

calls a <quote>sensitive cursor</>. It is often wise to use <literal>FOR

UPDATE</> if the cursor is intended to be used with <command>UPDATE

... WHERE CURRENT OF</> or <command>DELETE ... WHERE CURRENT OF</>,

since this will prevent other sessions from changing the rows between

the time they are fetched and the time they are updated. Without

<literal>FOR UPDATE</>, a subsequent <literal>WHERE CURRENT OF</> command

will have no effect if the row was changed meanwhile.

</para>

<para>

The SQL standard only makes provisions for cursors in embedded

<acronym>SQL</acronym>. The <productname>PostgreSQL</productname>

<title>Compatibility</title>

<para>

The SQL standard allows cursors only in embedded

<acronym>SQL</acronym> and in modules. <productname>PostgreSQL</>

permits cursors to be used interactively.

The SQL standard specifies that by default, cursors are sensitive to

concurrent updates of the underlying data. In

<productname>PostgreSQL</productname>, cursors are insensitive by default,

and can be made sensitive by specifying <literal>FOR UPDATE</>.

</para>

<para>

The SQL standard allows cursors to update table data. All

<productname>PostgreSQL</> cursors are read only.

The SQL standard allows cursors only in embedded

<acronym>SQL</acronym> and in modules. <productname>PostgreSQL</>

permits cursors to be used interactively.

</para>

<para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.30 2007/02/01 00:28:19 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.31 2007/06/11 01:16:21 tgl Exp $

PostgreSQL documentation

-->

<synopsis>

DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]

[ USING <replaceable class="PARAMETER">usinglist</replaceable> ]

[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]

[ WHERE <replaceable class="PARAMETER">condition</replaceable>| WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable>]

[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]

</synopsis>

</refsynopsisdiv>

<term><replaceable class="parameter">condition</replaceable></term>

<listitem>

<para>

An expression returning a value of type

<type>boolean</type>, which determines the rows that are to be

deleted.

An expression that returns a value of type <type>boolean</type>.

Only rows for which this expression returns <literal>true</>

will be deleted.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">cursor_name</replaceable></term>

<listitem>

<para>

The name of the cursor to use in a <literal>WHERE CURRENT OF</>

condition. The row to be deleted is the one most recently fetched

from this cursor. The cursor must be a simple (non-join, non-aggregate)

query on the <command>DELETE</>'s target table.

Note that <literal>WHERE CURRENT OF</> cannot be

specified together with a boolean condition.

</para>

</listitem>

</varlistentry>

Delete completed tasks, returning full details of the deleted rows:

<programlisting>

DELETE FROM tasks WHERE status = 'DONE' RETURNING *;

</programlisting>

</para>

<para>

Delete the row of <structname>tasks</> on which the cursor

<literal>c_tasks</> is currently positioned:

<programlisting>

DELETE FROM tasks WHERE CURRENT OF c_tasks;

</programlisting>

</para>

</refsect1>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.43 2007/02/01 00:28:19 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.44 2007/06/11 01:16:22 tgl Exp $

PostgreSQL documentation

-->

SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |

( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]

[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]

[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]

[ WHERE <replaceable class="PARAMETER">condition</replaceable>| WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable>]

[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]

</synopsis>

</refsynopsisdiv>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">cursor_name</replaceable></term>

<listitem>

<para>

The name of the cursor to use in a <literal>WHERE CURRENT OF</>

condition. The row to be updated is the one most recently fetched

from this cursor. The cursor must be a simple (non-join, non-aggregate)

query on the <command>UPDATE</>'s target table.

Note that <literal>WHERE CURRENT OF</> cannot be

specified together with a boolean condition.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">output_expression</replaceable></term>

<listitem>

COMMIT;

</programlisting>

</para>

<para>

Change the <structfield>kind</> column of the table

<structname>films</structname> in the row on which the cursor

<literal>c_films</> is currently positioned:

<programlisting>

UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;

</programlisting>

</para>

</refsect1>

<refsect1>

subdir = src/backend/executor

top_builddir = ../../..

include$(top_builddir)/src/Makefile.global

OBJS = execAmi.o execGrouping.o execJunk.o execMain.o\

OBJS = execAmi.oexecCurrent.oexecGrouping.o execJunk.o execMain.o\

execProcnode.o execQual.o execScan.o execTuples.o\

execUtils.o functions.o instrument.o nodeAppend.o nodeAgg.o\

nodeBitmapAnd.o nodeBitmapOr.o\