⚙️ Functions & Procedures

SELECT
    ROUND(3.14159, 2)   AS rounded,       -- 3.14
    CEIL(3.2)            AS ceiling,       -- 4  (round UP)
    FLOOR(3.9)           AS floored,       -- 3  (round DOWN)
    ABS(-42)             AS absolute,      -- 42
    MOD(17, 5)          AS remainder,     -- 2  (17 / 5 = 3 remainder 2)
    POWER(2, 10)        AS two_to_ten,    -- 1024
    SQRT(144)            AS square_root,   -- 12
    RANDOM()             AS random_num;    -- 0.7291... (random 0-1)

SELECT name,
       price,
       ROUND(price * 0.18, 2) AS gst,
       ROUND(price * 1.18, 2) AS total_with_gst
FROM products
ORDER BY price DESC
LIMIT 5;

-- Pick 3 random products for a daily deal
SELECT name, price
FROM products
ORDER BY RANDOM()
LIMIT 3;

SELECT
    NOW()                AS current_timestamp,  -- 2026-03-19 14:30:15.123+05:30
    CURRENT_DATE         AS today,              -- 2026-03-19
    CURRENT_TIME         AS time_now,           -- 14:30:15.123+05:30
    CURRENT_TIMESTAMP    AS same_as_now;        -- same as NOW()

SELECT
    EXTRACT(YEAR   FROM NOW()) AS year,    -- 2026
    EXTRACT(MONTH  FROM NOW()) AS month,   -- 3
    EXTRACT(DAY    FROM NOW()) AS day,     -- 19
    EXTRACT(DOW    FROM NOW()) AS weekday, -- 0=Sun, 1=Mon ... 6=Sat
    EXTRACT(HOUR   FROM NOW()) AS hour;    -- 14

-- Truncate to the beginning of the month
SELECT DATE_TRUNC('month', NOW()) AS month_start;
-- Result: 2026-03-01 00:00:00+05:30

-- Great for grouping orders by month
SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*) AS total_orders
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- How old is this order?
SELECT AGE(NOW(), '2024-06-15'::timestamp) AS order_age;
-- Result: 1 year 9 mons 4 days 14:30:15.123

-- How long has each employee been with us?
SELECT name,
       hire_date,
       AGE(NOW(), hire_date) AS tenure
FROM employees
ORDER BY hire_date;

SELECT
    TO_CHAR(NOW(), 'DD-Mon-YYYY')          AS formatted1,  -- 19-Mar-2026
    TO_CHAR(NOW(), 'Day, DD Month YYYY')  AS formatted2,  -- Thursday , 19 March 2026
    TO_CHAR(NOW(), 'HH12:MI AM')           AS formatted3;  -- 02:30 PM

SELECT
    NOW() + INTERVAL '7 days'    AS next_week,
    NOW() - INTERVAL '3 months'  AS three_months_ago,
    NOW() + INTERVAL '2 hours'   AS in_two_hours;

-- Practical: Find orders from the last 30 days
SELECT * FROM orders
WHERE order_date >= NOW() - INTERVAL '30 days';

SELECT name,
       COALESCE(phone, email, 'No contact info') AS contact
FROM customers;

-- Avoid division by zero
SELECT total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM monthly_stats;

SELECT
    GREATEST(10, 25, 5, 30) AS biggest,    -- 30
    LEAST(10, 25, 5, 30)    AS smallest;   -- 5

-- Practical: Ensure price is never below the minimum
SELECT name,
       GREATEST(price, 9.99) AS final_price
FROM products;

SELECT name, salary,
       CASE
           WHEN salary >= 90000 THEN 'Senior'
           WHEN salary >= 70000 THEN 'Mid-Level'
           ELSE 'Junior'
       END AS level
FROM employees;

-- A function to calculate 18% GST on any price
CREATE FUNCTION calculate_gst(price NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
AS $$
    SELECT ROUND(price * 0.18, 2);
$$;

SELECT name,
       price,
       calculate_gst(price) AS gst,
       price + calculate_gst(price) AS total
FROM products;

CREATE FUNCTION format_inr(amount NUMERIC)
RETURNS TEXT
LANGUAGE sql
AS $$
    SELECT '₹' || TO_CHAR(amount, 'FM99,99,999.00');
$$;

-- Usage
SELECT format_inr(1250000.50);
-- Result: ₹12,50,000.50

CREATE FUNCTION is_valid_email(email TEXT)
RETURNS BOOLEAN
LANGUAGE sql
AS $$
    SELECT email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
$$;

-- Usage
SELECT is_valid_email('john@example.com');  -- true
SELECT is_valid_email('not-an-email');      -- false

CREATE FUNCTION function_name(params)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
    -- Variables go here
    my_variable INTEGER;
BEGIN
    -- Logic goes here
    RETURN result;
END;
$$;

CREATE FUNCTION greet(person_name TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    greeting TEXT;
BEGIN
    greeting := 'Hello, ' || person_name || '! Welcome to PostgreSQL.';
    RETURN greeting;
END;
$$;

SELECT greet('Alice');
-- Result: Hello, Alice! Welcome to PostgreSQL.

CREATE FUNCTION classify_customer(total_spending NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    tier TEXT;
BEGIN
    IF total_spending >= 100000 THEN
        tier := '💎 Platinum';
    ELSIF total_spending >= 50000 THEN
        tier := '🥇 Gold';
    ELSIF total_spending >= 10000 THEN
        tier := '🥈 Silver';
    ELSE
        tier := '🥉 Bronze';
    END IF;

    RETURN tier;
END;
$$;

-- Usage: Classify all customers
SELECT name,
       total_spending,
       classify_customer(total_spending) AS tier
FROM customers
ORDER BY total_spending DESC;

CREATE FUNCTION countdown(start_num INTEGER)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER := start_num;
BEGIN
    WHILE i > 0 LOOP
        RAISE NOTICE '% ...', i;
        i := i - 1;
    END LOOP;
    RAISE NOTICE 'Liftoff! 🚀';
END;
$$;

SELECT countdown(3);
-- NOTICE:  3 ...
-- NOTICE:  2 ...
-- NOTICE:  1 ...
-- NOTICE:  Liftoff! 🚀

CREATE FUNCTION list_expensive_products(min_price NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    product RECORD;
    result TEXT := '';
BEGIN
    FOR product IN
        SELECT name, price FROM products
        WHERE price >= min_price
        ORDER BY price DESC
    LOOP
        result := result || product.name || ' ($' || product.price || '), ';
    END LOOP;

    RETURN RTRIM(result, ', ');
END;
$$;

CREATE PROCEDURE transfer_funds(
    sender_id INTEGER,
    receiver_id INTEGER,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Deduct from sender
    UPDATE accounts
    SET balance = balance - amount
    WHERE id = sender_id;

    -- Add to receiver
    UPDATE accounts
    SET balance = balance + amount
    WHERE id = receiver_id;

    COMMIT;
END;
$$;

-- Transfer $500 from account 1 to account 2
CALL transfer_funds(1, 2, 500.00);

-- Step 1: Create the trigger function
CREATE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

-- Step 2: Attach the trigger to a table
CREATE TRIGGER trg_employees_updated_at
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

-- First, create an audit log table
CREATE TABLE audit_log (
    id          SERIAL PRIMARY KEY,
    table_name  TEXT,
    operation   TEXT,
    old_data    JSONB,
    new_data    JSONB,
    changed_at  TIMESTAMP DEFAULT NOW(),
    changed_by  TEXT DEFAULT CURRENT_USER
);

-- Create the audit trigger function
CREATE FUNCTION log_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD));
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW));
        RETURN NEW;
    END IF;
END;
$$;

-- Attach it to the employees table
CREATE TRIGGER trg_employees_audit
    AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION log_changes();

-- A view that shows only Engineering employees
CREATE VIEW engineering_team AS
SELECT id, name, salary, hire_date
FROM employees
WHERE department = 'Engineering';

-- Now query it like a regular table!
SELECT * FROM engineering_team;

-- A view that joins orders with customers and calculates totals
CREATE VIEW order_summary AS
SELECT
    c.name AS customer_name,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS total_spent,
    MAX(o.order_date) AS last_order
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- Now anyone can run this simple query instead of the complex one
SELECT * FROM order_summary
WHERE total_spent > 10000;

-- You can INSERT/UPDATE/DELETE through the view!
UPDATE engineering_team
SET salary = 95000
WHERE name = 'Alice Johnson';
-- This actually updates the employees table

CREATE VIEW engineering_team AS
SELECT id, name, department, salary
FROM employees
WHERE department = 'Engineering'
WITH CHECK OPTION;

-- This will FAIL because 'Sales' doesn't match the view's WHERE
INSERT INTO engineering_team (name, department, salary)
VALUES ('Sneaky Pete', 'Sales', 50000);
-- ERROR: new row violates check option for view "engineering_team"

-- Modify an existing view without dropping it first
CREATE OR REPLACE VIEW engineering_team AS
SELECT id, name, salary, hire_date,
       AGE(NOW(), hire_date) AS tenure
FROM employees
WHERE department = 'Engineering';

DROP VIEW IF EXISTS engineering_team;