Hi Garth, You're right, this syntax is not supported in PostgreSQL 9.3. Do note, however, that it will be supported in a future version: http://www.postgresql.org/docs/devel/static/sql-update.html.
Given that PostgreSQL will soon add support, I'm a bit reluctant of offering this sort of transformation for 9.3 and less, as it appears to be quite hard to get right. Are you aware of any formal proof that there are formal transformations that produce equivalent results using UPDATE .. FROM? Note that we have annotated all of jOOQ's API with @Support annotations. In the case of UpdateSetFirstSte.set(Row[N], Select), we have: @Support <http://www.jooq.org/javadoc/3.4.x/org/jooq/Support.html>(value <http://www.jooq.org/javadoc/3.4.x/org/jooq/Support.html#value-->={DB2 <http://www.jooq.org/javadoc/3.4.x/org/jooq/SQLDialect.html#DB2>,H2 <http://www.jooq.org/javadoc/3.4.x/org/jooq/SQLDialect.html#H2>,HSQLDB <http://www.jooq.org/javadoc/3.4.x/org/jooq/SQLDialect.html#HSQLDB>,INGRES <http://www.jooq.org/javadoc/3.4.x/org/jooq/SQLDialect.html#INGRES>,ORACLE <http://www.jooq.org/javadoc/3.4.x/org/jooq/SQLDialect.html#ORACLE>})<T1,T2> UpdateFromStep <http://www.jooq.org/javadoc/3.4.x/org/jooq/UpdateFromStep.html><R <http://www.jooq.org/javadoc/3.4.x/org/jooq/UpdateSetFirstStep.html>> set(Row2 <http://www.jooq.org/javadoc/3.4.x/org/jooq/Row2.html><T1,T2> row, Select <http://www.jooq.org/javadoc/3.4.x/org/jooq/Select.html><? extends Record2 <http://www.jooq.org/javadoc/3.4.x/org/jooq/Record2.html><T1,T2>> select)Specify a multi-column set clause for the UPDATE statement. See also: - http://www.jooq.org/javadoc/latest/org/jooq/UpdateSetFirstStep.html#set-org.jooq.Row2-org.jooq.Select- - http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/sql-dialects/ Right now, we cannot issue compilation warnings, although we do have a feature request on the road map that will use these @Support annotations to do precisely that: https://github.com/jOOQ/jOOQ/issues/1027 One idea that we've had would be using an API preprocessor, that would physically remove all methods from the binaries that cannot be called in the context of your database dialect. Another idea would be to use JSR-308: http://types.cs.washington.edu/jsr308 I've met Mike Ernst (from the JSR-308 spec lead team) in person. He was actually quite thrilled about this solution. It's something we'll certainly look into for jOOQ 3.6. The idea is that you can annotate your DAOs or even your packages with some annotation, e.g. @Require(POSTGRES). An annotation checker would then kick in as a compiler plugin and issue warnings or errors whenever you bind to jOOQ API that does not have a @Support(POSTGRES) annotation on it. But until then, I'm afraid that you will have to consider the Javadoc to see if a clause is supported in your dialect. Hope this helps, Lukas 2014-11-11 21:17 GMT+01:00 <[email protected]>: > I noticed that the sql code generated by > > db.update(table1) > .set(DSL.row(fields...), > db.select(more fields) > .from(table2) > .where()) > > Generates code of the form: > > update table1 set (fields) = (select table2...) > > Which is not supported on postgres. According to postgres docs ( > http://www.postgresql.org/docs/9.3/static/sql-update.html). > > Ideally this would be transformed into UPDATE FROM, however a compilation > warning/error would be ok too. > > -- > 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. > -- 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.
