在插入表之前更改值/PostgreSQL

Question

我有一个问题。我想改变一些INSERT语句的值，例如下面的查询：

INSERT INTO table(a,b) values(x,y);

但是表格还有另一列c。我想在插入之前检查上面查询中b的值，并根据其值设置c=z，但仅适用于刚刚插入的这一行。

我认为一些触发器可以帮助解决我的问题，但我不知道如何做到这一点。任何伪代码将不胜感激。

- kozooh

Answer 1

限制普通用户直接访问表格是良好的实践，可以通过使用存储过程实现，并仅授予它们访问权限。例如（代码有点长，请见谅）：

create table foo (
    a integer,
    b integer,
    c integer);

--== Create table. Yes, it is no PK, but for example it is not necesarry

create or replace function p_foo_insert(in aa integer, in ab integer) returns integer as
$$
declare
    result integer;
begin
    insert into foo (a, b, c) values (aa, ab, aa + ab) returning c into result;
    return result;
end; $$ language plpgsql SECURITY DEFINER;

--== It is function for row insertion. Inside this function you can do any data manipulation.

select p_foo_insert(1, 2);

--== Test statement. Result must be a + b = 1 + 2 = 3

所以，忘掉insert into，使用存储过程 :o)

PS：关于SECURITY DEFINER选项的说明。它允许授予函数访问权限，而无需授予其内部使用的对象的访问权限。

- Abelisto

---

太好了！谢谢@Abelisto，这正是我需要的。顺便说一下，我之前写了一些触发器，希望它们也能起作用，但后来错误的查询破坏了触发器实际上所做的事情。我在这上面浪费了很多时间，呵呵:) - kozooh

希望我的回答有所帮助。顺便说一下，我看到您将问题标记为“postgresql-8.4”。我不确定确切的情况，但在某些9.x分支中，PG至少会在数据插入方面提高2倍的速度。因此，请尝试升级: o) - Abelisto

Answer 2

您可以在 INSERT 语句中完成此操作，无需触发器。

给定表格 t：

CREATE TEMP TABLE t (a int, b int, c int)

c的默认值是NULL。
考虑下面的示例：

WITH i(x,y,z) AS (
    VALUES
     (1, 2, 111)
    ,(1, 7, 666)
    )
INSERT INTO t(a,b,c)
SELECT x, y, CASE WHEN y = 2 THEN z ELSE NULL END
FROM   i
RETURNING *

WITH （需要Postgres 9.1+）和RETURNING从测试方便性考虑而存在。
重要的部分是带有CASE语句的SELECT。
这个测试应该适用于pg 8.4：

INSERT INTO t(a,b,c)
SELECT x, y, CASE WHEN y = 2 THEN z ELSE NULL END
FROM   (
    VALUES
     (1, 2, 111)
    ,(1, 7, 666)
    ) i (x,y,z)
RETURNING *

- Erwin Brandstetter

---

谢谢您提醒我使用 with 子句。对于 Postgres 来说，这是一个宝石级别的特性，作为 Oracle 程序员，我总是忘记使用它 :o) 但我不确定它是否适用于源问题。 - Abelisto

@Abelisto：根据所给的问题，该解决方案是合适的。对于这种简单情况，plpgsql函数可能过于复杂了。如果您需要一个函数，请将CASE语句包装在SQL函数中。 - Erwin Brandstetter

“对于这种简单的情况，使用plpgsql函数可能有点大材小用。” - 是的，我同意。 但这是我们的惯例：限制常规用户/程序员对DB对象的访问，除了存储过程。 这很方便，很安全，并有助于在服务器端保留应用逻辑。 - Abelisto