Postgres function returns ERROR of no destination

user_12898912 0
Viewed 31

Here is my function for my DB:

create function calculate_metrics()
  returns table
      (
        fail_count int,
        hold_pay_count int
      )
as
$$
declare
  total_count               int;
  fail_count                int;
  hold_pay_count            int;
begin
    select count(1)                                         as total_count,
       sum(case when status = 'FAIL' then 1 else 0 end)     as fail_count,
       sum(case when status = 'HOLD_PAY' then 1 else 0 end) as hold_pay_count
    from bundle where updated_at > (now() - interval '1 day');
  if total_count > 0
  then
    return query select fail_count, hold_pay_count;
  end if;
end;
$$ language plpgsql;

When I'm trying to get select calculate_metrics(), I'm getting the error:

SQL Error [42601]: ERROR: query has no destination for result data
  Suggestion: If you want to discard the results of a SELECT, use PERFORM instead.
  Where: PL/pgSQL function calculate_metrics() line 10 at SQL statement

The function is successfully create, and I have already insert data which consists the conditions of the requests. Where is my mistake?

postgresql
1 Answers

Naming the column is not enough, you need to save the output into your variables

select count(1)                                         as total_count,
       sum(case when status = 'FAIL' then 1 else 0 end)     as fail_count,
       sum(case when status = 'HOLD_PAY' then 1 else 0 end) as hold_pay_count
INTO total_count, fail_count, hold_pay_count
from bundle where updated_at > (now() - interval '1 day');
user_76355690
Related