Hi Rob
2014-09-23 0:32 GMT+02:00 Rob Ward <[email protected]>:
> I got the Postgresql tsrange operator working with JOOQ, and figured it
> might be helpful for others and generate a useful discussion. The
> following is probably highly ill-advised and totally unsupported by JOOQ.
>
Let's see :-) jOOQ is made for extension via plain SQL. So, quite possibly,
you're still very much within the range of what is well-advised.
> 1) Using tsrange values and operators in a query:
> To use a tsrange as a condition in a where clause:
>
>
> String condition = "appointment.period && tsrange('2015-01-01 00:00:00',
> '2015-02-01 00:00:00')";
> List<Appointment> appointments =
> DSL.select().from(APPOINTMENT).where(condition).fetchInto(Appointment.class);
> // JOOQ POJO
>
That's how the where(String) method is intended to be used. Of course, this
will not be using bind values, so the number of distinct parsed SQL
statements may explode if this kind of predicate is used very often.
You can, of course, further integrate with the jOOQ API to allow for
reusing certain expressions. E.g.
public static Condition ampAmp(Field<?> left, Timestamp from, Timestamp to)
{
return ampAmp(left, DSL.val(from), DSL.val(to));
}
public static Condition ampAmp(Field<?> left, Field<Timestamp> from,
Field<Timestamp> to) {
return DSL.condition("{0} && tsrange({1}, {2})", left, from, to);
}
> 2) Inserting tsrange values:
> The only way I've found to do this is with a workaround - having the start
> and end values as columns and using a trigger to update the tsrange column.
> Example:
>
> create table appointment (
> appointment_id serial primary key,
> start timestamp not null,
> end timestamp not null,
> period tsrange not null,
> );
>
> create or replace function appointment_range() returns trigger as
> $appointment_range$
> begin
> new.period := tsrange(new.start::timestamp, new.end::timestamp);
> return new;
>
> end;
> $appointment_range$ language plpgsql;
>
> create trigger appointment_range before insert or update on appointment
> for each row execute procedure appointment_range();
>
>
> Of course, the limitation here is that you cannot change whether the range
> limits are inclusive or not inclusive at runtime.
>
> Initially I tried passing a String to AppointmentRecord.setPeriod similar
> to: tsrange('2015-01-01 00:00', '2015-02-01 00:00') but that fails with the
> exception: "ERROR: column "period" is of type tsrange but expression is of
> type character varying. Hint: You will need to rewrite or cast the
> expression.". I also tried the same thing with insertInto(...).set(...),
> with the same result.
>
Yes, unfortunately, PostgreSQL is a bit demanding with users explicitly
casting to the target type, even if it could be inferred from the statement
context. In the upcoming jOOQ 3.5, we'll be implementing TypeProviders,
which allow for specifying exactly how each <T> type should be serialised /
deserialised to JDBC. This will then be the basis for custom extensions
that allow for all these vendor-specific types, which we don't want to
hard-code into jOOQ.
While you cannot pass custom expressions to UpdatableRecords right now, you
could of course also use plain SQL with customer INSERT or UPDATE
statements. Something along the lines of:
DSL.using(configuration)
.insertInto(APPOINTMENT, PERIOD)
.values(DSL.field("tsrange(?, ?)", from, to))
.execute();
This would mean you'd be missing out on the UpdatableRecord's store()
method and other features.
Hope this helps,
Lukas
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
