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;… and then:
select * from f_2(1);
And the above will return:
+----+----+ | v2 | v3 | +----+----+ | 1 | 2 | +----+----+
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:
select * from f_3(1);
And the above will return:
+----+----+ | v2 | v3 | +----+----+ | 1 | 2 | | 2 | 4 | +----+----+
And we can LATERAL join that function to other tables if we want:
select * from book, lateral f_3(book.id)
… which might yield, for example:
+----+--------------+----+----+ | id | title | v2 | v3 | +----+--------------+----+----+ | 1 | 1984 | 1 | 2 | | 1 | 1984 | 2 | 4 | | 2 | Animal Farm | 2 | 4 | | 2 | Animal Farm | 4 | 6 | +----+--------------+----+----+
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);
… and the output:
routine_name | data_type | parameter_name | data_type -------------+-----------+----------------+---------- f_1 | integer | v1 | integer f_1 | integer | v2 | integer f_2 | record | v1 | integer f_2 | record | v2 | integer f_2 | record | v3 | integer f_3 | record | v1 | integer f_3 | record | v2 | integer f_3 | record | v3 | integer
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;Which will give us:
routine_name | parameter_name | data_type | position | -------------+----------------+-----------+----------+ f_3 | v2 | integer | 1 | f_3 | v3 | integer | 2 |
How to run such queries in jOOQ?
Once the above code is generated, you can easily call the table-valued function in any jOOQ query. Consider again the BOOK example (in SQL):
select * from book, lateral f_3(book.id)
… and with jOOQ:
DSL.using(configuration) .select() .from(BOOK, lateral(F_3.call(BOOK.ID))) .fetch();
The returned records then contain values for:
record.getValue(F_3.V2); record.getValue(F_3.V3);
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!)
| Reference: | PostgreSQL’s Table-Valued Functions from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
