触发函数IF-ELSE Postgres

Question

我有以下触发器:

CREATE OR REPLACE FUNCTION record_deleted_item() RETURNS TRIGGER AS $$
BEGIN
    IF (select number_itens from grup where NEW.gruop_id = group_id) != 0 THEN
    UPDATE gruop SET number_itens= (number_itens-1) WHERE gruop_id=NEW.gruop_id;
   END IF;
RETURN NEW;
END; $$
LANGUAGE plpgsql;
CREATE TRIGGER deleted_item
BEFORE DELETE ON item
FOR EACH ROW EXECUTE PROCEDURE record_deleted_item();

在我的if-else子句中，我应该检查来自组表的列值number_items是否不为0。我怎么核对呢?

Answer 1

SELECT为变量并与零比较。您可能还想检查SELECT是否实际返回任何内容。

DECLARE
    ni bigint;
BEGIN
    SELECT number_itens INTO ni FROM ...;
    IF NOT FOUND THEN
    END IF;
    IF NI != 0 THEN
    END IF;
END

Answer 2

我认为这里根本不需要条件语句。如果grup中没有任何内容与NEW.gruop_id = group_id匹配，则UPDATE根本不会做任何事情，因此不需要两个查询:

CREATE OR REPLACE FUNCTION record_deleted_item() RETURNS TRIGGER AS $$
BEGIN
    UPDATE gruop SET number_itens = (number_itens - 1) WHERE gruop_id = NEW.gruop_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

上面假设grup只是一个错字，您只有一个gruop表。

另外，如果这真的是一个DELETE触发器，那么你将需要使用OLD而不是NEW:

CREATE OR REPLACE FUNCTION record_deleted_item() RETURNS TRIGGER AS $$
BEGIN
    UPDATE gruop SET number_itens = (number_itens - 1) WHERE gruop_id = OLD.gruop_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;