Generate HTML from PostgreSQL function

Generate HTML from PostgreSQL function

postgresql html output
how to connect html form to postgresql database
how to fetch data from postgresql database in php and display in html table
postgresql select
postgresql function return table all columns
postgres function return string
postgresql example query
postgres function return types

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;


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...

Generate HTML from PostgreSQL function, can anybody assist me to with this particular. I've got a task to rite a function, which may generate html tables from given table title in postgreSQL(plpgsql  You can generate XML using Postgres' XML features and transform that into HTML using XSLT. There is an example for that in the manual There is an example for that in the manual But this is definitely not a recommended approach.


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.

Documentation: 9.4: CREATE FUNCTION, However, functions of different argument types can share a name (this is called overloading). To replace the current definition of an existing function, use CREATE  A CREATE FUNCTION command is defined in the SQL standard. The PostgreSQL version is similar but not fully compatible. The attributes are not portable, neither are the different available languages. For compatibility with some other database systems, argmode can be written either before or after argname. But only the first way is standard-compliant.


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'.

Documentation: 9.1: Set Returning Functions, Function, Argument Type, Return Type, Description. generate_series(start, stop), int or bigint, setof int or setof bigint (same as argument type), Generate a series  In PostgreSQL, both stored procedures and user-defined functions are created with CREATE FUNCTION statement. There are differences between the notion of stored procedures and functions in database systems: SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list.


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;

Documentation: 9.2: Query Language (SQL) Functions, The syntax of the CREATE FUNCTION command requires the function body to be written as a string constant. It is usually most convenient to use dollar quoting  CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition. To be able to define a function, the user must have the USAGE privilege on the language. If a schema name is included, then the function is created in the specified schema.


Documentation: 9.1: CREATE TRIGGER, CREATE TRIGGER creates a new trigger. The trigger will be associated with the specified table or view and will execute the specified function function_name  To create a new user-defined function in PostgreSQL, you use the CREATE FUNCTION statement as follows: CREATE FUNCTION function_name(p1 type , p2 type ) RETURNS type AS BEGIN -- logic END ; LANGUAGE language_name;


Documentation: 7.3: Functional Indexes, CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));. The function in the index definition can take more than one argument, but they must be table  PostgreSQL functions, also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code.


Documentation: 9.1: Indexes on Expressions, SELECT * FROM test1 WHERE lower(col1) = 'value';. This query can use an index if one has been defined on the result of the lower(col1) function: CREATE  Generate A UUID In PostgreSQL. Postgres has support for universally unique identifiers (UUIDs) as a column data type via uuid. If you have a UUID column, you may need to generate a UUID. This requires the uuid-ossp module. This module provides a number of functions for generating UUIDs including the uuid_generate_v4() function which bases