On 09/04/2014 12:17 AM, Marko Tiikkaja wrote:
> 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.
What do you have in mind ?
Is your wiki page already available somewhere ?
>
> It's also not clear whether all of this flexibility is required.
> Enforcing "exactly one" conveniently is my main priority.
What do you want here on top of SELECT ... INTO STRICT ... ?
> 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.
Definitely not trivial, but at least doable :)
Finding and processing SELECT[...] could probably even be done with
a (regex-based ?) pre-parser .
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ
