What are scalar functions vs table-valued functions?

Scalar function — returns one value

CREATE FUNCTION age_in_years(birth_date DATE)
RETURNS INT
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT EXTRACT(YEAR FROM AGE(birth_date))::INT;
$$;

SELECT name, age_in_years(birth_date) AS age FROM people;

Used exactly like a built-in function (UPPER(), COALESCE()) — once per row, in a SELECT list, WHERE, ORDER BY, anywhere a single expression is valid.

Table-valued function — returns a full result set

CREATE FUNCTION orders_for_customer(cust_id INT)
RETURNS TABLE (order_id INT, order_date DATE, total NUMERIC)
LANGUAGE sql
AS $$
    SELECT id, order_date, total FROM orders WHERE customer_id = cust_id;
$$;

-- Used in FROM, like a table or view, but parameterized:
SELECT * FROM orders_for_customer(42) WHERE total > 100;

This is effectively a parameterized view — a regular view can't accept arguments, but a table-valued function can, letting you encapsulate a reusable, parameterized query the same way you'd encapsulate a parameterless one in a view.

Performance characteristics differ meaningfully

• Scalar functions called per-row in a large query can be a significant performance trap — if a scalar function is invoked once per row of a million-row query and internally runs its own additional query, that's a million extra queries hiding behind what looks like a simple function call in the SELECT list. This is a common, easy-to-miss source of slow reports.
• Table-valued functions, especially ones written in pure SQL (not a procedural language), are often inlined by the optimizer much like a view — the outer query's filters can sometimes be pushed down into the function's body, unlike a procedural scalar function that behaves as an opaque black box to the optimizer.

Prefer table-valued functions (or plain views/CTEs) over scalar functions for anything data-set-oriented — reserve scalar functions for lightweight, genuinely per-value computations (formatting, simple math, type conversions) that don't themselves need to query other tables. If you find yourself writing a scalar function that runs a SELECT internally and it's called across many rows, that's usually a sign the logic should be restructured as a join or table-valued function instead.