creating several records from one, based on a numerical value stored in a field of the source record

Question

In a PostgreSQL db, I have a table with a numerical field, among others

In such field, some natural numbers are stored and they represent the number of occurrences of something

like this, roughly

record1....3
record2....5
record3....1
record4....2

I'd like to create a view that has 3 records created from record1, 5 records created from record2, one record created from reocord3 and 2 records created form record4

is this possible ?

How would I achieve that ?

Answer 1

I'm not sure I understood your problem correctly but let's assume you've got one field with occurrence count :

create table test_object as select occurrence from  generate_series(1,10) as occurences;

Then you can generate as many records as in occurs column this way :

select 
  test_object.*,
  item 
from test_object 
join generate_series(1,test_object.occurs) as item on true;

Here generate_series will drive your rows creation with the upper bound tied to occurs field.