Postgresql rule for suppressing 'DELETE FROM table' in some cases

Question

I have two tables in my db: order and deliveries. When creating order, I generate at the same time a number of deliveries depending on the order's start and end dates.

Deliveries have boolean done field, that is checked when the delivery is done. I have to suppress deleting any order if it has any deliveries that are done -otherwise the deliverer will loose the data (f.ex. delivery weight), as such info is stored in the order. Only in the case that none of deliveries is done, I can delete them together with the order. In such case I can use rules (Disable DELETE on table in PostgreSQL?), but it seems not to work in my case: "syntax error close to IF". Are rules good here? Why can't I get my IF-THEN statement working?

CREATE RULE delete_order AS ON DELETE TO orders DO INSTEAD
(
    DELETE FROM deliveries WHERE (order = OLD.id AND done = false);
    IF (NOT EXISTS(SELECT 1 FROM deliveries WHERE order = OLD.id)) THEN
            DELETE FROM orders WHERE id = OLD.id;
    ELSE
            RAISE NOTICE 'Cannot delete order that is partially done.';
    END IF;
)

The question needs table definitions for involved tables (`\d tbl` in psql) and the version of Postgres. — Erwin Brandstetter, May 22 '14 at 23:24

score 2 · Accepted Answer · answered May 23 '14 at 02:03

2

You are better off writing a trigger function here.

CREATE FUNCTION delete_order () RETURNS trigger AS $$
BEGIN
  IF EXISTS(SELECT 1 FROM deliveries WHERE order = OLD.id AND done = TRUE) THEN
    RAISE NOTICE 'Cannot delete order that is partially or completely done.';
  ELSE
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER before_delete_order
  BEFORE DELETE ON order
  FOR EACH ROW EXECUTE PROCEDURE delete_order();

Every time you want to delete an order, the function delete_order() is called before the actual deletion takes place. If there are any deliveries already done then an error is raised, aborting the deletion. Otherwise OLD is returned and the system performs the deletion. This assumes that the deletion of the order record cascades to the associated deliveries; if not, the trigger function should probably delete those elivery records first in order not to get an FK violation when the order record is deleted.

answered May 23 '14 at 02:03

Patrick

23,688
4
49
75

Thanks for this explanation. So, if I RAISE a notice in my BEFORE DELETE trigger, the **delete** action will be cancelled, and no row affected in the `order` table? Am I right? – MaciekS May 23 '14 at 09:02
That's correct. See the documentation [here](http://www.postgresql.org/docs/current/static/plpgsql-trigger.html). If a `DELETE` trigger does not `RETURN OLD` then the operation is cancelled. In the trigger function you never do the operation yourself, the system will do that, but you can modify the process (i.e. insert other data, do an operation on another table) or even cancel it. – Patrick May 23 '14 at 09:53
Patrick, your solution works. I just had to change `RAISE NOTICE` to `RAISE EXCEPTION`. Thanks! – MaciekS May 23 '14 at 14:41

Alexandr dmitruk · Answer 2 · 2021-02-04T03:43:53.443

1

You can do it using Postgres Rule without triggers

create function fn_prevent_deleting(OLD RECORD, tableName text)
returns void as
$BODY$
begin
    //Some condition here
   if (OLD.employee_id is not null) then
     raise exception 'Cant delete row on table %', tableName;
   end if;
end;
$BODY$
LANGUAGE plpgsql volatile;

CREATE RULE delete_order AS ON DELETE TO orders DO INSTEAD
select fn_prevent_deleting(OLD, 'orders');

edited Feb 04 '21 at 03:43

answered Jan 22 '21 at 04:23

Alexandr dmitruk

45
5

Thanks! And where does the part responsible for deleting and checking copndition go? Shall I put `IF EXISTS(SELECT 1 FROM deliveries WHERE order = OLD.id AND done = TRUE)` before raising exception and do the delete operation in `else` clause? – MaciekS Jan 22 '21 at 08:21
I've added condition to a pgsql function as example – Alexandr dmitruk Feb 04 '21 at 03:44

Postgresql rule for suppressing 'DELETE FROM table' in some cases

2 Answers2