Evaluate random function for each row

I have the following sql:

SELECT SERIES.TIME AS TIME
FROM GENERATE_SERIES(
             CAST('now' AS TIMESTAMP) - cast('1 month' AS INTERVAL),
             CAST('now' AS TIMESTAMP),
             CAST('1 day' AS INTERVAL)
         ) AS SERIES (TIME)
         CROSS JOIN
         (SELECT * FROM GENERATE_SERIES(1, (floor(random() * 10)::int))) as RANDOM_TABLE

This should generate a series of dates with an interval of one hour in it. What I now try to get is each day to be randomly fanned out. I have noticed that in the above sql statement random() is only evaluated once even though it is in a subquery.

Now without the CROSS JOIN I get:

| 2019-02-13 |
| 2019-02-14 |
| 2019-02-15 |

and with the CROSS JOIN I now get for example:

| 2019-02-13 |
| 2019-02-13 |
| 2019-02-13 |
| 2019-02-14 |
| 2019-02-14 |
| 2019-02-14 |
| 2019-02-15 |
| 2019-02-15 |
| 2019-02-15 |

What I would like to have is a random number each time I fan out. E.g.:

| 2019-02-13 |
| 2019-02-14 |
| 2019-02-14 |
| 2019-02-14 |
| 2019-02-14 |
| 2019-02-15 |
| 2019-02-15 |
| 2019-02-15 |

How do I need to modify my query to achieve that?

Answer

The problem is premature optimization. Postgres “forgets” that random() is volatile and should be called each time.

Here is a fix:

SELECT SERIES.TIME AS TIME
FROM (SELECT s.time, (floor(random() * 10)::int) as num
      FROM GENERATE_SERIES(now() - interval '1 month' , now(), interval '1 day') s(time)
         ) AS SERIES (TIME)
         CROSS JOIN LATERAL
         GENERATE_SERIES(1, num) as RANDOM_TABLE

And a db<>fiddle.

Note that I made some substitutions that seem more “Postgres-like” — using now(), interval and so on for date arithmetic rather than converting strings.