PostgreSQL 8.2：要求 UPDATE 语句中存在特定列

Question

我想强制用户指定某个表（sometbl）的更新来源， 例如。指定“本地”或“远程”（对于 col2） - 但在执行 UPDATE 语句时应在数据库级别检查该要求，因此：

UPDATE sometbl SET col1 = 'abc';

应该抛出错误（异常），但是：

UPDATE sometbl SET col1 = 'abc', col2 = 'remote';

...会成功的。

我尝试为该表创建 BEFORE update 触发器，但我无法检查是否 NEW.col2 已明确设置。

我使用了条件

IF NEW.col2 IS NULL THEN 
   RAISE EXCEPTION 'you must specify source of this update (local/remote)'
END IF;

，但每次当更新中未指定 col2 (UPDATE sometbl SET col1 = 'abc') 时，

我都会在 NEW.col2 伪变量中获得该字段的当前值，而不是假定的 NULL。

当 UPDATE stmt 中不存在指定字段时，是否有任何解决方法可以阻止 UPDATING 行？

Answer 1

我将使用一个安全定义器函数，该函数由除管理员和/或表所有者之外的唯一用户拥有，该用户对sometbl具有更新权限。

像这样的事情：

create table sometbl (
  id serial primary key,
  col1 text,
  col2 text not null,
    check (col2 in ('local','remote'))
);
create role sometbl_updater;
grant update on sometbl to sometbl_updater;

create function update_sometbl(integer, text, text)
returns void as
$
  update sometbl set col1=$2, col2=$3 where id=$1;
$ security definer volatile language sql;
alter function update_sometbl(integer, text, text)
  owner to sometbl_updater;

但是要小心 安全定义器功能安全。

Answer 2

几个触发器怎么样？其中一个在更新之前运行，并将该列设置为空。如果列仍然为空，则在更新后运行并进行 pukes（返回 NULL）。 （如果触发器返回 NULL，则更新失败。）

Answer 3

您可以创建第二个表，其中包含具有 ID 值的本地和远程条目，然后只需在第一个表中使用该表的非空外键。

Answer 4

您可以创建几个存储过程，例如

create or replace function update_remote(text) returns void
    as 'update sometbl SET col1 = $1, col2 = ''remote'''
    language SQL
    volatile
    strict;

Answer 5

好的，阅读文档并尝试后，我可以报告 BEFORE 触发器是可行的方法。触发器提供绑定到新元组和旧元组的名称 NEW 和 OLD。从 BEFORE 触发器返回 NULL 会阻止更新。因此：

CREATE OR REPLACE FUNCTION prevent_not_changing_col2()
  RETURNS trigger AS $
begin
  if NEW.col2 = OLD.col2 then return NULL; end if;
  return NEW;
end ;
$ LANGUAGE plpgsql;

CREATE TRIGGER col2_check
  BEFORE UPDATE
  ON sometbl
  FOR EACH ROW
  EXECUTE PROCEDURE prevent_not_changing_col2();