Missing UNNEST functionality

[Mark L.]

Hi,

I wanted the parser to parse these both Postgres statements
1. SELECT unnest(array[41,2,13]);
2. SELECT * FROM unnest(array[41,2,13]);

But the parse throws the error: UNNEST not supported. Because we are using this feature very often I was wondering if there are any plans for implementing this feature?

Greets Mark

[Lukas Eder]

Hi Mark,

Thanks for your message and for pointing this out. This is a known limitation. There are quite a few things we still want to support in the parser (and / or in jOOQ, in general). The most recent list of open issues is here:

https://github.com/jOOQ/jOOQ/issues/7171

Do note that the first syntax you've referenced will not be supported by jOOQ and I personally advise against using it in PostgreSQL in general:

2018-04-25 10:34 GMT+02:00 Mark L. <spiri...@gmail.com>:

1. SELECT unnest(array[41,2,13]);

It is not very clear what the semantics projecting a table should be. E.g. what happens when you run

SELECT unnest(array[1, 2]), unnest(array[3, 4])

Curiously, this will not produce a cross join as one might expect, but pair (1, 3) and (2, 4), which I find very surprising, given that the first unnest produces a cross join with the hypothetical identity table.

This however:

 

2. SELECT * FROM unnest(array[41,2,13]);

Should be supported by the parser as it is also supported by jOOQ, already. I'll look into it in the context of issue #7171

Thanks,

Lukas

[Lukas Eder]

This however:

 

2. SELECT * FROM unnest(array[41,2,13]);

Should be supported by the parser as it is also supported by jOOQ, already. I'll look into it in the context of issue #7171

For the record, this is now supported in jOOQ 3.11

Thanks,

Lukas