PostgreSQL触发器报错：record 'new'无'user_id'字段求助

执行的插入语句 #

INSERT INTO cs222p_interchange.Ad(ad_id, plan, content, pic_num, item_id, seller_user_id, placed_date)
VALUES ('ADT32457', 'Gold', 'New games available!', 1, 'F7E1N', '4Z5VC', '2022-11-06');

错误上下文 #

ERROR:  record "new" has no field "user_id"
CONTEXT:  SQL statement "INSERT INTO TargetedAds(ad_id, user_id)
SELECT NEW.ad_id, NEW.user_id WHERE (
       SELECT category
       FROM cs222p_interchange.item i
       JOIN cs222p_interchange.ad a ON i.item_id = a.item_id
     WHERE ad_id = NEW.ad_id AND (buyer_user_id = NEW.user_id OR seller_user_id = NEW.user_id) )
LIKE (
       SELECT category
       FROM cs222p_interchange.User u
       JOIN cs222p_interchange.Categories c  ON u.user_id = c.user_id
       WHERE user_id = NEW.user_id)
       ON CONFLICT DO NOTHING"
PL/pgSQL function addad() line 1 at SQL statement

触发器及函数定义 #

CREATE FUNCTION AddAd() RETURNS Trigger AS 
$$ 
BEGIN 
INSERT INTO TargetedAds(ad_id, user_id) 
SELECT NEW.ad_id, NEW.user_id 
WHERE (
        SELECT * 
        FROM cs222p_interchange.item i 
        JOIN cs222p_interchange.ad a ON i.item_id = a.item_id 
        WHERE ad_id = NEW.ad_id AND (buyer_user_id = NEW.user_id OR seller_user_id = NEW.user_id) )
= (
        SELECT category 
        FROM cs222p_interchange.User u 
        JOIN cs222p_interchange.Categories c  ON u.user_id = c.user_id 
        WHERE user_id = NEW.user_id) 
        ON CONFLICT DO NOTHING; 
RETURN NEW; 
END; 
$$ 
LANGUAGE PLPGSQL; 

CREATE TRIGGER TargetedAdsLogger AFTER INSERT ON cs222p_interchange.Ad FOR EACH ROW EXECUTE FUNCTION AddAd();

TargetedAds表结构 #

CREATE TABLE TargetedAds( 
  ad_id text, 
  user_id text, 
  PRIMARY KEY (ad_id, user_id), 
  FOREIGN KEY (ad_id) REFERENCES cs222p_interchange.Ad(ad_id) ON DELETE CASCADE, 
  FOREIGN KEY (user_id) REFERENCES cs222p_interchange.Seller(user_id) ON DELETE CASCADE 
); 

Ad表结构 #

CREATE TABLE cs222p_interchange.Ad(
        ad_id        text NOT NULL,
        plan text NOT NULL ,
        content text ,
        pic_num int NOT NULL,
        item_id text NOT NULL,
        seller_user_id text NOT NULL,
        placed_date date NOT NULL,
        PRIMARY KEY (ad_id),
        FOREIGN KEY(item_id) REFERENCES cs222p_interchange.Item(item_id) ON DELETE CASCADE,
        FOREIGN KEY(pic_num, item_id) REFERENCES cs222p_interchange.Picture(pic_num, item_id) ON DELETE CASCADE,
        FOREIGN KEY(seller_user_id) REFERENCES cs222p_interchange.Seller(user_id) ON DELETE CASCADE
);

错误原因 #

1. 字段不存在：Ad表结构中没有user_id字段，只有seller_user_id，但触发器函数AddAd()中多次引用NEW.user_id，NEW代表插入Ad表的新记录，自然没有该字段，直接触发报错。
2. 逻辑语法错误：原函数中用SELECT *返回的多列结果和单列的category做等于比较，这是语法错误，即使user_id字段存在也无法正常执行。

修正方案 #

根据需求（推送广告给与商品有交易关系且兴趣分类匹配的用户），重新编写触发器函数：

CREATE OR REPLACE FUNCTION AddAd() RETURNS Trigger AS 
$$ 
BEGIN 
    -- 插入符合条件的用户到TargetedAds
    INSERT INTO TargetedAds(ad_id, user_id)
    SELECT NEW.ad_id, u.user_id
    FROM cs222p_interchange.Item i
    -- 获取与该商品有交易关系的用户（买家或卖家）
    JOIN (
        SELECT buyer_user_id AS user_id FROM cs222p_interchange.Item WHERE item_id = NEW.item_id
        UNION
        SELECT seller_user_id AS user_id FROM cs222p_interchange.Item WHERE item_id = NEW.item_id
    ) AS item_users ON true
    -- 关联用户的兴趣分类
    JOIN cs222p_interchange.Categories c ON item_users.user_id = c.user_id
    JOIN cs222p_interchange."User" u ON c.user_id = u.user_id
    -- 匹配商品分类和用户兴趣分类
    WHERE i.category = c.category
      AND i.item_id = NEW.item_id
    ON CONFLICT DO NOTHING;
    
    RETURN NEW;
END; 
$$ 
LANGUAGE PLPGSQL;

修正说明 #

• 移除了对不存在的NEW.user_id的引用，改为从Item表获取与商品相关的买家/卖家用户ID
• 修正了多列与单列比较的语法错误，通过JOIN关联表来匹配分类条件
• 使用UNION去重获取商品的关联用户，避免重复插入
• 保留ON CONFLICT DO NOTHING避免主键冲突

内容的提问来源于stack exchange，提问作者Canjian Chen