Here is the view:
CREATE OR REPLACE RECURSIVE VIEW employees_recursive(id, name, child_category_id,parent_category_id, level,topic_id, type_name, inserted_at, updated_at) AS ( SELECT ee.id, ee.name, child_category_id, parent_category_id, 1 as level, ct.id, ct.name, ee.inserted_at, ee.updated_at FROM category_hierarchy cth LEFT JOIN categories ee ON cth.child_category_id = ee.id INNER JOIN category_types ct ON ee.topic_id = ct.id WHERE parent_category_id IS NULL UNION ALL SELECT ee.id, ee.name, c.child_category_id, c.parent_category_id, ct.id, ct.name, ee.inserted_at, ee.updated_at FROM employees_recursive p INNER JOIN category_hierarchy c ON p.child_category_id = c.parent_category_id LEFT JOIN categories ee ON c.child_category_id = ee.id INNER JOIN category_types ct ON ee.topic_id = ct.id )
I've been trying to write a function to delete rows from the view by id using functions and triggers following this post. The Postgres docs say that views with "WITH" or "UNION" keywords at the top level are not updateable.
does that mean I can't delete rows from this view?
If that's the case, is there a way to delete rows from the actual table and somehow refresh the view?