BEGIN;
--
DROP TABLE IF EXISTS urednici CASCADE;
--
CREATE TABLE urednici (
       id      integer PRIMARY KEY,
       jmeno   text UNIQUE,
       vedouci integer,
       FOREIGN KEY (vedouci) REFERENCES urednici(id)
);
--
INSERT INTO urednici (vedouci, id, jmeno) VALUES
(NULL,  1, 'Marie'    ),
(  1,   2, 'Jiří'     ),
(  1,   3, 'Jan'      ),
(  6,   4, 'Jana'     ),
(  4,   5, 'Petr'     ),
(  1,   6, 'Pavel'    ),
(  5,   7, 'Jaroslav' ),
( 10,   8, 'František'),
( 10,   9, 'Věra'     ),
(  1,  10, 'Eva'      ),
(NULL, 11, 'Martin'   ),
( 11,  12, 'Tomáš'    ),
( 11,  13, 'Anna'     ),
( 11,  14, 'Hana'     ),
( 11,  15, 'Josef'    ),
( 17,  16, 'Štepán'   ),
( 11,  17, 'Miroslav' ),
( 17,  18, 'Václav'   ),
( 16,  19, 'Michal'   ),
( 19,  20, 'Milan'    ),
( 18,  21, 'Lenka'    ),
( 19,  22, 'Alena'    ),
( 19,  23, 'Jaroslava'),
(NULL, 24, 'Lucie'    ),
( 24,  25, 'Vladimír' ),
( 24,  26, 'Petra'    ),
( 28,  27, 'Kateřina' ),
( 24,  28, 'David'    ),
( 28,  29, 'Jakub'    ),
( 25,  30, 'Lukáš'    ),
( 25,  31, 'Helena'   ),
( 25,  32, 'Ludmila'  ),
( 30,  33, 'Ladislav' ),
( 30,  34, 'Jitka'    ),
( 34,  35, 'Jarmila'  ),
( 34,  36, 'Veronika' ),
( 21,  37, 'Martina'  ),
( 21,  38, 'Jiřina'   ),
( 14,  39, 'Michaela' ),
( 13,  40, 'Tereza'   );
--
WITH RECURSIVE hierarchie (id,jmeno,vedouci,uroven) AS (
     SELECT id, jmeno, vedouci,0
     FROM   urednici
     WHERE  jmeno='Marie' -- vedouci IS NULL je jina uloha 
     UNION ALL
     SELECT urednici.id, urednici.jmeno, urednici.vedouci,
     	    hierarchie.uroven+1
     FROM   urednici
     JOIN   hierarchie ON hierarchie.id = urednici.vedouci
)
SELECT *
FROM   hierarchie;
--
COMMIT;
