<refsynopsisdiv>

<synopsis>

DECLARE <replaceable class="parameter">cursor_name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">prepared_name</replaceable>

DECLARE <replaceable class="parameter">cursor_name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>

DECLARE <replaceable class="parameter">cursor_name</replaceable> [ BINARY ] [ASENSITIVE |INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">prepared_name</replaceable>

DECLARE <replaceable class="parameter">cursor_name</replaceable> [ BINARY ] [ASENSITIVE |INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>

</synopsis>

</refsynopsisdiv>

<refsynopsisdiv>

<synopsis>

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

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

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

</synopsis>

</refsynopsisdiv>

</varlistentry>

<varlistentry>

<term><literal>ASENSITIVE</literal></term>

<term><literal>INSENSITIVE</literal></term>

<listitem>

<para>

Indicates that data retrieved from the cursor should be

unaffected by updates to the table(s) underlying the cursor that occur

after the cursor is created. In <productname>PostgreSQL</productname>,

this is the default behavior; so this key word has no

effect and is only accepted for compatibility with the SQL standard.

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. <literal>INSENSITIVE</literal>

means they are not visible, <literal>ASENSITIVE</literal> means the

behavior is implementation-dependent. A third behavior,

<literal>SENSITIVE</literal>, meaning that such changes are visible in

the cursor, is not available in <productname>PostgreSQL</productname>.

In <productname>PostgreSQL</productname>, all cursors are insensitive;

so these key words have no effect and are only accepted for

compatibility with the SQL standard.

</para>

<para>

Specifying <literal>INSENSITIVE</literal> together with <literal>FOR

UPDATE</literal> or <literal>FOR SHARE</literal> is an error.

</para>

</listitem>

</varlistentry>

</variablelist>

<para>

The key words <literal>BINARY</literal>,

The key words <literal>ASENSITIVE</literal>, <literal>BINARY</literal>,

<literal>INSENSITIVE</literal>, and <literal>SCROLL</literal> can

appear in any order.

</para>

fetched, in the same way as for a regular

<link linkend="sql-select"><command>SELECT</command></link> 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</quote>. (Specifying <literal>INSENSITIVE</literal>

together with <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> is an error.)

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

</para>

<caution>

<para>

The main reason not to use <literal>FOR UPDATE</literal> with <literal>WHERE

CURRENT OF</literal> is if you need the cursor to be scrollable, or to be

insensitive to the subsequent updates (that is, continue to show the old

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.

</para>

<refsect1>

<title>Compatibility</title>

<para>

The SQL standard says that it is implementation-dependent whether cursors

are sensitive to concurrent updates of the underlying data by default. In

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

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

products may work differently.

</para>

<para>

The SQL standard allows cursors only in embedded

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

permits cursors to be used interactively.

</para>

<para>

According to the SQL standard, changes made to insensitive cursors by

<literal>UPDATE ... WHERE CURRENT OF</literal> and <literal>DELETE

... WHERE CURRENT OF</literal> statements are visibible in that same

cursor. <productname>PostgreSQL</productname> treats these statements like

all other data changing statements in that they are not visible in

insensitive cursors.

</para>

<para>

Binary cursors are a <productname>PostgreSQL</productname>

extension.

T211Basic trigger capability08Multiple triggers for the same event are executed in the order in which they were created in the catalogNOintentionally omitted

T212Enhanced trigger capabilityYES

T213INSTEAD OF triggersYES

T231Sensitive cursorsYES

T231Sensitive cursorsNO

T241START TRANSACTION statementYES

T251SET TRANSACTION statement: LOCAL optionNO

T261Chained transactionsYES

Query*result;

Query*query;

if ((stmt->options&CURSOR_OPT_SCROLL)&&

(stmt->options&CURSOR_OPT_NO_SCROLL))

ereport(ERROR,

(errcode(ERRCODE_INVALID_CURSOR_DEFINITION),

errmsg("cannot specify both SCROLL and NO SCROLL")));

errmsg("cannot specify both %s and %s",

"SCROLL","NO SCROLL")));

if ((stmt->options&CURSOR_OPT_ASENSITIVE)&&

(stmt->options&CURSOR_OPT_INSENSITIVE))

ereport(ERROR,

(errcode(ERRCODE_INVALID_CURSOR_DEFINITION),

errmsg("cannot specify both %s and %s",

"ASENSITIVE","INSENSITIVE")));

query=transformStmt(pstate,stmt->query);

if (query->rowMarks!=NIL&& (stmt->options&CURSOR_OPT_INSENSITIVE))

ereport(ERROR,

(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),

(errcode(ERRCODE_INVALID_CURSOR_DEFINITION),

errmsg("DECLARE INSENSITIVE CURSOR ... %s is notsupported",

errmsg("DECLARE INSENSITIVE CURSOR ... %s is notvalid",

LCS_asString(((RowMarkClause*)

linitial(query->rowMarks))->strength)),

errdetail("Insensitive cursors must be READ ONLY.")));

AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC

ASSERTION ASSIGNMENT ASYMMETRIC AT ATTACH ATTRIBUTE AUTHORIZATION

ASENSITIVEASSERTION ASSIGNMENT ASYMMETRIC AT ATTACH ATTRIBUTE AUTHORIZATION

BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT

BOOLEAN_P BOTH BREADTH BY

|cursor_optionsNOSCROLL{$$ =$1 | CURSOR_OPT_NO_SCROLL; }

|cursor_optionsSCROLL{$$ =$1 | CURSOR_OPT_SCROLL; }

|cursor_optionsBINARY{$$ =$1 | CURSOR_OPT_BINARY; }

|cursor_optionsASENSITIVE{$$ =$1 | CURSOR_OPT_ASENSITIVE; }

|cursor_optionsINSENSITIVE{$$ =$1 | CURSOR_OPT_INSENSITIVE; }

;

| ALSO

| ALTER

| ALWAYS

| ASENSITIVE

| ASSERTION

| ASSIGNMENT

| AT

| AND

| ANY

| ASC

| ASENSITIVE

| ASSERTION

| ASSIGNMENT

| ASYMMETRIC

elseif (Matches("DECLARE",MatchAny))

COMPLETE_WITH("BINARY","INSENSITIVE","SCROLL","NO SCROLL",

COMPLETE_WITH("BINARY","ASENSITIVE","INSENSITIVE","SCROLL","NO SCROLL",

"CURSOR");

#defineCURSOR_OPT_SCROLL0x0002/* SCROLL explicitly given */

#defineCURSOR_OPT_NO_SCROLL0x0004/* NO SCROLL explicitly given */

#defineCURSOR_OPT_INSENSITIVE0x0008/* INSENSITIVE */

#defineCURSOR_OPT_HOLD0x0010/* WITH HOLD */

#defineCURSOR_OPT_ASENSITIVE0x0010/* ASENSITIVE */

#defineCURSOR_OPT_HOLD0x0020/* WITH HOLD */

#defineCURSOR_OPT_FAST_PLAN0x0100/* prefer fast-start plan */

#defineCURSOR_OPT_GENERIC_PLAN 0x0200/* force use of generic plan */

PG_KEYWORD("array",ARRAY,RESERVED_KEYWORD,AS_LABEL)

PG_KEYWORD("as",AS,RESERVED_KEYWORD,AS_LABEL)

PG_KEYWORD("asc",ASC,RESERVED_KEYWORD,BARE_LABEL)

PG_KEYWORD("asensitive",ASENSITIVE,UNRESERVED_KEYWORD,BARE_LABEL)

PG_KEYWORD("assertion",ASSERTION,UNRESERVED_KEYWORD,BARE_LABEL)

PG_KEYWORD("assignment",ASSIGNMENT,UNRESERVED_KEYWORD,BARE_LABEL)

PG_KEYWORD("asymmetric",ASYMMETRIC,RESERVED_KEYWORD,BARE_LABEL)

8 | one

(1 row)

---sensitive cursors can't currently scroll back, so this is an error:

---FOR UPDATE cursors can't currently scroll back, so this is an error:

FETCH RELATIVE 0 FROM c1;

ERROR: cursor can only scan forward

HINT: Declare it with SCROLL option to enable backward scan.

8 | one

(2 rows)

-- Check insensitive cursor with INSERT

-- (The above tests don't test the SQL notion of an insensitive cursor

-- correctly, because per SQL standard, changes from WHERE CURRENT OF

-- commands should be visible in the cursor. So here we make the

-- changes with a command that is independent of the cursor.)

BEGIN;

DECLARE c1 INSENSITIVE CURSOR FOR SELECT * FROM uctest;

INSERT INTO uctest VALUES (10, 'ten');

FETCH NEXT FROM c1;

f1 | f2

----+-------

3 | three

(1 row)

FETCH NEXT FROM c1;

f1 | f2

----+-----

8 | one

(1 row)

FETCH NEXT FROM c1; -- insert not visible

f1 | f2

----+----

(0 rows)

COMMIT;

SELECT * FROM uctest;

f1 | f2

----+-------

3 | three

8 | one

10 | ten

(3 rows)

DELETE FROM uctest WHERE f1 = 10; -- restore test table state

-- Check inheritance cases

CREATE TEMP TABLE ucchild () inherits (uctest);

INSERT INTO ucchild values(100, 'hundred');

SELECT*FROM uctest;

UPDATE uctestSET f1= f1+10WHERE CURRENT OF c1;-- no-op

SELECT*FROM uctest;

FETCH RELATIVE0FROM c1;

ROLLBACK;

SELECT*FROM uctest;

BEGIN;

DECLARE c1 INSENSITIVE CURSOR FORSELECT*FROM uctest;

INSERT INTO uctestVALUES (10,'ten');

FETCH NEXTFROM c1;

FETCH NEXTFROM c1;

FETCH NEXTFROM c1;-- insert not visible

COMMIT;

SELECT*FROM uctest;

DELETEFROM uctestWHERE f1=10;-- restore test table state

CREATE TEMP TABLE ucchild () inherits (uctest);

INSERT INTO ucchildvalues(100,'hundred');