From 95af0cb9bb65381107f3504e61afac2a96d32a06 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Mon, 7 Sep 1998 15:56:20 +0000 Subject: [PATCH] Fix Postgres to support SQL92 syntax. Old syntax still works too. --- doc/src/sgml/ref/declare.sgml | 249 ++++++++++++++++++++-------------- doc/src/sgml/ref/fetch.sgml | 187 ++++++++++++++----------- doc/src/sgml/ref/move.sgml | 117 ++-------------- 3 files changed, 269 insertions(+), 284 deletions(-) diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml index b4a16ff5f5..b9b2f4087a 100644 --- a/doc/src/sgml/ref/declare.sgml +++ b/doc/src/sgml/ref/declare.sgml @@ -1,26 +1,27 @@ - + - DECLARE STATEMENT + DECLARE SQL - Language Statements - DECLARE STATEMENT + DECLARE - Declares a cursor + Defines a cursor for table access - 1998-04-15 + 1998-09-04 - DECLARE cursor [ BINARY ] - FOR SELECT query +DECLARE cursor [ BINARY ] [ INSENSITIVE ] [ SCROLL ] + CURSOR FOR query + [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] ] - + 1998-04-15 @@ -29,56 +30,117 @@ - - - - - - - BINARY + cursor - 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.. + - cursor + BINARY - The cursor's name. + Causes the cursor to fetch data in binary + rather than in text format. + - query + INSENSITIVE - An SQL query which will provide the rows to be governed by the - cursor. + SQL92 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 Postgres this is always the case. +This keyword has no effect. + + + + + + + SCROLL + + + +SQL92 keyword indicating that data may be retrieved +in multiple rows per FETCH operation. Since this is allowed at all times +by Postgres this keyword has no effect. + + + + + + query + + + 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. - - - + + + + READ ONLY + + + +SQL92 keyword indicating that the cursor will be used +in a readonly mode. Since this is the only cursor access mode +available in Postgres this keyword has no effect. + + + + + + + UPDATE + + + +SQL92 keyword indicating that the cursor will be used +to update tables. Since cursor updates are not currently +supported in Postgres this keyword +provokes an informational error message. + + + + + + + column + + + +Column(s) to be updated. +Since cursor updates are not currently +supported in Postgres the UPDATE clause +provokes an informational error message. + + + + - + 1998-04-15 @@ -87,16 +149,11 @@ + - - - - - - - SELECT + SELECT @@ -104,10 +161,11 @@ + - NOTICE - BlankPortalAssignName: portal "cursor" already exists + NOTICE + BlankPortalAssignName: portal "cursor" already exists @@ -115,16 +173,25 @@ - - - + + + +ERROR: Named portals may only be used in begin/end transaction blocks + + + +This error occurs if the cursor is not declared within a transaction block. + + + + - + - 1998-04-15 + 1998-09-04 Description @@ -132,39 +199,55 @@ <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 in ASCII or in binary foramt. + data either in text 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 the ASCII format. In addition, - ASCII formats are often larger in size than binary format. - Once the information comes back in ASCII, the client - application often has to convert it to a binary format to + 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. </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 @@ -173,8 +256,9 @@ 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> @@ -207,49 +291,14 @@ SQL92 - SQL92 specifies some additional capabilities for the DECLARE statement: - - - DECLARE cursor [ INSENSITIVE ] [ SCROLL ] CURSOR - FOR SELECT expression - [ ORDER BY column [, ... ] [ ASC | DESC ] - [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ] - - - - - - - - INSENSITIVE - - - UPDATE and DELETE CURRENT operations are not allowed - if the cursor is declared to be INSENSITIVE. - - - - - SCROLL - - - If SCROLL is not specified, only FETCH NEXT will be allowed. - - - - - FOR READ ONLY/UPDATE - - - If READ ONLY is specified, UPDATE/DELETE CURRENT operations - will not be allowed. - - - - - - - +SQL92 allows cursors only in embedded SQL +and in modules. Postgres permits cursors to be used +interactively. +SQL92 allows embedded or modular cursors to +update database information. +All Postgres cursors are readonly. +The BINARY keyword is a Postgres extension. +