更新可能ビューとINSTEAD OFトリガー

CREATE VIEW active_users AS
  SELECT id, email, full_name FROM users WHERE deleted_at IS NULL;

UPDATE active_users SET full_name = 'Alice' WHERE id = 1;
-- Translates to UPDATE on the users table.

CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL
  WITH CHECK OPTION;

INSERT INTO active_users (email, deleted_at) VALUES (..., NOW());
-- ERROR — would create a row outside the view's WHERE.

CREATE VIEW user_with_address AS
  SELECT u.id, u.email, a.city, a.country
  FROM users u JOIN addresses a ON a.user_id = u.id;

CREATE TRIGGER user_addr_update
INSTEAD OF UPDATE ON user_with_address
FOR EACH ROW EXECUTE FUNCTION update_user_addr();

CREATE FUNCTION update_user_addr() RETURNS TRIGGER AS $$
BEGIN
  UPDATE users SET email = NEW.email WHERE id = NEW.id;
  UPDATE addresses SET city = NEW.city, country = NEW.country WHERE user_id = NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE VIEW my_things AS
SELECT * FROM things WHERE owner_id = current_user_id()
WITH (security_barrier);

ALTER TABLE things ENABLE ROW LEVEL SECURITY;
CREATE POLICY owner_only ON things USING (owner_id = current_user_id());

CREATE RECURSIVE VIEW tree (id, parent_id, depth) AS
  SELECT id, parent_id, 0 FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent_id = t.id;

GRANT SELECT, UPDATE ON active_users TO app_user;