Does jOOQ support the PostgreSQL array functions and operators?
array(select ... from ...)
array_length(array)
...
See http://www.postgresql.org/docs/current/static/functions-array.html for an overview.
edit
I have added an example query below. I have also added queries to create and fill in the tables, so try out the query.
drop table if exists person_country;
drop table if exists person;
drop table if exists country;
create table person
(
identifier integer not null,
name text not null,
primary key(identifier)
);
create table country
(
identifier integer not null,
name text not null,
primary key(identifier)
);
create table person_country
(
person_identifier integer not null,
country_identifier integer not null,
primary key(person_identifier, country_identifier),
foreign key(person_identifier) references person,
foreign key(country_identifier) references country
);
insert into person values(1, 'John');
insert into person values(2, 'Bill');
insert into person values(3, 'Jill');
insert into country values(1, 'Sweden');
insert into country values(2, 'China');
insert into country values(3, 'Germany');
insert into country values(4, 'Swiss');
insert into person_country values(1, 1);
insert into person_country values(1, 2);
insert into person_country values(1, 4);
insert into person_country values(2, 3);
insert into person_country values(2, 4);
select r.identifier, r.name, r.a1 a1
from
(
select p.identifier, p.name, array(select pc.country_identifier from person_country pc where pc.person_identifier = p.identifier) a1
from person p
) r
where array_length(a1, 1) >= 1;
Yes, jOOQ supports PostgreSQL arrays. Some popular functions are natively supported from PostgresDSL
. Not all vendor-specific built-in functions are supported, though, as there are simply too many. If you're ever missing a feature from jOOQ, you can always resort to "plain SQL" and create support for that function yourself. For instance:
@SuppressWarnings({ "rawtypes", "unchecked" })
static <T> Field<T[]> array(Select<? extends Record1<T>> select) {
return DSL.field("array({0})", (DataType)
select.getSelect().get(0).getDataType().getArrayDataType(), select);
}
static Field<Integer> arrayLength(
Field<? extends Object[]> array,
Field<Integer> dimension
) {
return DSL.field("array_length({0}, {1})", Integer.class, array, dimension);
}
Note that these particular functions will be supported out-of-the box in jOOQ 3.6 (#3985)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With