Y.S Y.S - 3 months ago 16
SQL Question

Dynamic query results to HTML table

I'm new to Postgres,

I've seen many SQL Server solutions (stored procedures, functions) which allows exporting query results (no matter what the query is) into HTML table.

I've been trying to do the same in Postgres but did not find an easy way. I need a function with a query as an argument which returns valid HTML code. I'm looking for a pure SQL solution.

Answer

The function generates a simple html table from a query:

create or replace function html_table (query text)
returns setof text language plpgsql as $$
declare
    rec record;
    header boolean := true;
begin
    return next '<table>';
    for rec in 
        execute format($q$
            select row_to_json(q) json_row
            from (%s) q
            $q$, query)
    loop
        if header then
            return query select 
                format ('<tr><th>%s</th></tr>', string_agg(key, '</th><th>'))
            from json_each(rec.json_row);
            header := false;
        end if;
        return query select 
            format ('<tr><td>%s</td></tr>', string_agg(value, '</td><td>'))
        from json_each_text(rec.json_row);
    end loop;
    return next '</table>';
end $$;

Example usage:

create temp table test_data (id int, name text, amount int);
insert into test_data values
    (1, 'Jim', 10),
    (2, 'Ann', 20),
    (3, 'Bob', 30);

select html_table('select * from test_data');

                    html_table                    
--------------------------------------------------
 <table>
 <tr><th>id</th><th>name</th><th>amount</th></tr>
 <tr><td>1</td><td>Jim</td><td>10</td></tr>
 <tr><td>2</td><td>Ann</td><td>20</td></tr>
 <tr><td>3</td><td>Bob</td><td>30</td></tr>
 </table>
(6 rows)    

The function uses json functions row_to_json(), json_each_text() and aggregate function string_agg() which can be combined to present rows as lists of column names and values, like in this example:

select string_agg(key, ' - ') column_names, string_agg(value, ' - ') column_values
from test_data, json_each_text(row_to_json(test_data))
where id = 1;

    column_names    | column_values 
--------------------+---------------
 id - name - amount | 1 - Jim - 10
(1 row)

Using this technique you can freely format the rows resulting from a dynamically executed query. The function can be easily modified by adding html IDs, classes etc.