2

Back in jOOQ 2.5 it looks like you could set the PostgreSQL search_path through the FactoryOperations class, but that class is gone in jOOQ 3.5. Apparently FactoryOperations split into DSL and DSLContext, but I can't seem to find where the use(Schema) method wound up. How do we set the PostgreSQL search_path in more recent versions of jOOQ?

Fuwjax
  • 2,327
  • 20
  • 18
  • 1
    Are you aware that you can do an `alter user foo set search_path = ...` then you will always have the correct path, regardless of the tool. –  Jan 09 '15 at 22:23
  • @a_horse_with_no_name - Thank you for mentioning that. Unfortunately, in this instance the same user will need to access several identical schemas. – Fuwjax Jan 11 '15 at 23:55
  • Using different users (with different search paths) for different applications might be another solution. –  Jan 12 '15 at 06:52

1 Answers1

2

When jOOQ 3.0 was released, some of these "legacy" features were removed mostly because they weren't well thought through. In this particular case, the use(Schema) method pretended that the different RDBMS supported by jOOQ actually had a common idea of what the "current schema" is. The implementation, however, was a bit misleading and confusing to those users that use jOOQ with more than one database.

More information on the jOOQ 3.0 feature removals here.

The solution today is to

1. use vendor-specific commands like

ALTER USER my_user SET search_path = ...

More info about that here

2. use jOOQ's runtime schema mapping feature

Using this feature, you can specify at run time what the real schema name generated by jOOQ will be. For instance, if the schema in your generated code is FOO and the "ordinary" SQL statement generated would look like this:

SELECT foo.table.column FROM foo.table

You can specify a mapping from FOO to BAR to let jOOQ produce the following SQL instead, without touching your Java code:

SELECT bar.table.column FROM bar.table

More info about that here

Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • 1
    We are using jOOQ's runtime schema mapping. However, any user defined functions that contain schema-less references use the search path rather than the schema the function is defined in. Perhaps that warrants a different question? – Fuwjax Jan 11 '15 at 23:57
  • 1
    @Fuwjax: Hmm, very interesting - yes, definitely good material for another question! ([For future readers, the question is here](http://stackoverflow.com/q/27911948/521799)) – Lukas Eder Jan 13 '15 at 17:26