Custom Set-Returning Functions

In this lesson, you will learn how to write custom functions that return multiple rows—or "sets"—to treat procedural logic like a virtual table.

Understanding Set-Returning Functions (SRFs)

Normally, a standard SQL function returns a single value (a scalar). However, there are scenarios where you need a function to act like a table source, allowing you to use it directly in the FROM clause of a query. PostgreSQL achieves this through Set-Returning Functions (SRFs).

When you use the RETURNS SETOF clause, the function generates a stream of results. Internally, PostgreSQL manages the state of the function execution, yielding values one by one until the set is exhausted.

Concrete Example: The Split-String Iterator

Imagine you have a comma-separated string of IDs and you need to join them against a real table. Instead of writing complex string-parsing logic in every query, you can encapsulate that logic in an SRF:

CREATE OR REPLACE FUNCTION split_to_rows(input_text text)
RETURNS SETOF integer AS $$
DECLARE
    item text;
BEGIN
    FOREACH item IN ARRAY string_to_array(input_text, ',')
    LOOP
        RETURN NEXT item::integer;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Once defined, you can treat the output like any other relation:

SELECT users.name
FROM users
JOIN split_to_rows('101,102,105') AS id_list(val) 
  ON users.id = id_list.val;

Why use SRFs?

1. Abstraction: You hide complex business logic behind a clean table-like interface.
2. Performance: By processing logic on the server side in a single set-based operation, you reduce the need for iterative client-side processing.
3. Flexibility: You can return SETOF record or even define a custom TABLE structure, allowing you to return multiple columns per row, effectively simulating a temporary view that accepts parameters.

Keep in mind that when using SRFs, they are evaluated for every row in the query if placed in the SELECT list, which can lead to unexpected "Cartesian product" behavior. It is almost always cleaner and more predictable to place them in the FROM clause.

Key Takeaway: Set-Returning Functions allow you to transform procedural code into virtual tables, enabling complex data generation and decomposition directly within your SQL FROM clause.