Advertisement Jump to content
Sign in to follow this  

[web] Help with Stored Procedures in PostgreSQL

This topic is 3833 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:
	current_game_id ALIAS FOR $1;
	changed_game_id integer;
	game_data RECORD;
	game_assignments RECORD;
	nothing RECORD;
	game_data := "SELECT * FROM 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)";

' 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
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, you agree to our community Guidelines, Terms of Use, and Privacy Policy. is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!