PostgreSQL 8.2:要求特定列出现在 UPDATE 语句中 [英] PostgreSQL 8.2: Require specific column to be present in UPDATE statement

问题描述

我想强制用户指定某个表(sometbl)的更新来源，例如.指定 'local' 或 'remote' (for col2) - 但是当执行 UPDATE 语句时，应在 DB 级别检查该要求，因此:

I would like to force user to specify origin of update to some table (sometbl), eg. to specify 'local' or 'remote' (for col2) - but checking of that requirement should occur at DB level when UPDATE statement is executed so:

UPDATE sometbl SET col1 = 'abc';

应该抛出错误(异常)，但是:

should throw error (exception), but:

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

...会成功的.

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

I tried to create BEFORE update trigger for that table, but I was unable to check if NEW.col2 was explictly set.

我使用了条件

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

but every time, when col2 was not specified in update (UPDATE sometbl SET col1 = 'abc')

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

I got current value of that field in NEW.col2 pseudo-var, instead of supposed NULL.

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

Is there any workaround to prevent UPDATING row when specified field is not present in UPDATE stmt?

推荐答案

几个触发器?一个运行之前更新，并将列设置为空.如果该列仍然为空，则在 更新和呕吐(返回 NULL)之后运行.(如果触发器返回 NULL，则更新失败.)

How about a couple of triggers? One runs before the update, and sets the column to null. One runs after the update and pukes (returns NULL) if the column is still null. (If a trigger returns NULL, the update fails.)

这篇关于PostgreSQL 8.2:要求特定列出现在 UPDATE 语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！