PostgreSQL: UPDATE из DELETE-триггера в обход UPDATE-триггера

CREATE FUNCTION array_unique(a BIGINT[]) RETURNS BIGINT[] AS $$
DECLARE
	element BIGINT;
	ret BIGINT[] := ARRAY[]::BIGINT[];
BEGIN
	FOREACH element IN ARRAY a LOOP
		IF NOT ret @> ARRAY[element] THEN
			SELECT array_append(ret, element) INTO ret;
		END IF;
	END LOOP;
	
	RETURN ret;
END;
$$ LANGUAGE plpgsql;

-- fields with trigger functions

CREATE TABLE fields (
	id BIGSERIAL PRIMARY KEY,
	name CITEXT NOT NULL,
	position BIGINT NOT NULL,
	visible1 BOOLEAN NOT NULL DEFAULT TRUE,
	visible2 BOOLEAN NOT NULL DEFAULT FALSE,
	deletable BOOLEAN NOT NULL DEFAULT TRUE,
	
	CONSTRAINT fields_name_unique UNIQUE (name),
	CONSTRAINT fields_name_trimmed CHECK (trim(name) = name),
	CONSTRAINT fields_name_long_enough CHECK (name <> ''),
	CONSTRAINT fields_name_short_enough CHECK (length(name) <= 256),
	
	CONSTRAINT fields_position_unique UNIQUE (position) DEFERRABLE,
	CONSTRAINT fields_position_not_negative CHECK (position >= 0)
);

CREATE FUNCTION fields_before_insert() RETURNS trigger AS $$
DECLARE
	nextval BIGINT;
BEGIN
	LOCK TABLE fields IN ACCESS EXCLUSIVE MODE;
	SELECT COALESCE(MAX(position) + 1, 0) INTO nextval FROM fields;
	NEW.position = nextval;
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION fields_before_delete() RETURNS trigger AS $$
BEGIN
	-- prevent deletion of field required for system operation
	IF OLD.deletable = FALSE THEN
		RETURN NULL;
	END IF;
	
	LOCK TABLE fields IN ACCESS EXCLUSIVE MODE;
	SET CONSTRAINTS fields_position_unique DEFERRED;
	RAISE NOTICE '{%}', OLD.position;
	UPDATE fields SET position = position - 1 WHERE position > OLD.position;
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION fields_before_update() RETURNS trigger AS $$
BEGIN
	-- position must be updated through fields_reorder(ids BIGINT[]) function, see below
	NEW.position = OLD.position;
	RAISE NOTICE '{%}', TG_OP;
	-- prevent removal of deletable flag
	IF OLD.deletable = FALSE THEN
		NEW.deletable = FALSE;
	END IF;
	
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fields_before_insert BEFORE INSERT ON fields FOR EACH ROW EXECUTE PROCEDURE fields_before_insert();
CREATE TRIGGER fields_before_delete BEFORE DELETE ON fields FOR EACH ROW EXECUTE PROCEDURE fields_before_delete();
CREATE TRIGGER fields_before_update BEFORE UPDATE OF position, deletable ON fields FOR EACH ROW EXECUTE PROCEDURE fields_before_update();

CREATE FUNCTION fields_reorder(ids BIGINT[]) RETURNS VOID AS $$
DECLARE
	rec RECORD;
	pos BIGINT;
	id BIGINT;
BEGIN
	SELECT array_unique(ids) INTO ids;
	
	LOCK TABLE fields IN ACCESS EXCLUSIVE MODE;
	SET CONSTRAINTS fields_position_unique DEFERRED;
	
	pos := 0;
	
	FOREACH id IN ARRAY ids LOOP
		EXECUTE format('UPDATE fields SET position = %L WHERE id = %L', pos, id);
		pos := pos + 1;
	END LOOP;
	
	FOR rec IN SELECT fields.id FROM fields WHERE NOT (fields.id = ANY(ids)) LOOP
		EXECUTE FORMAT('UPDATE fields SET position = %L WHERE id = %L', pos, rec.id);
		pos := pos + 1;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

INSERT INTO fields (name) VALUES ('Vendor Name');
INSERT INTO fields (name) VALUES ('VPN');
INSERT INTO fields (name) VALUES ('SKU');
INSERT INTO fields (name) VALUES ('EANUPC');
INSERT INTO fields (name) VALUES ('Dealer Price');
INSERT INTO fields (name) VALUES ('Stock');

-- end fields with trigger functions