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

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

sql
10 rows. Sample: txn_id=2, amount=387.25, payment_method=bank_transfer, fee=1.94 (0.5%). txn_id=3, amount=25666.55, payment_method=card, fee=641.66 (2.5%). txn_id=4, amount=28093.71, payment_method=upi, fee=280.94 (1%).

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

sql
CREATE 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. The p_ prefix is a naming convention to avoid conflicts with column names.

Q4: Test get_merchant_daily_stats

sql
1 row. transaction_count=0, total_volume=null, avg_amount=null (no transactions for that merchant on that date)

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

sql
CREATE FUNCTION

Key concepts:

  • RETURNS TRIGGER โ€” This is a trigger function, not a regular function.
  • NEW โ€” The new row being inserted. Access columns like NEW.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

sql
CREATE 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)

sql
INSERT 0 1 (1 row inserted. The trigger automatically logged this to audit_logs.)

Q8: Verify the Audit Log Entry

sql
1 row. log_id=1, table_name=transactions, row_id=501, action=INSERT, old_data=null, new_data={full JSON of the transaction}, changed_by=saikumarallaka, changed_at=2026-04-16 14:28:18.863440

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

sql
CREATE FUNCTION

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

sql
CREATE TRIGGER

Q11: Update a Transaction (Trigger Fires)

sql
UPDATE 1 (1 row updated. The update trigger logged the change.)

Q12: Check the Audit Log for the Update

sql
1 row. action=UPDATE, old_data={status: success, ...}, new_data={status: refunded, ...}. You can see the status changed from 'success' to 'refunded'.

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

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

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

sql
CREATE FUNCTION

Q17: Test the Audit Summary

sql
2 rows. action=INSERT, count=1. action=UPDATE, count=1.

Q18: List All Functions

sql
6 rows. calculate_fee (FUNCTION, numeric), count_audit_logs_by_action (FUNCTION, record), get_merchant_daily_stats (FUNCTION, record), log_transaction_insert (FUNCTION, trigger), log_transaction_update (FUNCTION, trigger), prevent_delete_delivered (FUNCTION, trigger).

To drop a function:

sql
DROP 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.

Show hint

Start with CREATE FUNCTION get_top_merchants(p_date DATE) RETURNS TABLE(...) and use RETURN QUERY with a GROUP BY and ORDER BY.

sql