Right, let’s talk about getting data out of your functions. This is where the rubber meets the road, and where I’ve seen more developers get tripped up than on a poorly placed extension cord. You’ve got three main ways to do it: RETURNS, INOUT parameters, and RETURNS TABLE. They’re not just different syntaxes; they’re different tools for different jobs. Picking the wrong one is like using a sledgehammer to put a picture hook in the wall—it’ll work, but you’re going to look silly and probably damage the drywall.

The Workhorse: RETURNS

This is your bread and butter. You define the data type the function will spit out right there in the header. It’s straightforward, predictable, and the optimizer loves it. Use this when you’re returning a single, well-defined thing: an integer, a UUID, a JSONB object, or a single row from a custom composite type.

CREATE OR REPLACE FUNCTION get_customer_email(p_customer_id INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_email TEXT;
BEGIN
    SELECT email INTO v_email
    FROM customers
    WHERE customer_id = p_customer_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Customer with ID % not found', p_customer_id;
    END IF;

    RETURN v_email;
END;
$$;

Why is this great? Clarity. Anyone (including future you at 3 AM) can look at that function signature and know exactly what they’re getting: a piece of text. The RETURN statement is the final word; it ends the function’s execution and hands off the value. Simple.

The Two-Way Street: INOUT Parameters

Ah, INOUT. The concept is simple: a parameter that acts as both input and output. You pass a value in, the function mangles it, and the same parameter now holds the new value. It feels clever, like a neat party trick.

CREATE OR REPLACE FUNCTION increment_counter(INOUT p_counter INT)
LANGUAGE plpgsql
AS $$
BEGIN
    p_counter := p_counter + 1;
    -- No RETURN statement needed; the INOUT param *is* the return.
END;
$$;

-- To call it:
SELECT * FROM increment_counter(5); -- returns 6

Here’s my brutally honest take: I almost never use these. Why? They make the function signature opaque. You can’t see what’s being returned without reading the entire definition. It breaks the beautiful left-to-right, input-to-output flow of a standard function call. They have their niche, like when you’re retrofitting a function and want to avoid changing its signature, but for new code? I’d usually just use a separate OUT parameter or a standard RETURNS. They’re a bit too clever for their own good.

The Power Tool: RETURNS TABLE

This is how you return multiple rows with multiple columns. Think of it as defining a virtual table on the fly. This is your go-to for any function that acts as a more complex, parameterized view.

CREATE OR REPLACE FUNCTION get_active_orders(p_customer_id INT)
RETURNS TABLE (
    order_id INT,
    order_date TIMESTAMPTZ,
    total_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.created_at, o.total
    FROM orders o
    WHERE o.customer_id = p_customer_id
      AND o.status = 'active';
END;
$$;

-- Use it just like a table:
SELECT * FROM get_active_orders(123);

The magic sauce here is RETURN QUERY, which appends the result of that SELECT statement to the function’s output. You can call RETURN QUERY multiple times, and it will happily union all the results together. It’s incredibly powerful.

But here’s the pitfall, the one that gets everyone: performance. RETURN QUERY doesn’t just return the query plan; it executes the query and materializes the results. If you do this multiple times in a loop, you’re building the result set in memory, which can get ugly fast for large datasets. For set-returning functions, the gold standard is using RETURNS SETOF your_table and then RETURN QUERY SELECT ...—this lets the optimizer do its thing much more effectively, often streaming results instead of building a giant intermediate set.

So, choose wisely. Use RETURNS for a single scalar or a single composite value. Use RETURNS TABLE (or better yet, RETURNS SETOF) when you need a result set. And maybe just wave politely at INOUT parameters as you walk past them to the more useful tools.