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.

Solution

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();

Comments

[jps]

Is using the recursive style likely to cause stack overflows if traversing a deeply nested structure?

[klin]

@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.