Erklärt: writeable Common Table Expressions

[akretschmer]

Damit (in Kurz: wCTE) wird in PostgreSQL ein Feature bezeichnet, mit dem man u.a., aber nicht nur, eine Reihe von Abfragen, die aufeinander aufbauen und 'normalerweise' sequentiell an die Datenbank zu schicken sind (wobei dann auch in der Applikation auf Resultate der vorhergehenden Abfrage zu reagieren ist, in ein einzelnes komplexeres Query verpackt werden kann.

Um das mal zu demonstrieren habe ich eine Tabelle Kunden, eine Tabelle Buecher und eine Tabelle Ausleihe.

Es sind bereits Kunden da und es gibt Bücher.

Nun kommt heute ein neuer Kunde, dieser ist zu erfassen und bekommt eine ID. Außerdem leiht er gleich 2 Bücher aus. Zu diesen müssen die Bücher-IDs ermittelt werden. Die Kunden-Id und die IDs der 2 Bücher sollen dann in der Ausleihe-Tabelle gespeichert werden. Ich weiß weder, welche Kunden-ID er bekommt, noch kenne ich die Bücher-ID. Macht nix, mit einem einzigen SQL bekomme ich ALLES gespeichert und erfahre auch noch die neu vergebene Kunden-ID als Resultat. Glaubt ihr nicht?

Also, die Tabellen:

test=# select * from kunden;
 id |  eintritt  |   name
----+------------+-----------
  1 | 2013-01-30 | testuser1
  2 | 2013-02-04 | testuser2
  3 | 2013-02-06 | testuser3
  4 | 2013-02-08 | testuser4
(4 rows)

Time: 0,164 ms
test=*# select * from buecher;
 id |                titel
----+-------------------------------------
  1 | MySQL
  2 | Oraggle
  3 | PostgreSQL
  4 | Von MySQL nach PostgreSQL migrieren
(4 rows)

Time: 0,160 ms
test=*# select * from ausleihe;
 id | datum | kunde | buch
----+-------+-------+------
(0 rows)

Nun mein SQL:

WITH
  neuer_kunde AS (INSERT INTO kunden (name) VALUES ('DB-Forum User') RETURNING id),
  ausleih_buecher AS (SELECT id FROM buecher WHERE titel IN ('PostgreSQL','Von MySQL nach PostgreSQL migrieren')),
  erfolg AS (
    INSERT INTO ausleihe (datum,kunde,buch) SELECT current_date, a.id, b.id FROM neuer_kunde a CROSS JOIN ausleih_buecher b
    RETURNING id
  )
  SELECT neuer_kunde.id AS neue_kunden_id, count(ausleih_buecher) AS anzahl_ausleihe FROM neuer_kunde, ausleih_buecher  GROUP BY neuer_kunde.id
;

Ich hab das mal in einer externen Datei und lade das nun mit \i (ist ein psql-Befehl):

test=*# \i wcte_demo.sql
 neue_kunden_id | anzahl_ausleihe
----------------+-----------------
              5 |               2
(1 row)

Time: 0,948 ms
test=*# select * from ausleihe;
 id |   datum    | kunde | buch
----+------------+-------+------
  5 | 2013-02-09 |     5 |    3
  6 | 2013-02-09 |     5 |    4
(2 rows)

Cool, oder?


Fragen? Fragen!

Andreas