Greenplum设置资源备注信息

参考文档 http://www.glphp.com/statics/api/postgresql/files/sql-comment.html

GP的comment信息都是存储在pg_description里面的。可以为每个数据库对象保存一个备注信息。 COMMENT命令可以增删改某个对象的备注。 增加修改都使用这个命令，

COMMENT ON ROLE myname IS 'this is comment';
COMMENT ON TABLESPACE myname IS 'this is comment'; COMMENT ON DATABASE myname IS 'this is comment'; COMMENT ON SCHEMA myname IS 'this is comment'; COMMENT ON TABLE myname IS 'this is comment'; COMMENT ON COLUMN myname IS 'this is comment'; COMMENT ON VIEW myname IS 'this is comment';

删除可以通过把 IS 后面的值写为 null。

如：
comment on database myname is null;

Name	Return Type	Description
col_description(table_oid, column_number)	text	get comment for a table column（表的列）
obj_description(object_oid, catalog_name)	text	get comment for a database object（数据库中的对象）
shobj_description(object_oid, catalog_name)	text	get comment for a shared database object(集群级别的对象)

查询数据库备注

testdb=# select datname,  shobj_description(d.oid,'pg_database')
from pg_database d;
  datname  |     shobj_description     
-----------+---------------------------
 template1 | default template database
 template0 | 
 postgres  | testdb3 | testdb2 | tesila | testdb | testdb13 | testdb14 | this is testdb143 (9 rows)

查询模式备注

select nspname, obj_description(d.oid,'pg_namespace')
from pg_namespace d;

      nspname       |                       obj_description                       
--------------------+-------------------------------------------------------------
 pg_catalog         | system catalog schema
 pg_toast           | reserved schema for TOAST tables
 pg_bitmapindex     | Reserved schema for internal relations of bitmap indexes
 public             | standard public schema pg_aoseg | Reserved schema for Append Only segment list and eof tables guagua | guagua wangwang pg_toast_temp_1 | information_schema | pg_toast_temp_44 | gp_toolkit | tu1schema | (11 rows)

查询表的备注信息

创建了两个表。guagua.table1,guagua.table2 ，给table2设置了备注信息。

查找模式guagua下的所有的表的备注

select relname,obj_description(c.oid,'pg_class') from pg_class c where relnamespace=(select oid from pg_namespace where nspname='guagua'); relname | obj_description ---------+----------------- table1 | table2 | this is table2 (2 rows)

查询字段的备注信息

select b.attname as columnname,coalesce(a.description,'') as comment from pg_description a,pg_attribute b where a.objoid='table3'::regclass and a.objoid=b.attrelid and a.objsubid=b.attnum;

查询视图的备注信息

转载于:https://www.cnblogs.com/fangjx/p/6549534.html