PL/pgSQL CURSOR support
Hi folks,
I just committed changes to the SPI manager and PL/pgSQL,
providing full CURSOR support. A detailed description is
attached as a Postscript file. Brief description follows.
#
# Note:
# The version with the attachment didn't make it up to now.
# Resending it without.
#
Enhancement of SPI:
There are a couple of new functions and internal changes to
the SPI memory management. SPI now creates separate memory
contexts for prepared and saved plans and tuple result
sets. The contexts are children of where the allocations
used to happen, so it's fully upgrade compatible. New
functions SPI_freeplan(plan) and SPI_freetuptable(tuptab)
allow to simply destroy the contexts when no longer needed.
The other new functions deal with portals:
Portal
SPI_cursor_find(char *name);
Get an existing portal by name
Portal
SPI_cursor_open(char *name, void *plan,
Datum *Values, char *Nulls);
Use a prepared or saved SPI plan to create a new
portal. if <name> is NULL, the function will make up
a unique name inside the backend. A portal created by
this can be accessed by the main application as well
if SPI_cursor_open() was called inside of an explicit
transaction block.
void
SPI_cursor_fetch(Portal portal, bool forward, int count);
Fetch at max <count> tuples from <portal> into the
well known SPI_tuptable and set SPI_processed.
<portal> could be any existing portal, even one
created by the main application using DECLARE ...
CURSOR.
void
SPI_cursor_move(Portal portal, bool forward, int count);
Same as fetch but suppress tuples.
void
SPI_cursor_close(Portal portal);
Close the given portal. Doesn't matter who created it
(SPI or main application).
New datatype "refcursor"
A new datatype "refcursor" is created as a basetype, which
is equivalent to "text". This is required below.
Enhancement of PL/pgSQL
Explicit cursor can be declared as:
DECLARE
...
curname CURSOR [(argname type [, ...])]
IS <select_stmt>;
...
The <select_stmt> can use any so far declared variable or
positional function arguments (possibly aliased). These
will be evaluated at OPEN time.
Explicit cursor can be opened with:
BEGIN
...
OPEN curname [(expr [, ...])];
...
The expression list is required if and only if the explicit
cursor declaration contains an argument list. The created
portal will be named 'curname' and is accessible globally.
Reference cursor can be declared as:
DECLARE
...
varname REFCURSOR;
...
and opened with
BEGIN
...
OPEN varname FOR <select_stmt>;
-- or
OPEN varname FOR EXECUTE <string expression>;
...
The type "refcursor" is a datatype like text, and the
variables "value" controls the "name" argument to
SPI_cursor_open(). Defaulting to NULL, the resulting portal
will get a generic, unique name and the variable will be
set to that name at OPEN. If the function assigns a value
before OPEN, that'll be used as the portal name.
Cursors (of both types) are used with:
BEGIN
...
FETCH cursorvar INTO {record | row | var [, ...]};
...
CLOSE cursorvar;
FETCH sets the global variable FOUND to flag if another row
is available. A typical loop thus looks like this:
BEGIN
OPEN myrefcur FOR SELECT * FROM mytab;
LOOP
FETCH myrefcur INTO myrow;
EXIT WHEN NOT FOUND;
-- Process one row
END LOOP;
CLOSE myrefcur;
The "refcursor" type can be used for function arguments or
return values as well. So one function can call another to
open a cursor, assigning it's return value to a
"refcursor", pass that down to other functions and - you
get the 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
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
Import Notes
Resolved by subject fallback
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)
Show quoted text
Anybody know this ?
Andreas
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
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
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
Show quoted text
-----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 supportExplicit 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?
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 supportExplicit 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?---------------------------(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 19026So 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 19026Import Notes
Reply to msg id not found: 200105301617.f4UGHTB01795@jupiter.us.greatbridge.com | Resolved by subject fallback
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
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
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> ||
\\========================================================//
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 19026Attachments:
/bjm/difftext/plainDownload
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
{
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
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 19026Attachments:
/pgpatches/plisfortext/plainDownload
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
{