PostgreSQL trigger which updates count in another table

Question

I am rather new to PostgreSQL and I am really stuck with an apparently simple task. I have a table with music albums and a table with bands features, including how many albums such band released in a specific time, let say between 1990 and 1995. So I need to create a trigger that keeps the bands tabe updated whenever I insert, delete or updaste the albums table. These are the tables:

Album

id_album   id_band    year
1          1          1995
2          1          1985
3          2          1993
4          3          1998


Band

id_band   num_albums9095
1         1 
2         1 
3         0

So I have created the following function and trigger:

CREATE FUNCTION update_num_nineties()
RETURNS trigger AS $$ BEGIN
UPDATE band
SET num_albums_eighties = (SELECT COUNT (*) FROM album
where album.year between 1990 and 1995
GROUP BY album.id_band);
END;
$$LANGUAGE plpgsql;

CREATE TRIGGER update_nineties_album_mod_album AFTER UPDATE ON album FOR EACH row EXECUTE PROCEDURE update_num_nineties();

But I would get a subquery used as an expression returned more than one row message anytime I try to update any value to test it. Would anyone be so kind to help me see why I am goind in the wrong direction?

Generally a bad idea to store aggregates.

wildplasser
– wildplasser

2020-11-20 01:24:37 +00:00
Commented Nov 20, 2020 at 1:24 — wildplasser
– wildplasser, Commented Nov 20, 2020 at 1:24

GMB · Accepted Answer · 2020-11-19 23:39:58Z

1

You need to correlate the subquery with the outer query:

update band b
set num_albums_eighties = (
    select count (*) 
    from album a
    where a.year between 1990 and 1995 and a.id_band = b.id_band
)                                        --^-- correlation --^--

While this technically works, it is still rather inefficient, because it resets the whole table when just one row in modified. You can restrict the rows with a where clause:

update band b
set num_albums_eighties = (
    select count (*) 
    from album a
    where a.year between 1990 and 1995 and a.id_band = b.id_band
)
where b.id_band in (old.band_id, new.band_id)

answered Nov 19, 2020 at 23:39

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chancar Over a year ago

Many thanks, @GMB. I am going to try it and let you know if it works!

Collectives™ on Stack Overflow

PostgreSQL trigger which updates count in another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related