July 22, 2008 - 2:54pm
July 28, 2008 - 1:04pm
July 28, 2008 - 3:38pm
I'm not sure how an
I'm not sure how an additional date field will help. The goal is to, no matter what, make a snapshot of the game's details before it is updated, regardless of how much or how little is changed. INSERT INTO table SELECT FROM would work on my games table, but would not be as useful for my assignments table where there are multiple rows to duplicate. Thanks for the feedback though, I do appreciate it.
August 2, 2008 - 1:09am
Here's a run down of what I'm working with. I have a table that contains game information (home team, away team, location, etc.). I have a second table that contains game assignments (who is working the game); this table has a game_id to reference the, well, game's id. I'm wanting to use a stored procedure to store snapshots of both tables whenever a change is made to a game. Initially I was going to create a stored procedure for each table and set a trigger before an update occurred, but there is information I want to store that I don't have access to when the triggers go off. Because of this, I'm trying to create a function that will perform both actions when I call it. I'm not accustomed to writing stored procedures, so I may be doing unnecessary actions (such as nothing := "INSERT ..."; it's the only way I could create the function without getting some kind of compile error). Here's what I have so far:
DECLARE
current_game_id ALIAS FOR $1;
changed_game_id integer;
game_data RECORD;
game_assignments RECORD;
nothing RECORD;
BEGIN
game_data := "SELECT * FROM operations.games WHERE id = current_game_id";
changed_game_id := "SELECT nextval(operations.game_change_id_seq)";
nothing := "INSERT INTO operations.game_changes(id, game_id, ts, level, hometeam, awayteam, location, status, user_id)
VALUES(changed_game_id, current_game_id, game_data.ts, game_data.level, game_data.hometeam, game_data.awayteam, game_data.location, game_data.status, game_data.user_id)";
FOR game_assignments IN SELECT * FROM operations.assignments WHERE game_id = current_game_id LOOP
nothing := "INSERT INTO operations.assignment_changes(game_change_id, user_id, game_fee, travel_fee, perdiem_fee, status)
VALUES(changed_game_id, game_assignments.user_id, game_assignments.fee_id, game_assignments.travel_fee, game_assignments.perdiem_fee, game_assignments.status)";
END LOOP;
RETURN;
END
' LANGUAGE plpgsql
The intent is to pass the game_id, select all data of the specified game, store it in a separate table, grab all assignments for that game and store that information in a separate table as well. After calling this function I would update the game and assignments accordingly so that I can maintain a history of changes for the games. Any ideas on what I'm doing wrong?
Who can bring a charge to God's elect? It is God who justifies!