php - 如何将sql转换为具有多个语句的事务？

我有一个sql，我在php中使用pg_query_params运行它。但是，如果我尝试用BEGIN；and COMMIT将其转换为事务，则会出现以下错误：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
从manual开始：
PostgreSQL实际上将每个SQL语句都视为在事务中执行。如果不发出BEGIN命令，则每个语句都有一个隐式BEGIN和（如果成功）COMMIT包装。

关于php - 如何将sql转换为具有多个语句的事务？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18822662/