Untitled

CREATE OR REPLACE FUNCTION mdm.codelco_get_inactive_materials(
stages integer[], 
inactivities integer[], 
organization integer DEFAULT NULL::integer, 
creation_user uuid DEFAULT NULL::uuid, 
material_id integer DEFAULT NULL::integer
)
 RETURNS SETOF mdm.codelco_material_creation
 LANGUAGE plpgsql
AS $function$
DECLARE
    query text := 'SELECT * FROM mdm.codelco_material_creation WHERE ';
    array_size integer := array_length(stages, 1);
    inactivities_size integer := array_length(inactivities, 1);
    position integer;
    stage integer;
BEGIN

    IF (stages IS NULL OR array_length(stages, 1) = 0) OR (inactivities IS NULL OR array_length(inactivities, 1) = 0) THEN
        RAISE EXCEPTION 'Las listas no pueden ser null o vacias.';
        RETURN;
    ELSEIF array_size <> inactivities_size THEN 
        RAISE EXCEPTION 'Siempre debe de coincidir un stage con un dia de inactividad. El tamaño de las listas son diferentes.';
        RETURN;
    END IF;

	-- Si material_id no es null siempre habrá un stage y un numero de inactividad al que pertenece
    IF material_id IS NOT NULL THEN
       query := query || '(id = ' || material_id ||' AND date_part(''day'', current_timestamp  - updated_at)::integer >= ' || inactivities[1] || ')' ;  
       RETURN QUERY EXECUTE query;
       RETURN;
    END IF;

    -- Solo hay un elemento en la lista    
    IF array_length(stages, 1) = 1 THEN
        query := query || ' step_id = ' || stages[1];    
   
    	IF organization IS NOT NULL THEN
            query := query || ' AND organization_division_id = ' || organization;    
        END IF;
        IF creation_user IS NOT NULL THEN
            query := query || ' AND creation_user_id = ''' || creation_user || '''';
        END IF;
        query := query || ' AND date_part(''day'', current_timestamp  - updated_at)::integer >= ' || inactivities[1];    
    ELSE
    -- Hay más de un elemento en la lista
       position := 1;
       FOREACH stage IN ARRAY stages 
       LOOP
           query := query || ' (step_id = ' || stage;
           -- Si organizacion no es null se agrega a la query
           IF organization IS NOT NULL THEN
                query := query || ' AND organization_division_id = ' || organization;    
           END IF;
           
            -- Si creation_user no es null se agrega a la query
           IF creation_user IS NOT NULL THEN
                query := query || ' AND creation_user_id = ''' || creation_user || '''';
           END IF;
           
           -- Si estoy en la ultima posicion del array no va el OR
           IF stage = stages[array_size] THEN
              query := query || ' AND date_part(''day'', current_timestamp  - updated_at)::integer >= ' || inactivities[position] || ')' ;    
           ELSE
              query := query || ' AND date_part(''day'', current_timestamp  - updated_at)::integer >= ' || inactivities[position] || ') OR ' ;    
           END IF;
           position := position + 1;
       END LOOP;
    END IF;
RETURN QUERY EXECUTE query;
END;
$function$