Untitled
unknown
pgsql
a year ago
3.1 kB
2
Indexable
Never
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$