Postgres 9.2 - добавление проверки условного ограничения

Я использую PostgreSQL 9.2 и вам нужно добавить условное ограничение для столбца. По сути, я хочу убедиться, что столбец является ложным, когда два других столбца имеют определенное значение.

gid          | int_unsigned           | not null default 0
realm        | character varying(255) | not null default ''::character varying
grant_update | smallint_unsigned      | not null default (0)::smallint
grant_delete | smallint_unsigned      | not null default (0)::smallint

Пример:

alter table node_access add constraint block_anonymous_page_edit
check (grant_update = 0 WHERE (gid = 1 AND realm = 'nodeaccess_rid'));

Что это должно сделать, убедитесь, что grant_update равно 0, когда gid равен 1 и realm = nodeaccess_rid. Тем не менее, я думаю, вместо того, чтобы делать то, что хочу, он на самом деле пытается сделать все столбцы имитирующими эти значения. По сути, он пытается удостовериться, что grant_update всегда 0, gid всегда 1, а realm всегда nodeaccess_rid. Ошибка, которую я получаю:

ERROR:  check constraint "block_anonymous_page_edit" is violated by some row

ИЗМЕНИТЬ

Я думаю, что это будет функция, которая запускается при обновлении.

ИЗМЕНИТЬ

Я добавил строку к указанному выше вопросу и, следовательно, обновил утвержденное решение с комментарием ниже.

Ответ 1

Как только вы обманетесь вокруг логики, это довольно простое ограничение CHECK:

CREATE TABLE tbl (
   gid          int      NOT NULL DEFAULT 0
  ,realm        text     NOT NULL DEFAULT ''
  ,grant_update smallint NOT NULL DEFAULT 0
  ,CHECK (gid <> 1
          OR realm <> 'nodeaccess_rid'
          OR grant_update = 0)
);

Тест:

INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 0);          -- works

INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 1);           -- check violation!

INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'some_string',    1);           -- works

INSERT INTO tbl(gid, realm, grant_update)
VALUES (2, 'nodeaccess_rid', 1);           -- works

Ответ 2

Я бы написал это как триггер. Это дает вам гибкость при сбое ошибки (возможно, с помощью специального кода, который может быть лучше всего проверен) или просто для решения проблемы, и установки grant_update = 0, когда gid = 1 и realm = 'nodeaccess_rid'

Ответ 3

Я закончил работу с триггерной функцией. Это проверит роль и отключит ненужную функциональность с помощью логических полей grant_update и grant_delete. Функция ниже также сохраняет значение grant_view, а не перезаписывает его.

CREATE OR REPLACE function block_anonymous_page_edit()
RETURNS trigger AS $function$
BEGIN
  IF NEW.gid = 1 AND NEW.realm != 'nodeaccess_author' AND (NEW.grant_update = 1 OR NEW.grant_delete = 1) THEN
    RAISE WARNING 'Anonymous users are not allowed to edit pages.';
    NEW.grant_update := 0;
    NEW.grant_delete := 0;
  END IF;
  RETURN NEW;
END;
$function$ LANGUAGE plpgsql;

CREATE TRIGGER tgr_block_anonymous_page_edit BEFORE INSERT OR UPDATE ON node_access FOR EACH ROW EXECUTE PROCEDURE block_anonymous_page_edit();