CODEWITHSUNDEEP
sqlpostgresqlnested-loopsplpgsql
JeanLucBizarre
JeanLucBizarre

Reputation: 43

Multiply rows by column value

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?

Upvotes: 1

Views: 386

Answers (2)

Jim Jones
Jim Jones

Reputation: 19643

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

Upvotes: 1

user330315
user330315

Reputation:

Jim answered immediate question about the syntax error. However, you don't need a function or PL/pgSQL for this. The same can be achieved by using the built-in function generate_series()

insert into dest_tbl (multiplier, content)
select st.multiplier, st.content
from source_tbl st
  cross join generate_series(1, st.multiplier);

Upvotes: 2

Related Questions