File: sql-declare.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (515 lines) | stat: -rw-r--r-- 8,293 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>   DECLARE
  </TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="   CREATE VIEW
  "
HREF="sql-createview.html"><LINK
REL="NEXT"
TITLE="   DELETE
  "
HREF="sql-delete.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-createview.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-delete.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>DECLARE</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>   DECLARE
   &#8212;    Defines a cursor for table access
  </DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>DECLARE <TT
CLASS="REPLACEABLE"
><I
>cursor</I
></TT
> [ BINARY ] [ INSENSITIVE ] [ SCROLL ]
    CURSOR FOR <TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
>
    [ FOR { READ ONLY | UPDATE [ OF <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> [, ...] ] ]
  </PRE
><DIV
CLASS="REFSECT2"
><H3
>    Inputs
   </H3
><P
>   </P
><P
></P
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>cursor</I
></TT
></DT
><DD
><P
>         The name of the cursor to be used in subsequent FETCH operations..
	  </P
></DD
><DT
>BINARY</DT
><DD
><P
>	   Causes the cursor to fetch data in binary
	   rather than in text format.
	  </P
></DD
><DT
>INSENSITIVE</DT
><DD
><P
>	   <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> 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 <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> this is always the case.
This keyword has no effect.
	  </P
></DD
><DT
>SCROLL</DT
><DD
><P
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> keyword indicating that data may be retrieved
in multiple rows per FETCH operation. Since this is allowed at all times
by <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> this keyword has no effect.
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
></DT
><DD
><P
>	   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.
	  </P
></DD
><DT
>READ ONLY</DT
><DD
><P
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> keyword indicating that the cursor will be used
in a readonly mode. Since this is the only cursor access mode
available in <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> this keyword has no effect.
	  </P
></DD
><DT
>UPDATE</DT
><DD
><P
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> keyword indicating that the cursor will be used
to update tables. Since cursor updates are not currently
supported in <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> this keyword
provokes an informational error message.
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
></DT
><DD
><P
>Column(s) to be updated.
Since cursor updates are not currently
supported in <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> the UPDATE clause
provokes an informational error message.
	  </P
></DD
></DL
></DIV
><DIV
CLASS="REFSECT2"
><H3
>    Outputs
   </H3
><P
>&#13;   <P
></P
></P><DL
><DT
>SELECT</DT
><DD
><P
>          The message returned if the SELECT is run successfully.
	  </P
></DD
><DT
>NOTICE
	   BlankPortalAssignName: portal "<TT
CLASS="REPLACEABLE"
><I
>cursor</I
></TT
>" already exists</DT
><DD
><P
>	   This error occurs if cursor "<TT
CLASS="REPLACEABLE"
><I
>cursor</I
></TT
>" is already declared.
	  </P
></DD
><DT
>ERROR:  Named portals may only be used in begin/end transaction blocks</DT
><DD
><P
>This error occurs if the cursor is not declared within a transaction block.
	  </P
></DD
></DL
><P>
  </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Description
  </H2
><P
>   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 in text or in binary foramt.
  </P
><P
>   Normal cursors return data  in text format, either ASCII or another
encoding scheme depending on how the <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
backend was built. Since
   data is stored natively in binary format, the system must
   do a conversion to produce the text format. In addition,
   text formats are often larger in size than the corresponding binary format.
   Once the information comes back in text form,  the client
   application may have to convert it to a binary format to
   manipulate it anyway.
  </P
><P
>   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.</P
><P
>   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').

<P
></P
><TABLE
CLASS="CAUTION"
BORDER="1"
WIDTH="100%"
><TR
><TD
ALIGN="CENTER"
><B
>Caution</B
></TD
></TR
><TR
><TD
ALIGN="LEFT"
><P
>BINARY cursors should be used carefully. User applications such
as <SPAN
CLASS="APPLICATION"
>psql</SPAN
> are not aware of binary cursors
and expect data to come back in a text format.</P
></TD
></TR
></TABLE
>

  </P
><P
>   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 (e.g. "big-endian" versus "little-endian"),
 you will probably not want your data returned in
   binary format.

<BLOCKQUOTE
CLASS="TIP"
><P
><B
>Tip: </B
>   If you intend to display the data in
   ASCII,  getting it back in ASCII will save you some
   effort on the client side.</P
></BLOCKQUOTE
>
  </P
><DIV
CLASS="REFSECT2"
><H3
>    Notes
   </H3
><P
>    Cursors are only available in transactions.
   </P
><P
>    <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
 does not have an explicit <B
CLASS="COMMAND"
>OPEN cursor</B
>
    statement; a cursor is considered to be open when it is declared.

<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>In <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> cursors are only available in
embedded applications. <SPAN
CLASS="APPLICATION"
>ecpg</SPAN
>, the
embedded SQL preprocessor for <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>,
supports the <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> conventions, including those
involving DECLARE and OPEN statements.</P
></BLOCKQUOTE
>

   </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Usage
  </H2
><P
>   To declare a cursor:
  </P
><PRE
CLASS="PROGRAMLISTING"
>DECLARE liahona CURSOR
    FOR SELECT * FROM films;
  </PRE
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Compatibility
  </H2
><P
>  </P
><DIV
CLASS="REFSECT2"
><H3
>    SQL92
   </H3
><P
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> allows cursors only in embedded <SPAN
CLASS="ACRONYM"
>SQL</SPAN
>
and in modules. <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> permits cursors to be used
interactively.
<SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> allows embedded or modular cursors to
update database information.
All <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> cursors are readonly.
The BINARY keyword is a <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> extension.&#13;</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-createview.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-delete.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>CREATE VIEW</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>DELETE</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>