0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

【SQL】スキーマを飛び越えて横グシでみたいよ~

Posted at

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

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

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

前提

  • ポスグレ
  • 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名がおなじになるのが気に食わないのであれば、管理テーブルとか作ったほうがええんやで。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?