Thread: Re: [GENERAL] Fun with Cursors- how to rewind a cursor
"Postgres User" <postgres.developer@gmail.com> writes:
> On 3/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Postgres User" <postgres.developer@gmail.com> writes:
>>> <before opening cursor> ref_entry = 'c_entry';
>>> <after looping thru cursor> MOVE Backward All In c_entry;
>>
>> You have to use EXECUTE for the latter.
> I had tried several variations of MOVE Backward inside an Execute
> statement earlier. And now, I'm seeing this error appear again:
> ERROR: 0A000: cannot manipulate cursors directly in PL/pgSQL
Hm, you're right. This arises from the fact that _SPI_execute_plan
rejects cursor-related utility statements. While I'd never stopped
to question that before, it does seem like this restriction is a
bit pointless. Does anyone remember why it's like that?
regards, tom lane
Tom Lane wrote: > "Postgres User" <postgres.developer@gmail.com> writes: > > On 3/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> "Postgres User" <postgres.developer@gmail.com> writes: > >>> <before opening cursor> ref_entry = 'c_entry'; > >>> <after looping thru cursor> MOVE Backward All In c_entry; > >> > >> You have to use EXECUTE for the latter. > > > I had tried several variations of MOVE Backward inside an Execute > > statement earlier. And now, I'm seeing this error appear again: > > ERROR: 0A000: cannot manipulate cursors directly in PL/pgSQL > > Hm, you're right. This arises from the fact that _SPI_execute_plan > rejects cursor-related utility statements. While I'd never stopped > to question that before, it does seem like this restriction is a > bit pointless. Does anyone remember why it's like that? Is there anything to do on this item? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes: > Tom Lane wrote: >> Hm, you're right. This arises from the fact that _SPI_execute_plan >> rejects cursor-related utility statements. While I'd never stopped >> to question that before, it does seem like this restriction is a >> bit pointless. Does anyone remember why it's like that? > Is there anything to do on this item? I dug in the archives and realized that SPI was originally written by Vadim, not Jan as I'd been thinking, so there's nobody left on the project who has any special insight into this. I found this message describing it: http://archives.postgresql.org/pgsql-hackers/1997-08/msg00338.php wherein Vadim says >>> 4. I have some code for server programming interface (SPI) >>> which allows to run queries from user defined C-functions. >>> With current postgres limitations (no nested transactions, cursors >>> inside BEGIN/END only) SPI disallows using of BEGIN/END & cursors. >>> It's bad for procedures but it's enough for triggers! so it seems he saw this just as an implementation restriction rather than a fundamental property of SPI. And I don't see why cursors being within-transaction only means SPI shouldn't touch them --- maybe he just wasn't thinking carefully about that. I'd venture that we should try to get rid of the restriction, but I'm unsure whether removing the error check is sufficient or whether there are real problems it's preventing. regards, tom lane
I wrote:
> I'd venture that we should try to get rid of the restriction, but I'm
> unsure whether removing the error check is sufficient or whether there
> are real problems it's preventing.
I did a little experimentation and it seems that DECLARE CURSOR,
FETCH, and CLOSE work perfectly fine when executed through SPI
(after diking out the error check). So I see no reason to have
that prohibition in place --- we may as well just take it out and
save a few lines of code.
regards, tom lane