SQL User-Defined Functions (UDFs)

-- Built-in function
SELECT UPPER('hello');  -- Returns: HELLO

-- User-defined function (same usage pattern)
SELECT calculate_tax(100.00, 'CA');  -- Returns: 7.25

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    hire_date DATE NOT NULL
);
INSERT INTO employees (id, name, department, salary, hire_date) VALUES
(1, 'Alice',   'Engineering', 95000, '2019-03-15'),
(2, 'Bob',     'Engineering', 88000, '2021-06-01'),
(3, 'Charlie', 'Sales',       72000, '2018-11-20'),
(4, 'Diana',   'Sales',       68000, '2022-01-10'),
(5, 'Eve',     'Marketing',   74000, '2020-08-05'),
(6, 'Frank',   'Marketing',   70000, '2023-02-14');

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50) NOT NULL,
    signup_date DATE NOT NULL,
    lifetime_spent DECIMAL(12,2) NOT NULL
);
INSERT INTO customers (id, name, country, signup_date, lifetime_spent) VALUES
(101, 'Acme Corp',  'US', '2022-01-15', 4250.00),
(102, 'Globex Inc', 'CA', '2022-06-20', 2180.00),
(103, 'Initech',    'UK', '2023-03-10', 1520.00),
(104, 'Umbrella',   'US', '2024-01-05', 320.00);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    country VARCHAR(50) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);
INSERT INTO orders (id, customer_id, order_date, amount, country) VALUES
(1, 101, '2024-01-05', 250.00, 'US'),
(2, 102, '2024-01-12', 430.00, 'CA'),
(3, 101, '2024-02-08', 180.00, 'US'),
(4, 103, '2024-02-15', 520.00, 'UK'),
(5, 102, '2024-03-01', 310.00, 'CA'),
(6, 101, '2024-03-18', 640.00, 'US');

-- Scalar function returns one value per call
SELECT calculate_tax(250.00, 'US') AS tax;
-- Returns: 20.00

-- Table-valued function returns a set of rows
SELECT * FROM get_recent_orders(101, 30);
-- Returns multiple rows:
-- | order_id | order_date | amount |
-- |----------|------------|--------|
-- | 6        | 2024-03-18 | 640.00 |
-- | 3        | 2024-02-08 | 180.00 |

CREATE OR REPLACE FUNCTION function_name (
    parameter1 datatype,
    parameter2 datatype
)
RETURNS return_datatype
LANGUAGE plpgsql     -- or LANGUAGE sql for simple functions
AS $$
BEGIN
    -- logic here
    RETURN value;
END;
$$;

DELIMITER //

CREATE FUNCTION function_name (
    parameter1 datatype,
    parameter2 datatype
)
RETURNS return_datatype
DETERMINISTIC  -- or NOT DETERMINISTIC
BEGIN
    -- logic here
    RETURN value;
END //

DELIMITER ;

CREATE FUNCTION function_name (
    @parameter1 datatype,
    @parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
    DECLARE @result return_datatype;
    -- logic here
    RETURN @result;
END;

CREATE OR REPLACE FUNCTION years_of_service (p_hire_date DATE)
RETURNS INT
LANGUAGE sql
AS $$
    SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, p_hire_date))::INT;
$$;

DELIMITER //

CREATE FUNCTION years_of_service (p_hire_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_hire_date, CURDATE());
END //

DELIMITER ;

CREATE FUNCTION years_of_service (@p_hire_date DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @p_hire_date, GETDATE());
END;

-- PostgreSQL / MySQL
SELECT
    name,
    hire_date,
    years_of_service(hire_date) AS tenure
FROM employees;

-- SQL Server (schema-qualified call required)
SELECT
    name,
    hire_date,
    dbo.years_of_service(hire_date) AS tenure
FROM employees;

CREATE OR REPLACE FUNCTION calculate_tax (
    p_amount DECIMAL(10,2),
    p_country VARCHAR(2)
)
RETURNS DECIMAL(10,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_rate DECIMAL(5,4);
BEGIN
    v_rate := CASE p_country
        WHEN 'US' THEN 0.08
        WHEN 'CA' THEN 0.13
        WHEN 'UK' THEN 0.20
        WHEN 'DE' THEN 0.19
        ELSE 0.10
    END;

    RETURN ROUND(p_amount * v_rate, 2);
END;
$$;

DELIMITER //

CREATE FUNCTION calculate_tax (
    p_amount DECIMAL(10,2),
    p_country VARCHAR(2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE v_rate DECIMAL(5,4);

    SET v_rate = CASE p_country
        WHEN 'US' THEN 0.08
        WHEN 'CA' THEN 0.13
        WHEN 'UK' THEN 0.20
        WHEN 'DE' THEN 0.19
        ELSE 0.10
    END;

    RETURN ROUND(p_amount * v_rate, 2);
END //

DELIMITER ;

SELECT
    id,
    amount,
    country,
    calculate_tax(amount, country) AS tax,
    amount + calculate_tax(amount, country) AS total_with_tax
FROM orders;

CREATE OR REPLACE FUNCTION get_loyalty_tier (p_lifetime_spent DECIMAL(10,2))
RETURNS VARCHAR(20)
LANGUAGE sql
AS $$
    SELECT CASE
        WHEN p_lifetime_spent >= 5000 THEN 'Platinum'
        WHEN p_lifetime_spent >= 2000 THEN 'Gold'
        WHEN p_lifetime_spent >= 500  THEN 'Silver'
        ELSE 'Bronze'
    END;
$$;

SELECT
    name,
    lifetime_spent,
    get_loyalty_tier(lifetime_spent) AS tier
FROM customers
ORDER BY lifetime_spent DESC;

CREATE OR REPLACE FUNCTION salary_band (p_salary DECIMAL(10,2))
RETURNS VARCHAR(30)
LANGUAGE sql
AS $$
    SELECT CASE
        WHEN p_salary >= 90000 THEN 'Senior Band'
        WHEN p_salary >= 75000 THEN 'Mid Band'
        WHEN p_salary >= 60000 THEN 'Junior Band'
        ELSE 'Entry Band'
    END;
$$;

-- In WHERE: filter by computed tier
SELECT name, salary
FROM employees
WHERE salary_band(salary) = 'Senior Band';

-- In GROUP BY: aggregate by computed band
SELECT
    salary_band(salary) AS band,
    COUNT(*) AS employee_count,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY salary_band(salary)
ORDER BY avg_salary DESC;

-- In ORDER BY
SELECT name, salary, salary_band(salary) AS band
FROM employees
ORDER BY salary_band(salary), salary DESC;

CREATE OR REPLACE FUNCTION format_currency (
    p_amount DECIMAL(10,2),
    p_currency VARCHAR(3) DEFAULT 'USD'
)
RETURNS VARCHAR(20)
LANGUAGE sql
AS $$
    SELECT CASE p_currency
        WHEN 'USD' THEN '$' || TO_CHAR(p_amount, 'FM999,999,990.00')
        WHEN 'EUR' THEN '€' || TO_CHAR(p_amount, 'FM999,999,990.00')
        WHEN 'GBP' THEN '£' || TO_CHAR(p_amount, 'FM999,999,990.00')
        ELSE TO_CHAR(p_amount, 'FM999,999,990.00') || ' ' || p_currency
    END;
$$;

SELECT
    name,
    format_currency(salary) AS annual_salary,
    format_currency(salary / 12.0) AS monthly_salary
FROM employees;

CREATE OR REPLACE FUNCTION get_department_roster (p_department VARCHAR(50))
RETURNS TABLE (
    employee_id INT,
    employee_name VARCHAR(100),
    salary DECIMAL(10,2),
    tenure INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.id,
        e.name,
        e.salary,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, e.hire_date))::INT
    FROM employees e
    WHERE e.department = p_department
    ORDER BY e.salary DESC;
END;
$$;

SELECT * FROM get_department_roster('Engineering');

-- Use it like a table: join, filter, aggregate
SELECT
    dr.employee_name,
    dr.salary,
    dr.tenure
FROM get_department_roster('Sales') dr
WHERE dr.tenure >= 2;

CREATE OR REPLACE FUNCTION get_high_earners (p_min_salary DECIMAL(10,2))
RETURNS SETOF employees
LANGUAGE sql
AS $$
    SELECT * FROM employees
    WHERE salary >= p_min_salary
    ORDER BY salary DESC;
$$;

SELECT name, salary FROM get_high_earners(75000);

CREATE FUNCTION get_department_roster (@p_department VARCHAR(50))
RETURNS TABLE
AS
RETURN (
    SELECT
        id AS employee_id,
        name AS employee_name,
        salary,
        DATEDIFF(YEAR, hire_date, GETDATE()) AS tenure
    FROM employees
    WHERE department = @p_department
);

SELECT * FROM dbo.get_department_roster('Engineering');

CREATE FUNCTION get_customer_order_summary (@p_customer_id INT)
RETURNS @result TABLE (
    order_count INT,
    total_spent DECIMAL(10,2),
    avg_order DECIMAL(10,2),
    last_order_date DATE
)
AS
BEGIN
    INSERT INTO @result
    SELECT
        COUNT(*),
        SUM(amount),
        AVG(amount),
        MAX(order_date)
    FROM orders
    WHERE customer_id = @p_customer_id;

    RETURN;
END;

SELECT * FROM dbo.get_customer_order_summary(101);

-- Join customers with their loyalty tier
SELECT
    c.name,
    c.lifetime_spent,
    get_loyalty_tier(c.lifetime_spent) AS tier,
    COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.lifetime_spent
ORDER BY c.lifetime_spent DESC;

SELECT
    name,
    department,
    salary,
    salary_band(salary) AS band,
    RANK() OVER (
        PARTITION BY salary_band(salary)
        ORDER BY salary DESC
    ) AS rank_within_band
FROM employees;

-- PostgreSQL: LATERAL join with a table-valued function
SELECT
    d.dept_name,
    r.employee_name,
    r.salary,
    r.tenure
FROM (VALUES ('Engineering'), ('Sales'), ('Marketing')) AS d(dept_name)
CROSS JOIN LATERAL get_department_roster(d.dept_name) r;

-- SQL Server: CROSS APPLY with a table-valued function
SELECT
    d.dept_name,
    r.employee_name,
    r.salary,
    r.tenure
FROM (VALUES ('Engineering'), ('Sales'), ('Marketing')) AS d(dept_name)
CROSS APPLY dbo.get_department_roster(d.dept_name) r;

-- PostgreSQL: Function-based constraint
CREATE OR REPLACE FUNCTION is_valid_email (p_email VARCHAR)
RETURNS BOOLEAN
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT p_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
$$;

ALTER TABLE customers ADD CONSTRAINT chk_valid_email
CHECK (is_valid_email(email));

-- GOOD use of a function: computation used in a SELECT
SELECT
    name,
    salary,
    calculate_tax(salary, 'US') AS annual_tax,
    salary_band(salary) AS band
FROM employees;

-- GOOD use of a function: reusable logic in WHERE
SELECT * FROM customers
WHERE get_loyalty_tier(lifetime_spent) = 'Platinum';

-- GOOD use of a function: table-valued function as a data source
SELECT * FROM get_department_roster('Sales');

-- GOOD use of a procedure: performs multiple data modifications
CALL transfer_funds(1, 2, 500.00);

-- GOOD use of a procedure: complex workflow with transaction control
CALL process_order(101, 501, 3);

-- GOOD use of a procedure: administrative operations
CALL archive_old_records('2023-01-01');

-- IMMUTABLE: Pure computation, no external dependencies
CREATE OR REPLACE FUNCTION celsius_to_fahrenheit (p_celsius DECIMAL)
RETURNS DECIMAL
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT ROUND(p_celsius * 9.0 / 5.0 + 32, 2);
$$;

-- STABLE: Reads database but result is consistent within one query
CREATE OR REPLACE FUNCTION get_setting (p_key VARCHAR)
RETURNS VARCHAR
LANGUAGE sql
STABLE
AS $$
    SELECT value FROM app_settings WHERE key = p_key;
$$;

-- VOLATILE: May return different results each time (default)
CREATE OR REPLACE FUNCTION generate_code ()
RETURNS VARCHAR
LANGUAGE sql
VOLATILE
AS $$
    SELECT 'ORD-' || LPAD(FLOOR(RANDOM() * 1000000)::TEXT, 6, '0');
$$;

-- SQL Server: This can be extremely slow on large tables
SELECT
    name,
    salary,
    dbo.salary_band(salary) AS band  -- Called once per row, no parallelism
FROM employees;  -- 1 million rows = 1 million function calls

-- SQL Server: Inline TVF is "unfolded" into the query plan
CREATE FUNCTION get_salary_bands ()
RETURNS TABLE
AS
RETURN (
    SELECT
        name,
        salary,
        CASE
            WHEN salary >= 90000 THEN 'Senior Band'
            WHEN salary >= 75000 THEN 'Mid Band'
            WHEN salary >= 60000 THEN 'Junior Band'
            ELSE 'Entry Band'
        END AS band
    FROM employees
);

SELECT * FROM dbo.get_salary_bands();

-- POTENTIALLY SLOW: Function executes a query per row
CREATE OR REPLACE FUNCTION get_order_count (p_customer_id INT)
RETURNS INT
LANGUAGE sql
STABLE
AS $$
    SELECT COUNT(*) FROM orders WHERE customer_id = p_customer_id;
$$;

-- This runs the COUNT query once per customer row
SELECT name, get_order_count(id) FROM customers;
-- 100,000 customers = 100,000 separate COUNT queries!

-- BETTER: Use a JOIN instead
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- This simple SQL function will be inlined
CREATE OR REPLACE FUNCTION double_value (p_val INT)
RETURNS INT
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT p_val * 2;
$$;

-- PostgreSQL replaces this:
SELECT double_value(salary) FROM employees;
-- With this (internally):
SELECT salary * 2 FROM employees;

-- PostgreSQL: CREATE OR REPLACE
CREATE OR REPLACE FUNCTION calculate_tax (...)
RETURNS DECIMAL(10,2)
LANGUAGE plpgsql
AS $$ ... $$;

-- SQL Server: ALTER FUNCTION
ALTER FUNCTION dbo.calculate_tax (...)
RETURNS DECIMAL(10,2)
AS
BEGIN ... END;

-- MySQL: Must DROP and recreate
DROP FUNCTION IF EXISTS calculate_tax;
DELIMITER //
CREATE FUNCTION calculate_tax (...) ...
END //
DELIMITER ;

-- All databases
DROP FUNCTION function_name;

-- Safe version
DROP FUNCTION IF EXISTS function_name;

-- PostgreSQL: May need to specify parameter types if overloaded
DROP FUNCTION IF EXISTS calculate_tax(DECIMAL, VARCHAR);

-- PostgreSQL
SELECT routine_name, data_type AS return_type
FROM information_schema.routines
WHERE routine_schema = 'public' AND routine_type = 'FUNCTION';

-- MySQL
SHOW FUNCTION STATUS WHERE Db = 'your_database';

-- SQL Server
SELECT name, type_desc FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF');
-- FN = Scalar, IF = Inline TVF, TF = Multi-statement TVF

-- SQL Server: This will NOT compile
CREATE FUNCTION update_balance (@id INT, @amount DECIMAL)
RETURNS DECIMAL
AS
BEGIN
    UPDATE accounts SET balance = balance + @amount WHERE id = @id;
    RETURN @amount;
END;
-- ERROR: Invalid use of a side-effecting operator

-- BAD: Scalar function that queries another table
CREATE FUNCTION get_customer_name (p_id INT)
RETURNS VARCHAR
LANGUAGE sql STABLE
AS $$
    SELECT name FROM customers WHERE id = p_id;
$$;

-- Calling it per-row on a large orders table:
SELECT id, amount, get_customer_name(customer_id) AS customer
FROM orders;
-- If orders has 500,000 rows, this runs 500,000 separate lookups!

SELECT o.id, o.amount, c.name AS customer
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- BAD: Function reads a table but is declared IMMUTABLE
CREATE OR REPLACE FUNCTION get_tax_rate (p_country VARCHAR)
RETURNS DECIMAL
LANGUAGE sql
IMMUTABLE  -- WRONG! This reads from a table
AS $$
    SELECT rate FROM tax_rates WHERE country = p_country;
$$;
-- PostgreSQL may cache the result and return stale data
-- when the tax_rates table is updated!

CREATE OR REPLACE FUNCTION get_tax_rate (p_country VARCHAR)
RETURNS DECIMAL
LANGUAGE sql
STABLE  -- Correct: consistent within a query, may change between queries
AS $$
    SELECT rate FROM tax_rates WHERE country = p_country;
$$;

-- SQL Server: This fails
SELECT calculate_tax(100.00, 'US');
-- ERROR: 'calculate_tax' is not a recognized built-in function name

-- Fix: Include the schema
SELECT dbo.calculate_tax(100.00, 'US');

-- BAD: 200-line function with multiple queries, loops, and conditionals
-- This should probably be a stored procedure or application code
CREATE FUNCTION process_entire_order (...)
RETURNS ...