Procedures Triggers Functions

🔥 PostgreSQL Summary: Triggers, Functions, and Procedures

Coming from an MS SQL (BI/DE) background, I instinctively default to stored procs for any non-relational logic. However, in PostgreSQL, functions and triggers are incredibly powerful and often a better fit. So here’s a concise breakdown of triggers, functions, and procedures in PostgreSQL, including when to use each and permission considerations.

🔥 Summary Table: Triggers vs. Functions vs. Procedures

Feature	Triggers	Functions	Procedures
Purpose	Auto-execute logic on table changes	Return a value from a query	Execute multi-step operations
Called By	INSERT, UPDATE, DELETE events	SELECT function_name()	CALL procedure_name()
Returns	Nothing	A value (or table)	Nothing
Can Modify Data?	✅ Yes	⚠️ Yes (if SECURITY DEFINER)	✅ Yes
Transaction Control?	❌ No	❌ No	✅ Yes (COMMIT / ROLLBACK)
Main Use Case	Auditing, enforcing constraints	Encapsulating queries	Batch operations, data migration
Grant Permission	GRANT TRIGGER	GRANT EXECUTE ON FUNCTION	GRANT EXECUTE ON PROCEDURE

---

🚀 When to Use Each?

✔ Use a TRIGGER when you need automatic actions on table updates (e.g., logging changes).
✔ Use a FUNCTION when you want to return values or encapsulate queries.
✔ Use a PROCEDURE when you need multi-step changes inside a single transaction.

---

🛠 1. PostgreSQL Triggers

📌 What Are Triggers?

• Triggers are automatic actions executed before or after INSERT, UPDATE, or DELETE operations on a table.
• They enforce business logic, audit changes, or cascade updates.

📌 When to Use?

✔ Automatically log changes (audit logs).
✔ Enforce constraints beyond standard foreign keys.
✔ Modify data before inserting/updating.

📌 Key Permissions

Privilege	Effect
TRIGGER	Allows creating, modifying, and dropping triggers.
Without TRIGGER	User can only execute existing triggers.

🔥 Example: Create an Audit Log Trigger

CREATE TABLE user_audit (
    id SERIAL PRIMARY KEY,
    user_id UUID,
    action TEXT,
    changed_at TIMESTAMP DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_user_changes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_audit (user_id, action)
    VALUES (NEW.user_id, TG_OP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION log_user_changes();

✅ Automatically logs inserts, updates, and deletes for users.

---

🛠 2. PostgreSQL Functions

📌 What Are Functions?

• Functions return a value (RETURNS something) and are used for calculations, data transformations, and queries.
• Functions run inside a transaction (safe for SELECT queries).

📌 When to Use?

✔ Compute derived values (e.g., full_name(first_name, last_name)).
✔ Encapsulate complex queries for reuse.
✔ Perform operations inside queries (e.g., formatting).

📌 Key Permissions

Privilege	Effect
EXECUTE	Allows running the function.
Without EXECUTE	The user cannot call the function.

🔥 Example: Create a Function

CREATE OR REPLACE FUNCTION get_active_users() RETURNS TABLE(user_id UUID, email TEXT) AS $$
BEGIN
    RETURN QUERY SELECT user_id, primary_email FROM users WHERE is_active = TRUE;
END;
$$ LANGUAGE plpgsql;

✅ Can be used like a table:

SELECT * FROM get_active_users();

Grant Execute Permission

GRANT EXECUTE ON FUNCTION get_active_users() TO app_user;

✅ Now app_user can call the function.

---

🛠 3. PostgreSQL Procedures

📌 What Are Procedures?

• Procedures (CALL procedure_name()) allow executing multiple SQL statements inside a single transaction.
• Unlike functions, procedures can commit or roll back transactions.

📌 When to Use?

✔ Execute multi-step database changes.
✔ Run batch operations.
✔ Perform transaction-safe updates.

📌 Key Permissions

Privilege	Effect
EXECUTE	Allows calling the procedure.
Without EXECUTE	The user cannot run it.

🔥 Example: Create a Procedure

CREATE OR REPLACE PROCEDURE deactivate_users(days_old INT) AS $$
BEGIN
    UPDATE users
    SET is_active = FALSE
    WHERE last_login < NOW() - INTERVAL '1 day' * days_old;
END;
$$ LANGUAGE plpgsql;

✅ To call it:

CALL deactivate_users(30);

Grant Execute Permission

GRANT EXECUTE ON PROCEDURE deactivate_users(INT) TO app_user;

✅ Now app_user can deactivate old users.

---