我寫了以下函式：
CREATE OR REPLACE FUNCTION process_init_transfer(
holder_email TEXT,
order_key BIGINT, ticket_key BIGINT,
email_address TEXT,
first_name TEXT, last_name TEXT
) RETURNS TEXT AS $$
DECLARE
_id BIGINT;
_token TEXT;
_title TEXT;
_tickets_count INTEGER;
_transfers_count INTEGER;
BEGIN
SELECT
COUNT(*), events.name
INTO
_tickets_count, _title
FROM
tickets, purchases, ticket_books, events
WHERE
purchases.email = holder_email AND
purchases.id = order_key AND
tickets.order_id = purchases.id AND
tickets.id = ticket_key AND
ticket_books.id = tickets.book_id AND
events.id = ticket_books.event_id
GROUP BY
events.name;
IF _tickets_count <> 1 THEN
RAISE EXCEPTION 'Error finding your ticket, contact support';
END IF;
SELECT
COUNT(*)
INTO
_transfers_count
FROM
ticket_transfers
WHERE
ticket_id = ticket_key AND
(status = 'PENDING' OR status = 'ACCEPTED' OR status = 'CONFIRMED');
IF _transfers_count > 0 THEN
RAISE EXCEPTION 'Ticket already transfered, contact support if you believe this is an error';
END IF;
INSERT INTO
ticket_transfers (ticket_id, email, name, surname, token)
VALUES
(ticket_key, email_address, first_name, last_name, random_string(16))
RETURNING
token INTO _token;
INSERT INTO
emails (payload, template_id)
VALUES
(json_build_object('recipient', holder_email, 'ticket', ticket_key,
'email', holder_email, 'title', _title, 'token', _token), 7)
RETURNING
id INTO _id;
RETURN _id;
END;
$$ LANGUAGE plpgsql;
有幾個實體函式會引發例外。我理解這應該中止函式執行的檔案。然而，我不相信這就是正在發生的事情。
我看到以下錯誤：insert or update on table "ticket_transfers" violates foreign key constraint。這意味著盡管有例外，執行達到：INSERT INTO ticket_transfers. 這不應該發生。我希望以下內容會停止執行
IF _tickets_count <> 1 THEN
RAISE EXCEPTION 'Error finding your ticket, contact support';
END IF;
請指教！
uj5u.com熱心網友回復：
第一個SELECT可能設定_tickets_count為 NULL，因為查詢不回傳任何行。由于NULL <> 1不是 TRUE，因此不會觸發錯誤。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/526204.html