Question

0

239

Vistas

PostgreSQL Trigger to update game count on insert/delete of array elements

I want to create a trigger on a game_catalog table so that every time a user adds or deletes a game from his collection the total game count gets updated. Since the game collection is stored as a text array I decided to use array_length function to count the number of games.

game_catalog

id - BIGSERIAL primary key
user_id - INTEGER
game_list - text []
game_count - INTEGER

I've attempted to create a trigger so that it recalculates the length of the game_list column after insert or delete but it does not work. Here is what I have for now:

CREATE OR REPLACE FUNCTION count_games() 
RETURNS TRIGGER AS $$ 
BEGIN 
UPDATE game_catalog 
SET game_count = (SELECT array_length(game_list, 1) from game_catalog) 
WHERE user_id = NEW.user_id; 
RETURN NEW; 
END; 
$$ LANGUAGE plpgsql; 

CREATE TRIGGER count_games 
AFTER INSERT OR DELETE ON game_catalog FOR EACH ROW 
EXECUTE PROCEDURE count_games();

over 4 years ago · Santiago Trujillo

1 Respuestas

Responde la pregunta

0

Responde la pregunta

Encuentra empleos remotos

Accepted Answer · 2022-05-26T13:19:51.333Z

So, a couple of things:

I do not know your exact use case, but this seems like a bad idea for a few reasons
Please be sure you have a unique constraint on your user_id or this is going to get messy (you key on user_id for your update. Better to use the primary key if you are going to key on anything, but it is not really necessary, as shown) [EDIT - nevermind. Your query has handled this. But you do update every record for that user... which could get expensive if there is duplication...]
If you really want this item to be available to the user without computing it (and since it always references the same row), it looks to me like it really belongs in a view or materialized view
Usually, trigger aggregations are for table-level aggregations when lots of data is expected (i.e. not by record), and would live in a separate table. When it is on the same table, you run the risk of an infinite loop (Update a row, trigger an update, trigger an update, etc.)
In general, you should ask yourself lots of questions before using an array or json column in relational data modeling. While possible, you may be creating pain for your future self
For a DELETE trigger, NEW is undefined (see Postgres docs on trigger functions, they are very helpful and have some nice examples)
If you edit the record itself as it goes in (more efficient, as you do not have a select query going on), you need to use a BEFORE trigger, as AFTER triggers cannot change the value of things going into the table.

That said, you can define your trigger differently to make it work:

-- only watch for insert/updates on the game_list column to avoid infinite loop
-- delete doesn't matter here
CREATE TRIGGER count_games
 BEFORE INSERT OR UPDATE OF game_list
 ON game_catalog
 FOR EACH ROW
 EXECUTE PROCEDURE count_games();

Although a view would be better:

CREATE OR REPLACE VIEW game_catalog_count AS
SELECT id, user_id, game_list, array_length(game_list,1) as game_count
FROM game_catalog;

And your trigger function could use a little improvement (unless you do plan to reference other rows... but then you have other problems in your trigger function design):

CREATE OR REPLACE FUNCTION count_games() 
RETURNS TRIGGER AS $$ 
BEGIN 

NEW.game_count := array_length(NEW.game_list, 1);

RETURN NEW; 
END; 
$$ LANGUAGE plpgsql;

Hope that is helpful!

0

239

PostgreSQL Trigger to update game count on insert/delete of array elements

1 Respuestas

0

Encuentra empleos remotos

Andres GPT