How can I use 'BEFORE DELETE' trigger with conditions

Question

colleagues! I created 'product' table with the next query:

CREATE TABLE product (
id serial not null,
product_name text not null,
description varchar(50),
delivery_date timestamp,
warehouse jsonb
) 

And I'm trying to use trigger before delete with the sort by list:

CREATE OR REPLACE FUNCTION product_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF (OLD.product_name IN ('Milk','Egg','Cheese'))
        THEN 
        DELETE FROM product WHERE product_name = OLD.product_name;
    ELSE
         RAISE EXCEPTION
    'Value out of list.  Fix the product_delete_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER delete_product_trigger
  BEFORE DELETE ON product
  FOR EACH ROW EXECUTE PROCEDURE product_delete_trigger();

How I got it I need to use 'OLD' special parameter , but If I when I use it, I have an issue:

ERROR: ERROR: Stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (the current value is 2048 KB), first making sure that the OS provides sufficient stack size.

Is it possible to do this by this query?

DELETE FROM product where product_name = 'Cheese';

Answer 1

There is no point in running DELETE in your BEFORE trigger – that happens anyway if you let the database have its course. So all you have to do is throw an error if you are unhappy with the proceedings:

CREATE OR REPLACE FUNCTION product_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ((OLD.product_name IN ('Milk','Egg','Cheese')) IS NOT TRUE)
    THEN 
         RAISE EXCEPTION
         'Value out of list.  Fix the product_delete_trigger() function!';
    END IF;
    /* proceed with the DELETE */
    RETURN OLD;
END;
$$
LANGUAGE plpgsql;