1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SAVEPOINT test_begin;
select pgmig.assert_count(1);
-- ----------------------------------------------------------------------------
SELECT pgmig.pkg_op_before('init', 'test_pgmig', 'v0.0', 'git');
SELECT pgmig.assert_eq('pkg_op_before'
, (SELECT jsonb_build_object('code',code,'version',version)
     FROM pgmig.pkg where code='test_pgmig')
, '{
        "version": "v0.0",
        "code": "test_pgmig"
   }'::jsonb
);
ROLLBACK TO SAVEPOINT test_begin;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

select r.*
, abs.abs_rank
, cl.cl_rank
from results r
  left join (
    select
      id
    , row_number() over() as abs_rank
    from results where in_abs=1
  ) abs using(id)
  , lateral  (
      select * --coalesce(cl_rank,null) as cl_rank 
        from (
          select
            id
          , case when r.cl_next is null then 
              null
            else 
              row_number() over(partition by cl_next order by drv1_id)
            end as cl_rank
          from results r2 
          where
            r.cl_next is null or
            r.cl_next = any(r2.cls)
        ) cll
      where id = r.id
  ) cl
;