Keep a certain number of records in a table
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);
开发者_如何学Go 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?
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);
This one will handle the duplicates on stamp
correctly (if any)
INSERT
INTO pref_hand (id, hand)
VALUES (_id, _hand);
DELETE
FROM pref_hand
WHERE ctid IN
(
SELECT ctid
FROM pref_hand
WHERE id = _id
ORDER BY
stamp
OFFSET 10
);
精彩评论