CODEWITHSUNDEEP
postgresql
Jan Tojnar
Jan Tojnar

Reputation: 5524

Creating custom window function

I have table of teams participating in events.

id,
event_id,
gender enum('men', 'mixed', 'women'),
age enum('ultraveteran', 'superveteran', 'veteran', 'open', 'junior'),
score

For every team I am trying to get a rank in each of age categories (note that superveteran category also contains ultraveterans, veterans contain both previous categories and open contains all categories).

I can get the rank for veterans with following query:

select id, (case age in ('ultraveteran', 'superveteran', 'veteran') then
      row_number() over (partition by event_id, gender, age in ('ultraveteran', 'superveteran', 'veteran')
      order by score desc)
   else null end) as rank_v

It isn't very nice as you can see. Because I have five categories the whole query starts to look quite messy (and I have another five similar "columns" for number of teams in each category).

I thought I would create window function to simplify it a bit but when I defined my window function (there isn't almost any documentation on this btw) I couldn't even get it to return argument passed to it.

select class_rank('veteran', 'open') over (partition by event_id, gender) as one
from team where event_id in (11,19);


create or replace function class_rank(ranked_class text, team_class text)
returns text as $$
 select ranked_class;
$$
language 'sql' window;

I thought maybe postgre doesn't support sql window functions as

This is currently only useful for functions written in C

was stated in the docs (section Parameters → WINDOW) but it didn't throw any error and only returned null so I tried select 'foo'::text and this one worked so I assume you can define SQL window functions as well.

How can I create the function so that it accepts arguments properly?

P.S.: I know this would be much more efficient when written in C but the results are cached so it doesn't matter that much.

Upvotes: 2

Views: 2547

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45910

Custom windows aggregates in SQL, PL/pgSQL are not really supported. Currently, there is no way to do this.

For more information look to this thread.

Upvotes: 3

Related Questions