Re: How to find LIMIT in SQL standard - Mailing list pgsql-general

Thanks for the reply, Stephan. Sorry about the late reply.

Your dissection of the standard (and thanks for the translation, by
the way!) is a clear reason to use unique row ids. Unfortunately the
project I am reporting bugs on does not do this at present.

Many kind regards,
Rory

On 22/09/03, Stephan Szabo (sszabo@megazone.bigpanda.com) wrote:
> On Mon, 22 Sep 2003, Rory Campbell-Lange wrote:
>
> > I have to file a bug about the implementation of some delivery report
> > database calls in the Kannel (open source WAP/SMS) server code.
> >
> > Essentially the call (as defined below) asks for an update and adds a
> > LIMIT parameter on the end of the UPDATE. (eg update where x=1 limit 1).
> > Postgres doesn't like this and I assume it isn't SQL standards
> > compliant and need to refer to this in my bug report.
>
> Well, for SQL92, it looks like the correct section to start in is
> 13.10 (update statement: searched) which looks like:
>
> UPDATE <table name>
>  SET <set clause list>
>  WHERE <search condition>
>
> The last of those is the interesting one which is 8.12 (search condition)
>
>
>          <search condition> ::=
>                 <boolean term>
>               | <search condition> OR <boolean term>
>
>          <boolean term> ::=
>                 <boolean factor>
>               | <boolean term> AND <boolean factor>
>
>          <boolean factor> ::=
>               [ NOT ] <boolean test>
>
>          <boolean test> ::=
>               <boolean primary> [ IS [ NOT ] <truth value> ]
>
>          <truth value> ::=
>                 TRUE
>               | FALSE
>               | UNKNOWN
>
>          <boolean primary> ::=
>                 <predicate>
>               | <left paren> <search condition> <right paren>
>
>
> Then 8.1 (predicate)
>
>          <predicate> ::=
>                 <comparison predicate>
>               | <between predicate>
>               | <in predicate>
>               | <like predicate>
>               | <null predicate>
>               | <quantified comparison predicate>
>               | <exists predicate>
>               | <unique predicate>
>               | <match predicate>
>               | <overlaps predicate>
>
> Also, since tables are effectively unordered, unless the other
> where conditions are guaranteed to get a single row anyway which
> row is modified is fairly indeterminate; this is only interesting
> because it means that you don't necessarily get the same row
> as a previous select (if any) would get. That's not always important,
> but since update also has no way to order the rows that I know of,
> if it were important you couldn't really get around it.