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
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!