CODEWITHSUNDEEP
postgresql
Maksym Rybalkin
Maksym Rybalkin

Reputation: 543

Retutring from a function using case

Here is my postgres function:

create or replace function fail_hold_pay_count_metric()
    returns table (
        fail_count     int,
        hold_pay_count int
    )
as
$$
    declare
        total_count         int;
        fail_count          int;
        hold_pay_count      int;
        required_rows_count int;
        percent_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
        into total_count, fail_count, hold_pay_count
        from bundle where updated_at > (now() - interval '1 year');
        if total_count > 10
        then
            required_rows_count := (select fail_count + hold_pay_count);
            percent_count := (select (required_rows_count / total_count * 100));
        end if;
        return query (select case when (percent_count > 50) then (fail_count, hold_pay_count) else (0, 0) end);
    end;
$$ language plpgsql;

I need to return (0,0) if percent_count was lower than 50. My function doesn't work. Where did I make a mistake?

Upvotes: 0

Views: 17

Answers (1)

Mike Organek
Mike Organek

Reputation: 12484

You are returning a single column of a row type instead of a row of two int types.

Please try this, instead:

return query select case when percent_count > 50 then fail_count else 0 end,
                    case when percent_count > 50 then hold_pay_count else 0 end;

Upvotes: 1

Related Questions