投稿サボってるわけじゃない

ちょっと真面目に仕事しなきゃならない状況になったため、
投稿頻度が爆下がりになってます。どうも　え～すけです。

仕事はいつも真面目にやってますが、
真面目過ぎてスケジュールとか前倒しで終わってたりするので、
ちょっと息抜き・暇つぶしに投稿してます。

前提

ポスグレ
public以外の特定のスキーマ間で同じ構造のテーブルを持ってる

やりたいこと

publicスキーマに各スキーマの対象テーブルをunion allしたviewを作って、データを横グシで眺めたいよ～

普通に書く

create or replace view  public.v_test AS 
   select * from "スキーマ0001".t_test
union all 
   select * from "スキーマ0002".t_test
union all 
   select * from "スキーマ0003".t_test

問題点

スキーマが新しく増えたりとか、テーブルが新しく増えたりした時に、
毎回SQL組み直して実行し直すとか面倒くささの極み。

それプロシージャでやればよくね？

とりあえずテーブル名の定義はベタ書き（管理テーブルとか作ってもいいかも）
スキーマ情報はpg_catalog.pg_namespace に入ってる

プロシージャジャーン

create or replace procedure create_view() as $$ declare
 sc_name text;
 tb_name text;
 tb_arr text[] := array['t_table1', 't_table2', 't_table3'];
 sql_template text := '';
begin
  foreach tb_name in array tb_arr loop
    for sc_name in 
      select
          nspname
      from
          pg_catalog.pg_namespace 
      where
          nspname like 'スキーマ%'
    loop 
      if length(sql_template) > 0 then
        sql_template = concat(sql_template, ' union all ');
      end if;
      sql_template = concat(sql_template, format('select * from "%s"."%s"', sc_name, tb_name));
    end loop; 
    sql_template = concat(format('create or replace view  public."%s" AS ', tb_name), sql_template);
    raise info '%', sql_template; 
    execute sql_template;
    sql_template = '';
  end loop;
end; 

$$ language plpgsql;

スキーマ名の命名がきちんとしてれば、like検索部分を変えるだけでええんやで。
executeまでしてるからこのプロシージャ呼ぶだけでViewの再作成が出来るんやで。
テーブルが増えたとしても、このプロシージャの頭の方で定義してる配列に入れれば終わりやで。
でもテーブル名とView名がおなじになるのが気に食わないのであれば、管理テーブルとか作ったほうがええんやで。