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
)
 RETURNS SETOF mdm.codelco_material_creation
 LANGUAGE plpgsql
AS $function$
DECLARE
    query text := 'SELECT * FROM mdm.codelco_material_creation WHERE ';
    position integer;
    stage integer;
BEGIN
    -- 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$