Hello Julien,
As normally you would always use a connection pool (like BoneCP or c3p0), you can easily configure an InitSQL property to initialize your connection as needed.
Note that JDBC driver for now does not support search_path at all, as the OID cache lookup is not taking it into an account. So prepare for some crazy problems when for example returning a type that exists in several schemas with the same name.
With best regards,
-- Valentine
On Tue, Jul 31, 2012 at 2:41 AM, David Johnston
<polobo@yahoo.com> wrote:
There have been a number of messages on this list suggesting that the Postgres JDBC driver should support setting the search path as part of the connection string.
I have a use case where there doesn't appear to be any good alternatives: using BIRT, with multiple clients sharing a Postgres database with one schema each, one cannot just execute a SET SCHEMA statement (UPDATE pg_settings ... is not possible either) and qualifying object names is also not always possible (say, if there are stored procedures involved that use unqualified object names).
Besides being useful in this case and surely others, this feature makes sense: schemas can be used to offer separation of database objects very similar to that offered by having multiple databases, and the database itself is part of the connection string.
Julien
You can customize the connection string to accomplish this. You vary the “user” that is connecting and configure a search_path specific to that user.
ALTER ROLE custom_role SET search_path = …
David J.