need connection support currentSchema

[rendongsc]

Hope that through the configuration can be specified default schema, like this
var cn = {
host: 'localhost',
port: 5432,
database: 'MyDB',
user: 'user1',
password: 'pw',
currentSchema:'C0001'
};
thanks!

[alainib]

me too it will be great ! thanks

[joskuijpers]

You can set the schema using a query:
SET SCHEMA 'schemaname'.

I think it will be better if you do this yourself, instead that we code this query into the library.

@brianc opinion?

[bramkoot]

Fair point. Could you suggest at what point you should run that query? This should run for every new connection that is setup, but before any other queries are executed. On/pool/connect doesn't seem to support an async/callback function so I'm curious what the best approach would be.

[hoegaarden]

Hi @bramkoot,
There is https://gist.github.com/hoegaarden/47f38c9b65844d78dba7 - that's pretty old and probably should be adapted. However I used that exactly to run some stuff once for each (new) connection.
Maybe that helps ... ?

[bramkoot]

Thanks, that was a nice idea. I've made another implementation that works for now, but it feels very hacky. For anyone who is interested, this is what I used;

const oldVersion = pg.Pool.prototype._create
pg.Pool.prototype._create = function (cb) {
	oldVersion.apply(this, [(err, client) => {
		if (err) cb(err)

		// setup client
		client.query('SET search_path TO users')
			.then(_args => {
				cb(null, client)
			})
			.catch(cb)
	}])
}

I'm using this in conjunction with pg-async and that works as expected. I would still be very interested in a proper implementation.

[hoegaarden]

I guess @brianc is happy to review a pull request ;)

[charmander]

See also #1393 (comment) to set arbitrary options.

[guikubivan]

Not sure how you would use @charmander's solution to set the search path, but the next comment worked for me: #1393 (comment)

pool.on('connect', (client) => {
  client.query(`SET search_path TO ${schema}, public`);
});