PG匿名块、函数添加表注释

为了添加注释不报错，采用匿名块或者函数来添加。

一、使用匿名块

DO $$ << comment_test >>
DECLARE
    v_schemaname pg_namespace.nspname % type :='test';   -- 模式名
	  v_tablename pg_class.relname % type :='author';      -- 表名
	  v_comment pg_description.description % type :='xxxxxxxxxxxxxxxxxxxxxxxx';    -- 表的注释内容
	flag boolean;
	v_sql text :=concat('comment on table ',v_schemaname,'.',v_tablename,' is ','''',v_comment,'''');
begin	
select case when t3.description is null or t3.description = '' then TRUE else FALSE end
       into flag
from pg_class t1 
join pg_namespace t2 on t1.relnamespace=t2.oid
left join pg_description t3 on t1.oid=t3.objoid
where 1=1
      and t1.relname = v_tablename 
	  and t2.nspname = v_schemaname;
raise notice 'flag=%',flag;  
if flag
then
   raise notice 'v_sql=%',v_sql;  
   execute v_sql;
else
   raise notice '表存在注释！！！';
end if;
END comment_test$$;

二、使用函数

create or replace function comment_test(
v_schemaname varchar(50),
v_tablename varchar(50),
v_comment varchar(200)
)
returns varchar(100)
as $$
DECLARE
  flag boolean;
  v_sql text :=concat('comment on table ',v_schemaname,'.',v_tablename,' is ','''',v_comment,'''');
BEGIN
select case when t3.description is null or t3.description = '' then TRUE else FALSE end
       into flag
from pg_class t1 
join pg_namespace t2 on t1.relnamespace=t2.oid
left join pg_description t3 on t1.oid=t3.objoid
where 1=1
      and t1.relname = v_tablename 
	  and t2.nspname = v_schemaname;
if flag
then
   -- raise notice 'v_sql=%',v_sql;  
   execute v_sql;
   return '注释已添加！！！';
else
   return '表存在注释！！！';
end if;
end;
$$ language plpgsql;