On 2014-09-03 23:19, Hannu Krosing wrote:
> 1. Conditions for number of rows returned by SELECT or touched by UPDATE
> or DELETE
> ---------------------------------------------------------------------------------------------------------
>
> Enforcing number of rows returned/affected could be done using the
> following syntax which is concise and clear (and should be in no way
> backwards incompatible)
>
> SELECT[1] - select exactly one row, anything else raises error
> SELECT[0:1] - select zero or one rows, anything else raises error
> SELECT[1:] - select one or more rows
>
> plain SELECT is equivalent to SELECT[0:]
>
> same syntax could be used for enforcing sane affected row counts
> for INSERT and DELETE
I'm not sure how much I like that syntax in cases like:
WITH t AS ( -- multi-line query here ) SELECT[0:] foo, bar INTO _bat, _man FROM foo JOIN .. JOIN .. WHERE
.. -- etc.
It seems quite well hidden compared to a single keyword at the beginning
of the query.
It's also not clear whether all of this flexibility is required.
Enforcing "exactly one" conveniently is my main priority. Supporting
the "at most one" case could be nice, too, but anything else feels like
overkill. Though if the syntax is based on numbers (and not a keyword),
then I guess we get the flexibility for free anyway.
I also have my doubts about how easy it would be to implement this
syntax given that we're using the "real" SQL parser.
.marko