
>       Explicit cursor can be declared as:
> 
>           DECLARE
>               ...
>               curname CURSOR [(argname type [, ...])]
>                       IS <select_stmt>;

In esql you would have FOR instead of IS.

DECLARE curname CURSOR ... FOR ....

Thus the question, where is the syntax from ?
There seems to be a standard for "the" SQL stored procedure language:

"Persistent Stored Module definition of the ANSI SQL99 standard" (quote from DB/2)
Anybody know this ?

Andreas

Tue, 22 May 2001 11:32:40 +0200
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
AW: PL/pgSQL CURSOR support
Definitely it's  Oracle's syntax.

"Zeugswetter Andreas SB " <ZeugswetterA@wien.spardat.at> �������/�������� �
�������� ���������:
news:11C1E6749A55D411A9670001FA6879633682EA@sdexcsrv1.f000.d0188.sd.spardat.
at...
>
> >       Explicit cursor can be declared as:
> >
> >           DECLARE
> >               ...
> >               curname CURSOR [(argname type [, ...])]
> >                       IS <select_stmt>;
>
> In esql you would have FOR instead of IS.
>
> DECLARE curname CURSOR ... FOR ....
>
> Thus the question, where is the syntax from ?
> There seems to be a standard for "the" SQL stored procedure language:
>
> "Persistent Stored Module definition of the ANSI SQL99 standard" (quote
from DB/2)
> Anybody know this ?
>
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl



Tue, 22 May 2001 13:54:22 +0400
"Sergey E. Volkov" <sve@raiden.bancorp.ru>
Re: PL/pgSQL CURSOR support
Zeugswetter Andreas SB wrote:
>
> >       Explicit cursor can be declared as:
> >
> >           DECLARE
> >               ...
> >               curname CURSOR [(argname type [, ...])]
> >                       IS <select_stmt>;
>
> In esql you would have FOR instead of IS.
>
> DECLARE curname CURSOR ... FOR ....
>
> Thus the question, where is the syntax from ?

    From  the  worlds  most  expens\b\b\b\b\b\b  -  er - reliable
    commercial database system.

> There seems to be a standard for "the" SQL stored procedure language:
>
> "Persistent Stored Module definition of the ANSI SQL99 standard" (quote from DB/2)
> Anybody know this ?

    The entire PL/pgSQL was written with  some  compatibility  in
    mind. Otherwise FOR loops would look more like

        [ <<label>> ]
        FOR <loop_name> AS
            [ EACH ROW OF ] [ CURSOR <cursor_name> FOR ]
            <cursor_specification> DO
            <statements>
        END FOR;

    The  good  thing  is  that we can have any number of loadable
    procedural languages. It's  relatively  easy  to  change  the
    PL/pgSQL  parser and create some PL/SQL99 handler. As long as
    the symbols in the modules don't conflict, I  see  no  reason
    why we shouldn't.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Tue, 22 May 2001 09:35:14 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: AW: PL/pgSQL CURSOR support
>From SQL99 (Note: the 'FOR' keyword seems standard...):

         14  Data manipulation



         14.1  <declare cursor>

         Function

         Define a cursor.

         Format

         <declare cursor> ::=
              DECLARE <cursor name> [ <cursor sensitivity> ]
                [ <cursor scrollability> ] CURSOR
                [ <cursor holdability> ]
                [ <cursor returnability> ]
                FOR <cursor specification>

         <cursor sensitivity> ::=
                SENSITIVE
              | INSENSITIVE
              | ASENSITIVE

         <cursor scrollability> ::=
                SCROLL
              | NO SCROLL

         <cursor holdability> ::=
                WITH HOLD
              | WITHOUT HOLD

         <cursor returnability> ::=
                WITH RETURN
              | WITHOUT RETURN

         <cursor specification> ::=
              <query expression> [ <order by clause> ]
                [ <updatability clause> ]

         <updatability clause> ::=
              FOR { READ ONLY | UPDATE [ OF <column name list> ] }

         <order by clause> ::=
              ORDER BY <sort specification list>

         <sort specification list> ::=
              <sort specification> [ { <comma> <sort specification> }... ]

         <sort specification> ::=
              <sort key> [ <collate clause> ] [ <ordering specification> ]

         <sort key> ::=
              <value expression>

         <ordering specification> ::= ASC | DESC

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Zeugswetter
> Andreas SB
> Sent: Tuesday, 22 May 2001 5:33 PM
> To: 'Jan Wieck'; PostgreSQL HACKERS
> Subject: AW: [HACKERS] PL/pgSQL CURSOR support
> 
> 
> 
> >       Explicit cursor can be declared as:
> > 
> >           DECLARE
> >               ...
> >               curname CURSOR [(argname type [, ...])]
> >                       IS <select_stmt>;
> 
> In esql you would have FOR instead of IS.
> 
> DECLARE curname CURSOR ... FOR ....
> 
> Thus the question, where is the syntax from ?
> There seems to be a standard for "the" SQL stored procedure language:
> 
> "Persistent Stored Module definition of the ANSI SQL99 standard" 
> (quote from DB/2)
> Anybody know this ?
> 
> Andreas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 


Wed, 23 May 2001 09:56:29 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: PL/pgSQL CURSOR support

Can someone comment on the use of FOR/IS in cursors?

[ Charset ISO-8859-1 unsupported, converting... ]
> >From SQL99 (Note: the 'FOR' keyword seems standard...):
> 
>          14  Data manipulation
> 
> 
> 
>          14.1  <declare cursor>
> 
>          Function
> 
>          Define a cursor.
> 
>          Format
> 
>          <declare cursor> ::=
>               DECLARE <cursor name> [ <cursor sensitivity> ]
>                 [ <cursor scrollability> ] CURSOR
>                 [ <cursor holdability> ]
>                 [ <cursor returnability> ]
>                 FOR <cursor specification>
> 
>          <cursor sensitivity> ::=
>                 SENSITIVE
>               | INSENSITIVE
>               | ASENSITIVE
> 
>          <cursor scrollability> ::=
>                 SCROLL
>               | NO SCROLL
> 
>          <cursor holdability> ::=
>                 WITH HOLD
>               | WITHOUT HOLD
> 
>          <cursor returnability> ::=
>                 WITH RETURN
>               | WITHOUT RETURN
> 
>          <cursor specification> ::=
>               <query expression> [ <order by clause> ]
>                 [ <updatability clause> ]
> 
>          <updatability clause> ::=
>               FOR { READ ONLY | UPDATE [ OF <column name list> ] }
> 
>          <order by clause> ::=
>               ORDER BY <sort specification list>
> 
>          <sort specification list> ::=
>               <sort specification> [ { <comma> <sort specification> }... ]
> 
>          <sort specification> ::=
>               <sort key> [ <collate clause> ] [ <ordering specification> ]
> 
>          <sort key> ::=
>               <value expression>
> 
>          <ordering specification> ::= ASC | DESC
> 
> Chris
> 
> > -----Original Message-----
> > From: pgsql-hackers-owner@postgresql.org
> > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Zeugswetter
> > Andreas SB
> > Sent: Tuesday, 22 May 2001 5:33 PM
> > To: 'Jan Wieck'; PostgreSQL HACKERS
> > Subject: AW: [HACKERS] PL/pgSQL CURSOR support
> > 
> > 
> > 
> > >       Explicit cursor can be declared as:
> > > 
> > >           DECLARE
> > >               ...
> > >               curname CURSOR [(argname type [, ...])]
> > >                       IS <select_stmt>;
> > 
> > In esql you would have FOR instead of IS.
> > 
> > DECLARE curname CURSOR ... FOR ....
> > 
> > Thus the question, where is the syntax from ?
> > There seems to be a standard for "the" SQL stored procedure language:
> > 
> > "Persistent Stored Module definition of the ANSI SQL99 standard" 
> > (quote from DB/2)
> > Anybody know this ?
> > 
> > Andreas
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://www.postgresql.org/search.mpl
> > 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 30 May 2001 10:45:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PL/pgSQL CURSOR support

So FOR is standard ANSI, and IS is Oracle, and we went with Oracle. 
Should we allow both?


> Bruce Momjian wrote:
> >
> > Can someone comment on the use of FOR/IS in cursors?
> >
> 
>     DECLARE  <name>  CURSOR IS <select_stmt> is the Oracle PL/SQL
>     syntax. Since PL/pgSQL was written from the  start  with  one
>     eye  on  portability  from/to  Oracle, I'd like to stick with
>     that.
> 
>     It's relatively simple to just substitute  all  PLpgSQL  (and
>     other case combos) occurences by something else, then replace
>     the gram.y and scan.l files with whatever you want and voila,
>     you come up with another procedural language as compatible as
>     possible to your formerly preferred  database.  There  is  no
>     reason  other  than  that  we'll  have  more  PL  handlers to
>     support,  why  we  shouldn't  have  two  or  three  different
>     procedural  SQL dialects. All can coexist and only those used
>     in your DB schema will get loaded.
> 
> 
> Jan
> 
> --
> 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
> 
> 
> 
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 30 May 2001 12:15:02 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PL/pgSQL CURSOR support
Bruce Momjian wrote:
>
> Can someone comment on the use of FOR/IS in cursors?
>

    DECLARE  <name>  CURSOR IS <select_stmt> is the Oracle PL/SQL
    syntax. Since PL/pgSQL was written from the  start  with  one
    eye  on  portability  from/to  Oracle, I'd like to stick with
    that.

    It's relatively simple to just substitute  all  PLpgSQL  (and
    other case combos) occurences by something else, then replace
    the gram.y and scan.l files with whatever you want and voila,
    you come up with another procedural language as compatible as
    possible to your formerly preferred  database.  There  is  no
    reason  other  than  that  we'll  have  more  PL  handlers to
    support,  why  we  shouldn't  have  two  or  three  different
    procedural  SQL dialects. All can coexist and only those used
    in your DB schema will get loaded.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Wed, 30 May 2001 12:17:29 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: PL/pgSQL CURSOR support
Jan Wieck writes:

>     There  is  no
>     reason  other  than  that  we'll  have  more  PL  handlers to
>     support,

... which is a pretty big reason ...

>     why  we  shouldn't  have  two  or  three  different
>     procedural  SQL dialects. All can coexist and only those used
>     in your DB schema will get loaded.

Or you can make one PL support alternative, non-conflicting dialects.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Wed, 30 May 2001 20:30:51 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: PL/pgSQL CURSOR support
On Wed, 30 May 2001, Bruce Momjian wrote:
>
> So FOR is standard ANSI, and IS is Oracle, and we went with Oracle.
> Should we allow both?
>

I have the opinion that PostgreSQL should always support ANSI first and
*then* go ahead and add the features that are becoming the standard due
to inclusion in a major database system like Oracle.  As such, I think it
would be wise to allow both.

-- 
//========================================================\\
||  D. Hageman                    <dhageman@dracken.com>  ||
\\========================================================//


Wed, 30 May 2001 13:33:33 -0500 (CDT)
"D. Hageman" <dhageman@dracken.com>
Re: PL/pgSQL CURSOR support
>     DECLARE  <name>  CURSOR IS <select_stmt> is the Oracle PL/SQL
>     syntax. Since PL/pgSQL was written from the  start  with  one
>     eye  on  portability  from/to  Oracle, I'd like to stick with
>     that.
> 
>     It's relatively simple to just substitute  all  PLpgSQL  (and
>     other case combos) occurences by something else, then replace
>     the gram.y and scan.l files with whatever you want and voila,
>     you come up with another procedural language as compatible as
>     possible to your formerly preferred  database.  There  is  no
>     reason  other  than  that  we'll  have  more  PL  handlers to
>     support,  why  we  shouldn't  have  two  or  three  different
>     procedural  SQL dialects. All can coexist and only those used
>     in your DB schema will get loaded.

OK, how about this patch that allows both FOR and IS.  Seems like a
good idea, and we can document FOR.

Also, I don't see any documentation on the new plpgsql cursor support.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.19
diff -c -r1.19 gram.y
*** src/pl/plpgsql/src/gram.y	2001/05/21 14:22:18	1.19
--- src/pl/plpgsql/src/gram.y	2001/05/30 20:05:03
***************
*** 355,361 ****
  					{
  						plpgsql_ns_rename($2, $4);
  					}
! 				| decl_varname K_CURSOR decl_cursor_args K_IS K_SELECT decl_cursor_query
  					{
  						PLpgSQL_var *new;
  						PLpgSQL_expr *curname_def;
--- 355,361 ----
  					{
  						plpgsql_ns_rename($2, $4);
  					}
! 				| decl_varname K_CURSOR decl_cursor_args decl_is_from K_SELECT decl_cursor_query
  					{
  						PLpgSQL_var *new;
  						PLpgSQL_expr *curname_def;
***************
*** 499,505 ****
  						plpgsql_ns_push(NULL);
  					}
  				;
! 				
  
  decl_aliasitem	: T_WORD
  					{
--- 499,507 ----
  						plpgsql_ns_push(NULL);
  					}
  				;
! 
! decl_is_from	:	K_IS |
! 					K_FOR;
  
  decl_aliasitem	: T_WORD
  					{
Wed, 30 May 2001 16:09:07 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PL/pgSQL CURSOR support
Peter Eisentraut wrote:
> Jan Wieck writes:
>
> >     There  is  no
> >     reason  other  than  that  we'll  have  more  PL  handlers to
> >     support,
>
> ... which is a pretty big reason ...
>
> >     why  we  shouldn't  have  two  or  three  different
> >     procedural  SQL dialects. All can coexist and only those used
> >     in your DB schema will get loaded.
>
> Or you can make one PL support alternative, non-conflicting dialects.

Hmmm,

    combining  it,  we  need a place to tell the language handler
    about it's personality. So the handler  in  plpgsql.so  could
    serve  more  than  one  dialect  and just jump into different
    gram.y path's. Note that it already does kinda that by faking
    a first token telling if actually a function or trigger get's
    compiled.

    Will sleep over that idea.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Wed, 30 May 2001 16:09:57 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: PL/pgSQL CURSOR support
Jan has approved the following patch that allows both FOR and IS for
PL/PgSQL cursors.

> >     DECLARE  <name>  CURSOR IS <select_stmt> is the Oracle PL/SQL
> >     syntax. Since PL/pgSQL was written from the  start  with  one
> >     eye  on  portability  from/to  Oracle, I'd like to stick with
> >     that.
> > 
> >     It's relatively simple to just substitute  all  PLpgSQL  (and
> >     other case combos) occurences by something else, then replace
> >     the gram.y and scan.l files with whatever you want and voila,
> >     you come up with another procedural language as compatible as
> >     possible to your formerly preferred  database.  There  is  no
> >     reason  other  than  that  we'll  have  more  PL  handlers to
> >     support,  why  we  shouldn't  have  two  or  three  different
> >     procedural  SQL dialects. All can coexist and only those used
> >     in your DB schema will get loaded.
> 
> OK, how about this patch that allows both FOR and IS.  Seems like a
> good idea, and we can document FOR.
> 
> Also, I don't see any documentation on the new plpgsql cursor support.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.19
diff -c -r1.19 gram.y
*** src/pl/plpgsql/src/gram.y	2001/05/21 14:22:18	1.19
--- src/pl/plpgsql/src/gram.y	2001/05/30 20:05:03
***************
*** 355,361 ****
  					{
  						plpgsql_ns_rename($2, $4);
  					}
! 				| decl_varname K_CURSOR decl_cursor_args K_IS K_SELECT decl_cursor_query
  					{
  						PLpgSQL_var *new;
  						PLpgSQL_expr *curname_def;
--- 355,361 ----
  					{
  						plpgsql_ns_rename($2, $4);
  					}
! 				| decl_varname K_CURSOR decl_cursor_args decl_is_from K_SELECT decl_cursor_query
  					{
  						PLpgSQL_var *new;
  						PLpgSQL_expr *curname_def;
***************
*** 499,505 ****
  						plpgsql_ns_push(NULL);
  					}
  				;
! 				
  
  decl_aliasitem	: T_WORD
  					{
--- 499,507 ----
  						plpgsql_ns_push(NULL);
  					}
  				;
! 
! decl_is_from	:	K_IS |		/* Oracle */
! 					K_FOR;		/* ANSI */
  
  decl_aliasitem	: T_WORD
  					{
Thu, 31 May 2001 13:16:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PL/pgSQL CURSOR support