PL/pgSQL

-- 1) A function with parameters, locals, control flow
CREATE OR REPLACE FUNCTION place_order(p_customer_id bigint, p_total_cents bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_order_id bigint;
  v_status   text := 'new';
BEGIN
  IF p_total_cents < 0 THEN
    RAISE EXCEPTION 'total_cents must be >= 0';
  END IF;

  INSERT INTO orders (customer_id, total_cents, status, created_at)
  VALUES (p_customer_id, p_total_cents, v_status, now())
  RETURNING id INTO v_order_id;

  UPDATE customers SET orders_count = orders_count + 1
   WHERE id = p_customer_id;

  RETURN v_order_id;
END;
$$;

-- 2) Set-returning function — looks like a table
CREATE OR REPLACE FUNCTION top_customers(n int)
RETURNS TABLE(customer_id bigint, name text, ltv numeric)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT c.id, c.name,
           COALESCE(SUM(o.total_cents) / 100.0, 0) AS ltv
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id AND o.status IN ('paid', 'shipped')
    GROUP BY c.id
    ORDER BY ltv DESC
    LIMIT n;
END;
$$;

SELECT * FROM top_customers(10);

-- 3) Loops + cursors — when you really need row-at-a-time
CREATE OR REPLACE FUNCTION backfill_slugs()
RETURNS int LANGUAGE plpgsql AS $$
DECLARE
  r RECORD;
  c int := 0;
BEGIN
  FOR r IN SELECT id, name FROM products WHERE slug IS NULL LOOP
    UPDATE products
       SET slug = lower(regexp_replace(r.name, '[^a-zA-Z0-9]+', '-', 'g'))
     WHERE id = r.id;
    c := c + 1;
  END LOOP;
  RETURN c;
END;
$$;

-- 4) Exception handling — trap a specific SQLSTATE
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric LANGUAGE plpgsql AS $$
BEGIN
  RETURN a / b;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE WARNING 'divide-by-zero at %', clock_timestamp();
    RETURN NULL;
END;
$$;

-- 5) Dynamic SQL — parameterised inside EXECUTE
CREATE OR REPLACE FUNCTION find_by(uname text)
RETURNS SETOF users LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT * FROM users WHERE username = $1'
    USING uname;
END;
$$;

-- 6) Trigger function — keep an audit log on writes
CREATE OR REPLACE FUNCTION orders_audit_trg()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_audit (order_id, op, before, after, changed_at)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    to_jsonb(OLD), to_jsonb(NEW),
    now()
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_audit_trg();

-- 7) Performance + observability
-- VOLATILE / STABLE / IMMUTABLE markers help the planner inline calls.
-- RAISE NOTICE prints into psql; RAISE LOG goes to the server log.
-- EXPLAIN ANALYZE works on functions via auto_explain.

-- 8) Permissions
GRANT EXECUTE ON FUNCTION place_order(bigint, bigint) TO app_user;
REVOKE INSERT, UPDATE, DELETE ON orders FROM app_user;

CREATE OR REPLACE FUNCTION inc(uid bigint)
RETURNS void AS $$
BEGIN
    UPDATE counters SET n = n + 1 WHERE user_id = uid;
END;
$$ LANGUAGE plpgsql;