iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Functions

By · Reviewed & last updated · Part of PostgreSQL Tutorial

A SQL function is a server-side routine you can call from queries. PostgreSQL supports several languages — SQL, PL/pgSQL, PL/Python, PL/Perl — but most of what you write is SQL or PL/pgSQL. Functions live inside the database, so they centralise logic, win on round-trip count, and let you wrap complex queries in a name.

SQL and PL/pgSQL functions with returning types

EXAMPLE
-- 1) A simple SQL function (inlinable, planner-friendly)
CREATE OR REPLACE FUNCTION cents_to_dollars(cents bigint)
RETURNS numeric(10, 2)
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT
AS $$ SELECT cents / 100.0 $$;

-- Use it wherever an expression is allowed
SELECT id, cents_to_dollars(total_cents) AS total_aud FROM orders LIMIT 5;

-- 2) Function that reads from a table (use STABLE, not IMMUTABLE)
CREATE OR REPLACE FUNCTION customer_lifetime_value(p_customer_id bigint)
RETURNS numeric(12, 2)
LANGUAGE sql
STABLE
AS $$
  SELECT COALESCE(SUM(total_cents) / 100.0, 0)
  FROM orders
  WHERE customer_id = p_customer_id
    AND status IN ('paid', 'shipped');
$$;

-- 3) Set-returning function — call like a table
CREATE OR REPLACE FUNCTION top_customers(n int)
RETURNS TABLE(customer_id bigint, name text, ltv numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT c.id, c.name, customer_lifetime_value(c.id) AS ltv
  FROM customers c
  ORDER BY ltv DESC
  LIMIT n;
$$;

SELECT * FROM top_customers(10);

-- 4) PL/pgSQL function — full procedural language
CREATE OR REPLACE FUNCTION place_order(p_customer_id bigint, p_total_cents bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_order_id bigint;
BEGIN
  IF p_total_cents < 0 THEN
    RAISE EXCEPTION 'total must be >= 0';
  END IF;

  INSERT INTO orders(customer_id, total_cents, status, created_at)
  VALUES (p_customer_id, p_total_cents, 'new', 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;
$$;

SELECT place_order(42, 4995);

-- 5) RAISE for logging + EXCEPTION for control flow
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric LANGUAGE plpgsql AS $$
BEGIN
  IF b = 0 THEN
    RAISE NOTICE 'divide by zero attempted at %', clock_timestamp();
    RETURN NULL;
  END IF;
  RETURN a / b;
EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'unexpected: %', SQLERRM;
    RETURN NULL;
END;
$$;

-- 6) Mark VOLATILITY accurately
--    IMMUTABLE: same input -> always same output, no IO       (cents_to_dollars)
--    STABLE:    same within a statement (reads tables)         (customer_lifetime_value)
--    VOLATILE:  default; writes, random, now()                 (place_order)
-- The planner uses this to inline, cache, or push down.

-- 7) Permissions — grant EXECUTE separately from SELECT
GRANT EXECUTE ON FUNCTION place_order(bigint, bigint) TO app_user;
REVOKE INSERT, UPDATE, DELETE ON orders FROM app_user;

-- 8) Inspect
\df+ place_order
SELECT prosrc FROM pg_proc WHERE proname = 'place_order';

Why it matters

Mark functions IMMUTABLE / STABLE accurately. IMMUTABLE lets the planner constant-fold the call across the query; STABLE lets it cache the result within a single statement. VOLATILE (the default) blocks both — getting this wrong leaves dramatic performance on the table for free.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
CREATE OR REPLACE FUNCTION add(a int, b int)
RETURNS int AS $$
    SELECT a + b;
$$ LANGUAGE sql IMMUTABLE;
Try it Yourself »

Discussion

Loading…