Postgresql – ny way to delete from this postgres 9.6 view

postgresql

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 with

And of course you can make a function that works on the underlying table and updates it.