Jump to content
  • Advertisement
Sign in to follow this  
Mathachew

[web] Help with Stored Procedures in PostgreSQL

This topic is 3745 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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:
CREATE OR REPLACE FUNCTION game_snapshot(int) RETURNS void AS '
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?

Share this post


Link to post
Share on other sites
Advertisement
After getting a second set of eyes to look and critique my actions, I finally have a working solution. Not sure if anyone would be interested, but if so, I can post the solution.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!