INSERT INTO votes (user_id, link_id)
VALUES (1, 2)
ON CONFLICT DO NOTHING;
Simple upsert
Slide 4
Slide 4 text
INSERT INTO profiles (user_id, name, age)
VALUES (27, "Arjan", 32)
ON CONFLICT
ON CONSTRAINT profiles_users_fk
ON UPDATE SET name = EXCLUDED.name,
age = EXCLUDED.age;
Upsert with update
Slide 5
Slide 5 text
class User < ActiveRecord::Base
default_scope
-> { where(deleted_at: nil) }
scope :account_managers,
-> { where(role: 'account_manager') }
end
Slide 6
Slide 6 text
CREATE VIEW account_managers
AS SELECT * FROM users
WHERE role = 'account_manager'
AND deleted_at IS NULL
WITH CASCADED CHECK OPTION;
class AccountManager < ActiveRecord::Base
end
Views
Slide 7
Slide 7 text
CREATE MATERIALIZED VIEW invoices
AS SELECT number, SUM(lines.amount)
FROM offers
LEFT JOIN lines ON lines.offer_id = offers.id
GROUP BY number;
Materialized views
Slide 8
Slide 8 text
CREATE FUNCTION refresh_invoices()
RETURNS trigger AS $$
BEGIN
REFRESH MATERIALIZED VIEW invoices;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_refresh_invoices
AFTER TRUNCATE OR INSERT OR UPDATE OR DELETE
ON offers FOR EACH STATEMENT
EXECUTE PROCEDURE refresh_invoices();
Refreshing materialised views
Slide 9
Slide 9 text
CREATE TRIGGER soft_delete_user
BEFORE DELETE ON users
FOR EACH ROW EXECUTE PROCEDURE soft_delete();
CREATE FUNCTION soft_delete()
RETURNS trigger AS $$
DECLARE
command text := ' SET deleted_at =
current_timestamp WHERE id = $1';
BEGIN
EXECUTE 'UPDATE ' || TG_TABLE_NAME || command
USING OLD.id;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
Triggers per row
Slide 10
Slide 10 text
class User < ActiveRecord::Base
validates :email,
presence: true,
length: { maximum: 250 }
end