Trigger Function IF-ELSE Postgres

Question

I have the following trigger:

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();

In my if-else clause I should check if the column value number_items from group table is not 0. How could I check it? The way I've done returns me an error.

Michael Krelin - hacker · Accepted Answer · 2011-12-13 20:17:00Z

2

SELECT it into variable and compare to zero. You may also want to check if SELECT actually returned anything.

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

answered Dec 13, 2011 at 20:17

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mu is too short · Accepted Answer · 2011-12-13 20:59:20Z

I don't think you need a conditional in there at all. If there is nothing in grup that matches NEW.gruop_id = group_id when the UPDATE just won't do anything at all so there is no need for two queries:

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;

The above assumes that grup is just a typo and you only have a gruop table.

Also, if this really is a DELETE trigger then you'll want to work with OLD instead of 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;

Collectives™ on Stack Overflow

Trigger Function IF-ELSE Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related