投稿サボってるわけじゃない
ちょっと真面目に仕事しなきゃならない状況になったため、
投稿頻度が爆下がりになってます。どうも え~すけです。
仕事はいつも真面目にやってますが、
真面目過ぎてスケジュールとか前倒しで終わってたりするので、
ちょっと息抜き・暇つぶしに投稿してます。
前提
- ポスグレ
- 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名がおなじになるのが気に食わないのであれば、管理テーブルとか作ったほうがええんやで。