PostgreSQL Rule Example

ban-user.sql

CREATE TABLE forums.banned_users
(
    banned_user_id                              integer PRIMARY KEY REFERENCES account.users,
    banned_by                                   integer REFERENCES account.users,
    reason                                      text,
    browser                                     text,
    ip_address                                  text,
    user_agent                                  text,
    audit_ts                                    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT(NOW())
);

CREATE OR REPLACE VIEW forums.banned_user_insert_view
AS
SELECT * FROM forums.banned_users;

CREATE OR REPLACE RULE check_before_banning AS 
ON INSERT TO forums.banned_user_insert_view
DO INSTEAD 
INSERT INTO forums.banned_users
(
    banned_user_id,
    banned_by,
    reason,
    browser,
    ip_address,
    user_agent,
    audit_ts
)
SELECT
    NEW.banned_user_id,
    NEW.banned_by,
    NEW.reason,
    NEW.browser,
    NEW.ip_address,
    NEW.user_agent,
    COALESCE(NEW.audit_ts, NOW())
WHERE NOT EXISTS
(
    --Cannot ban an administrator
    SELECT 1 
    FROM account.users
    INNER JOIN account.roles
    ON account.roles.role_id = account.users.role_id
    AND account.roles.is_administrator
    AND account.users.user_id = NEW.banned_user_id
)
AND NOT EXISTS
(
    --Cannot ban an already banned user
    SELECT 1
    FROM forums.banned_users
    WHERE banned_user_id = NEW.banned_user_id
)
AND EXISTS
(
    --Only admin can ban someone
    SELECT 1 
    FROM account.users
    INNER JOIN account.roles
    ON account.roles.role_id = account.users.role_id
    AND account.roles.is_administrator
    AND account.users.user_id = NEW.banned_by
);

Why with timezone?