/*****************************************************************
 * Lösche bereits existierende Views, Tabellen und Domänen.
 *****************************************************************/

DROP VIEW IF EXISTS vorfahren CASCADE;
DROP TABLE IF EXISTS eltern CASCADE;


/*****************************************************************
 * Tabellen
 *****************************************************************/

CREATE TABLE eltern
(kind  VARCHAR(50) NOT NULL,
 elter VARCHAR(50) NOT NULL,

 CONSTRAINT pk_eltern
   PRIMARY KEY (kind, elter)
);

/*****************************************************************
 * Daten
 *****************************************************************/

INSERT INTO 
eltern(kind, elter)
VALUES
('Sibylle',    'Wolfgang'),
('Sibylle',    'Marianne'),
('Sonja',      'Wolfgang'),
('Sonja',      'Marianne'),
('Verena',     'Wolfgang'),
('Verena',     'Marianne'),
('Marianne',   'Jakob'),
('Marianne',   'Franziska'),
('Franziska',  'Theresia'),
('Wolfgang',   'Karl'),
('Karl',       'Karl sen.'),
('Karl sen.',  'Franz'),
('Franz',      'Franz sen.'),
('Franz sen.', 'Matthias'),
('Wolfgang',   'Ilse'),
('Ilse',       'Erna'),
('Ilse',       'Robert'),
('Robert',     'Ludwig'),
('Robert',     'Engel')
;


/*****************************************************************
 * Views
 *****************************************************************/

/*
CREATE RECURSIVE VIEW vorfahren (person, vorfahre, generation)
AS
(
  -- Die Eltern sind Vorfahren (nicht-rekrusiver Zweig).
  SELECT kind, elter, 1 FROM eltern

  UNION

  -- Die Vorfahren der Eltern sind auch Vorfahren (rekursiver Zweig).
  SELECT e.kind, v.vorfahre, v.generation+1
  FROM   eltern e, vorfahren v
  WHERE  e.elter = v.person
)
;
*/

CREATE VIEW vorfahren (person, vorfahre, generation)
AS
-- Temporäre rekursive View 
-- (da PostgreSQL rekursive Views noch nicht unterstützt)
WITH RECURSIVE vorfahren_aux (person, vorfahre, generation)
AS
(
  -- Die Eltern sind Vorfahren (nicht-rekrusiver Zweig).
  SELECT kind, elter, 1 FROM eltern

  UNION

  -- Die Vorfahren der Eltern sind auch Vorfahren (rekursiver Zweig).
  SELECT e.kind, v.vorfahre, v.generation+1
  FROM   eltern e JOIN vorfahren_aux v ON e.elter = v.person
)
-- Selektiere alles von der temporären View.
SELECT person, vorfahre, generation FROM vorfahren_aux
;

-- Inhalt der View "vorfahren"
SELECT   person, vorfahre, generation 
FROM     vorfahren
;


/*****************************************************************
 * EOF
 *****************************************************************/
