vertica 获取字段备注、名称、类型的sql

1. 创建一张表并添加备注字段

   CREATE TABLE public.base_role
   (
       role_id int NOT NULL,
       role_name varchar(100),
       role_code varchar(1024)
   );
   
   
   CREATE PROJECTION public.base_role_super /*+baserole_name(user_1),createtype(L)*/ 
   (
    role_id,
    role_name,
    role_code
   )
   AS
    SELECT base_role.role_id,
           base_role.role_name,
           base_role.role_code
   		
    FROM public.base_role
    ORDER BY base_role.role_id,
             base_role.role_name,
             base_role.role_code
   SEGMENTED BY hash(base_role.role_id, base_role.role_name, base_role.role_code) ALL NODES OFFSET 0;
   
   
   COMMENT ON COLUMN public.base_role_super."role_id" IS '角色id';
   COMMENT ON COLUMN public.base_role_super."role_code" IS '角色编码';
   COMMENT ON COLUMN public.base_role_super."role_name" IS '角色名称';
   

2. 获取每个字段的名称、类型、注释

   SELECT 
   	col.column_name,
   	col.data_type,
   	com.comment 
   FROM COLUMNS as col 
   	left join comments com on com.object_schema 	= col.table_schema 
   	and com.object_type = 'COLUMN' 
   	and com.object_name = CONCAT(CONCAT(col.TABLE_NAME,'_super.'),col.column_name) 
   WHERE 
   	col.TABLE_NAME = 'user_1' 
   	AND table_schema = 'public'