时间戳

首页 > 解决方案 > Postgres返回按列分组的HTML表格标签以获取结果集

html - Postgres返回按列分组的HTML表格标签以获取结果集

问题描述

我正在尝试构建一个生成行和单元格 HTML 标记的函数，这些标记将在另一个应用程序中用于构建基本的 HTML 表。行和单元格需要按区域 ID 分组/连接。这是我目前正在努力的部分。

在此处输入图像描述

在此处输入图像描述

我尝试将以下函数放在一起，但不确定去哪里确保输出按 region_id 正确分组。

这不是我通常会做的 SQL，但我正在使用一些有限的技术。


create table reporting
(
    id integer,
    region_id integer,
    category text,
    item text,
    status text
);


insert into reporting values
   (1, 1, 'audio', 'speakers', 'delivered'),
   (2, 1, 'display', 'monitors', 'pending'),
   (3, 2, 'cables', 'hdmi', 'pre-order'),
   (4, 3, 'storage', 'sdd', 'cancelled'),
   (5, 3, 'software', 'business', 'delivered'),
   (6, 3, 'other', 'support', 'delivered');


create function html_out (query text)
returns  TABLE(region_id text, result text) language plpgsql as $$
declare
    rec record;
    header boolean := true;
begin
    for rec in
        execute format($q$
            select row_to_json(q) json_row
            from (%s) q
            $q$, query)
    loop
        return query select region_id,
            format ('<tr><td>%s</td></tr>', string_agg(value, '</td><td>'))
        from json_each_text(rec.json_row);
    end loop;
end $$;

select html_out('select region_id, category, item, status from reporting');

标签： htmlsqljsonpostgresqlaggregate-functions

解决方案

您可以使用字符串聚合。我认为你想要的逻辑是：

select
    region_id,
    '<tr><td>' 
    || string_agg(concat_ws('</td><td>', category, item, status), '</td></tr><tr><td>')
    || '</td></tr>' html
from reporting
group by region_id
order by 1

DB Fiddle 上的演示

region_id | html                                                                                                                                                                     
--------: | :------------------------------------------------ -------------------------------------------------- -------------------------------------------------- ----------------------
        1 | <tr><td>音频</td><td>扬声器</td><td>交付</td></tr><tr><td>显示器</td><td>显示器</td> <td>待定</td></tr>                                                         
        2 | <tr><td>电缆</td><td>hdmi</td><td>预购</td></tr>                                                                                                                      
        3 | <tr><td>存储</td><td>sdd</td><td>取消</td></tr><tr><td>软件</td><td>业务</td> <td>已交付</td></tr><tr><td>其他</td><td>支持</td><td>已交付</td></tr>

推荐阅读