Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15

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();

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo