postgresql 根据 表 创建 视图

发表于 | in | | 访客

创建postgresql 视图

根据表tag创建视图tag_view

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
do
$$
declare
  l_keys text;
begin
   drop view if exists tag_view cascade;
   select string_agg(distinct format('tags ->> %L as %I',jkey, jkey), ', ')
     into l_keys
   from tag, jsonb_object_keys(tags) as t(jkey);
   execute 'create view tag_view as select resource_ins_id, resource_id, resource_type, '||l_keys||' from tag';
end;
$$
;