Hey Guys,
I tried to write a littel sql-function witch auto adds the sngs-results. Unfortunately I can't excute it in PostgreSql!!
[codelko]
-- Function: public.insert_sngwon()
-- DROP FUNCTION public.insert_sngwon();
CREATE OR REPLACE FUNCTION public.insert_sngwon()
RETURNS integer AS
$$
DECLARE
i integer := 0;
amtwon real := 0;
finished integer := 9;
tryamt integer := 0;
cntplayer integer := 9;
tryid integer := 1;
lhwin integer := 0;
trbuyin real := 0;
BEGIN
SELECT INTO tryamt count(*) FROM public.tourney_holdem_results WHERE (public.tourney_holdem_results.id_player = 9);
LOOP
i := i+1;
SELECT INTO tryid, cntplayer, lhwin public.tourney_holdem_hand_summary.id_tourney, public.tourney_holdem_hand_summary.cnt_players, public.tourney_holdem_hand_summary.id_winner FROM public.tourney_holdem_hand_summary
WHERE ((public.tourney_holdem_hand_summary.id_tourney) in (i)) ORDER BY public.tourney_holdem_hand_summary.cnt_players asc LIMIT 1;
SELECT INTO trbuyin public.tourney_holdem_summary.amt_buyin FROM public.tourney_holdem_summary WHERE (public.tourney_holdem_summary.id_tourney = tryid);
IF lhwin <> 9 THEN
IF cntplayer > 2 THEN
IF cntplayer = 3 THEN
UPDATE public.tourney_holdem_results
SET public.tourney_holdem_results.val_finish = cntplayer, public.tourney_holdem_results.amt_won = (((trbuyin*9)/10)*2)
WHERE (public.tourney_holdem_results.id_tourney = tryid) AND (public.tourney_holdem_results = 9);
ELSE
UPDATE public.tourney_holdem_results
SET public.tourney_holdem_results.val_finish = cntplayer, public.tourney_holdem_results.amt_won = 0
WHERE (public.tourney_holdem_results.id_tourney = tryid) AND (public.tourney_holdem_results = 9);
END IF;
ELSE
UPDATE public.tourney_holdem_results
SET public.tourney_holdem_results.val_finish = 2, public.tourney_holdem_results.amt_won = (((trbuyin*9)/10)*3)
WHERE (public.tourney_holdem_results.id_tourney = tryid) AND (public.tourney_holdem_results = 9);
END IF;
ELSE
UPDATE public.tourney_holdem_results
SET public.tourney_holdem_results.val_finish = 1, public.tourney_holdem_results.amt_won = (((trbuyin*9)/10)*5)
WHERE (public.tourney_holdem_results.id_tourney = tryid) AND (public.tourney_holdem_results = 9);
END IF;
EXIT WHEN i >= tryamt;
END LOOP;
RETURN i;
END;
$$
LANGUAGE 'plpgsql'
[/codelko]
The playerid is set! The better option is to declare it as input.
What do you think?
matic