Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Table-valued functions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some databases support functions that can produce tables for use in arbitrary SELECT statements. jOOQ supports these functions out-of-the-box for such databases. For instance, in SQL Server, the following function produces a table of (ID, TITLE) columns containing either all the books or just one book by ID:
CREATE FUNCTION f_books (@id INTEGER)
RETURNS @out_table TABLE (
id INTEGER,
title VARCHAR(400)
)
AS
BEGIN
INSERT @out_table
SELECT id, title
FROM book
WHERE @id IS NULL OR id = @id
ORDER BY id
RETURN
END
The jOOQ code generator will now produce a generated table from the above, which can be used as a SQL function:
// Fetching all books records
Result<FBooksRecord> r1 = create.selectFrom(fBooks(null)).fetch();
// Lateral joining the table-valued function to another table using CROSS APPLY:
create.select(BOOK.ID, F_BOOKS.TITLE)
.from(BOOK.crossApply(fBooks(BOOK.ID)))
.fetch();
Feedback
Do you have any feedback about this page? We'd love to hear it!