开发者

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
        );
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜