Hi Malte, Thanks for your message
2018-02-06 9:34 GMT+01:00 'Malte Schirmacher' via jOOQ User Group < [email protected]>: > Hello, > > lets start with what i want to achieve: > I want to use the "generate_series" feature of postgresql with > timestamps/dates. > > I know there is DSL.generateSeries(Date, DatePart). But this version has a > huge drawback: DatePart doesn't know WEEK. What a pity! > > So i need to implement my own version which supports the WEEK as a date > part like so: > > DSL.table("generate_series({0}, {1}, {2}", timestamp1, timestamp2, > datePart); > > this kind of work. But now i want to JOIN on the result. But unfortunately > the resulting `Table` thingy is a crippled version of an actual table and > doesn't declare any fields i could reference in my query. > You can alias the table table("...").as("t", "a") and then create an appropriate field reference Field<Timestamp> a = field(name("t", "a"), Timestamp.class); Indeed, a bit of manual plumbing, but should be good enough as a workaround... > I then started to investigate how jooq itself implements > DSL.generateSeries(..) and found it basucally instantiates a new Object of > a class `GenerateSeries` which extends AbstractTable<Record1<Integer>> > Yes, that is implemented in a separate class to be able to emulate the clause in other databases. > Too bad i cannot extend from the very same Class. > As long as you stick to Java 8 or less, you could place your implementation in the org.jooq.impl package, as a workaround, to extend from AbstractTable. Of course, you shouldn't :) but you could. > but `Custom*` to the rescue! Which one could i extend from? CustomField? > nope. I want something i can use as a table. > > CustomTable maybe? Nope this on is intended to represent an actual table i > e.g. could insert into - i cant insert into a series. > But finally CustomQueryPart will do the job! Nope. The CustomQueryPart is > not allowed to declare any fields. > Good point, we should definitely enhance CustomTable to be able to wrap table expressions like you intend to do. There has been a recent pull request, due for jOOQ 3.11, which is closely related: https://github.com/jOOQ/jOOQ/pull/7115 I have created a feature request for this: https://github.com/jOOQ/jOOQ/issues/7145 > What to do now? Sure, i could implement the interface org.jooq.Table > comletly. But, honestly, who wants to implemnt a quadrillion of dummy > methods, just because DatePart is missing WEEK for YEARS now?! > Two things: 1. You'll have to add "dummy" methods anyway, because we don't implement the complete catalog of every vendor including every vendor specific function. Specifically, PostgreSQL has a ton and most of them can be generated by the code generator anyway... 2. Adding a non-SQL-standard DatePart isn't as easy as it looks. This type is used by all SQLDialects but most of them don't support parts other than the ones that exist. Of course, it would be great to have DatePart.WEEK... -- 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.
