Generate HTML from PostgreSQL function

Question

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;

Answer 1

You could search the calalogs for the columns in the table first, then use them to generate the query and to set the table header.

colsql := $QUERY$SELECT attname
                 FROM pg_attribute AS a JOIN pg_class AS c ON a.attrelid = c.oid
                 WHERE c.relname = '$QUERY$
          || tablename || $QUERY$' AND attnum > 0;$QUERY$;

header := '';
searchsql := $QUERY$SELECT ''$QUERY$;
FOR col IN EXECUTE colsql LOOP
    header := header || '<th>' || col || '</th>';
    searchsql := searchsql || $QUERY$||'<td>'||$QUERY$ || col;
END LOOP;

searchsql := searchsql || ' FROM ' || tablename;

-- rest of your function here

Obviously this gets messy and brittle fast...

Answer 2

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.

Answer 3

Here's a modified version that works with multiple columns using a text[] array for the column names. It also prints new lines and tabs to format the output.

CREATE OR REPLACE FUNCTION genhtml(text, text, text, text[])
  RETURNS text AS $BODY$ 

DECLARE 
  schemaname ALIAS FOR $1;
  tablename ALIAS FOR $2;
  tabletype ALIAS FOR $3;
  columnnames ALIAS FOR $4;
  result TEXT := ''; 
  searchsql TEXT := ''; 
  var_match TEXT := ''; 
  col RECORD;
  header TEXT;

BEGIN

  header := E'\t' || '<tr>' || E'\n';
  searchsql := $QUERY$SELECT ''$QUERY$;
  FOR col IN SELECT attname 
    FROM pg_attribute AS a 
    JOIN pg_class AS c ON a.attrelid = c.oid 
    WHERE c.relname = tablename
        AND n.nspname = schemaname
        AND c.relkind = tabletype
        AND attnum > 0 
        AND attname = ANY(columnnames)
  LOOP
    header := header || E'\t\t' || '<th>' || col || '</th>' || E'\n';
    searchsql := searchsql || $QUERY$ || E'\n\t\t' || '<td>' || $QUERY$ || col ||     $QUERY$ || '</td>' $QUERY$;
  END LOOP;
  header := header || E'\t' || '</tr>' || E'\n';

  searchsql := searchsql || ' FROM ' || schemaname || '.' || tablename;

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

  RETURN result; 

END; 
$BODY$ 
  LANGUAGE 'plpgsql' VOLATILE;

Call the function with something like:

SELECT genhtml('public', 'tablenamehere', 'r', ARRAY['col1', 'col2', 'col3']);

The 'r' is for normal tables. If you are using a VIEW instead, change it to 'v'.

Answer 4

Piggy backing off of the other answers above, i modified this because i found several issues in the answers listed above which include:

1. The Join being incorrect; invalid alias (i.e. n)
2. The function is unable to handle null values
3. Function should generate an entire HTML document with defined html document type

NOTE: While having to generate and HTML document in postgres is not ideal, there are instances where this may by required to be done. Which I found myself to be in. In addition to the issues listed above, I also included bootstrap for handling formatting and css on the table. I hope this is helpful to others.

CREATE OR REPLACE FUNCTION genhtml (text, text, text, text[])
   RETURNS text AS $body$
DECLARE
   schemaname ALIAS FOR $1; 
   tablename ALIAS FOR $2; 
   tabletype ALIAS FOR $3; 
   columnnames ALIAS FOR $4; 
   result TEXT := ''; 
   searchsql TEXT := '';
   varmatch TEXT := '';
   col RECORD; 
   html_doctype TEXT := '<!DOCTYPE html>' || E'\n'; 
   html_meta TEXT := '<meta charset="uft-8">' || E'\n\t' || '<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit="no">' || E'\n'; 
   html_link TEXT := '<link rel="stylesheet" href="boostrapCSSLinkHere.css">' || E'\n';
   html_bscript TEXT := '<script src="bootstraptScriptHere.js"> </script>' || E'\n';
   html_jscript TEXT := '<script src="jQueryScriptHere.js"> </script>' || E'\n'; 
   html_head TEXT := '<html>' || E'\n' || '<head>' || E'\n\t' || html_meta || E'\t' || hmtml_link || E'\t' || html_jscript ||  E'\t' || html_bscript || '</head>' || E'\n'; 
   html_body TEXT := '<body>';
   header TEXT; 
BEGIN
   header := E'\t'|| '<tr>' || E'\n'; 
   searchsql := $QUERY$SELECT ''$QUERY$; 

   FOR col IN select attname
      FROM pg_attribute AS a
      JOIN pg_class AS c ON a.attrelid = c.oid
      JOIN pg_namespace AS n ON n.oid = c.relnamespace
      WHERE c.relname = tablename
      AND n.nspname = schemaname
      AND c.relkind = tabletype
      AND attnum > 0 
      AND attname = ANY(columnnames)
   LOOP
      header := header || E'\t\t' || '<th>' || col || '</th>' || E'\n';
      searchsql := searchsql || $QUERY$ || E'\n\n\t' || '<td>' || $QUERY$ || 'coalesce(' || col || ', ''N/A'')' || $QUERY$ || '<td>' $QUERY$;
   END LOOP; 

   header := header || E'\t' || '<tr>' || E'\n'; 
   searchsql := searchsql || ' FROM ' || schemaname || '.' || tablename;
   result := html_doctype || html_head || html_body || E'\n\t' || '<table class="table table-striped table-hover">' || E'\n'; 
   result := result || header; 

   FOR varmatch IN EXECUTE (searchsql) LOOP
      IF result > '' THEN
         result := result || E'\t' || '<tr>' || varmatch || E'\n\t' || </tr> || E'\n';
      END IF;
   END LOOP;
   result := result || E'\t' || </table> || E'\n' || '</body> || E'\n' || '</html>'; 

   RETURN result; 

END; 
$body$
   LANGUAGE 'plpgsql' VOLATILE;