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
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
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
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
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