On 2014-09-03 23:19, Hannu Krosing wrote:
1. Conditions for number of rows returned by SELECT or touched by UPDATE

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

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.


Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to