在表中保留一定数量的记录

发布于 2024-10-12 02:18:35 字数 815 浏览 9 评论 0原文

我有一个 SQL 表保存纸牌游戏中玩家收到的最后一手牌。手牌由整数表示（32 位 == 32 张牌）：

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

由于玩家不断玩牌，因此数据并不重要（只是在玩家个人资料页面上显示的噱头），而且我不希望我的数据库增长太快，我只想为每个玩家 ID 保留最多 10 条记录。所以我试图声明这个 PL/PgSQL 过程：

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;

但不幸的是，这失败了：

ERROR:  syntax error at or near "offset"

因为删除不支持偏移量。请问有人有更好的主意吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忆离笙 2024-10-19 02:18:35

像这样的东西（未经测试！）

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

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

回复收藏 0 原文

叫思念不要吵 2024-10-19 02:18:35

这将正确处理 stamp 上的重复项（如果有）

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

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 原文

~没有更多了~