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:
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:
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:
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!)
Like this:
Like Loading...
