How to skip unsupported syntax/commercial features with JOOQ parser?

I am using JOOQ SQL parser to process some CREATE TABLE statements on DDL files, which I cannot modify myself. These files contain various statements, such as CREATE FUNCTION, which I don't need.

I tried using an H2 database to execute said DDL files and then get the information via Meta / information schema, but some of these DDL files use unsupported syntax for H2.

I am currently using the parser like this:

String sql = """
create function fn_1(
  param1 text,
  param2 text
) returns boolean as $$
  select true
$$ language sql immutable;

create table if not exists example (
  id bigint not null
);

create or replace function fn_2(
  param1 numeric,
  param2 numeric
) returns boolean as $$
  select true
$$ language sql immutable;
"""
Settings settings = new Settings();
settings.setParseIgnoreCommercialOnlyFeatures(true);
settings.setParseUnsupportedSyntax(ParseUnsupportedSyntax.IGNORE);
Parser parser = DSL.using(dialect, settings).parser();
parser.parse(sql);

I understand that create function parsing is a commercial feature, which is why I set ParseIgnoreCommercialOnlyFeatures to true, but parser.parse(sql) still throws an error, saying it cannot parse fn_1:

org.jooq.impl.ParserException: ALIAS, FUNCTION, GENERATOR, GLOBAL TEMPORARY TABLE, INDEX, OR ALTER, OR REPLACE, PRIVATE SYNONYM, PROCEDURE, PUBLIC ALIAS, PUBLIC SYNONYM, SCHEMA, SEQUENCE, SYNONYM, TABLE, TEMPORARY TABLE, TRIGGER, TYPE, UNIQUE INDEX, or VIEW expected: [1:8] create [*]function fn_1(
  param1 text,
  param2 text
) returns boolean as $$
  select tru...

I would like to ask if there is a way to use the parser to extract only create table statements, ignoring everything else. I read the DDLDatabase documentation here, which mentions skipping unsupported content via comments, but I would need a different mechanism for skipping unsupported content, because as said, I cannot modify these DDL files.

Thanks in advance

Solution

The ParseIgnoreCommercialOnlyFeatures flag tells the parser to ignore statements, functions, etc. that aren't supported in the jOOQ Open Source Edition. But ignoring them simply means that they're not known to the parser, so you get the same exception that you would get if the parser encountered unsupported syntax (e.g. vendor specific storage clauses, etc.)

because as said, I cannot modify these DDL files

But you can! After reading the files, do some heuristics where you remove any create function or similar statement. Luckily, you don't have to parse the entirety of PL/pgSQL since PostgreSQL function bodies are contained in string literals, usually delimited by dollar quoted literals ($$ .. $$). That makes it easy to match them with a regex. A pragmatic solution here is to match create( or replace)? function.*?\\$\\$.*?\\$\\$.*?; or similar.

Alternatively, you can try upgrading, of course.