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:

SELECT 1 as fk FROM generate_series(1, sc.getfreq(1), 1)
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.

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

  • The recursive part of an rCTE can only build on the number of rows returned from the previous iteration - and dies down once an iteration produces no new rows. Your example suggests steadily decreasing results for getfreq(fk), making an rCTE seem possible. But I think that's misleading and getfreq(4) > getfreq(3) is possible, so rCTEs are basically out. You should clarify that in the question. Jan 9 at 0:16
  • Hi @ErwinBrandstetter, thank you for your comment. In my example, I suggest incrementing "FK" by 1 in every iteration, until 10. It could start from 10 and decrease to 1; as order would not matter. as for getfreq(fk) this returns a "random" frequency, for e.g. getfreq(1) returns 5, getfreq(2) returns 10, getfreq(3) returns 6. If I hinted towards a certain pattern for the input of getfreq "fk" to the output; it was not intended. Is this is what you were referring to? In any case, I do not think the return of getfreq has any implications on the recursive part of the CTE. Am I wrong? Jan 9 at 10:51

I think you don't need a recursive solution.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.