sql - PostgreSQL 触发器错误 : 'column "t"of relation "inventory_product" does not exist'

Question

我正在尝试创建一个触发器，根据插入到 中的 qty 减少 inventory_product 表中的 qty 列>account_sale。触发器适用于 MySQL(使用不同的语法)，但我不确定 PostgreSQL 版本有什么问题。

当我在 inventory_sale 上运行插入时，我得到:

error: column "t" of relation "inventory_product" does not exist

触发器:

CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.product_id IS NOT NULL THEN
    UPDATE inventory_product AS t
    SET t.qty = t.qty - NEW.qty #error is thrown here
    WHERE t.id = NEW.product_id;
  END IF;
END; $$ LANGUAGE 'plpgsql';

CREATE TRIGGER after_insert_account_sale
AFTER INSERT ON account_sale
FOR EACH ROW
EXECUTE PROCEDURE update_inventory();

库存产品:

CREATE TABLE public.inventory_product
(
    id integer NOT NULL DEFAULT nextval('inventory_product_id_seq'::regclass),
    upc character varying(45) COLLATE pg_catalog."default",
    sku character varying(45) COLLATE pg_catalog."default",
    asin character varying(45) COLLATE pg_catalog."default",
    ebay_sku character varying(45) COLLATE pg_catalog."default",
    tcgplayer_sku integer,
    qty integer NOT NULL,
    opt_qty integer NOT NULL,
    reserve integer NOT NULL,
    sell_price numeric(10,2) NOT NULL,
    buy_price numeric(10,2) NOT NULL,
    product_weight_g numeric(12,5) NOT NULL,
    dim_x_cm numeric(12,5) NOT NULL,
    dim_y_cm numeric(12,5) NOT NULL,
    dim_z_cm numeric(12,5) NOT NULL,
    stock_image_path character varying(75) COLLATE pg_catalog."default",
    CONSTRAINT inventory_product_pkey PRIMARY KEY (id),
    CONSTRAINT inventory_product_asin_key UNIQUE (asin)
,
    CONSTRAINT inventory_product_ebay_sku_key UNIQUE (ebay_sku)
,
    CONSTRAINT inventory_product_stock_image_path_key UNIQUE (stock_image_path)
,
    CONSTRAINT inventory_product_tcgplayer_sku_key UNIQUE (tcgplayer_sku)

)

account_sale:

CREATE TABLE public.account_sale
(
    id integer NOT NULL DEFAULT nextval('account_sale_id_seq'::regclass),
    unit_price numeric(10,2) NOT NULL,
    qty integer NOT NULL,
    order_id integer NOT NULL,
    product_id integer,
    CONSTRAINT account_sale_pkey PRIMARY KEY (id),
CONSTRAINT account_sale_order_id_product_id_8c7f2e6a_uniq UNIQUE (order_id, product_id)
,
CONSTRAINT account_sale_order_id_7724b965_fk_account_order_id FOREIGN KEY (order_id)
        REFERENCES public.account_order (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT account_sale_product_id_716f2cb2_fk_inventory_product_id FOREIGN KEY (product_id)
        REFERENCES public.inventory_product (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

插入:

INSERT INTO account_sale (qty, unit_price, order_id, product_id)
SELECT $1::integer,$2::float,$3::integer,t.id FROM inventory_product 
AS t WHERE t.ebay_sku=$4 
UNION 
SELECT $5::integer,$6::float,$7::integer,t.id FROM inventory_product 
AS t WHERE t.ebay_sku=$8 

插入参数:

[
2, 79.98, 167, '1',
2, 19.98, 167, '2',
2, 79.98, 168, '1',
2, 79.98, 169, '3',
2, 79.98, 170, '4'
]

请注意，当我移除触发器时，插入件工作正常。

此外，我正在从 Node.js 服务器运行插入(但我认为这不相关)。

我在这里错过了什么？

Answer 1

不要在 SET 赋值的左侧使用目标的表别名。那里的表总是很清楚。顺便说一句:函数语言是一个标识符，不应该被引用:

CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.product_id IS NOT NULL THEN
    UPDATE inventory_product AS t
       SET qty = t.qty - NEW.qty
       -- ^ here
    WHERE t.id = NEW.product_id;
  END IF;
END; $$ LANGUAGE plpgsql;

事实上，您根本不需要在 UPDATE 语句中使用任何别名:

CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.product_id IS NOT NULL THEN
    UPDATE inventory_product 
       SET qty = qty - NEW.qty
    WHERE id = NEW.product_id;
  END IF;
END; $$ LANGUAGE plpgsql;