PostgreSQL for loop using the list of views?-CodePudding

I want to do for loop, which would insert into from select. Destination table is always the same, but source views are different. And I would like to loop through the list of the views (structure is the same) and do the inserts.

I would do something like this in Python (just an example what I want to achieve, I want/need PostgreSQL solution, not Py)

list = ["view_1", "view_2","view_3"]
for l in list:
    insert into table_1 (col1, col2) select col1, col2 from l

I know there's no list object in Postgres, but array can be used, right?

Any suggestion/examples how to do this?

CodePudding user response：

You will need to use dynamic SQL for this. For a one-time thing, you can use a DO block:

do
$$
declare
  l_views text := array['view_1', 'view_2', 'view_3'];
  l_view  text;
  l_sql   text;
begin
  foreach l_view in l_views 
  loop
    l_sql := format('insert into table_1 (col1, col2) select col1, col2 from %I', l_view);
    execute l_sql;
  end loop;
end;
$$;

If you need that more frequently you can put that into a stored procedure.

Another option is to create one additional view that combines all views:

create view all_views
as
select col1, col2
from view_1
union all
select col1, col2
from view_2
union all
select col1, col2
from view_3;

Then you don't need dynamic SQL or a loop:

insert into table_1 (col1, col2)
select col1, col2
from all_views;

If a new view as the source is needed, you only need to adjust the one that combines all other views.