Re: PL/pgSQL 1.2 - Mailing list pgsql-hackers

From Marko Tiikkaja
Subject Re: PL/pgSQL 1.2
Date
Msg-id 54079371.9090804@joh.to
Whole thread Raw
In response to Re: PL/pgSQL 1.2  (Hannu Krosing <hannu@2ndQuadrant.com>)
Responses Re: PL/pgSQL 1.2
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: B-Tree support function number 3 (strxfrm() optimization)
Next
From: Bruce Momjian
Date:
Subject: Re: PQputCopyEnd doesn't adhere to its API contract