Does jOOQ support PostgreSQL array functions and operators?

Question

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;

Do you mean this? jooq.org/javadoc/3.3.x/org/jooq/util/postgres/PostgresDSL.html — Marko Topolnik
– Marko Topolnik, Commented Jan 25, 2015 at 14:41

Lukas Eder · Accepted Answer · 2015-01-26 13:30:13Z

13

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)

edited Jan 26, 2015 at 13:30

answered Jan 26, 2015 at 7:56

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jef Jedrison Over a year ago

Damn, that templating is very flexible! Very nice solution. Thank you for your feedback.

Lukas Eder Over a year ago

Yes, jOOQ can't possibly support all those vendor-specific features, but often they're very easy to integrate and compose on a string basis... This way, you can hide the implementation behind your own custom API, which is again type safe.

Jef Jedrison Over a year ago

If you want to be as complete as possible, you might also want to add the conversion from a single field to an array containing that field:

public static <T> Field<T[]> array(final Field<T> field) { return DSL.field("array[{0}]", (DataType) field.getDataType().getArrayDataType(), field); }

Lukas Eder Over a year ago

@JefJedrison: Hmm, yes. Though, I wonder if that one should be added to DSL itself, as it'll work also on HSQLDB and H2... Also, this method should probably take a varargs argument... I've created #4006 for this.

Collectives™ on Stack Overflow

Does jOOQ support PostgreSQL array functions and operators?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related