PostgreSQL’s Table-Valued Functions
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. |