如何将 sql 转换为具有多个语句的事务 - How to turn sql into a transaction with multiple statements?

How to turn sql into a transaction with multiple statements?

本文关键字：语句事务 sql 转换 | 更新日期: 2023-09-27

我有这个sql，我使用pg_query_params在php中运行。但是，如果我尝试将其转换为使用 BEGIN 的交易;和提交;我收到以下错误：Query failed: ERROR: cannot insert multiple commands into a prepared statement

是否可以在同一查询中准备具有多个语句的事务？或者我应该如何修改它以进行交易？

谢谢！

WITH usr1 AS (
         SELECT id FROM users WHERE mongoid = $1
     ),
     usr2 AS (
         INSERT INTO users (mongoid, shopid, idinshop, attributes)
           SELECT $1, $2, $3, $4
           WHERE  NOT EXISTS (SELECT 1 FROM usr1)
           RETURNING id
     ),
     ses1 AS (
         UPDATE sessions 
           SET traffic=$7, counts=$8 
           WHERE mongoid=$5 
           RETURNING id
     ),
     ses2 AS (
         INSERT INTO sessions (mongoid, shopid, userid, session, traffic, counts)
           SELECT $5, $2, (SELECT id FROM usr1 NATURAL FULL OUTER JOIN usr2),
                  $6, $7, $8
             WHERE  NOT EXISTS (SELECT 1 FROM ses1)
         RETURNING id
     )
INSERT INTO events (shopid, sessionid, userid, type, attributes, mongoid) 
  VALUES (
      $2,
      (SELECT id FROM usr1 NATURAL FULL OUTER JOIN usr2),
      (SELECT id FROM ses1 NATURAL FULL OUTER JOIN ses2),
      $9, $10, $11
);

然后我跑了：pg_query_params($db, $sql, array(...))

你不需要begin/commit块。整个语句本身就是一个事务

create table t (id int primary key);
with s as (
    insert into t values (1) 
    returning id
)
insert into t (id)
select id from s
;
ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (id)=(1) already exists.

CTE（with...）插入已回滚：

select * from t;
 id 
----
(0 rows)

http://sqlfiddle.com/#!12/0eeef/2

从手册：

PostgreSQL实际上将每个SQL语句视为在事务中执行。如果不发出 BEGIN 命令，则每个单独的语句都有一个隐式 BEGIN 和（如果成功）COMMIT 环绕。