Postgres function returns ERROR of no destination

Question

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?

JGH · Accepted Answer · 2021-04-19 14:34:35Z

2

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');

answered Apr 19, 2021 at 14:34

JGH

18.2k5 gold badges41 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres function returns ERROR of no destination

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related