Thank you for the answer. It works as expected.
But I realized that it will not solve my original problem I have in mind. Actually, I'm tryting to come up with some more or less ANSI compliant solution for the following issue:
I have two (or more) columns in database that represents the state of some single complex object. Taking an example from above, let's imagine that "bar" and "baz" are parts of one object. In case of "foo" contains scalar value (not collection) of the object, both columns "bar" and "baz" will have scalar types (not array):
create table foo (
id varchar(256) primary key,
bar integer,
baz varchar(256)
);
and I can select using following query:
select * from foo where bar = ... and baz = ...But in case of "foo" contains collection of the this objects, both columns "bar" and "baz" become array types:
create table foo (
id varchar(256) primary key,
bar integer array,
baz varchar(256) array
);
and if I select using following query:
select * from foo where ... = any(bar) and ... = any(baz)it will give me false matches, as I need to compare only values from both arrays of the same ordinality.
While unnest in H2 supports unnesting of multiple arrays, it is impossible to use arrays taken from the currently selected row (it seems it is unable to reference columns of outside query from unnest finction). like this:
select * from foo where exists (select 1 from unnest ((select bar from foo where id = foo.id), (select baz from foo where id = foo.id)) as e(e1, e2) where e1 = '...' and e2 = '...')In PostgreSQL referencing columns of curretly selected row from unnest is possible though. As well as it supports more clean way, allowing to use column references directly instead of array expressions within the unnest function:
select * from foo where exists (select 1 from unnest (bar, baz) as e(e1, e2) where e1 = '...' and e2 = '...')BTW, Is there are any plans to support latest?
Thanks!
With respect,
Artem