Générer du HTML à partir de la fonction PostgreSQL

Question

Quelqu'un peut-il m'aider ? J'ai pour tâche d'écrire une fonction qui génère des tables HTML à partir d'un nom de table donné dans PostgreSQL (langage plpgsql). J'ai écrit ceci, mais c'est loin de ce dont j'ai besoin. Elle génère un tableau pour les colonnes que je donne (pour l'instant juste une), mais j'ai besoin de donner un nom au tableau.

CREATE OR REPLACE FUNCTION genhtml2(tablename text, columnname text)
R $BODY [ ] [ ] [ ] var_match text := '' ; BEGIN [ ] 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

Je suis persuadé que vous ne devriez pas faire cela, car c'est un cauchemar potentiel pour la maintenance. La meilleure chose à faire est de renvoyer les résultats de la ligne vers une application ou une autre couche et de travailler à partir de là vers le html.

Answer 2

Voici une version modifiée qui fonctionne avec plusieurs colonnes en utilisant un tableau text[] pour les noms de colonnes. Elle imprime également de nouvelles lignes et des tabulations pour formater la sortie.

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;

Appelez la fonction avec quelque chose comme :

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

Le 'r' est pour les tables normales. Si vous utilisez une VIEW à la place, changez-le en 'v'.

Answer 3

Vous pourriez d'abord rechercher dans les calalogues les colonnes de la table, puis les utiliser pour générer la requête et définir l'en-tête de la table.

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

Évidemment, cela devient vite désordonné et cassant...

Answer 4

J'ai modifié cette réponse parce que j'ai trouvé plusieurs problèmes dans les réponses ci-dessus :

1. Le joint étant incorrect ; alias invalide (i.e. n)
2. La fonction est incapable de gérer les valeurs nulles
3. La fonction doit générer un document HTML complet avec le type de document html défini.

NOTE : Bien que le fait de devoir générer un document HTML dans postgres ne soit pas idéal, il y a des cas où cela peut être nécessaire. C'est ce qui s'est passé pour moi. En plus des problèmes énumérés ci-dessus, j'ai également inclus bootstrap pour gérer le formatage et les css sur la table. J'espère que cela sera utile à d'autres.

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;