SQLAlchemy: Update values in table based on values update in other table

Question

I have two tables with following fileds:

"category":
id: int - primary key
item_count: int

"subcategory":
id: int - primary key
parent_id: int - foreign key
item count: int

Tables are linked by category.id = subcategory.parent_id.

Is it possible to set this tables up so after every successful transaction column category.item_count would update based on sum of child subcategoruies item count?
Like automatically running something like

UPDATE category
SET item_count =
  (SELECT sum(item_count)
   FROM subcategory
   WHERE parent_id = category.id)

Example:
Initial category table state:

Then I add some rows to sybcategory table:

After that I want category table autoupdate and look like this:

I'm using latest PostgresSQL and SQLAlchemy in Python.

I've tried to create category.item_count column as a generated postgres column like

ALTER TABLE category
ADD COLUMN item_count integer GENERATED ALWAYS AS ((SELECT COALESCE(SUM(item_count), 0) FROM subcategory WHERE parent_id = category.id)) STORED;

but got error message saying that I can't use subqueries in generated column expression.

Thanks in advance!

Answer 1

You can do this using a trigger:

CREATE FUNCTION update_category_count()
    RETURNS TRIGGER AS
'
BEGIN
UPDATE category SET item_count = (SELECT sum(item_count) FROM subcategory WHERE parent_id = category.id);
RETURN NEW;
END;
'
LANGUAGE plpgsql;

CREATE TRIGGER t_update_category_count AFTER INSERT OR UPDATE ON subcategory EXECUTE PROCEDURE update_category_count();

update_category_count is a function which returns a trigger which runs your update query. CREATE TRIGGER is then used to call the function after an insert or update occurs on subcategory.

Here's a dbfiddle to demonstrate.