Re: [PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
Added to TODO for pl/pgsql: o Review handling of MOVE and FETCH http://archives.postgresql.org/pgsql-patches/2007-04/msg00527.php --- Pavel Stehule wrote: I would argue that we should likewise not allow them in plpgsql's MOVE, although this is more of a judgment call than is the case for FETCH. I just don't think it's a good idea to provide two redundant ways to do the same thing, when we might want to make one of the ways mean something else later. There's no upside and there might be a downside. It's question. There are lot of links to FETCH in doc, and we support from FETCH direction only subset. It needs at least notice in documentation. When I testeid MOVE I found an form MOVE FORWARD 10 ... more natural than MOVE RELATIVE 10 and if we support MOVE FORWARD ... then is logic support MOVE FORWARD n , else FORWARD, BACKWARD are nonstandard and MOVE statement too. Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Pavel Stehule wrote: I would argue that we should likewise not allow them in plpgsql's MOVE, although this is more of a judgment call than is the case for FETCH. I just don't think it's a good idea to provide two redundant ways to do the same thing, when we might want to make one of the ways mean something else later. There's no upside and there might be a downside. It's question. There are lot of links to FETCH in doc, and we support from FETCH direction only subset. It needs at least notice in documentation. When I testeid MOVE I found an form MOVE FORWARD 10 ... more natural than MOVE RELATIVE 10 and if we support MOVE FORWARD ... then is logic support MOVE FORWARD n , else FORWARD, BACKWARD are nonstandard and MOVE statement too. Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
Do we have a patch to make this consistent? no, not yet. It's topic for discussion and ToDo Regards Pavel Stehule ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
Pavel Stehule wrote: I would argue that we should likewise not allow them in plpgsql's MOVE, although this is more of a judgment call than is the case for FETCH. I just don't think it's a good idea to provide two redundant ways to do the same thing, when we might want to make one of the ways mean something else later. There's no upside and there might be a downside. It's question. There are lot of links to FETCH in doc, and we support from FETCH direction only subset. It needs at least notice in documentation. When I testeid MOVE I found an form MOVE FORWARD 10 ... more natural than MOVE RELATIVE 10 and if we support MOVE FORWARD ... then is logic support MOVE FORWARD n , else FORWARD, BACKWARD are nonstandard and MOVE statement too. Do we have a patch to make this consistent? -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
On Fri, 2007-04-20 at 09:46 +0200, Pavel Stehule wrote: I refreshed Magnus's patch http://archives.postgresql.org/pgsql-patches/2007-02/msg00275.php from februar. Applied, thanks. BTW, I notice that the documentation for PL/PgSQL's FETCH command states that only the direction variants that fetch a *single* row are allowed. This is not actually the case: FETCH RELATIVE 2 FROM c INTO v results in assigning the first row from c into v, and then discarding the second row. Is this the best behavior? At the least, we should describe it in the documentation. p.s. scrollable cursors in plpgsql need little work still. I forgot for nonstandard (postgresql extension) direction forward all, forward n, backward n. Forward all propably hasn't sense. Yes, these are certainly needed for MOVE, and we may as well allow them for FETCH as well. -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
Neil Conway [EMAIL PROTECTED] writes: BTW, I notice that the documentation for PL/PgSQL's FETCH command states that only the direction variants that fetch a *single* row are allowed. This is not actually the case: FETCH RELATIVE 2 FROM c INTO v results in assigning the first row from c into v, and then discarding the second row. What? That's not what it did when I was reviewing the code. It should skip one row and assign the second one to v. p.s. scrollable cursors in plpgsql need little work still. I forgot for nonstandard (postgresql extension) direction forward all, forward n, backward n. Forward all propably hasn't sense. Yes, these are certainly needed for MOVE, and we may as well allow them for FETCH as well. No, because these variants specify returning multiple rows, which is exactly what plpgsql doesn't support. FETCH FORWARD 2 and FETCH RELATIVE 2 are *entirely* different animals, and we shouldn't confuse them. If we do, we'll regret it someday when we'd like to actually offer that functionality somehow in plpgsql. I would argue that we should likewise not allow them in plpgsql's MOVE, although this is more of a judgment call than is the case for FETCH. I just don't think it's a good idea to provide two redundant ways to do the same thing, when we might want to make one of the ways mean something else later. There's no upside and there might be a downside. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
I would argue that we should likewise not allow them in plpgsql's MOVE, although this is more of a judgment call than is the case for FETCH. I just don't think it's a good idea to provide two redundant ways to do the same thing, when we might want to make one of the ways mean something else later. There's no upside and there might be a downside. It's question. There are lot of links to FETCH in doc, and we support from FETCH direction only subset. It needs at least notice in documentation. When I testeid MOVE I found an form MOVE FORWARD 10 ... more natural than MOVE RELATIVE 10 and if we support MOVE FORWARD ... then is logic support MOVE FORWARD n , else FORWARD, BACKWARD are nonstandard and MOVE statement too. Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
Hello
I refreshed Magnus's patch
http://archives.postgresql.org/pgsql-patches/2007-02/msg00275.php from
februar.
Regards
Pavel Stehule
p.s. scrollable cursors in plpgsql need little work still. I forgot for
nonstandard (postgresql extension) direction forward all, forward n,
backward n. Forward all propably hasn't sense.
_
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig 2007-04-20 09:01:50.0 +0200
--- ./doc/src/sgml/plpgsql.sgml 2007-04-20 09:09:04.0 +0200
***
*** 1524,1529
--- 1524,1536
/listitem
listitem
para
+ A commandMOVE/ statement sets literalFOUND/literal
+ true if is success, false if is out of table.
+/para
+ /listitem
+
+ listitem
+para
A commandFOR/ statement sets literalFOUND/literal true
if it iterates one or more times, else false. This applies to
all three variants of the commandFOR/ statement (integer
***
*** 2567,2572
--- 2574,2624
/sect3
sect3
+ titleliteralMOVE//title
+
+ synopsis
+ MOVE optional replaceabledirection/replaceable FROM /optional replaceablecursor/replaceable;
+ /synopsis
+
+ para
+ MOVE repositions a cursor without retrieving any data. MOVE works
+ exactly like the FETCH command, except it only positions the
+ cursor and does not return rows. As with commandSELECT
+ INTO/command, the special variable literalFOUND/literal can
+ be checked to see whether a cursor was repositioned or not.
+ /para
+
+ para
+ The replaceabledirection/replaceable clause can be any of the
+ variants allowed in the SQL xref linkend=sql-fetch
+ endterm=sql-fetch-title command except the ones that can fetch
+ more than one row; namely, it can be
+ literalNEXT/,
+ literalPRIOR/,
+ literalFIRST/,
+ literalLAST/,
+ literalABSOLUTE/ replaceablecount/replaceable,
+ literalRELATIVE/ replaceablecount/replaceable,
+ literalFORWARD/, or
+ literalBACKWARD/.
+ Omitting replaceabledirection/replaceable is the same
+ as specifying literalNEXT/.
+ replaceabledirection/replaceable values that require moving
+ backward are likely to fail unless the cursor was declared or opened
+ with the literalSCROLL/ option.
+ /para
+
+ para
+ Examples:
+ programlisting
+ MOVE curs1;
+ MOVE LAST FROM curs3;
+ MOVE RELATIVE -2 FROM curs4;
+ /programlisting
+/para
+ /sect3
+
+ sect3
titleliteralCLOSE//title
synopsis
*** ./src/pl/plpgsql/src/gram.y.orig 2007-04-19 19:15:28.0 +0200
--- ./src/pl/plpgsql/src/gram.y 2007-04-19 19:39:36.0 +0200
***
*** 125,131
%type stmt stmt_assign stmt_if stmt_loop stmt_while stmt_exit
%type stmt stmt_return stmt_raise stmt_execsql stmt_execsql_insert
%type stmt stmt_dynexecute stmt_for stmt_perform stmt_getdiag
! %type stmt stmt_open stmt_fetch stmt_close stmt_null
%type list proc_exceptions
%type exception_block exception_sect
--- 125,131
%type stmt stmt_assign stmt_if stmt_loop stmt_while stmt_exit
%type stmt stmt_return stmt_raise stmt_execsql stmt_execsql_insert
%type stmt stmt_dynexecute stmt_for stmt_perform stmt_getdiag
! %type stmt stmt_open stmt_fetch stmt_move stmt_close stmt_null
%type list proc_exceptions
%type exception_block exception_sect
***
*** 179,184
--- 179,185
%token K_IS
%token K_LOG
%token K_LOOP
+ %token K_MOVE
%token K_NEXT
%token K_NOSCROLL
%token K_NOT
***
*** 635,640
--- 636,643
{ $$ = $1; }
| stmt_fetch
{ $$ = $1; }
+ | stmt_move
+ { $$ = $1; }
| stmt_close
{ $$ = $1; }
| stmt_null
***
*** 1478,1483
--- 1481,1499
fetch-rec = rec;
fetch-row = row;
fetch-curvar = $4-varno;
+ fetch-is_move = false;
+
+ $$ = (PLpgSQL_stmt *)fetch;
+ }
+ ;
+
+ stmt_move : K_MOVE lno opt_fetch_direction cursor_variable ';'
+ {
+ PLpgSQL_stmt_fetch *fetch = $3;
+
+ fetch-lineno = $2;
+ fetch-curvar = $4-varno;
+ fetch-is_move = true;
$$ = (PLpgSQL_stmt *)fetch;
}
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2007-04-20 09:24:27.0 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2007-04-20 09:25:14.0 +0200
***
*** 3112,3118
return PLPGSQL_RC_OK;
}
-
/* --
* exec_stmt_fetch Fetch from a cursor into a target
* --
--- 3112,3117
***
*** 3164,3208
}
/* --
! * Determine if we fetch into a record or a row
! * --
! */
! if (stmt-rec != NULL)
! rec =
