Postgresql – Generate HTML from PostgreSQL function

databaseplpgsqlpostgresql

Can anyone help me with this? I have a task to write a function, which would generate HTML tables from given table name in PostgreSQL(plpgsql language).
I have written this, but it's far from what I need. It would generate a table for columns I would give (at the moment just one), but I need to just give the table a name.

CREATE OR REPLACE FUNCTION genhtml2(tablename text, columnname text)
RETURNS text AS
$BODY$
DECLARE
result text := '';
searchsql text := '';
var_match text := ''; BEGIN
searchsql := 'SELECT ' || columnname || '
FROM ' || tablename || '';

result := '<table>';
FOR var_match IN EXECUTE(searchsql) LOOP
    IF result > '' THEN
        result := result || '<tr>' || var_match || '</tr>';
    END IF;
END LOOP;
result :=  result || '</table>';

RETURN result; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE;

Best Answer

I am quite confident that you should not do this because it is a potential maintenance nightmare. The best thing to do is return the row results to any application or another layer and work from there towards html.