PostgreSQL处理文档数据

drop table if exists documents;
drop type if exists ctdocument;

create type ctdocument as (
  标题 text,
  作者 text,
  内容 text
);

create table documents(
    objectid bigserial not null,                           --编号
    doc ctdocument not null,                               --文档，注意类型为ctdocument
    constraint pk_documents_objectid primary key(objectid)
);

insert into documents(doc) values(('标题1','作者1','内容1')::ctdocument);
insert into documents(doc) values(('标题2','作者2','内容2')::ctdocument);
insert into documents(doc) values(('标题3','作者3','内容3')::ctdocument);
insert into documents(doc) values(('标题4','作者4','内容4')::ctdocument);
insert into documents(doc) values(('标题5','作者5','内容5')::ctdocument);
--读取类型中的字段
select objectid,(doc)."标题",doc from documents;
--转换为json
select objectid,row_to_json(doc),doc from documents;

alter type ctdocument rename attribute "标题" to title;
--这时读取类型中的字段要改用新的名字
select objectid,(doc).title,doc from documents;
--转换为json5.select objectid,row_to_json(doc),doc from documents;

alter type ctdocument add attribute "发布日期"  timestamptz;
alter type ctdocument add attribute "阅读数"  integer;
--列出类型定义
\dS+ ctdocument;
--重新插入一条数据
insert into documents(doc) values(('标题6','作者6','内容6',now(),1)::ctdocument);
--读取类型中的字段
select objectid,(doc).title,doc from documents;
--转换为json10.select objectid,row_to_json(doc),doc from documents;

alter type ctdocument drop attribute if exists "发布日期";
--列出类型定义
\dS+ ctdocument;
--读取类型中的字段
select objectid,(doc).title,doc from documents;
--转换为json
select objectid,row_to_json(doc),doc from documents;

alter type ctdocument add attribute "发布日期"  timestamptz;
--注意，"发布日期"在2.3中已经被删除，然后重新添加的，此时的自定义类型顺序已经和2.2中不一样了,所以建议不要删除自定义类型的字段
insert into documents(doc) values(('标题7','作者7','内容7',1,now())::ctdocument);
alter type ctdocument alter attribute "发布日期" set data type date;

ERROR:  cannot alter type "ctdocument" because column "documents.doc" uses it

select * from json_populate_record(null::ctdocument,null);
select * from json_populate_record(null::ctdocument,(select row_to_json(doc) from documents where objectid=6));
select (json_populate_record(null::ctdocument,row_to_json(doc)))::ctdocument as doc from documents;

--null
select * from json_populate_recordset
(null::ctdocument,null);
--将表中的数据转换为json数组
select array_to_json(array_agg(row_to_json(doc) order by objectid)) from documents;
--将json数组转换为记录集显示
select * from json_populate_recordset
(null::ctdocument,(select array_to_json(array_agg(row_to_json(doc) order by objectid)) from documents));
--转换为json然后再转换回来
select (t1) from json_populate_recordset
(null::ctdocument,(select array_to_json(array_agg(row_to_json(doc) order by objectid)) from documents)) as t1;