PostgreSQL 8.2: Require specific column to be pres

问题:

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

...will succeed.

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

I used condition

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

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

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

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

回答1:

I'd use a security definer function, owned by the only user besides admin and/or table owner, that has update privilege on sometbl.

Something like this:

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;

But be careful about security definer functions security.

回答2:

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

回答3:

You could create a second table that contains your local and remote entries with an ID value, then simply use a not-null foreign key to that table in the first table.

回答4:

You could create a couple of stored procedures, a la

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

回答5:

OK, having read the docs and tried it out, I can report that a BEFORE trigger is the way to go. A trigger provides the names NEW and OLD bound to the new and former tuples. Returning NULL from a BEFORE trigger prevents the update. Hence:

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