僅當第一次插入成功時才向表中插入值

有沒有辦法可以檢查插入查詢的狀態是否成功。僅當第一次插入成功時，我才想寫入另一個表。

下面是我要進行第一次插入的表，它還應該返回我需要插入到第二個表中的會話 ID。

對話表 -

create table conversations (
 id uuid not null,
 conversation_id text UNIQUE PRIMARY KEY not null,
 participants jsonb not null,
 created_at timestamp with time zone,
 updated_at timestamp with time zone,
 avatar_url varchar(500),
 last_message varchar(32000)
)

在第一個表上成功插入後，我應該使用返回的會話 ID 並在消息表中插入一條記錄。成功插入後，它應該返回返回的消息欄位的值。

消息表 -

create table messages (
 id uuid not null primary key,
 conversation_id text references conversations.conversation_id not null,
 message text,
 sender_id text,
 receiver_id text,
 message_type text,
 created_at timestamp with time zone DEFAULT now()
)

在兩次插入之後，我需要使用消息欄位的返回值再次更新第一個表。

這是執行上述操作的完整功能，以下功能在使用 select 呼叫時有效，但是當我通過客戶端呼叫它時出現此錯誤

-- Could not find the public.new_conversation(members, the_conversationId) function in the schema cache

CREATE OR REPLACE FUNCTION public.new_conversation(the_conversationId text, members jsonb, sent_message text)
RETURNS text
LANGUAGE plpgsql
AS $function$
declare
conv_id text;
the_message text;
BEGIN
  SELECT conversation_id into conv_id FROM conversations WHERE conversation_id = the_conversationId;
  if not found then
     insert into conversations(conversation_id, participants) values(the_conversationId,members) Returning conversation_id     into conv_id;
       if found then
           insert into messages(conversation_id,message) values(conv_id,sent_message) Returning message into the_message;
           if found THEN
           update conversations set last_message = the_message where conversation_id = conv_id;
           end if; 
       end if;
  end if;
  return conv_id;
END;
$function$

我應該為我的插入語句做任何異常處理嗎？

假設所涉及的表上沒有並發寫入，這個更簡單的函式應該涵蓋您描述的所有內容：

CREATE OR REPLACE FUNCTION public.new_conversation(INOUT _conv_id text, _members jsonb, _sent_message text)
 LANGUAGE plpgsql AS
$func$
BEGIN
  IF NOT EXISTS (SELECT FROM conversations WHERE conversation_id = _conv_id) THEN
     INSERT INTO conversations (conversation_id, participants, last_message)
     VALUES (_conv_id, _members, _sent_message);

     INSERT INTO messages (conversation_id, message)
     VALUES (_conv_id, _sent_message);
  END IF;
END
$func$

db<>在這裡擺弄

最重要的是，不要INSERT和以後UPDATE在同一行，當你可以INSERT一次。

我使用一個INOUT參數，這意味著輸入_conv_id將是輸出，因為無論如何我都不需要（需要）改變它。

我沒有看到任何需要EXCEPTION處理的東西。如果發生任何異常，則整個函式（始終在外部事務中執行，因此是原子的！）將回滾。

唯一剩下的謎團是id柱子的作用。由於這些是NOT NULL，除非它們具有預設值，否則您需要寫信給它們。

除了：隱含地PRIMARY KEY使列，這：UNIQUE NOT NULL

conversation_id text UNIQUE PRIMARY KEY not null,

應該只是：

conversation_id text PRIMARY KEY,

如果可以有並發寫入，事情就沒有那麼簡單了。看：

• 函式中的 SELECT 或 INSERT 是否容易出現競爭條件？

引用自：https://dba.stackexchange.com/questions/298919