0079.yml

version: 79
description: Speed up hooks fetching last fires by date
migrationScript: |-
  begin
    create index hooks_last_fires_time on hooks_last_fires (hook_group_id,hook_id,task_create_time);
  end
downgradeScript: |-
  begin
    drop index hooks_last_fires_time;
  end
methods:
  get_last_fires:
    description: |-
      Get hooks last fires filtered by the `hook_group_id` and `hook_id` arguments,
      ordered by `hook_group_id`, `hook_id`, and `task_create_time`.
      If the pagination arguments are both NULL, all rows are returned.
      Otherwise, page_size rows are returned at offset page_offset.
    mode: read
    serviceName: hooks
    args: hook_group_id_in text, hook_id_in text, page_size_in integer, page_offset_in integer
    returns: table(hook_group_id text, hook_id text, fired_by text, task_id text, task_create_time timestamptz, result text, error text, etag uuid)
    body: |-
      begin
        return query
        select
          hooks_last_fires.hook_group_id,
          hooks_last_fires.hook_id,
          hooks_last_fires.fired_by,
          hooks_last_fires.task_id,
          hooks_last_fires.task_create_time,
          hooks_last_fires.result,
          hooks_last_fires.error,
          public.gen_random_uuid()
        from hooks_last_fires
        where
          hooks_last_fires.hook_group_id = hook_group_id_in and
          hooks_last_fires.hook_id = hook_id_in
        order by hook_group_id, hook_id, task_create_time DESC
        limit get_page_limit(page_size_in)
        offset get_page_offset(page_offset_in);
      end