Your First Function
Stored functions are reusable SQL code that lives inside your database. Instead of calculating fees in your application, you write a function once and call it from any query. PostgreSQL uses PL/pgSQL โ a procedural language that adds if/else, loops, and variables to SQL.
Let's build a fee calculator for NovaPay payment systems. Different payment methods have different fees: card (2.5%), UPI (1%), bank transfer (0.5%), M-Pesa (1.5%), others (2%).
Q1: Create the calculate_fee Function
Breakdown:
DECLAREโ Section where you define variables (here:fee_rate DECIMAL).IF/ELSIF/ELSEโ Conditional logic. Set the fee rate based on payment method.:=โ Assignment operator in PL/pgSQL (not=).RETURNโ Send the result back to the caller.$$ LANGUAGE plpgsqlโ Tells PostgreSQL this is PL/pgSQL code (double dollar signs delimit the function body).
Q2: Test the calculate_fee Function
The function runs on every row, calculating the correct fee for each payment method. Perfect! Now let's make functions that return entire result sets.
Functions That Return Tables
Sometimes you need a function to return multiple columns and rows, not just a scalar value. Use RETURNS TABLE to define what columns the result has.
Q3: Create get_merchant_daily_stats Function
Key differences:
RETURNS TABLE(...)โ Defines the output columns:transaction_count,total_volume,avg_amount.RETURN QUERYโ Executes a SELECT and returns its rows.p_merchant_id,p_dateโ Function parameters. Thep_prefix is a naming convention to avoid conflicts with column names.
Q4: Test get_merchant_daily_stats
Call table-returning functions like regular tables using SELECT * FROM function_name(...). This one returned 0 transactions because merchant 5 had no activity on that date.
Trigger Functions
A trigger is an automatic action that runs when something happens to your data. For example, every time someone inserts a transaction, you want to log it in an audit table. Triggers make this automatic โ no application code needed.
Trigger functions are special functions that return TRIGGER and have access to NEW (the new row being inserted/updated) and OLD (the old row being updated/deleted).
Q5: Create a Trigger Function for INSERT Logging
Key concepts:
RETURNS TRIGGERโ This is a trigger function, not a regular function.NEWโ The new row being inserted. Access columns likeNEW.txn_id,NEW.amount.row_to_json(NEW)::jsonbโ Converts the entire row to JSON and casts it to jsonb data type for storage.current_userโ PostgreSQL variable for the database user who triggered the action.RETURN NEWโ Always return the row (modified or not) so the INSERT proceeds normally.
Q6: Create the AFTER INSERT Trigger
Breakdown:
AFTER INSERT ON transactionsโ This trigger fires after every INSERT into the transactions table.FOR EACH ROWโ Run once per row inserted (important if you insert 100 rows, the trigger runs 100 times).EXECUTE FUNCTION log_transaction_insert()โ Call the trigger function.
BEFORE vs AFTER: Use BEFORE if you need to modify the row before it's saved (e.g., validate or clean data). Use AFTER if you just want to log or react to the change.
Testing Triggers
Now let's test our trigger and see the audit log in action.
Q7: Insert a Transaction (Trigger Fires)
Q8: Verify the Audit Log Entry
Beautiful! The audit log captured the entire transaction as JSON. No application code involved โ it happened automatically.
Q9: Create a Trigger Function for UPDATE Logging
Note: For UPDATE triggers, you have access to both OLD (the row before the update) and NEW (after the update). This lets you track exactly what changed.
Q10: Create the AFTER UPDATE Trigger
Q11: Update a Transaction (Trigger Fires)
Q12: Check the Audit Log for the Update
The UPDATE trigger captured both the old and new state. Perfect for compliance and debugging!
Preventing Bad Deletes
In a payment system, you should never delete delivered transactions โ they're part of customer records. Use a BEFORE DELETE trigger with RAISE EXCEPTION to prevent this.
Q13: Create Deletion Guard Function
Key points:
BEFORE DELETEโ Runs before the deletion happens.RAISE EXCEPTIONโ Throws an error and aborts the operation. The%is a placeholder for the variable after the string.RETURN OLDโ If the condition isn't met, return the old row to allow deletion.
Q14: Create the BEFORE DELETE Trigger
Why triggers instead of application code? Triggers enforce rules at the database level. Even if a hacker bypasses your application, the trigger still protects the data. This is called defense in depth.
Managing Functions & Triggers
As you build more functions, you'll need to list, query, and clean up old ones. PostgreSQL stores function metadata in the information_schema.
Q16: Audit Summary with a Function
Q17: Test the Audit Summary
Q18: List All Functions
To drop a function:
CASCADE drops dependent triggers automatically. Without it, you'd get an error if triggers depend on the function.
Challenge
Create a function called get_top_merchants that returns the top 5 merchants by transaction volume for a given date.
It should:
- Accept a parameter:
p_date DATE - Return columns:
merchant_id INTEGER, total_volume DECIMAL, transaction_count BIGINT - Filter to transactions with
status = 'success'on that date - Sort by total_volume DESC and limit to 5
Then test it with a real date from your data.
Start with CREATE FUNCTION get_top_merchants(p_date DATE) RETURNS TABLE(...) and use RETURN QUERY with a GROUP BY and ORDER BY.