Multiply rows by column value

Question

I need to transform data from my source-table into a destination-table.

The source table has 'content'-columns and 'multiplier'-columns. Based on the multiplier(X), the content of the source should be written X-times into the destination.

eg: if multilpier is '0', nothing will be written, if '1', content is written one time into destination table. Two times, if the multiplier is '2', and so on. I've never done functions in Postgres before.

My approach: a nested for-while-loop: for each row, while 'counter' is smaller than 'multiplier', insert content from source-table into destination table.

Example data:

--create source table
create table public.source_tbl(
id serial, 
multiplier int, 
content varchar,
primary key (id)
);
--create destination table
create table public.dest_tbl(
id serial, 
multiplier int, 
content varchar,
primary key (id)
);
--some content
insert into public.source_tbl(multiplier,content)
values(1,'foo'),(1,'bar'),(1,'random'),(2, 'content'),(3,'My'),(4,'creativity'),(3,'is'),(2,'very'),(6,'limited'),(7,'!!!'), (0, 'nothing should be written');

And thats the code I came up with:

do
$$
declare f record;
begin 
    for f in    select id, multiplier, content
                from public.source_tbl;
    loop
        do
        $$
        declare counter integer counter:=0;
        begin
            while counter < f.multiplier
            loop
                insert into public.dest_tbl(multiplier,content)
                select f.multiplier, f.content;
                counter := counter +1;
            end loop;
        end;
    end loop;
end;
$$

Needless to say that it does not work, I get an syntax-error with the second 'declare'. So what am I doing wrong?

Answer 1

You cannot declare a variable in the middle of a plpgsql code. It is also not necessary to create another anonymous code block for the second loop. Try this:

do
$$ 
declare 
 f record;
 counter integer :=0;
begin 
    for f in select id, multiplier, content from public.source_tbl  loop
      while counter < f.multiplier loop
         insert into public.dest_tbl(multiplier,content)
         select f.multiplier, f.content;
         counter := counter +1;
       end loop;   
       counter := 0;
    end loop;
end;
$$ language plpgsql;

Demo: db<>fiddle