CREATE OR REPLACE FUNCTION player_height_rank (firstname VARCHAR, lastname VARCHAR) RETURNS int AS $$
DECLARE
   player_rank int :=0;
   rank_offset int :=0;
   temp_height FLOAT := NULL;
   r record;
BEGIN

FOR r in select ((p.h_feet * 30.48) + (p.h_inches * 2.54)) As height, p.firstname, p.lastname
from players as p
Order by ((p.h_feet * 30.48) + (p.h_inches * 2.54)) DESC, height, p.firstname, p.lastname

LOOP

	IF r.height = temp_height then
		rank_offset := rank_offset + 1;
	ELSE
		player_rank := player_rank + rank_offset + 1;
		rank_offset := 0;
		temp_height := r.height;
	END IF;

	IF r.firstname = $1 AND r.lastname = $2 Then
		RETURN player_rank;
	END IF;
END LOOP;

--not in DB
RETURN 0;


END;
$$ LANGUAGE plpgsql;

-- executing the above function
-- select * from player_height_rank(‘Reggie’, ‘Miller’);


-- function 2 declaration

CREATE OR REPLACE FUNCTION player_weight_var (tid VARCHAR, yr INTEGER)
RETURNS FLOAT AS $$
DECLARE
	variant float := 0.0;


BEGIN

select variance(p.weight) into variant
from players p, player_rs r
where r.ilkid = p.ilkid and r.tid = $1 and r.year = $2;

if variant is NULL
Then
return -1.0;

ELSE
return variant;

END IF;

END;
$$ LANGUAGE plpgsql;