Part 115: User-Defined Functions (UDF): Scalar Functions

Custom functions encapsulate business logic directly in the database.

1. PostgreSQL Function Example

CREATE OR REPLACE FUNCTION calculate_tax(subtotal DECIMAL, tax_rate DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
  RETURN ROUND(subtotal * (tax_rate / 100.0), 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Using in queries:
SELECT order_id, calculate_tax(subtotal, 8.25) AS tax_amount FROM orders;

Key Takeaways

• Mark functions IMMUTABLE if they return the exact same output for the same input to enable compiler caching.

In Part 116, we explore Table-Valued Functions (TVF)!