Thank you very much for sharing this, Joe - very interesting approach indeed!
I'll comment on some parts, inline 2015-08-25 13:18 GMT+02:00 <[email protected]>: > > > On Wednesday, April 16, 2014 at 6:41:23 PM UTC+10, Ben Hood wrote: >> >> Hey Lukas, >> >> I was wondering if you've come across an example of using crosstabs in >> Postgres with JOOQ? >> >> Cheers, >> >> Ben >> > > > For what it's worth, the following utility function is what I use to make > use of crosstab combined with JOOQ. > > > public class PostgresWrapper > { > > public static Result<Record> pivot(DSLContext context, Select<?> raw, > Select<?> crossTabValues) > { > List<Field<?>> resultFields = new ArrayList<Field<?>>(); > > //The result will contain all but the last two columns for the > raw fields. > > Field<?> []rawFields = raw.fields(); > for (int i = 0; i < rawFields.length - 2; i++) > { > resultFields.add(rawFields[i]); > } > > //And then one column for each cross tab value specified > Result<?> crossTabHeadings = context.fetch(crossTabValues); > for (Record r : crossTabHeadings) > { > resultFields.add > ( > DSL.field > ( > r.getValue(0).toString(), > rawFields[rawFields.length - 2].getDataType(context. > configuration()) > ) > ); > } > > //And postgres requires that the names of the resultant fields be > specified > // explicitly, using 'ct' <"Name of Field", type> pairs... > StringBuffer ctList = new StringBuffer(); > for (int i = 0; i < resultFields.size(); i++) > { > ctList.append > ( > "\"" + resultFields.get(i).getName() + "\" " > + resultFields.get(i).getDataType(context. > configuration()) > .getTypeName(context. > configuration()) > ); > > if (i < resultFields.size() - 1) > { > ctList.append(", "); > } > } > Perhaps, you could use DSL.list(QueryPart...) here. It will construct a QueryPart that models a comma-separated list of other QueryParts. You can then use ordinary DSLContext.renderInlined() API to construct the SQL. Perhaps this makes things simpler...? > > > return > context > .fetch > ( > "select * from crosstab('" > + raw.getSQL(ParamType.INLINED).replace("'", "''") > + "', '" > + crossTabValues.getSQL(ParamType.INLINED).replace > ("'", "''") > + "') as ct(" + ctList.toString() + " )" > ); > } > } > That looks fine. Alternatively, you could use jOOQ's plain SQL templating to let jOOQ do the string escaping. Something along the lines of context.fetch( resultQuery( "select * from crosstab({0}, {1}) as ct({2})", val(raw.getSQL(ParamType.INLINED)), val(crossTabValues.getSQL(ParamType.INLINED)), ctList // If you construct it using DSL.list() ) ); > Any feedback on this approach would be appreciated. I couldn't work out a > way to make it more type-safe, and I'm not sure that is possible. > I doubt that you can do much more for a PIVOT query. We're facing the same issue with jOOQ's built-in Oracle / SQL Server PIVOT support. It's just not easy to produce type safe Record[N] types for all the possible permutations of pivot columns. Besides, a PIVOT operation might quickly produce more than jOOQ's 22 columns. In any case, thanks again for your display. We'll certainly take some inspiration when implementing https://github.com/jOOQ/jOOQ/issues/3190 Cheers, 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.
