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Ü -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
