上篇只做出了大部分功能，有很多細節沒做好，這篇會修正細節與優化

PostgreSQL IF statement

我會用到PostgreSQL的IF statement，來改掉上篇的INSERT部分

DO
$do$
BEGIN
   IF EXISTS (SELECT FROM orders) THEN
      DELETE FROM orders;
   ELSE
      INSERT INTO orders VALUES (1,2,3);
   END IF;
END
$do$

DO
$do$
BEGIN
IF EXISTS(SELECT uid FROM group_buying_user WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}') AND uid='{profile.user_id}' ) THEN
	UPDATE group_buying_user SET name='{profile.user_id}',quantity='{event.message.text[4::]}' WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}' and uid='{profile.user_id}');
ELSE
	INSERT INTO group_buying_user (mid, uid, name, quantity) VALUES ((SELECT mid FROM group_buying_message WHERE keyword='{key}'),'{profile.user_id}','{profile.display_name}','{event.message.text[4::]}');
END IF;
END
$do$			

修改

再+1時如果同時要顯示團購訊息跟名單，延遲太多我造成伺服器錯誤
我把顯示團購訊息跟名單加到關鍵字的if裡
+1 時資料會傳至資料庫，並顯示 關鍵字XXX+1
關鍵字
關鍵字 +1

if(event.message.text in keywords): 
        key=event.message.text
        profile = line_bot_api.get_profile(event.source.user_id)
        cursor=conn.cursor()
        #將取得的key作為關鍵字在group_buying_message裡找到訊息文字
        cursor.execute(f"SELECT message_text FROM group_buying_message WHERE keyword='{key}';")
        message_text = "".join(cursor.fetchone())
        #用key在group_buying_message裡找到訊息文字的mid，再用mid從message_emoji裡找到LINE emoji資訊
        cursor.execute(f"SELECT index,product_id,emoji_id FROM message_emoji WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}');")
        rows = cursor.fetchall()           
        emojis=[]
        #將資料一筆一筆寫入list中
        for row in rows:
            emojis.append({'index': row[0],'productId': row[1],'emojiId': row[2]})
        cursor.execute(f"SELECT uid,name,quantity FROM group_buying_user WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}');")
        users = cursor.fetchall()

        for user in users:
            message_text=message_text+"\U0001F449"+"".join(user[1])+" "+"".join(user[2])+"\n"
        message=TextSendMessage(message_text,emojis)
        line_bot_api.reply_message(event.reply_token,message)

if(event.message.text[:event.message.text.find(" "):] in keywords and len(event.message.text)>event.message.text.find(" ")):
        conn.commit()
        lastLength=event.message.text.find(" ")
        key=event.message.text[:lastLength:]
        profile = line_bot_api.get_profile(event.source.user_id)
        cursor=conn.cursor()
        cursor.execute(f"DO $do$ BEGIN if EXISTS(SELECT uid FROM group_buying_user WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}') and uid='{profile.user_id}' ) then UPDATE group_buying_user SET name='{profile.display_name}',quantity='{event.message.text[lastLength+1::]}' WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}' and uid='{profile.user_id}'); else INSERT INTO group_buying_user (mid, uid, name, quantity) VALUES ((SELECT mid FROM group_buying_message WHERE keyword='{key}'),'{profile.user_id}','{profile.display_name}','{event.message.text[lastLength+1::]}'); end if; END $do$")
        conn.commit()
        message=key+" "+profile.display_name+" "+event.message.text[lastLength+1::]
        message=TextSendMessage(message)
        line_bot_api.reply_message(event.reply_token,message)