Add support for a SPLIT table valued function

[lukaseder]

BigQuery supports SPLIT(), which splits a string to an array, which could be unnested: https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split

PostgreSQL has a similar function called regexp_split_to_array. We could support a table valued function that splits a string into an array or table. The table version can probably be emulated in other dialects using recursive SQL.

Emulations:

• BigQuery: SPLIT(): https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split
• PostgreSQL: REGEXP_SPLIT_TO_TABLE: https://www.postgresql.org/docs/current/functions-matching.html
• SQL Server: STRING_SPLIT: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16

[mrclrchtr]

I am trying to understand the following warning and have come across this issue. Does this have anything to do with it?

Ambiguous type name      : The database object public.regexp_matches generates a class de.mrclrchtr.holz.generated.persistence.entities.tables.RegexpMatchesTable (class org.jooq.meta.postgres.PostgresTableValuedFunction) which conflicts with the previously generated class de.mrclrchtr.holz.generated.persistence.entities.tables.RegexpMatchesTable (class org.jooq.meta.postgres.PostgresTableValuedFunction). Use a custom generator strategy to disambiguate the types. More information here:
 - https://www.jooq.org/doc/latest/manual/code-generation/codegen-generatorstrategy/
 - https://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/
Ambiguous type name      : The database object public.regexp_split_to_table generates a class de.mrclrchtr.holz.generated.persistence.entities.tables.RegexpSplitToTableTable (class org.jooq.meta.postgres.PostgresTableValuedFunction) which conflicts with the previously generated class de.mrclrchtr.holz.generated.persistence.entities.tables.RegexpSplitToTableTable (class org.jooq.meta.postgres.PostgresTableValuedFunction). Use a custom generator strategy to disambiguate the types. More information here:
 - https://www.jooq.org/doc/latest/manual/code-generation/codegen-generatorstrategy/
 - https://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/

[lukaseder]

Does this have anything to do with it?

No. Can you please create a new support request with more complete information?

[apptio-speravali]

Hi, is there a timeline when this will be released? As we use BigQuery Dialect and the query parser fails when the SQL statement contains the SPLIT function.

Are there any workaround you could suggest?

[lukaseder]

@apptio-speravali Thanks for your message.

This issue isn't strictly the same thing as what you're requesting. This issue is about a SPLIT style table valued function,w hich we might support in the DSL API in the future. You're requesting support for parsing the BigQuery specific SPLIT function (input: String, output: String[]). We currently don't have a means of representing the parsed output for that function in our query object model, but you can easily add support for it on your end using a ParseListener:

• https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/sql-parser-listener/