recursion in postgresql with working example

Postgre logo

recursion in postgresql is defined with key words “WITH RECURSIVE”.
you need to create CTE (Common Table Expressions)

in example below you can see using recursion for compute factorial for

WITH RECURSIVE t(n,m) AS ( -- this is CTE
    SELECT 5 AS n,5 AS m   -- something like n=5
  UNION ALL                -- this is needed for "join" iterations
    SELECT n-1, (n-1)*m FROM t WHERE (n-1)>0 -- for every iteration do value-- and make multiplication
)
SELECT MAX(n) AS NUMBER,MAX(m) AS factorial FROM t; -- finally show result