postgresql - Update 1 of 2 columns - depending on a condition -
in game 2 players trying allow skipping player's turn calling following stored function:
create or replace function skip_game( in in_uid integer, -- player id in in_gid integer) -- game id returns void $func$ begin update games set stamp1 = extract('epoch' current_timestamp) gid = in_gid , player1 = in_uid , stamp1 < stamp2; -- player1's turn if not found update games set stamp2 = extract('epoch' current_timestamp) gid = in_gid , player2 = in_uid , stamp2 < stamp1; -- player2's turn end if; end $func$ language plpgsql;
is please possible combine 2 above update-statements single 1 - , set either stamp1
or stamp2
current epoch time, depending on condition?
also, don't understand, why postgresql 9.5 report 1 row
being updated - event if call function invalid arguments?
# select skip_game(1, 1); skip_game ----------------- (1 row) # select skip_game(95, 95); skip_game ----------------- (1 row)
here games
table (i use epoch integers stamp
columns easier communication mobile app @ client side - uses sqlite):
create table games ( gid serial primary key, created timestamp not null, player1 integer references users(uid) on delete cascade not null, player2 integer references users(uid) on delete cascade, stamp1 integer, -- timestamp of last turn stamp2 integer, -- timestamp of last turn letters1 varchar(7) not null, letters2 varchar(7) not null, letters varchar(116) not null, board varchar(225) not null, style integer not null check (1 <= style , style <= 4) );
using single update
:
update games set stamp1 = case when stamp1 < stamp2 extract('epoch' current_timestamp) else stamp1 end ,stamp2 = case when stamp2 < stamp1 extract('epoch' current_timestamp) else stamp2 end gid = in_gid , (player1 = in_uid or player2 = in_uid);
Comments
Post a Comment