Recursive CTE in PostgreSQL to generate data with different frequencies

Question

I am trying to write a data generation query which uses recursive CTE in Postgresql 14.

Consider schema "sc" which includes a function getfreq. getfreq takes an int as param (which represents a foreign-key to another table), and returns an int back, which represents a frequency.

Now consider this query:

WITH RECURSIVE rec AS 
(
SELECT 1 as fk FROM generate_series(1, sc.getfreq(1), 1)
UNION ALL
SELECT r.fk + 1 FROM rec AS r WHERE r.fk + 1 <= 10
)
select row_number() OVER () as pk, fk from rec

getfreq expects an int from 1 to 10 (hence the r.fk <= 10 exit condition). It returns a frequency N. I want each iteration of the recursive CTE to create N rows. The result of every iteration is to be combined together by the UNION ALL clause. By the end I want a result where the count of rows is equal to the sum of frequencies returned by getfreq over the 10 iterations.

In the example above sc.getfreq(1) will always return 5, hence I am getting a result set of 50 rows; first 5 with fk = 1, second 5 with fk = 2 and so on. However, sc.getfreq() should in fact be called with the iterated value, so second iteration should be sc.getfreq(2) and so on. Naturally, sc.getfreq(2) would return a different frequency and not 5 and hence the end result should not have 50 rows.

I have tried to use "fk" in getfreq, as follows: sc.getfreq(fk); since "fk" is being incremented by the recursive part of the CTE (and would hence be 2 in second iteration, 3 in third iteration and so on), but column "fk" does not exist within the context of the FROM, presumably because the "SELECT" part would not have run yet.

Are recursive CTE's suitable to solve this? Can I achieve with I want with some tweaking?

Example output where getfreq(1) returns 5, getfreq(2) returns 2 and getfreq(3) returns 1.

PK	FK
1	1
2	1
3	1
4	1
5	1
6	2
7	2
8	3

...... and so on (this is an incomplete example of 3 iterations).

Answer 1

I think you don't need a recursive solution.

select 
    row_number() over (order by r.fk, s.p) as pk, 
    r.fk
from 
    generate_series(1, 10) as r (fk),
    generate_series(1, sc.getfreq(r.fk)) as s (p)
 ;