--Função que busca a posição correta da fila de reservas
CREATE OR REPLACE FUNCTION reserveposition(p_reserveid integer)
RETURNS integer as $BODY$
    DECLARE

        v_position integer;
        v_position_aux integer;
        v_reservestatusid integer;
       -- v_linha RECORD;
        v_itemnumber varchar;
    BEGIN 
	SELECT INTO v_reservestatusid reservestatusid FROM gtcreserve WHERE reserveid=p_reserveid;

	--se for atendida ou comunicada retorna 0 para depois ser tratado de outra forma
        IF v_reservestatusid = 2 OR v_reservestatusid = 3
        THEN
  	    v_position = 0;
	    
	ELSE --senão busca a posição da reserva  
            v_position = 0;
 
            FOR v_itemnumber IN (SELECT itemnumber FROM gtcreservecomposition WHERE reserveid = p_reserveid)
            LOOP
                
                SELECT INTO v_position_aux COUNT(DISTINCT gtcreserve.reserveid) 
              FROM gtcreserve 
        INNER JOIN gtcreservecomposition 
	        ON(gtcreserve.reserveid = gtcreservecomposition.reserveid) 
             WHERE gtcreservecomposition.itemnumber = v_itemnumber
               AND requesteddate <= (SELECT requesteddate 
                                       FROM gtcreserve 
	                              WHERE reserveid= p_reserveid) 
                AND reservestatusid='1'; 

                IF v_position_aux > v_position
                THEN 
                    v_position = v_position_aux;
                END IF; 
           
            END LOOP;

            
      	END IF;

        RETURN v_position;

     END;

$BODY$ language plpgsql;
