How to generate rows with dates in sql?

Question

PostgreSQL

The task is this, the database has a list of users and their date of registration. It is necessary to generate a table in which, for each user, as many rows will be added as the number of full segments of 30 days passed from his registration to the current day.

Example. Starting table:
user_id reg_date
1 01.11.2020
2 01.12.2020

Result:
user_id Date
1 01.11.2020
1 01.12.2020
1 31.12.2020
1 30.01.2021
1 01.03.2021
2 01.12.2020
2 31.12.2020
2 30.01.2021
2 03/01/2021

The difficulty is that for each user there may be a different number of resulting rows, depending on his registration date.
I can't figure out how to generate this exactly in the sql query, without resorting to variables / loops ...

Answer 1

Yeah ))
Well, in general, I think it will be something like this:
Example on MSSQL
Example on Psql13
Result:

+========+============+=====================+
| userid | regdate    | newdate             |
+========+============+=====================+
| 1      | 2020-11-01 | 2020-11-01 00:00:00 |
+--------+------------+---------------------+
| 1      | 2020-11-01 | 2020-12-01 00:00:00 |
+--------+------------+---------------------+
| 1      | 2020-11-01 | 2020-12-31 00:00:00 |
+--------+------------+---------------------+
| 1      | 2020-11-01 | 2021-01-30 00:00:00 |
+--------+------------+---------------------+
| 1      | 2020-11-01 | 2021-03-01 00:00:00 |
+--------+------------+---------------------+
| 2      | 2020-12-01 | 2020-12-01 00:00:00 |
+--------+------------+---------------------+
| 2      | 2020-12-01 | 2020-12-31 00:00:00 |
+--------+------------+---------------------+
| 2      | 2020-12-01 | 2021-01-30 00:00:00 |
+--------+------------+---------------------+
| 2      | 2020-12-01 | 2021-03-01 00:00:00 |
+--------+------------+---------------------+

Answer 2

Recursive functions and SQL are poorly compatible - such logic is usually taken out of the DBMS, which is what I advise you to do.