© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
SupabaseS
Supabase•4y ago•
4 replies
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
ORDER BY
table in the params, aswell as if it's
ASC
ASC
or
DESC
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;
$$;
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
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
ORDER BY
with
wizard_win_rate
wizard_win_rate
it works!

I tried a lot of things but nothing worked. Anyone has an idea?
Supabase banner
SupabaseJoin
Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.
45,816Members
Resources
Was this page helpful?

Similar Threads

Recent Announcements

Similar Threads

Order by in js sdk
SupabaseSSupabase / help-and-questions
4y ago
Order by random()
SupabaseSSupabase / help-and-questions
4y ago
Order by multiple columns in supabase
SupabaseSSupabase / help-and-questions
13mo ago
Authentication does not work in production
SupabaseSSupabase / help-and-questions
4y ago