SupabaseS
Supabase•4y ago
Relisora

order by does not work in a function

Hi!

I have noticed a very weird behaviour for my SQL function.

My goal is to send the ORDER BY table in the params, aswell as if it's ASC or DESC.

Overall I found a solution that works pretty well... Except for one special case.

Here is my function (please bear with me, I simplified as much as I could...):

create or replace function test(order_input text default 'wizard_battles', order_direction_input text default 'asc')
returns table (wizard_id int, wizard_battles bigint, wizard_wins bigint, wizard_win_rate numeric)
language plpgsql
as $$
begin
return query
SELECT
    wizard_battles.wizard_id,
    wizard_battles.wizard_battles,
    wizard_battles.wizard_wins,
    round(100 - (wizard_battles.wizard_wins::real / wizard_battles.wizard_battles::real * 100::real)::numeric, 2) AS wizard_win_rate
    FROM ( SELECT team.wizard_id,
            count(*) AS wizard_battles,
            count(*) FILTER (WHERE team.is_winner = true) AS wizard_wins
           FROM battle
             JOIN team ON team.battle_id = battle.id and team.wizard_id <> 'some-id'
          inner join "usersOnBattles" ON "usersOnBattles"."battleId" = battle.id
          inner join "user" ON "user".id = "usersOnBattles"."usersId"
          WHERE "user".id = 'some-uuid'
          GROUP BY team.wizard_id) wizard_battles
    GROUP BY wizard_battles.wizard_battles, wizard_battles.wizard_wins, wizard_battles.wizard_id
    ORDER BY 
        case when order_direction_input = 'asc' and order_input = 'wizard_win_rate' THEN wizard_win_rate end,
        case when order_direction_input = 'desc' and order_input = 'wizard_win_rate' THEN wizard_win_rate end desc;
end;
$$;


I initially had more case when which all work, I only left the one that doesn't for readability.

Funnily if I extract the query and populate the ORDER BY with wizard_win_rate it works!

I tried a lot of things but nothing worked. Anyone has an idea?
Was this page helpful?