Recursive function in postgres

Question

How to map below query to postgres function.

WITH RECURSIVE source (counter, product) AS (
SELECT
1, 1
UNION ALL
SELECT
counter + 1, product * (counter + 1)
FROM source
WHERE
counter < 10
)
SELECT counter, product FROM source;

I am new to postgres. I want to achieve same functionality using PL/pgsql function.

Create a function that uses `return query`. See the examples in the manual: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#AEN58213 — , May 06 '14 at 12:13

klin · Accepted Answer · 2022-06-23T12:21:29.873

A classic approach, known from other programming languages, in which a function calls itself:

create or replace function recursive_function (ct int, pr int)
returns table (counter int, product int)
language plpgsql
as $$
begin
    return query select ct, pr;
    if ct < 10 then
        return query select * from recursive_function(ct+ 1, pr * (ct+ 1));
    end if;
end $$;

select * from recursive_function (1, 1);

You can compare this with an iterative solution in a loop:

create or replace function loop_function ()
returns table (counter int, product int)
language plpgsql
as $$
declare
    ct int;
    pr int = 1;
begin
    for ct in 1..10 loop
        pr = ct* pr;
        return query select ct, pr;
    end loop;
end $$;

select * from loop_function ();

Of course, you can also put the recursive query inside an SQL function:

create or replace function recursive_query()
returns table (counter int, product int)
language sql
as $$
    with recursive source as (
        select 1 as counter, 1 as product
    union all
        select counter+ 1, product* (counter+ 1)
        from source
        where counter < 10
    )
    select counter, product
    from source
$$;

select * from recursive_query();

Is using the recursive style likely to cause stack overflows if traversing a deeply nested structure? — jps, Jul 10 '15 at 13:56
@rastapanda - Postgres has a configuration parameter `max_stack_depth`, that specifies the maximum depth of the server's execution stack. It's default value (2MB) severely limits recursive functions. A simple test recursive function with two integer arguments reaches the limit on about 1000th level. Read more in [the documentation](http://www.postgresql.org/docs/9.4/static/runtime-config-resource.html). — klin, Jul 10 '15 at 15:34

Recursive function in postgres

1 Answers1