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?
Best Answer
Firstly, afaik, any view can be "updatable" but only views that are “simple enough” can be automatically updatable. You can make any
VIEW
updatable withINSTEAD OF
trigger triggerAnd of course you can make a function that works on the underlying table and updates it.