postgres set returning functions SRF - ghdrako/doc_snipets GitHub Wiki

• Set Returning Functions (SRFs) in PostgreSQL are powerful tools that allow functions to return a set of rows, much like a table. These functions are useful when you want to encapsulate complex queries or calculations and reuse them as if they were a table.
• Set Returning Functions can be defined in SQL or PL/pgSQL and are typically used to return result sets that can be queried like a regular table. They are particularly useful for breaking down complex logic into reusable components.
• Set returning functions return a set of rows, similar to a table.

The below query creates a function to return all films rented by a specific customer:

CREATE OR REPLACE FUNCTION get_rented_films(customer_id
INT) RETURNS TABLE (film_id INT, title VARCHAR) AS $$
BEGIN
RETURN QUERY
SELECT f.film_id, f.title
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
WHERE r.customer_id = get_rented_films.customer_id;
END;
$$ LANGUAGE plpgsql;

Calling the set returning function.

SELECT * FROM get_rented_films(1);