UPDATE/DELETE with ORDER BY and LIMIT

Hi guys,

I've found some posts from 2007 about UPDATE/DELETE ... LIMIT N syntax
and I'd like to raise it again. Current PostgreSQL of UPDATE/DELETE
implement RETURNING statement, so extending by ORDER and LIMIT would be
really useful.

Let's say we need to reserve some kind of identifier for user.

CREATE TABLE slots (
    id INTEGER UNIQUE NOT NULL,
    user VARCHAR(32),
    expires TIMESTAMP WITH TIMEZONE,
    -- some other columns
);

-- We have 4 identifiers to use:
INSERT INTO slots VALUES (1);
INSERT INTO slots VALUES (2);
INSERT INTO slots VALUES (3);
INSERT INTO slots VALUES (4);

-- Now the clue: we want to reserve unused slot:
UPDATE slots SET user='joe' WHERE user IS NULL
    ORDER BY id LIMIT 1 RETURNING *;

-- Or another approach: reserve slot for 30 minutes:
UPDATE slots SET expires=now()+INTERVAL '30 minutes'
    WHERE expires<now() ORDER BY id LIMIT 1 RETURNING *;

Our query will return:
1. all data of the slot that have been reserved
2. no rows, if there is no free slot.

All that with just one query. In this specific example, the ORDER BY
statement could be even omitted if we don't care how slots are
distributed between users.


Comments are welcome.


Best regards,

--
Bartłomiej Korupczyński <bartek-fora@klolik.org>