Postgresql: Common Table Expressions

Ab Postgres Version 9.1 verfügbar: Common Table Expressions

WITH delayed AS
  (SELECT DISTINCT p.pnr_id,
                   d.code
   FROM pnr p
   JOIN leg l ON (p.pnr_id = l.pnr_id)
   JOIN delay d ON (d.carrier = l.carrier
                    AND d.flightnum = l.flnr
                    AND d.depdate = l.depart_time::date) LIMIT 10)
UPDATE pnr
SET delay_code = d.code
FROM delayed d
WHERE pnr.pnr_id = d.pnr_id;

Hier wird ein Select in der temporären Tabelle „delayed“ zwischengespeichert, um die Daten für ein Update zu nutzen.