How to return insert query result values using pg-promise helpers

Question

I am using pgp.helpers.insert to save data into PostgreSQL which is working well. However, I need to return values to present in a response. I am using:

this.collection.one(this.collection.$config.pgp.helpers.insert(values, null, 'branch'))

which returns no data.

What I want to be able to do is return the branch id after a successful insert, such as:

INSERT into branch (columns) VALUES (values) RETURNING pk_branchID

Answer 1

Simply append the RETURNING... clause to the generated query:

var h = this.collection.$config.pgp.helpers;
var query = h.insert(values, null, 'branch') + 'RETURNING pk_branchID';

return this.collection.one(query);

You must have a large object there if you want to automatically generate the insert. Namespace helpers is mostly valued when generating multi-row inserts/updates, in which case a ColumnSet is used as a static variable:

var h = this.collection.$config.pgp.helpers;
var cs = new h.ColumnSet(['col_a', 'col_b'], {table: 'branch'});
var data = [{col_a: 1, col_b: 2}, ...];

var query = h.insert(data, cs) + 'RETURNING pk_branchID';

return this.collection.many(query);

Note that in this case we do .many, as 1 or more rows/results are expected back. This can even be transformed into just an array of id-s:

return this.collection.map(query, [], a => a.pk_branchID);

see: Database.map