Common table expressions

Uit Yapf

Inleiding

Sinds postgresql 8.4 ondersteunt PostgreSQL de zogenaamde Common Table Expressions.

Kort samengevat zijn dit aliassen voor subqueries, die worden gedefineerd bovenaan de query.

Bijvoorbeeld:

WITH
    mijncte AS
    (
      SELECT 'Hallo' AS groet
    )
SELECT
  groet
FROM
  mijncte;

Hierin wordt een CTE gedefinieerd onder de naam mijncte en die uitdrukking gedraagt zich voor de rest van de query als de uitkomst van de query Select 'Hallo' as groet. Een simpele SELECT op die CTE levert dus ook Hallo op.

Noot dat dit hele statement samen de query is. Een CTE wordt opgesteld als onderdeel van de query en is dus niet later herbruikbaar in een andere query, ze worden niet opgeslagen en ze hoeven niet verwijderd te worden.

Je kunt per query meerdee CTE's definieren en je kunt CTE's ook gebruiken binnen andere CTE's:

WITH
    cte_groeten AS
    (
      SELECT CAST('Hallo' AS TEXT) AS groet
    )
    , cte_namen AS
    (
      SELECT groet || ' ' || CAST('Kareltje' AS TEXT) FROM cte_groeten
    )
SELECT
  *
FROM
  cte_namen;

in bovenstaande voorbeeld wordt een CTE cte_groeten gedefinieerd die een veld groet teruggeeft met de tekst Hallo erin. Daarna wordt een tweede CTE gedefinieerd die selecteert uit cte_groeten en plakt een naam aan de groet uit de eerst CTE.

De CAST() in beide CTE's is alleen nodig omdat er een string wordt geselecteerd en de database moet weten als welk datatype hij dat moet behandelen, het heeft verder geen betrekking op de werking van CTE's.

Performance

De CTE maakt in principe een tijdelijke tabel met daarin de resultaten van de query. Dat betekent dat je de data kunt hergebruiken binnen je query, zonder extra kosten.

Recursie

Een grote kracht van CTE's is dat ze ook recursief kunnen zijn.

De syntax gebruikt het keyword RECURSIVE en een constructie met een UNION tussen de originele query en een query die gejoind is aan de CTE zelf.

WITH RECURSIVE DATA
AS
(
  SELECT
    *
  FROM
    tabel
  WHERE id=10 -- Geef resultaten voor alles wat samenhangt met record 10
  UNION ALL
  SELECT
    tabel.*
  FROM
    tabel 
  INNER JOIN
    DATA AS kinderen -- koppel recursief aan de CTE zelf
  ON
    kinderen.parentid= tabel.id
)
SELECT
  *
FROM
  DATA;

Deze query haalt alles op uit tabel waar id=10, en daarbij recursief alles uit tabel waarbij de parentid gelijk is aan het id van id=10, en daarna weer alles wat een parentid heeft uit die set, etc tot er niets meer gevonden wordt.

Voorbeelden

URL's uit een CMS

CREATE TABLE menu (

 itemid integer,
 parentitemid integer,
 title1 text

) WITH (

 OIDS=FALSE

); ALTER TABLE menu OWNER TO www;

INSERT INTO menu (itemid, parentitemid, title) values(1,null,'hoofd'), (2,1,'sub1'),(3,2,'sub2');

Voorbeeld 1 Paden

Om alle kinderen van een bepaalde node op te halen in een mooi pad:

WITH RECURSIVE subdepartment AS
(
    -- non-recursive term
    SELECT * FROM department WHERE name = 'A'
 
    UNION ALL
 
    -- recursive term
    SELECT d.*
    FROM
        department AS d
    JOIN
        subdepartment AS sdddd
        ON (d.parent_department = sd.id)
)
SELECT GROUP_CONCAT_DELIMITER(name,'/') FROM subdepartment;

Noot: group_concat_delimiter() is geen standaard functie in PgSQL, zie hier.

URL's opzoeken in een CMS

Een van de prettigste mogelijkheden die Recursieve CTE's bieden is het opbouwen van URL's uit een contentstructuur. Een URL is opgebouwd uit een hierarchie.

Stel dat je pagina's zijn opgeslagen in een standaard hierarchie met een parentid:

Demo code

CREATE TABLE content_nodes
(
  nodeid integer NOT NULL,
  urlpart text,
  parent_nodeid integer,
  CONSTRAINT pkey PRIMARY KEY (nodeid)
)
WITH (
  OIDS=FALSE
);
 
INSERT INTO content_nodes VALUES (1, 'home', NULL);
INSERT INTO content_nodes VALUES (2, 'contact', 1);
INSERT INTO content_nodes VALUES (3, 'colofon', 1);
INSERT INTO content_nodes VALUES (4, 'nieuws', 1);
INSERT INTO content_nodes VALUES (5, 'oud nieuws', 4);
INSERT INTO content_nodes VALUES (6, 'nieuw nieuws', 4);

De CTE

Dan kan een recursieve CTE met een kleine aanpassing alle URL's opboeren:

WITH RECURSIVE subpages AS (
  SELECT
    content_nodes.urlpart
    , content_nodes.nodeid
    , content_nodes.parent_nodeid
  FROM
    content_nodes
  WHERE
    parent_nodeid IS NULL
  UNION ALL
  SELECT
    sd.urlpart || '/' || d.urlpart
    , d.nodeid
    , d.parent_nodeid
  FROM
    content_nodes d
  JOIN
    subpages sd
  ON
    d.parent_nodeid = sd.nodeid
        )
SELECT
  *
FROM 
  subpages;

Noot dat de tweede query van de CTE nu de urlpart velden aan elkaar plakt met een slash ertussen. Door de recursie wordt dat gecombineerde pad steeds groter tot het het complete pad bevat.

Implementeren in en view

Voor het gemak giet je dat in een view:

CREATE OR REPLACE VIEW view_pages
AS
WITH RECURSIVE subpages AS (
  SELECT content_nodes.urlpart AS url, content_nodes.nodeid, content_nodes.parent_nodeid
  FROM content_nodes WHERE parent_nodeid IS NULL
  UNION ALL 
  SELECT sd.url || '/' || d.urlpart AS url , d.nodeid, d.parent_nodeid
  FROM content_nodes d
  JOIN subpages sd ON d.parent_nodeid = sd.nodeid
)
SELECT
  url
FROM 
  subpages;

Toepassing

De view levert nu de URL's van alle pagina's dus daar kun je nu op zoeken:

SELECT *
FROM view_contentnodes
WHERE 'home/nieuws' = url
ORDER BY length(url) DESC
LIMIT 1;

In dynamische omgevingen is de daadwerkelijke URL regelmatig langer dan de pure URL waaronder het CMS de pagina kent, denk aan een .html extentie. Daarnaast is de url bij voorkeur niet hoofdlettergevoelig, en dan kom je uit op dit:

SELECT *
FROM view_contentnodes
WHERE 'home/nieuws.html' ILIKE url || '%'
ORDER BY length(url) DESC
LIMIT 1;

Een abstract voorbeeld

Dit voorbeeld selecteert uit een set values VALUES(1), en koppelt dat aan een query die weer die waarde selecteert, maar dan +1. Voor de veiligheid is er een limiet n<100 op gezet, zonder dat gaat de query door tot al het geheugen op is.

WITH RECURSIVE
  t(n) AS 
  (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
  )
SELECT
  n
FROM
  t