Untitled

mail@pastecode.io avatar
unknown
plain_text
7 months ago
985 B
1
Indexable
Never
CREATE OR REPLACE FUNCTION mdm.codelco_average_time_each_step(organization integer DEFAULT NULL::integer, creation_user uuid DEFAULT NULL::uuid)
 RETURNS SETOF mdm.codelco_step_log
 LANGUAGE plpgsql
AS $function$
	declare query text := '';
begin
	query := 'select  CAST((DATE_PART(''day'', end_action::timestamp - action_date::timestamp) * 24 +  DATE_PART(''hour'', end_action::timestamp - action_date::timestamp)) as int4) as id,  workflow_id, material_creation_id, action_id ,action_date ,end_action ,user_id , before_step , actual_step ,rejection_reason_id ,comment from mdm.codelco_step_log csl inner join mdm.codelco_material_creation cmc on cmc.id = csl.material_creation_id where action_id  = 26 ';
		
	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 user_id = ' || creation_user;
	END IF;
RETURN QUERY EXECUTE query;
END;
$function$