Cannot step through asynchronous iterator manually

[Chris Angelico]

On Sun, Jan 31, 2016 at 6:42 AM, Michael Torrie <torriem at gmail.com> wrote:
> On 01/30/2016 01:22 AM, Frank Millman wrote:
>> There are times when I want to execute a SELECT statement, and test for
>> three possibilities -
>>     - if no rows are returned, the object does not exist
>>     - if one row is returned, the object does exist
>>     - if more that one row is returned, raise an exception
>
> Is there a reason you cannot get SQL to answer this question for you?
> Something like:
>
> SELECT count(some_field) WHERE condition
>
> That will always return one row, with one field that will either be 0,
> 1, or more than 1.

Efficiency. That's a fine way of counting actual rows in an actual
table. However, it's massive overkill to perform an additional
pre-query for something that's fundamentally an assertion (this is a
single-row-fetch API like "select into", and it's an error to fetch
anything other than a single row - but normal usage will never hit
that error), and also, there's no guarantee that the query is looking
at a single table. Plus, SQL's count function ignores NULLs, so you
could get a false result. Using count(*) might be better, but the only
way I can think of to be certain would be something like:

select count(*) from (...)

where the ... is the full original query. In other words, the whole
query has to be run twice - once to assert that there's exactly one
result, and then a second time to get that result. The existing
algorithm ("try to fetch a row - if it fails error; then try to fetch
another - if it succeeds, error") doesn't need to fetch more than two
results, no matter how big the query result is.

ChrisA