<REFENTRY ID="SQL-DECLARESTATEMENT-1">

<REFENTRY ID="SQL-DECLARE">

<REFMETA>

<REFENTRYTITLE>

DECLARE STATEMENT

DECLARE

</REFENTRYTITLE>

<REFMISCINFO>SQL - Language Statements</REFMISCINFO>

</REFMETA>

<REFNAMEDIV>

<REFNAME>

DECLARE STATEMENT

DECLARE

</REFNAME>

<REFPURPOSE>

Declares a cursor

Defines a cursor for table access

</REFPURPOSE>

<REFSYNOPSISDIV>

<REFSYNOPSISDIVINFO>

<DATE>1998-04-15</DATE>

<DATE>1998-09-04</DATE>

</REFSYNOPSISDIVINFO>

<SYNOPSIS>

DECLARE <replaceable class="parameter">cursor</replaceable> [ BINARY ]

FOR SELECT <replaceable class="parameter">query</replaceable>

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

CURSOR FOR <replaceable class="parameter">query</replaceable>

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

</SYNOPSIS>

<REFSECT2 ID="R2-SQL-DECLARESTATEMENT-1">

<REFSECT2 ID="R2-SQL-DECLARE-1">

<REFSECT2INFO>

<DATE>1998-04-15</DATE>

</REFSECT2INFO>

</TITLE>

<PARA>

</PARA>

<VARIABLELIST>

<VARLISTENTRY>

<TERM>

</TERM>

<LISTITEM>

<PARA>

<VARIABLELIST>

<VARLISTENTRY>

<TERM>

<ReturnValue>BINARY</ReturnValue>

<replaceable class="parameter">cursor</replaceable>

</TERM>

<LISTITEM>

<PARA>

The BINARY keyword causes the cursor to fetch data in binary

rather than in ASCII format.

The name of the cursor to be used in subsequent FETCH operations..

</PARA>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

</TERM>

<LISTITEM>

<PARA>

The cursor's name.

Causes the cursor to fetch data in binary

rather than in text format.

</PARA>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

</TERM>

<LISTITEM>

<PARA>

An SQL query which will provide the rows to be governed by the

cursor.

<acronym>SQL92</acronym> keyword indicating that data retrieved

from the cursor should be unaffected by updates from other processes or cursors.

Since cursor operations occur within transactions

in <productname>Postgres</productname> this is always the case.

This keyword has no effect.

</PARA>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

SCROLL

</TERM>

<LISTITEM>

<PARA>

<acronym>SQL92</acronym> keyword indicating that data may be retrieved

in multiple rows per FETCH operation. Since this is allowed at all times

by <productname>Postgres</productname> this keyword has no effect.

</PARA>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

<replaceable class="parameter">query</replaceable>

</TERM>

<LISTITEM>

<PARA>

An SQL query which will provide the rows to be governed by the

cursor.

Refer to the SELECT statement for further information about

valid arguments.

</PARA>

</LISTITEM>

</VARLISTENTRY>

</variablelist>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

READ ONLY

</TERM>

<LISTITEM>

<PARA>

<acronym>SQL92</acronym> keyword indicating that the cursor will be used

in a readonly mode. Since this is the only cursor access mode

available in <productname>Postgres</productname> this keyword has no effect.

</PARA>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

UPDATE

</TERM>

<LISTITEM>

<PARA>

<acronym>SQL92</acronym> keyword indicating that the cursor will be used

to update tables. Since cursor updates are not currently

supported in <productname>Postgres</productname> this keyword

provokes an informational error message.

</PARA>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

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

</TERM>

<LISTITEM>

<PARA>

Column(s) to be updated.

Since cursor updates are not currently

supported in <productname>Postgres</productname> the UPDATE clause

provokes an informational error message.

</PARA>

</LISTITEM>

</VARLISTENTRY>

</VARIABLELIST>

</REFSECT2>

<REFSECT2 ID="R2-SQL-DECLARESTATEMENT-2">

<REFSECT2 ID="R2-SQL-DECLARE-2">

<REFSECT2INFO>

<DATE>1998-04-15</DATE>

</REFSECT2INFO>

</TITLE>

<PARA>

</PARA>

<VARIABLELIST>

<VARLISTENTRY>

<TERM>

</TERM>

<LISTITEM>

<PARA>

<VARIABLELIST>

<VARLISTENTRY>

<TERM>

<ReturnValue>SELECT</ReturnValue>

SELECT

</TERM>

<LISTITEM>

<PARA>

The message returned if the SELECT is run successfully.

</PARA>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

<ReturnValue>NOTICE

BlankPortalAssignName: portal "<replaceable class="parameter">cursor</replaceable>" already exists</ReturnValue>

NOTICE

BlankPortalAssignName: portal "<replaceable class="parameter">cursor</replaceable>" already exists

</TERM>

<LISTITEM>

<PARA>

This error occurs if cursor "<replaceable class="parameter">cursor</replaceable>" is already declared.

</PARA>

</LISTITEM>

</VARLISTENTRY>

</variablelist>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

ERROR: Named portals may only be used in begin/end transaction blocks

</TERM>

<LISTITEM>

<PARA>

This error occurs if the cursor is not declared within a transaction block.

</PARA>

</LISTITEM>

</VARLISTENTRY>

</VARIABLELIST>

</REFSECT2>

</REFSYNOPSISDIV>

<REFSECT1 ID="R1-SQL-DECLARESTATEMENT-1">

<REFSECT1 ID="R1-SQL-DECLARE-1">

<REFSECT1INFO>

<DATE>1998-04-15</DATE>

<DATE>1998-09-04</DATE>

</REFSECT1INFO>

<TITLE>

Description

</TITLE>

<PARA>

DECLARE 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 inASCII or in binary foramt.

data either intext or in binary foramt.

</PARA>

<PARA>

Normal cursors return data in ASCII format. Since

Normal cursors return data in text format, either ASCII or another

encoding scheme depending on how the <productname>Postgres</productname>

backend was built. Since

data is stored natively in binary format, the system must

do a conversion to produce theASCII format. In addition,

ASCII formats are often larger in size than binary format.

Once the information comes back inASCII, the client

applicationoften has to convert it to a binary format to

do a conversion to produce thetext format. In addition,

text formats are often larger in size than the corresponding binary format.

Once the information comes back intext form, the client

applicationmay have to convert it to a binary format to

manipulate it anyway.

</PARA>

<PARA>

BINARY cursors give you back the data in the native binary

representation. So binary cursors will tend to be a

little faster since they suffer less conversion overhead.

For example, for an integer column, you get a C integer number like ^A

using a binary cursor, while you get a string value like '1'

using the non binary cursor.

</PARA>

<para>

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 value equal to control-A ('^A').

<caution>

<para>

BINARY cursors should be used carefully. User applications such

as <application>psql</application> are not aware of binary cursors

and expect data to come back in a text format.

</caution>

<PARA>

However,ASCII is architecture-neutral whereas binary

However,string representation is architecture-neutral whereas binary

representation can differ between different machine architectures.

Therefore, if your client machine and server machine use different

representations, you will probably not want your data returned in

representations (e.g. "big-endian" versus "little-endian"),

you will probably not want your data returned in

binary format.

Again, if you intend to display the data in

<tip>

<para>

If you intend to display the data in

ASCII, getting it back in ASCII will save you some

effort on the client side.

</tip>

</PARA>

<REFSECT2 ID="R2-SQL-DECLARESTATEMENT-3">

<REFSECT2 ID="R2-SQL-DECLARE-3">

<REFSECT2INFO>

<DATE>1998-04-15</DATE>

<DATE>1998-09-04</DATE>

</REFSECT2INFO>

<TITLE>

Notes

Cursors are only available in transactions.

</PARA>

<PARA>

PostgreSQL does not have an explicit <command>OPEN cursor</command>

statement; a cursor is considered to be open when it is DECLAREd.

<productname>Postgres</productname>

does not have an explicit <command>OPEN cursor</command>

statement; a cursor is considered to be open when it is declared.

</PARA>

</REFSECT2>

</refsect1>

SQL92

</TITLE>

<PARA>

SQL92 specifies some additional capabilities for the DECLARE statement:

</PARA>

<synopsis>

DECLARE cursor [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR SELECT expression

[ ORDER BY column [, ... ] [ ASC | DESC ]

[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

</synopsis>

<variablelist>

<varlistentry>

<term></term>

<listitem>

<variablelist>

<varlistentry>

<term>INSENSITIVE</term>

<listitem>

<para>

UPDATE and DELETE CURRENT operations are not allowed

if the cursor is declared to be INSENSITIVE.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>SCROLL</term>

<listitem>

<para>

If SCROLL is not specified, only FETCH NEXT will be allowed.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>FOR READ ONLY/UPDATE</term>

<listitem>

<para>

If READ ONLY is specified, UPDATE/DELETE CURRENT operations

will not be allowed.

</para>

</listitem>

</varlistentry>

</variablelist>

</listitem>

</varlistentry>

</variablelist>

<acronym>SQL92</acronym> allows cursors only in embedded <acronym>SQL</acronym>

and in modules. <productname>Postgres</productname> permits cursors to be used

interactively.

<acronym>SQL92</acronym> allows embedded or modular cursors to

update database information.

All <productname>Postgres</productname> cursors are readonly.

The BINARY keyword is a <productname>Postgres</productname> extension.

</REFENTRY>

<!-- Keep this comment at the end of the file