texas_hold_em_sql/deal_function.sql at master · jpodonnell/texas_hold_em_sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
--
-- Name: deal(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.deal(players integer) RETURNS void
    LANGUAGE plpgsql
    AS $$
    DECLARE
    count int := 1;
    hand_num int := 0;
    seat_num int := 1;
    card_num int := 0;
    card_suit char(1) := 'E';
    BEGIN
    hand_num = coalesce(max(hands.hand_no),0)+1 from hands;
    -- deal out the first card
    while seat_num <= players loop
    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    insert into hands(seat_no, card_1_suit, card_1_strength, hand_no)
    values(seat_num,card_suit, card_num,hand_num);
    count := count + 1;
    seat_num := seat_num + 1;
    end loop;
    --reset the seat posistion
    seat_num := 1;
    -- deal out the second card
    while seat_num <= players loop
    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;

    update hands set card_2_strength = card_num,
    card_2_suit = card_suit
    where seat_no=seat_num
    and hand_no=hand_num;

    count := count + 1;
    seat_num := seat_num + 1;
    end loop;

    -- deal the community cards
    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    insert into community_cards(hand_no,burn_1_strength ,burn_1_suit)
    values(hand_num, card_num, card_suit);
    -- goto next card
    count := count + 1;

    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;

    update community_cards set card_1_strength  = card_num, card_1_suit=card_suit where community_cards.hand_no = hand_num;

    -- goto next card
    count := count + 1;

    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;

    update community_cards set card_2_strength  = card_num, card_2_suit=card_suit where community_cards.hand_no = hand_num;

    -- goto next card
    count := count + 1;

    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;

    update community_cards set card_3_strength  = card_num, card_3_suit=card_suit where community_cards.hand_no = hand_num;

    -- goto next card
    count := count + 1;

    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;

    update community_cards set burn_2_strength  = card_num, burn_2_suit=card_suit where community_cards.hand_no = hand_num;

    -- goto next card
    count := count + 1;

    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;

    update community_cards set card_4_strength = card_num, card_4_suit=card_suit where community_cards.hand_no = hand_num;

    -- goto next card
    count := count + 1;

    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;

    update community_cards set burn_3_strength  = card_num, burn_3_suit=card_suit where community_cards.hand_no = hand_num;

    -- goto next card
    count := count + 1;

    card_num := number from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;
    card_suit := suit from cards join shuffled_deck on
    shuffled_deck.id=cards.id where place_in_deck=count;

    update community_cards set card_5_strength = card_num, card_5_suit=card_suit where community_cards.hand_no = hand_num;

    END;
$$;


ALTER FUNCTION public.deal(players integer) OWNER TO postgres;