postgresql查询问题请教 about blog HOT 1 CLOSED

create table posts (
id int primary key,
title text,
tags text,
body text,
parentid int
);

create table comments (
id int primary key,
postid int,
ans text
);

insert into posts values
(1,'标题1','标签1','问题1',null),
(2,'标题2','标签2','问题2',null),
(3,null,null,'问题1回答1',1),
(4,null,null,'问题1回答2',1),
(5,'标题3','标签3','问题3',null);

insert into comments values
(1,1,'问题1的留言1'),
(2,1,'问题1的留言2'),
(3,3,'问题1回答1的留言1'),
(4,4,'问题1回答2的留言1'),
(5,4,'问题1回答2的留言2');

create or replace function get_liuyan(int) returns text[] as $$
select array_agg(ans) from comments where postid=$1;
$$ language sql;

with recursive tmp as (
select id, title, tags, body, get_liuyan(ID) as liuyan, '' as huida, '{}'::text[] as huida_liuyan, 1 as level from
posts where parentid is null
union all
select posts.id, coalesce(posts.title,tmp.title), coalesce(posts.tags,tmp.tags), tmp.body, get_liuyan(posts.ID), posts.body, get_liuyan(posts.id) , tmp.level+1 from
tmp join posts on (posts.parentid=tmp.id)
)
select title,tags,body,max(liuyan) filter (where level=1),jsonb_object_agg(huida,huida_liuyan) filter (where level>1) from tmp
group by title,tags,body;

title | tags | body | max | jsonb_object_agg
-------+-------+-------+-----------------------------+-------------------------------------------------------------------------------------------------
标题3 | 标签3 | 问题3 | |
标题1 | 标签1 | 问题1 | {问题1的留言1,问题1的留言2} | {"问题1回答1": ["问题1回答1的留言1"], "问题1回答2": ["问题1回答2的留言1", "问题1回答2的留言2"]}
标题2 | 标签2 | 问题2 | |
(3 rows)

from blog.