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: postgresql.org/docs/current/static/… — user330315
– user330315, Commented May 6, 2014 at 12:13

klin · Accepted Answer · 2022-06-23 12:21:29Z

14

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

edited Jun 23, 2022 at 12:21

answered Jun 1, 2014 at 12:03

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jps Over a year ago

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

klin Over a year ago

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

Collectives™ on Stack Overflow

Recursive function in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related