postgresql觸發器將倒數第三個字符與屬性進行比較

我最近開始學習 PostgreSQL，但在創建觸發器時遇到了麻煩。在特定情況下，我應該檢查男運動員不能參加女子比賽，反之亦然；在 match_code 屬性中，插入“M”或“F”作為倒數第三個字符，以標識該種族是男性還是女性（例如：“Q100x4M06”）；性別屬性中僅存盤一個字符，即“M”或“F”。因此，我需要了解如何比較它們并在它們未正確輸入到參與表中時激活觸發器。這是我的假設，但我知道這是錯誤的，這只是一個想法，有人可以幫助我嗎？

CREATE FUNCTION check()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $BODY$
DECLARE
    x VARCHAR;
    y VARCHAR;
BEGIN
    SELECT match_code INTO x FROM race;
    SELECT gender INTO y FROM athlete;
    IF $x LIKE '&$y__' == athlete.gender
        THEN
        RETURN new;
    ELSE
        RAISE EXCEPTION $$It is not possible to add an athlete of a gender that is not compatible with the competition$$;
        RETURN NULL;
    END IF;
END;
$BODY$

CREAT TRIGGER triggerCheck
BEFORE INSERT OR UPDATE ON participation
FOR EACH ROW
EXECUTE PROCEDURE check();

以下是表的定義：

CREATE TABLE race (
    ID_g SERIAL NOT NULL,
    code_race VARCHAR (20) PRIMARY KEY,
    r_date DATE,
    discipline VARCHAR (20) NOT NULL
);

CREATE TABLE athlete (
    ID_a SERIAL NOT NULL,
    code_athlete INT CHECK (codice_atleta >= 0 AND codice_atleta <= 15000) PRIMARY KEY,
    name VARCHAR (30),
    surname VARCHAR (30) NOT NULL,
    nation VARCHAR (3) NOT NULL,
    gender CHAR CHECK (gender = 'M' OR gender = 'F'),
    b_date DATE,
    sponsor VARCHAR (20)
);

CREATE TABLE participation (
    ID_p SERIAL NOT NULL,
    codR VARCHAR (20) REFERENCES race (code_race) ON DELETE CASCADE ON UPDATE CASCADE,
    codA INT REFERENCES athlete (code_athlete) ON DELETE CASCADE ON UPDATE CASCADE,
    arrival_order INT CHECK (arrival_order > 0),
    r_time TIME DEFAULT '00:00:00.00',
    PRIMARY KEY (codG, codA)
);

uj5u.com熱心網友回復：

根據表定義，您的觸發器函式應該類似于：

CREATE FUNCTION check()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $BODY$
BEGIN       
    IF EXISTS ( SELECT 1
                  FROM race AS r
                 INNER JOIN athlete AS a
                    ON r.code_race ~ (a.gender  || '..$')
                 WHERE r.code_race = NEW.codR
                   AND a.code_athlete = NEW.codA
              ) 
    THEN
        RETURN NEW ;
    ELSE
        RAISE EXCEPTION 'It is not possible to add an athlete of a gender that is not compatible with the competition';
        RETURN NULL;
    END IF;
END;
$BODY$

在觸發器呼叫的函式中，您可以（必須）使用變數NEW(resp. OLD) 以便參考要插入或更新（僅要更新）的新行（相應的舊行）目標表（"participation"在您的情況下）請參閱手冊。

建議的函式不需要宣告任何變數，因為可以直接通過建議的sql查詢執行測驗。

建議的 sql 查詢：

• 在表中的行第一搜索"race"其code_race等于插入/更新值participation.codR =NEW.codR
• 然后，在表搜索的行"athlete"，其code_athlete等于participation.codA的插入/更新值=NEW.codA
• 最后比較兩個表中選定的行"race"并 "athlete"使用正則運算式將其code_race與gender值進行比較，請參閱手冊

順便一提，

(a) 我serial在表定義中沒有看到 type 列 ID 的附加值，特別是因為它們沒有用于任何主鍵和外鍵。

(b) 為code_race屬性定義的重要編碼（例如：定義性別的倒數第三個字符，定義競爭級別的另一個字符，......）是 30 年前計算機科學使用的一種相當老式的做法容量和性能有限。在更新的方法中，我建議您在表的專用列中管理這些有意義的資訊"race"，然后，如果您確實需要一個重要的組合code_race，將其實作為生成的列：

CREATE TABLE race 
( ID_g SERIAL NOT NULL PRIMARY KEY
, gender_race CHAR(1) NOT NULL CHECK (gender_race IN ('M', 'F'))
, competition_level VARCHAR(12) NOT NULL CHECK (competition_level IN ('Q-Qualifiers', 'H-Heats', 'S-Semifinals', 'F=Finals'))
, code_race VARCHAR (20) GENERATED ALWAYS AS (ID_g :: text || '-' || discipline || '-' || gender_race || '-' || left(competition_level, 1)) STORED
, r_date DATE NOT NULL
, discipline VARCHAR (20) NOT NULL
);

在db<>fiddle 中查看結果。

標籤：

上一篇：從陣列Bigquery中減去日期

下一篇：插入新行時添加帶有自動日期的列