First of all, im coming from this question, where I was recommended to use generate_series() , but after some adjustments in my db, I dont really understand how to create such sentence This is the old question: Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default?
But I will rephrase the question again:
Those are my initial tables
Users: id(int) PK, weeks_to_live(int)
Calendar: id(int) PK, user_id (int) (FK -> Users(id))*
CalendarField: id(int) PK, text(text), rating(int), calendar_id(int) (FK ->Calendar(id))*, week_number(int)
Point is, when I generate my calendar in my app, actually my Users, and Calendar tables get filled properly. Upon a generation they will end up looking like this
id weeks_to_live 1 3 2 2
id user_id 1 1 2 2
That part is fine, but I need my CalendarField table to be autogenerated too, and thats where supposedly generate_series() should come into action, so they will end up looking like this
id text rating calendar_id week_number 1 '' 0 1 0 2 '' 0 1 1 3 '' 0 1 2 4 '' 0 2 0 5 '' 0 2 1
As you can observe:
id: is autoincremental
text: always defaults to "",
rating: always defaults to 0,
calendar_id(fk) : is the relation with each calendar, who has an amount of calendarFields row equaling to the number up to weeks_to_live in users
week_number: this number, should begin at 0, and keep incrementing while its < than weeks_to_live in the users table
Now my question is, how do I generate that last insert so my CalendarField table ends up filling with that info?. I suppose that with generate series, I wont need to create an enormous insert with == weeks_to_live values in case it gets huge. But I don't understand how to do it.
Or should I do a giant Insert then?