SQL data access characteristics
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some dialects require the explicit specification of a few characteristics of a function, defining what kind of content a function is allowed (and expected) to have. These act both as contracts for your development (similar to the java.lang.FunctionalInterface annotation), as well as hints to the database regarding whether a function is expected to have side-effects (and thus maybe cannot be used in a SELECT statement), or whether it depends on data, or is purely deterministic (see also the DETERMINISTIC characteristic). The SQL data access characteristics include:
-
NO SQL -
CONTAINS SQL -
READS SQL DATA -
MODIFIES SQL DATA
While the semantics seem pretty clear, please refer to your database manual for the details, as there may be subtle differences, e.g. regarding what particular procedural statement constitues "SQL".
If a characteristic is not supported by your dialect, you can still specify it, and jOOQ will simply ignore it in generated SQL.
create.createFunction("f1").returns(INTEGER).noSQL().as(return_(1)).execute();
create.createFunction("f2").returns(INTEGER).containsSQL().as(return_(select(val(1)))).execute();
create.createFunction("f3").returns(INTEGER).readsSQLData().as(return_(selectCount().from(BOOK))).execute();
create.createFunction("f4").returns(INTEGER).modifiesSQLData().as(
insertInto(LOGS).columns(LOGS.TEXT).values("Function F4 was called"),
return_(1)
).execute();
This works just like SQL data access characteristics for procedures
Feedback
Do you have any feedback about this page? We'd love to hear it!