Subject Re: Cursor FETCH in PSQL and ROW_COUNT/'fetch past last record' error
Author Dmitry Yemanov
27.03.2017 11:23, 'Martijn Tonies wrote:
> The FOR SELECT loop returns 1..10 as the values.
> The fetch/suspend lines return 1..10, with a row count of 1, and another
> ‘10’ with a row count of 0.
> If I add another FETCH, I get this error while executing the routine:
>>>attempt to fetch past the last record in a record stream At procedure
> 'DEBUG_CURSOR_TESTS' line: 42, col: 3
> And here’s my question: the last fetch has a row_count of 0, another
> fetch returns an error. However, if I make this cursor a scrollable
> cursor, and my first ‘fetch’ is a ‘fetch prior’, I immediately get this
> error:
>>>attempt to fetch before the first record in a record stream At
> procedure 'DEBUG_CURSOR_TESTS' line: 9, col: 3
> I get the ‘past last record’ error when I use:
> fetch absolute 11 from c into :p_out1;
> I wonder, should the first fetch next that returns no data, return
> row_count=0, or also the ‘fetch past record stream’ error?

AFAIU, this is expected. When the cursor moves past the last record, "no
data" condition is returned. In API, this means fetch() returning 100
(EOF marker). Inside PSQL, row_count is set to 0 that also indicates
EOF. However, moving past EOF is not supported and it throws an error.
It may look counter-intuitive to someone, but this is how our engine
always worked (internally or via API).