Table-valued functions are an awesome thing. Many databases support them in one way or another and so does PostgreSQL. In PostgreSQL, (almost) everything is a table. For instance, we can write:
CREATE OR REPLACE FUNCTION
f_1 (v1 INTEGER, v2 OUT INTEGER)
AS $$
BEGIN
v2 := v1;
END
$$ LANGUAGE plpgsql;
… and believe it or not, this is a table! We can write:
select * from f_1(1);
And the above will return:
+----+
| v2 |
+----+
| 1 |
+----+
It’s kind of intuitive if you think about it. We’re just pushing out a single record with a single column. If we wanted two columns, we could’ve written:
CREATE OR REPLACE FUNCTION
f_2 (v1 INTEGER, v2 OUT INTEGER, v3 OUT INTEGER)
AS $$
BEGIN
v2 := v1;
v3 := v1 + 1;
END
$$ LANGUAGE plpgsql;
That’s useful, but those are just single records. What if we wanted to produce a whole table? It’s easy, just change your functions to actually return TABLE types, instead of using OUT parameters:
CREATE OR REPLACE FUNCTION f_3 (v1 INTEGER)
RETURNS TABLE(v2 INTEGER, v3 INTEGER)
AS $$
BEGIN
RETURN QUERY
SELECT *
FROM (
VALUES(v1, v1 + 1),
(v1 * 2, (v1 + 1) * 2)
) t(a, b);
END
$$ LANGUAGE plpgsql;
When selecting from the above very useful function, we’ll get a table like so:
In fact, it appears that the keyword LATERAL is optional in this case, at least for PostgreSQL.
Table-valued functions are very powerful!
Discovering table-valued functions
From jOOQ’s schema reverse-engineering perspective, things might get a bit tricky as can be seen in this Stack Overflow question. PostgreSQL deals with OUT parameters in a very similar way as with TABLE return types. This can be seen in the following query against the INFORMATION_SCHEMA:
SELECT r.routine_name, r.data_type, p.parameter_name, p.data_type
FROM information_schema.routines r
JOIN information_schema.parameters p
USING (specific_catalog, specific_schema, specific_name);
As you can see, the output is really indistinguishable from that perspective. Luckily, we can also join the pg_catalog.pg_proc table, which contains the relevant flag to indicate if a function returns a set or not:
SELECT r.routine_name,
r.data_type,
p.parameter_name,
p.data_type,
pg_p.proretset
FROM information_schema.routines r
JOIN information_schema.parameters p
USING (specific_catalog, specific_schema, specific_name)
JOIN pg_namespace pg_n
ON r.specific_schema = pg_n.nspname
JOIN pg_proc pg_p
ON pg_p.pronamespace = pg_n.oid
AND pg_p.proname = r.routine_name
ORDER BY routine_name, parameter_name;
Now, we’re getting:
routine_name | data_type | parameter_name | data_type | proretset
-------------+-----------+----------------+-----------+----------
f_1 | integer | v1 | integer | f
f_1 | integer | v2 | integer | f
f_2 | record | v1 | integer | f
f_2 | record | v2 | integer | f
f_2 | record | v3 | integer | f
f_3 | record | v1 | integer | t
f_3 | record | v2 | integer | t
f_3 | record | v3 | integer | t
We can see that f_3 is the only function actually returning a set of record, unlike f_1 and f_2, which only return a single record.
Now, remove all those parameters that are not OUT parameters, and you have your table type:
SELECT r.routine_name,
p.parameter_name,
p.data_type,
row_number() OVER (
PARTITION BY r.specific_name
ORDER BY p.ordinal_position
) AS ordinal_position
FROM information_schema.routines r
JOIN information_schema.parameters p
USING (specific_catalog, specific_schema, specific_name)
JOIN pg_namespace pg_n
ON r.specific_schema = pg_n.nspname
JOIN pg_proc pg_p
ON pg_p.pronamespace = pg_n.oid
AND pg_p.proname = r.routine_name
WHERE pg_p.proretset
AND p.parameter_mode = 'OUT'
ORDER BY routine_name, parameter_name;
All that typesafety is only available in the upcoming jOOQ 3.5, for free! (SQL Server, Oracle, and HSQLDB table-valued functions are already supported!)