Re: Is the UNNEST function standard and/or widespread?
[x-posted to comp.lang.java.programmer, since this is about JDBC and a
java embedded database]
For the benefit of cljp readers, UNNEST is an SQL-standard function which
takes an array value and returns a table-like value, which you can use in
join and IN conditions; i want it because i can use it to write queries
that are like "select everything with an ID in this set".
On Sun, 9 May 2010, Tom Anderson wrote:
FWIW, H2 doesn't support UNNEST. It does have something similar in the shape
of a TABLE pseudo-function, which lets you create temporary tables inline,
and into which you can substitute array parameters. In JDBC syntax:
SELECT * FROM TABLE(x INTEGER = ?);
You can put an array in as the parameter. Although H2 doesn't support the
java.sql.Array type; you have to use a normal java array instead. And you
can't use TABLE with IN; this:
SELECT * FROM thing WHERE thing_id IN TABLE(selected_id INTEGER = ?);
doesn't work. You have to rewrite it as a join:
Correction - you can also whip up a closer approximation of UNNEST, which
can be used in an IN clause, by hand:
SELECT * FROM thing WHERE thing_id IN (SELECT ARRAY_GET(?1, X) AS selected_id FROM SYSTEM_RANGE(1, ARRAY_LENGTH(?1)));
(the AS is unnecessary but informative)
That makes a 100% genuine subquery, so i'd be optimistic about the query
planner doing something sensible here.
tom
--
When the facts change, I change my mind. What do you do, sir? -- John
Maynard Keynes