使用PostgreSQL为表或视图创建备注的操作

drop table if exists test;

create table test(

   objectid serial not null ,

   num integer not null ,

  constraint pk_test_objectid primary key (objectid),

  constraint ck_test_num check (num < 123 ),

);

comment on table test is '我是表' ;

comment on column test.objectid is '我是唯一主键' ;

comment on column test.num is '数量字段' ;

comment on constraint pk_test_objectid on test is '我是约束,唯一主键' ;

comment on constraint ck_test_num on test is '我是约束,num字段必须小于123' ;

\dS+ test;

drop view if exists vtest;

create or replace view vtest

  as select 1 as col1, 'a' as col2, now() as col3;

comment on view vtest is '视图备注' ;

comment on column vtest.col1 is '第一列备注,integer类型' ;

comment on column vtest.col2 is '第二列备注,字符类型' ;

comment on column vtest.col3 is '第三列备注,日期时间类型' ;

COMMENT ON

{

  ACCESS METHOD object_name |

  AGGREGATE aggregate_name ( aggregate_signature ) |

  CAST (source_type AS target_type) |

  COLLATION object_name |

  COLUMN relation_name.column_name |

  CONSTRAINT constraint_name ON table_name |

  CONSTRAINT constraint_name ON DOMAIN domain_name |

  CONVERSION object_name |

  DATABASE object_name |

  DOMAIN object_name |

  EXTENSION object_name |

  EVENT TRIGGER object_name |

  FOREIGN DATA WRAPPER object_name |

  FOREIGN TABLE object_name |

  FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |

  INDEX object_name |

  LARGE OBJECT large_object_oid |

  MATERIALIZED VIEW object_name |

  OPERATOR operator_name (left_type, right_type) |

  OPERATOR CLASS object_name USING index_method |

  OPERATOR FAMILY object_name USING index_method |

  POLICY policy_name ON table_name |

  [ PROCEDURAL ] LANGUAGE object_name |

  PUBLICATION object_name |

  ROLE object_name |

  RULE rule_name ON table_name |

  SCHEMA object_name |

  SEQUENCE object_name |

  SERVER object_name |

  STATISTICS object_name |

  SUBSCRIPTION object_name |

  TABLE object_name |

  TABLESPACE object_name |

  TEXT SEARCH CONFIGURATION object_name |

  TEXT SEARCH DICTIONARY object_name |

  TEXT SEARCH PARSER object_name |

  TEXT SEARCH TEMPLATE object_name |

  TRANSFORM FOR type_name LANGUAGE lang_name |

  TRIGGER trigger_name ON table_name |

  TYPE object_name |

  VIEW object_name

} IS 'text'

where aggregate_signature is :

* |

[ argmode ] [ argname ] argtype [ , ... ] |

[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]

select c.relname 表名, cast (obj_description(relfilenode, 'pg_class' ) as varchar ) 名称,a.attname 字段,d.description 字段备注,concat_ws( '' ,t.typname, SUBSTRING (format_type(a.atttypid,a.atttypmod) from '.∗' )) as 列类型 from pg_class c,pg_attribute a,pg_type t,pg_description d

where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum

and c.relname in ( select tablename from pg_tables where schemaname= 'public' and position( '_2' in tablename)=0) order by c.relname,a.attnum

select tablename from pg_tables where schemaname= 'public' and position( '_2' in tablename)=0;

select * from pg_tables;

select relname as tabname, cast (obj_description(relfilenode, 'pg_class' ) as varchar ) as comment from pg_class c

where relname in ( select tablename from pg_tables where schemaname= 'public' and position( '_2' in tablename)=0);

select * from pg_class;

select relname as tabname,

cast (obj_description(relfilenode, 'pg_class' ) as varchar ) as comment from pg_class c

where relname = '表名' ;

select a.attnum,a.attname,concat_ws( '' ,t.typname, SUBSTRING (format_type(a.atttypid,a.atttypmod) from '.∗' )) as type,d.description from pg_class c,pg_attribute a,pg_type t,pg_description d

where c.relname= '表名' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum;