Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE person (
  person_id serial PRIMARY KEY
, name text NOT NULL
, team text
);

CREATE TABLE score (
  score_id serial PRIMARY KEY
, person_id int NOT NULL REFERENCES person
, score int NOT NULL
);

-- dummy values
WITH ins AS (
   INSERT INTO person(name, team)
   SELECT 'Jon Doe ' || p, t
   FROM   generate_series(1,20) p                   -- 20 guys x
        , unnest ('{team1,team2,team3}'::text[]) t  -- 3 teams
   RETURNING person_id
   )
INSERT INTO score(person_id, score)
SELECT i.person_id, (random() * 100)::int
FROM   ins i, generate_series(1,5) g;               -- 5 scores each


-- function
CREATE OR REPLACE FUNCTION f_demo(_agg        text DEFAULT 'sum'
                                , _left_join  bool DEFAULT false
                                , _where_name text DEFAULT null)
  RETURNS TABLE(person_id int, name text, team text, score numeric, rnk bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _agg_op  CONSTANT text[] := '{count, sum, avg}';  -- allowed aggregate functions
   _sql     text;
BEGIN
   -- assert --
   IF _agg ILIKE ANY (_agg_op) THEN
      -- all good
   ELSE
      RAISE EXCEPTION '_agg must be one of %', _agg_op;
   END IF;


   -- query --
   _sql := format('
   SELECT *
   FROM  (
      SELECT p.person_id, p.name, p.team, %1$s(s.score)::numeric AS score
           , rank() OVER (PARTITION BY p.team ORDER BY %1$s(s.score) DESC) AS rnk
       FROM  person p
       %2$s  score  s USING (person_id)
       %3$s
       GROUP BY 1
      ) sub
   WHERE  rnk < 3
   ORDER  BY team, rnk'
      , _agg                                                                -- %1$s
      , CASE WHEN _left_join THEN 'LEFT JOIN' ELSE 'JOIN' END               -- %2$s
      , CASE WHEN _where_name <> '' THEN 'WHERE p.name LIKE $1' ELSE '' END -- %3$s
   );

   -- debug   -- inspect query first
   -- RAISE NOTICE '%', _sql;

   -- execute -- unquote when tested ok
   RETURN QUERY EXECUTE _sql
   USING  _where_name;   -- $1
END
$func$;

CREATE TABLE



CREATE TABLE



INSERT 0 300



CREATE FUNCTION


-- raw SQL
SELECT *
FROM  (
   SELECT p.person_id, p.name, p.team, sum(s.score)::int AS score
        , rank() OVER (PARTITION BY p.team ORDER BY sum(s.score) DESC)::int AS rnk
   FROM  person p
   JOIN  score  s USING (person_id)
   GROUP BY 1
   ) sub
WHERE  rnk < 3;


person_id
name
team
score
rnk


12
Jon Doe 12
team1
414
1

18
Jon Doe 18
team1
360
2

30
Jon Doe 10
team2
382
1

29
Jon Doe 9
team2
342
2

41
Jon Doe 1
team3
337
1

44
Jon Doe 4
team3
328
2



SELECT 6


-- Function Call variants
SELECT * FROM f_demo();


person_id
name
team
score
rnk


12
Jon Doe 12
team1
414
1

18
Jon Doe 18
team1
360
2

30
Jon Doe 10
team2
382
1

29
Jon Doe 9
team2
342
2

41
Jon Doe 1
team3
337
1

44
Jon Doe 4
team3
328
2



SELECT 6


SELECT * FROM f_demo('sum', TRUE, '%2');


person_id
name
team
score
rnk


12
Jon Doe 12
team1
414
1

2
Jon Doe 2
team1
314
2

32
Jon Doe 12
team2
249
1

22
Jon Doe 2
team2
168
2

52
Jon Doe 12
team3
272
1

42
Jon Doe 2
team3
255
2



SELECT 6


SELECT * FROM f_demo('avg', FALSE);


person_id
name
team
score
rnk


12
Jon Doe 12
team1
82.8000000000000000
1

18
Jon Doe 18
team1
72.0000000000000000
2

30
Jon Doe 10
team2
76.4000000000000000
1

29
Jon Doe 9
team2
68.4000000000000000
2

41
Jon Doe 1
team3
67.4000000000000000
1

44
Jon Doe 4
team3
65.6000000000000000
2



SELECT 6


SELECT * FROM f_demo(_where_name := '%1_');


person_id
name
team
score
rnk


12
Jon Doe 12
team1
414
1

18
Jon Doe 18
team1
360
2

30
Jon Doe 10
team2
382
1

39
Jon Doe 19
team2
278
2

57
Jon Doe 17
team3
291
1

52
Jon Doe 12
team3
272
2



SELECT 6

person_id	name	team	score	rnk
12	Jon Doe 12	team1	414	1
18	Jon Doe 18	team1	360	2
30	Jon Doe 10	team2	382	1
29	Jon Doe 9	team2	342	2
41	Jon Doe 1	team3	337	1
44	Jon Doe 4	team3	328	2

person_id	name	team	score	rnk
12	Jon Doe 12	team1	414	1
2	Jon Doe 2	team1	314	2
32	Jon Doe 12	team2	249	1
22	Jon Doe 2	team2	168	2
52	Jon Doe 12	team3	272	1
42	Jon Doe 2	team3	255	2

person_id	name	team	score	rnk
12	Jon Doe 12	team1	82.8000000000000000	1
18	Jon Doe 18	team1	72.0000000000000000	2
30	Jon Doe 10	team2	76.4000000000000000	1
29	Jon Doe 9	team2	68.4000000000000000	2
41	Jon Doe 1	team3	67.4000000000000000	1
44	Jon Doe 4	team3	65.6000000000000000	2