Node.js中的PostgreSQL多行更新

Question

我已经在Stackoverflow上找到了相关信息，可以通过以下方式在一个查询中更新多行：

update test as t set
    column_a = c.column_a,
    column_c = c.column_c
from (values
    ('123', 1, '---'),
    ('345', 2, '+++')  
) as c(column_b, column_a, column_c) 
where c.column_b = t.column_b;

特别感谢@Roman Pekar提供的清晰答案。

现在我正在尝试将此更新方式与在NodeJS中查询PostgreSQL数据库的方法合并。

以下是我的代码片段:

var requestData = [
    {id: 1, value: 1234}
    {id: 2, value: 5678}
    {id: 3, value: 91011}
]


client.connect(function (err) {
    if (err) throw err;

client.query(buildStatement(requestData), function (err, result) {
    if (err) throw err;

    res.json(result.rows);

    client.end(function (err) {
        if (err) throw err;
    });
});
});


var buildStatement = function(requestData) {
var params = [];
var chunks = [];

for(var i = 0; i < requestData.length; i++) {

    var row = requestData[i];
    var valuesClause = [];

    params.push(row.id);
    valuesClause.push('$' + params.length);
    params.push(row.value);
    valuesClause.push('$' + params.length);

    chunks.push('(' + valuesClause.join(', ') + ')');

}

return {
    text: 'UPDATE fit_ratios as f set ratio_budget = c.ratio_budget from (VALUES ' +  chunks.join(', ') + ') as c(ratio_label, ratio_budget) WHERE c.ratio_label = f.ratio_label', values: params
        }
}

我没有收到任何错误提示，但是我的表并没有被更新，我真的不知道出了什么问题。也许是我的查询语句有语法错误？在NodeJS pg包中进行多行查询的更新操作的具体示例我并没有找到。

- Peter

Answer 1

下面的示例基于库pg-promise和其方法helpers.update：

// library initialization, usually placed in its own module:
const pgp = require('pg-promise')({
    capSQL: true // capitalize all generated SQL
});

const db = pgp(/*your connection details*/);

// records to be updated:
const updateData = [
    {id: 1, value: 1234},
    {id: 2, value: 5678},
    {id: 3, value: 91011}
];

// declare your ColumnSet once, and then reuse it:
const cs = new pgp.helpers.ColumnSet(['?id', 'value'], {table: 'fit_ratios'});

// generating the update query where it is needed:
const update = pgp.helpers.update(updateData, cs) + ' WHERE v.id = t.id';
//=> UPDATE "fit_ratios" AS t SET "value"=v."value"
//   FROM (VALUES(1,1234),(2,5678),(3,91011))
//   AS v("id","value") WHERE v.id = t.id

// executing the query:
await db.none(update);

生成多行更新的方法可以描述为：

• 非常快，因为它依赖于实现了智能缓存的类型ColumnSet来生成查询。
• 完全安全，因为所有数据类型都通过库的查询格式化引擎进行，以确保所有内容都被正确格式化和转义。
• 非常灵活，由于支持列定义的高级ColumnConfig语法。
• 非常易于使用，由于pg-promise实现的简化接口。

---

请注意，在列id前面使用?表示该列是条件的一部分，但不会被更新。有关完整列语法，请参见类Column和ColumnConfig结构。

---

相关问题：使用pg-promise进行多行插入。

- vitaly-t

---

v.id = t.id 中的 v 和 t 是从哪里来的？ - dbainbridge

@dbainbridge 从helpers.update文档中，只需按照提供给您的链接进行操作。 - vitaly-t

Answer 2

首先，我接受了@vitaly-t的答案，因为他教我使用了一个更好、更快的库pg-promise，并且解决了我的问题。(句号)

但是为了回答那些可能遇到同样问题并想继续使用pg库的人，这就是我犯的错误（只是语法）

在我的原始代码中，我在结尾处有这一行

return {
    text: 'UPDATE fit_ratios as f set ratio_budget = c.ratio_budget from (VALUES ' +  chunks.join(', ') + ') as c(ratio_label, ratio_budget) WHERE c.ratio_label = f.ratio_label', values: params
        }
}

第一次看到这个时很难理解，所以很容易犯错。将这行代码改为您在下面看到的内容可以修复我的原始问题。

return {
    text: 'UPDATE fit_ratios as f set ratio_budget = c.value from (VALUES ' +  chunks.join(', ') + ') as c(id, value) WHERE c.id = f.ratio_id',
                values: params
}

我刚刚犯了一个错误，使用了列名而不是对象的键。（例如 c.ratio_label 而不是 c.id 等）

- Peter

---

这不容易受到SQL注入攻击吗？ - Mr.P