Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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();
References to this page
Do you have any feedback about this page? We'd love to hear it!