Re: DELETE with LIMIT (or my first hack)

Hi,

actually introducing LIMIT and OFFSET with ORDER BY to DELETE/UPDATE would
make it much easier to push data from one (say "queue") table to another.
And to fetch chunks of queue entries updating their status in one statement.
Now I have to do SELECT...ORDER BY...LIMIT and then do some magic with
arrays of IDs and updates/deletes or UPDATE ... WHERE id in (SELECT .. ORDER
BY... LIMIT) RETURNING ... to make that work, but this is still possible to
do with the WHERE clause, though I am not quite sure if that is most
efficient in comparison to the direct approach. And speaking about pushing
data from one table to another, what I really would like to be able to do
would be also something like:

INSERT INTO ...
DELETE FROM ... WHERE... ORDER BY.. [LIMIT...]
RETURNING...;

this would be also quite efficient when re-arranging data in table
partitions (though LIMIT/OFFSET there will be just nice to have possibility
for reducing chunk sized of data being moved).

Additionally we need quite often to clean up some log tables depending not
on the timestamps but on the number of rows in that tables, so leaving only
last N newest records in a table... OFFSET would be really cool to have for
that usecase as well...

With best regards,

-- Valentine Gogichashvili