sql - 如何在一个 SQL 查询中使用 CTE 在 Postgres 中插入两个表
问题描述
我想通过一个使用 CTE 的查询将数据添加到 Postgres 中的两个表中。在用户从 table2 前端的表单提交数据后,我希望我的 SQL 查询在 table1 中插入 id、value1。然后将使用表一中相同的id来创建表2的数据。当我在 pgAdmin 中尝试下面的硬编码值时,代码的工作原理是为 table1 生成 id 并用于创建 table2 数据。
WITH ins AS (
INSERT INTO table1
(post_type, created_on)
VALUES
('keyword', 'NOW()')
RETURNING pid)
INSERT INTO table2
(pid, author, title, description, body, category, search_volume, is_deleted, created_on)
VALUES
((SELECT pid FROM ins), 'jet12', 'Head', 'Head is on top', 'Head is the most important part of the body', 'Head', '10000', 'false', 'NOW()')
但是,由于我将使用表单来填充数据,因此硬编码肯定行不通。我尝试了下面的代码,但似乎无法绕过它。
WITH ins AS (
INSERT INTO table1
(post_type, created_on)
VALUES
('keyword', 'NOW()')
RETURNING pid)
INSERT INTO table2
(pid, author, title, description, body, category, search_volume, is_deleted, created_on)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
请问如何编写查询以接受参数作为值?还有其他方法吗?请参阅下面的完整功能:
const keywordHandler = (req, res) => {
const values = [req.body.pid, req.body.username, req.body.title, req.body.description, req.body.body, req.body.category, req.body.search_volume, req.body.is_deleted, req.body.created_on]
pool.query(`WITH ins AS (
INSERT INTO table1
(post_type, created_on)
VALUES
('keyword', 'NOW()')
RETURNING pid)
INSERT INTO table2
(pid, author, title, description, body, category, search_volume, is_deleted, created_on)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
values, (k_err, k_res) => {
if (k_err) {
return console.error('Error executing query', k_err.stack)
}
res.json({
status: 'Keyword submitted successfully',
data: k_res.rows
});
})
};
解决方案
以下应该工作。
WITH ins AS (
INSERT INTO table1
(post_type, created_on)
VALUES
('keyword', now())
RETURNING pid
)
INSERT INTO table2
(pid, author, title, description, body, category, search_volume, is_deleted, created_on)
SELECT
ins.pid, $1, $2, $3, $4, $5, $6, $7, $8
FROM ins;
另一种选择——如果你可以选择的话,我会衷心推荐它——是使用 UUID 作为 id。那么您根本不需要 CTE 在两个语句之间传递值;只需在应用程序级别生成 UUID 并将其包含在您的两个插入语句中。由于生成重复 UUID 的可能性大约在“一年中每天赢得彩票大奖”的某个地方,在您的一生中几乎为零,因此应该将其视为具有额外好处的安全赌注。
推荐阅读
- react-native - 如何在 React Native(iOS 和 Android)中实现付费移除广告
- angularjs - 在测试中解决 CSV Stringify 承诺或在调用函数时结束测试?
- node.js - 导出函数并从不同的文件中调用它
- azure - 使用 sap .net 连接器将逻辑应用程序连接到 sap hana 服务器时出错
- node.js - Node JS 手动处理 HTTP 重定向并保留 cookie
- css - 如果输入已经填写,VueJS会更改输入的边框颜色
- python - 用python从左到右解码二维码
- php - 让本地 Symfony 2.8 应用程序像在 prod 环境中一样运行
- java - InputStream 不从 JAR 中读取某些文件
- r - 求一个变量被另一个变量子集化的平均值