CREATE OR REPLACE FUNCTION insert_proxy_bid(p_auction_company_id int, p_auction_id bigint, p_person_id bigint, p_bid numeric) RETURNS integer AS $$
DECLARE
t_person_id bigint;
t_high_bidder_id bigint;
t_high_bidder_proxy_bid numeric(15,5);
t_auction_proxy_bids_id bigint;
t_total_bids int;
t_bid_increment numeric(15,5);
t_high_bid numeric(15,5);
t_starting_price numeric(15,5);
t_proxy_loop boolean;
t_snipe_check int;
t_expire_check boolean;
BEGIN
-- run checks to determine whether we should proceed with this proxy bid placement
SELECT INTO
t_high_bidder_id,
t_expire_check
auction.high_bidder_id,
CASE WHEN current_timestamp >= ends THEN 't' ELSE 'f' END
FROM
auction,
person
WHERE auction.seller_id = person.person_id
AND auction_id = p_auction_id
AND auction_company_id = p_auction_company_id
AND auction_type_id = 1
AND p_person_id != auction.seller_id;
IF FOUND AND t_expire_check = 'f' THEN
SELECT INTO
t_person_id
person_id
FROM person
WHERE auction_company_id = p_auction_company_id
AND person_id = p_person_id;
IF FOUND THEN
-- acquire exclusive row level lock for this transaction
SELECT INTO
t_total_bids,
t_bid_increment,
t_starting_price,
t_high_bid
total_bids, (
SELECT bid_increment
FROM auction_bid_increment_level
WHERE auction_company_id = p_auction_company_id
AND (
( high_bid BETWEEN lower_limit AND upper_limit )
OR ( high_bid >= lower_limit AND upper_limit = -1 )
)
) AS bid_increment,
starting_price,
high_bid
FROM auction
WHERE auction_id = p_auction_id
FOR UPDATE;
IF t_total_bids > 0 THEN
-- bids exist so run the proxy bidding
IF p_bid >= t_high_bid + t_bid_increment THEN
-- first insert the incoming bid into proxy bid queue
INSERT INTO auction_proxy_bids ( auction_id, person_id, bid )
VALUES ( p_auction_id, p_person_id, p_bid );
SELECT INTO t_auction_proxy_bids_id currval('auction_proxy_bids_serial');
-- only start if incoming bid is not high bidder
IF t_person_id <> t_high_bidder_id THEN
-- get the max proxy bid for high bidder
SELECT INTO
t_high_bidder_proxy_bid
MAX(bid)
FROM auction_proxy_bids
WHERE auction_id = p_auction_id
AND person_id = t_high_bidder_id;
-- now process the proxy bids ( high bidder vs incoming proxy bid )
t_proxy_loop := 't';
WHILE t_proxy_loop LOOP
-- get the current bid increment for this level
SELECT INTO
t_bid_increment
bid_increment
FROM auction_bid_increment_level
WHERE auction_company_id = p_auction_company_id
AND (
( t_high_bid BETWEEN lower_limit AND upper_limit )
OR ( t_high_bid >= lower_limit AND upper_limit = -1 )
);
t_high_bid := t_high_bid + t_bid_increment;
IF t_high_bidder_proxy_bid >= t_high_bid AND p_bid >= t_high_bid THEN
INSERT INTO auction_bids ( auction_id, person_id, bid )
VALUES ( p_auction_id, t_high_bidder_id, t_high_bid );
SELECT INTO t_snipe_check snipe_check(p_auction_id);
UPDATE auction
SET
high_bidder_id = t_high_bidder_id,
high_bid = t_high_bid,
total_bids = total_bids + 1
WHERE auction_id = p_auction_id;
ELSIF p_bid >= t_high_bid THEN
INSERT INTO auction_bids ( auction_id, person_id, bid )
VALUES ( p_auction_id, p_person_id, t_high_bid );
SELECT INTO t_snipe_check snipe_check(p_auction_id);
UPDATE auction
SET
high_bidder_id = p_person_id,
high_bid = t_high_bid,
total_bids = total_bids + 1
WHERE auction_id = p_auction_id;
t_proxy_loop = 'f';
ELSE
t_proxy_loop = 'f';
END IF;
-- determine who the winner was and send losing party email, etc...
END LOOP;
END IF;
RETURN t_auction_proxy_bids_id;
END IF;
ELSIF p_bid >= t_starting_price THEN
-- no bids have been placed yet, so adjust the first bid to match the starting price
INSERT INTO auction_proxy_bids ( auction_id, person_id, bid )
VALUES ( p_auction_id, p_person_id, p_bid );
SELECT INTO t_auction_proxy_bids_id currval('auction_proxy_bids_serial');
INSERT INTO auction_bids ( auction_id, person_id, bid )
VALUES ( p_auction_id, p_person_id, t_starting_price );
SELECT INTO t_snipe_check snipe_check(p_auction_id);
UPDATE auction
SET
high_bidder_id = p_person_id,
high_bid = t_starting_price,
total_bids = total_bids + 1
WHERE auction_id = p_auction_id;
RETURN t_auction_proxy_bids_id;
END IF;
END IF;
END IF;
RETURN 0;
END;
$$ LANGUAGE plpgsql;