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