POSTGRES generate n rows based on a value n

Question

I need to generate a view, in postgres, containing n rows based on a value n in a column.

Let's make an example. I have a table like this:

   A*  |  B   |  C
 --------------------
  abc  |  def |  4
  ghi  |  jkl |  7

I need to generate a view made like this:

   A  |  B   |  C
------------------------
  abc  |  def |  4
  abc  |  def |  4
  abc  |  def |  4
  abc  |  def |  4
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7

Is there a way to do it smartly? At the moment I am doing N UNION ALL with N big enough to cover all the cases (e.g. in this case 7).

Answer 1

Just use generate_series():

select t.*, generate_series(1, t.c)
from t ;

If you don't want the value in the result set, use a lateral join:

select t.*
from t, lateral
     generate_series(1, t.c);

Or:

select t.*
from t cross join lateral
     generate_series(1, t.c);

Answer 2

Using Recursive CTE

You can try this.

WITH RECURSIVE result(A,B,L,C) AS(
    SELECT A,B,1 L,MAX(C) C
   FROM T
    GROUP BY A,B
    UNION ALL
    SELECT A,B,L+1,C
    FROM result
    WHERE L+1 <= C
)
SELECT A,B,C
FROM result
ORDER BY C,A

SQLFIDDLE