Mastering PostgreSQL 12 - Third Edition

Using SPI for set-returning functions

In many cases, you don't just want to execute some SQL and forget about it. In most cases, a procedure will loop over the result and do something with it. The following example will show how you can loop over the output of a query. On top of that, I decided to beef up the example a bit, and make the function return a composite data type. Working with composite types in Perl is very easy, because you can simply stuff the data into a hash and return it.

The return_next function will gradually build up the result set until the function is terminated with a simple return statement.

The example in the following code generates a table consisting of random values:

CREATE TYPE random_type AS (a float8, b float8); ...