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

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

caching - How to check if a url path exists in the service worker cache -

Redirect to a HTTPS version using .htaccess -