Postgres Procedure for delete related data table

Question

Halo, i'm new with postgresql, now i have a project to migrate db from mysql to postgresql. i want to make a trigger when i delete a parent data, the child will also deleted. here is the structure description:

table_A (trigger)
- table_B
  - table_C
  - table_D

here is the code that i tried:

CREATE OR REPLACE FUNCTION delete_relation() RETURNS trigger AS
$$
BEGIN
    DELETE FROM table_C 
        USING table_B 
        WHERE table_C.id = table_B.id_C;
    DELETE FROM table_C 
        USING table_B 
        WHERE table_D.id = table_B.id_D;
    DELETE FROM table_B WHERE table_B.id_A = OLD.id;    
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER 
    delete_table_A
AFTER DELETE ON 
    table_A 
FOR EACH ROW EXECUTE PROCEDURE delete_relation();

then, when i execute, it will return this:

Function executed no return statement: 7 
ERROR: control reached end of trigger procedure without RETURN
CONTEXT: PL/pgSQL function delete_relation()

@rad https://stackoverflow.com/questions/1457013/cascade-on-delete-or-use-triggers i cant use cascade because of this — iskandar suhaimi, Apr 24 '19 at 03:45

score 1 · Answer 1 · answered Apr 23 '19 at 17:57

1

You don't need to define any triggers, you can simply enable delete on cascade in the structure of your tables so that when a record is deleted from a table, all the records from other tables that have a reference (foreign key) to the deleted record, will be deleted too.
Take a look at this

answered Apr 23 '19 at 17:57

Amir Molaei

3,700
1
17
20

https://stackoverflow.com/questions/1457013/cascade-on-delete-or-use-triggers i cant use cascade because of this – iskandar suhaimi Apr 24 '19 at 03:45
In fact if you read the **edit** part of the answer you put in the comment, you can see that the respondent corrects himself and approves that it can be done using `delete on cascade`. I assure you that it is possible to cascade delete on multiple levels. In order to make sure, you can create some table related to each other with `delete on cascade` enabled, then see how deleting a row in the parent table cascades to all related children and grand children and so on. Triggers are useful in this case only when you want to do something with the deleted row. – Amir Molaei Apr 24 '19 at 04:03

score 1 · Accepted Answer · answered Apr 23 '19 at 18:01

Without judging on the efficiency of your approach to accomplish a certain task, the error message is quite obvious and means you are missing a RETURN statement as demanded by the documentation:

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.

For AFTER triggers, this should be:

...

RETURN NULL;
...

Postgres Procedure for delete related data table

2 Answers2