Keep a certain number of records in a table

Question

I have an SQL-table holding the last hands received by a player in card game. The hand is represented by an integer (32 bits == 32 cards):

create table pref_hand (
        id varchar(32) references pref_users,
        hand integer not NULL check (hand > 0),
        stamp timestamp default current_timestamp
);

As the players are playing constantly and that data isn't important (just a gimmick to be displayed at player profile pages) and I don't want my database to grow too quickly, I'd like to keep only up to 10 records per player id. So I'm trying to declare this PL/PgSQL procedure:

create or replace function pref_update_game(_id varchar,
    _hand integer) returns void as $BODY$
        begin

        delete from pref_hand offset 10 where id=_id order by stamp;
        insert into pref_hand (id, hand) values (_id, _hand);

        end;
$BODY$ language plpgsql;

but unfortunately this fails with:

ERROR:  syntax error at or near "offset"

because delete doesn't support offset. Does anybody please have a better idea here?

Answer 1

Something like this (not tested!)

DELETE FROM pref_hand
WHERE id = _id 
  AND stamp in (SELECT h2.stamp 
                FROM pref_hand h2
                WHERE h2.id = _id
                ORDER BY stamp DESC
                OFFSET 10);