向 PostgreSQL 存储过程添加逻辑

Question

我正在使用 PostgreSQL (8.3+) 并定义了一个枚举和一个表，如下所示：

CREATE TYPE "viewer_action" AS ENUM ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H');

CREATE TABLE "preferences"  ( 
    "user_id"       integer NOT NULL,
    "item_id"       integer NOT NULL,
    "rating"        viewer_action NOT NULL,
    "time_created"  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("user_id","video_id")
);

我还创建了一个存储过程，使用以下示例将新行更新到首选项表中 http://www.postgresql.org /docs/current/static/plpgsql-control-structs.html#PLPGSQL-UPSERT-EXAMPLE：

CREATE OR REPLACE FUNCTION add_preference(u INT, i INT, r viewer_action) RETURNS VOID AS $add_preference$
    BEGIN
        LOOP
            -- first try to update the key
            UPDATE preferences SET rating = r WHERE user_id = u AND item_id = i;
            IF found THEN
                RETURN;
            END IF;
            -- not there, so try to insert the key
            -- if someone else inserts the same key concurrently,
            -- we could get a unique-key failure
            BEGIN
                INSERT INTO preferences(user_id,item_id,rating) VALUES (u,i,r);
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing, and loop to try the UPDATE again
            END;
        END LOOP;
    END;
$add_preference$ LANGUAGE plpgsql;

我需要向 upsert 添加一些额外的逻辑，以防止某些值覆盖其他值。具体来说：

• A 可以被 B 覆盖，B 可以被 C 覆盖，C 可以被 D 覆盖，以此类推，通过 F。但是 B 不能被 A 覆盖，C 也不能被 B 覆盖，等等。
• F、G 或H 可以覆盖任何值，无论现有值是更低还是更高。

在伪代码中，这可能看起来像：

if (rating >= F) {
    insert;
} else if (rating > existing_rating) {
    insert;
} else {
    return;
}

Answer 1

在此表上创建一个 ON BEFORE INSERT OR UPDATE 触发器。为此，您可以使用您的函数，进行一些小的更改，例如将返回类型更改为 TRIGGER。

BEFORE 触发器在将行插入表之前触发，因此您可以在写入数据之前检查数据。

有关触发器的更多信息，您可以在这里找到：
http://www.postgresql.org/docs/9.0/interactive/plpgsql -trigger.html

Answer 2

查看方法是 CASE http: //www.postgresql.org/docs/8.2/static/functions-conditional.html#AEN13066 实现它应该不会那么困难。正如 @szymon-guz 建议的那样，将其放入触发器中。

Answer 3

你不能用案例陈述做点什么吗？

像这样的东西。

--If new rating is greater than existing rating update it else update to current value.
    UPDATE preferences
    SET rating = CASE WHEN r > rating THEN r ELSE rating END;