假设我有两个表（states，postal_codes），用户可以在postal_code中插入新的邮政编码及其状态。
| 状态ID|状态码|州名|
| - -----|- -----|- -----|
| 1| 'FL'|佛罗里达|
| 2|“嘎”|'格鲁吉亚'|
| 邮政编码|状态ID|
| - -----|- -----|
| “一二三四五”|1|
| ‘五四三二一’|2|
是否可以使用BEFORE INSERT触发器来允许INSERT INTO命令具有state_code，并且函数将其替换为适当的state_id？
我可以做如下事情：

CREATE OR REPLACE FUNCTION insert_postal_code()
RETURNS TRIGGER
AS $$
BEGIN
    SELECT s.state_id
    FROM states s
    WHERE s.state_code = NEW.state_code
    INTO NEW.state_id;
    RAISE NOTICE '%', NEW;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER insert_postal_code_trigger
    BEFORE INSERT
    ON postal_codes
    FOR EACH ROW
    EXECUTE FUNCTION insert_postal_code();

INSERT INTO postal_codes (postal_code, state_code)
VALUES ('12345', 'FL');

还是每次都要将SELECT包含到INSERT中？

INSERT INTO postal_codes (postal_code, state_id)
SELECT '12345', s.state_id
FROM states s
WHERE s.state_code = 'FL';