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

Inkling

Answer 1

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

const h = this.collection.$config.pgp.helpers;
const query = h.insert(values, null, 'branch') + 'RETURNING pk_branchID';
      
await 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:

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

const query = h.insert(data, cs) + 'RETURNING pk_branchID';
      
await 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:

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

see: Database.map

Esculent

Thank you. Originally, I had the SQL in a file but thought to use this way in order to cater for column values that can be null that may be missing from the request. – Inkling