1

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?

2

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.

  • 2
    A RULE isn't the only way to make a VIEW updateable. The recommended way is an instead of trigger. – a_horse_with_no_name Nov 23 '17 at 7:38
  • @a_horse_with_no_name good point, updated. – Evan Carroll Nov 30 '17 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.