如果满足条件，PostgreSQL 删除行，否则更新一些值

Question

我正在尝试根据一个简单的条件删除一行，如果满足条件，则删除该行，否则更新该行。基本上我用 is_deleted 标记我的数据，所以如果 is_deleted 等于 true，则执行删除，如果不是，则将 is_deleted 值设置为 true。

现在我正在做的是一个简单的查询，从查询的数据中检查 is_deleted 并根据它做一些事情。

用 clojure 编写的非常简单的伪代码($n 只是位置参数的占位符):

(defn find-by-username 
  [db username]
  "SELECT * FROM users WHERE username = $1") ;; to illustrate the query

(defn delete-by-username-impl
  [db username]
  "DELETE FROM users WHERE username = $1")

(defn set-is-deleted
  [db username]
  "UPDATE users SET is_deleted = true WHERE username = $1")

(defn delete-by-username 
  [db username]
  (let [user (find-by-username db username)]
    (if (:is-deleted user)
      (delete-by-username-impl db username)
      (set-is-deleted db username))))

我正在寻找的是只访问数据库一次。

Answer 1

只访问数据库一次是什么意思？运行一个查询？然后您可以将所有查询合并为一个:

IF (SELECT is_deleted FROM users WHERE username = $1) THEN
  DELETE FROM users WHERE username = $1
ELSE
  UPDATE users SET is_deleted = true WHERE username = $1
END IF;

如果你的意思是一起运行所有查询或者在一个独立的上下文中不运行它们(这样其他客户端不能在查询运行时改变状态，比如 is_deleted 标志)，你可以包装单独的查询使用 database transaction 进入交易:

Database transactions are available to ensure that multiple operations are performed atomically (i.e., all or none). The clojure.java.jdbc/with-db-transaction macro creates a transaction-aware connection from the database specification. Use the transaction-aware connection for the duration of the transaction:

;; Insert two new fruits atomically
(jdbc/with-db-transaction [trans-conn db-spec]
  (jdbc/insert! trans-conn :fruit {:name "Fig" :cost 12})
  (jdbc/insert! trans-conn :fruit {:name "Date" :cost 14}))
;; -> ({:grade nil, :unit nil, :cost 14, :appearance nil, :name "Date"})