Stored Procedure

 DROP TABLE IF EXISTS user_response;

create or replace procedure all_pending_status()
  language plpgsql
as $$
  declare
    assignments integer[];
    assignment integer;
begin
    select array_agg(action_assignments.id) into assignments from action_assignments where active = true;
    FOREACH assignment in ARRAY assignments
      LOOP
        call get_pending_status(assignment, CURRENT_DATE);
      end loop;
end;$$;

create or replace procedure get_pending_status(
  action_assignment int,
  due_date_target date
)
  language plpgsql
as $$

declare
  overdue_count integer;
  recent_event_id integer;
  responsible_users integer[];
  user_id int;
  result record;
  not_started_user varchar;
  action_assignment_record record;
begin
  create table if not exists user_response (
                                             id SERIAL PRIMARY KEY, name varchar, user_id int, response varchar,
                                             action_response_id int, action_event_id int, action_result_id int,
                                             action_assignment_id int, action_id int
  );
  drop table if exists event_first; -- first step
  drop table if exists event_overdue;
  -- raise is use to debug as below:
  -- raise notice 'action_assignment: %', action_assignment;
  select * into action_assignment_record from action_assignments where id = action_assignment limit 1;
  select id into recent_event_id from action_events where action_events.action_assignment_id = action_assignment and action_events.due_date <= CURRENT_DATE order by due_date desc limit 1;

  -- find responsible_users ( users join action_responsibles  where action_assignment_id = 107) then loop
  select array_agg(action_responsibles.user_id) into responsible_users from action_responsibles where action_assignment_id = action_assignment;

  -- find action_results by user_id and action_event_id 53
  FOREACH user_id IN ARRAY responsible_users
    LOOP
      SELECT * INTO result FROM "action_results"
                                  left outer join users on action_results.by_user_id = users.id or action_results.for_user_id = users.id
                                  left outer join action_responses on action_results.action_response_id = action_responses.id
      WHERE "action_results"."action_event_id" = recent_event_id AND (by_user_id = user_id or for_user_id = user_id) ORDER BY "action_results"."created_at" DESC LIMIT 1;
      if (select result::text ~ '[0-9\.]+') then
        insert into user_response ( name, user_id, response, action_response_id, action_event_id, action_result_id,
                                    action_assignment_id, action_id )
        values ( result.name, user_id, result.title, result.action_response_id, result.action_event_id,
                 result.id, action_assignment, action_assignment_record.action_id );
      else
        select name into not_started_user from users where id = user_id;
        insert into user_responses ( name, user_id, response, action_response_id, action_event_id, action_assignment_id,
                                    action_id )
        values ( not_started_user, user_id, 'Not Started', 0 , recent_event_id, action_assignment,
                 action_assignment_record.action_id );
      end if;
    END LOOP;
  commit;
end;$$;