Thread: AW: 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
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
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
>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
>
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,
Pennsylvania19026
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, thenreplace the gram.y and scan.l files with whatever you want and voila, you come up with another procedural languageas 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
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
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 doeskinda 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
> 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 {
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 {