PostgreSQL: keep a certain number of records in a table
- by Alexander Farber
Hello,
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?
Thank you! Alex
