如何使用pg-promise帮助器返回插入查询结果值 [英] How to return insert query result values using pg-promise helpers

问题描述

我正在使用 pgp.helpers.insert 来将数据保存到运行良好的PostgreSQL中。但是，我需要返回值以呈现在响应中。我正在使用：

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'))

不返回任何数据。

我想做的是在成功插入后返回分支ID，例如：

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

推荐答案

只需在生成的查询中附加 RETURNING ... 子句：

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);

如果要自动生成插入，必须在其中有一个大对象。生成多行插入/更新时，命名空间 helpers 最为重要，在这种情况下， ColumnSet 用作静态变量：

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);

请注意，在这种情况下，我们会。many ，因为希望返回1或更多行/结果。甚至可以将其转换为一个id-s数组：

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);

请参阅： Database.map

这篇关于如何使用pg-promise帮助器返回插入查询结果值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！