recursion in postgresql with working example
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 |