DDLDatabase throws parse error 'DEFAULT custom_function_name()'

[sreuland]

Expected behavior and actual behavior:

referencing the OSSP uuid functions as the default value on columns in ddl and jooq DDLDatabase codegen fails on that syntax:

CREATE TABLE tbl 
(
  pkey UUID NOT NULL DEFAULT uuid_generate_v1() , 
)

Steps to reproduce the problem (if possible, create an MCVE: https://github.com/jOOQ/jOOQ-mcve):

is there any workaround for this, other than doing alter statements wrapped with [jooq ignores] on each column after the create?

Versions:

• jOOQ:3.13.1
• Java:1.14
• Database (include vendor): Postgres
• OS: linux
• JDBC Driver (include name if inofficial driver):

[lukaseder]

Thanks a lot for your report. This should definitely work. We'll look into it. There are several steps here:

• Support parsing user-defined function calls (this is supported in the parser via a flag, but not currently active in the DDLDatabase)
• Support ignoring this function call in the DDLDatabase, because we don't know the function, and neither does H2, which is currently being used behind the scenes by the DDLDatabase

is there any workaround for this, other than doing alter statements wrapped with [jooq ignores] on each column after the create?

You could also add those comments inside of the CREATE TABLE statement, e.g.

CREATE TABLE tbl (
  pkey UUID NOT NULL /* [jooq ignore start] */DEFAULT uuid_generate_v1() /* [jooq ignore stop] */,
  ...
)

You don't have to do this manually. You could write a preprocessor that processes your SQL script with some regular expression search/replace prior to passing the script to jOOQ.

[lukaseder]

Looking into this now. There's no way to keep the function name around with our current infrastructure:

• We're not "remembering" the source DDL (though we could and maybe should?)
• We can't use the function in H2

Assuming:

• This is mainly about DEFAULT or GENERATED ALWAYS AS columns
• The actual function name isn't too important for the generated code
• The current workaround removes the DEFAULT clause entirely

I'd say that simply replacing the DEFAULT function_call() expression by a DEFAULT NULL expression should improve things. At least, now the generated code knows the column has a DEFAULT, even if the actual expression isn't available.

[lukaseder]

Fixed in jOOQ 3.19.0. I don't want to backport this, because I fear it could introduce very subtle regressions in client code that worked around this issue thus far. Given there has always been an acceptable workaround, I don't think a backport is of the essence.