Q:

Postgres: Update a query counter once a result is returned

I have a situation where a particular row can only be queried for a fixed number of times, say 1000. After which, it is made unavailable to that particular party permanently. Each query returns only 1 result i.e. LIMIT = 1.
I intend to implement this by having a counter that starts at 1000 and decrement with the number of times it gets queried.
Is there anyway where upon returning that result, that I am able to have its counter is immediately incremented?
This is as opposed to waiting for the result to be received by the application layer and then sending an UPDATE statement to increment the counter. Because between the time the result is returned till the time the UPDATE query is received, there can be another query.

A:

You can use a CTE that does the SELECT and the UPDATE in one query:
CREATE TABLE foo(
    id int primary key,
    content text,
    counter int default 0);

INSERT INTO foo(id, content, counter) VALUES(1, 'foo', default);
INSERT INTO foo(id, content, counter) VALUES(2, 'bar', default);
INSERT INTO foo(id, content, counter) VALUES(3, 'baz', default);

-- select the data and update the counter:
    WITH    step_1 AS (
        SELECT * FROM foo WHERE counter < 5 ORDER BY id LIMIT 1 -- now you can use LIMIT as well
    ), step_2 AS (
        UPDATE foo SET counter = foo.counter + 1 FROM step_1 WHERE foo.id = step_1.id RETURNING foo.*
    )
    SELECT  * FROM step_2;

