Mithrand1r Mithrand1r - 8 days ago 6
SQL Question

Is it possible to " Select N times"?

Unfortunately, the data model for my database must change so I am looking for the most painless way to migrate my data.

How it is at this moment:

create table cargo{
id serial primary key,
person_id int,
weight_id float,
reps_id int
);
//skipping constraints declaration


However it happened that person is able to lift different values of cargo each turn

What I am going to do is modify
cargo
table and add
turn
table like this:

create table cargo{
id serial primary key,
person_id int,
);
//skipping constraints declaration

create table turn{
id serial primary key,
cargo_id int,
weight float
);


normally I could migrate existing data like this:

insert into turn (cargo_id, weight) select id, weight from cargo;


but this way I am losing all of
reps
where
reps
> 1

Is it possible to create insert into select, where select would be called as many times as
reps
count without making script?

Answer

Try:

-- insert into turn (cargo_id, weight) 
SELECT c.id, c.weight_id
FROM cargo c,
LATERAL (
  SELECT * FROM generate_series(1, greatest( c.reps_id, 1 ) )
) xx;

Demo: http://sqlfiddle.com/#!15/4923e/1