Learning PostgreSQL 11 - Third Edition

Set-returning functions

Functions in PostgreSQL can return not only single values, but also relations. Such functions are called set-returning functions.

This is a typical task for every SQL developer: generate a sequence of numbers, each in a separate record. This sequence or relation can have many use cases. For example, suppose you need to select data from the car_portal database to count the number of cars for each year of manufacture, from 2010 till 2015, and you need to show zero for the years where no cars exist in the system at all. A simple SELECT query from the only car table cannot be used to implement this logic. It isn't possible to count records that are not present in the table data.

That's why it would be useful if there was ...