SNG Auto-Result Function

Forum for users that want to write their own custom queries against the PT database either via the Structured Query Language (SQL) or using the PT3 custom stats/reports interface.

Moderator: Moderators

SNG Auto-Result Function

Postby traedamatic » Mon Jan 28, 2008 5:23 pm

Hey Guys,

I tried to write a littel sql-function witch auto adds the sngs-results. Unfortunately I can't excute it in PostgreSql!! :cry:

[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
traedamatic
 
Posts: 7
Joined: Fri Jan 25, 2008 5:41 am

Return to Custom Stats, Reports, and SQL [Read Only]

Who is online

Users browsing this forum: No registered users and 6 guests

cron
highfalutin