cascade-delete-trigger.sql

create or replace function cleanup_tag() returns trigger
language 'plpgsql'
as $body$
begin
    delete from tag where id = OLD.tag_id;
    return null;
end;
$body$;

drop table if exists post cascade;
drop table if exists tag cascade;
drop table if exists post_tag cascade;

create table if not exists post (id bigint primary key generated by default as identity, title text);
create table if not exists tag (id bigint primary key generated by default as identity, title text);
create table if not exists post_tag(post_id bigint references post on delete cascade, tag_id bigint references tag on delete cascade, primary key (post_id, tag_id));

create trigger tag_delete after delete on post_tag for each row execute function cleanup_tag();

insert into post (title) values ('post 1'), ('post 2'), ('post 3');
insert into tag (title) values ('tag 1'), ('tag 2');
insert into post_tag (post_id, tag_id) values (1, 1), (2, 2), (3, 1);