PGHtml: HTML template engine using PostgreSQL

Description
Replacement example
Template example
Important qualities
Full example
Video

Description

Tool is designed to display on the site static information from database, which:

is rarely updates (e.g. dictionaries)
preparation takes a long time
does not require real-time relevance (e.g. TOP products)

PGHtml is a service that creates HTML, JS, JSON and other file types using data obtained from a PostgreSQL database.
The file is created from a template file in which SQL queries, tags and variables are replaced.
Template extension started with pg, i.e. index.html will be created based on the index.pghtml file

Replacements

$$ [SQL query] $$	—	SQL query result
<pghtml-include>	—	file contents
${[variable]}	—	variable value

<!-- single value from database --> 
$$
select user
$$

<!-- multiple values --> 
$$
select tablename from pg_tables
  where schemaname='pg_catalog' limit 5
$$

<!-- inclusion file example_include.html -->
<pghtml-include>include-simple.html</pghtml-include>

→

→

<!-- single value from database --> 
postgres

<!-- multiple values --> 
pg_statistic
pg_type
pg_foreign_server
pg_authid
pg_statistic_ext_data

<!-- inclusion file example_include.html -->
[ file example-header.html ]

Template

<!DOCTYPE html>
<head>
<title>${title}</title>
</head>

<body>
<h2>${caption}</h2>
${text}
</body>

</html>

Source and result pages

<pghtml-var name="title">Example</pghtml-var>
<pghtml-var name="caption">Multiline text</pghtml-var>
<pghtml-var name="text">
Text line 1
<br>
<i>Text line 2</i>
</pghtml-var>
<pghtml-include>/template/template-common.html</pghtml-include>

→

→

→

<!DOCTYPE html>
<head>
<title>Example</title>
</head>

<body>
<h2>Multiline text</h2>

Text line 1
<br>
<i>Text line 2</i>

</body>

</html>

<!-- single value from database --> 
$$
select user
$$

<!-- multiple values --> 
$$
select tablename from pg_tables
  where schemaname='pg_catalog' limit 5
$$

<!-- inclusion file example_include.html -->
<pghtml-include>include-simple.html</pghtml-include>

→

→

<!-- single value from database --> 
postgres

<!-- multiple values --> 
pg_statistic
pg_type
pg_foreign_server
pg_authid
pg_statistic_ext_data

<!-- inclusion file example_include.html -->
[ file example-header.html ]

Check for updates
- if contents of resulting file have not updated, then the file is not overwritten and web server does not redeploy it
Import
- support for importing other files (such as HTML fragment or JSON data)
Change list
- it is possible to create list of changed files
Error handling
- if an error occurs (for example, when executing a SQL query), processing continues, extended information is output to log (stderr) and at end of execution, the tool returns the code of unsuccessful execution to OS. This allows you to configure monitoring of data updates on the site
Selecting processed files
- possible processing of specific files, directories with recursion and setting file prefixes. At same time, built-in variables are created that can be used in referencies and in SQL queries. Usually root directory of site is specified
Variables
- support variables from the command line, built-in variables (directories, paths to files, etc.) and tag attributes when importing file
Unlimited nesting
- support including files inside an included file, redefining variables multiple times and using them inside another variable
Conditions and loops
- programming inside files not supported, PostgreSQL stored procedures are used to implement logic

Call from command line

Converting file example.pghtml to example.html

<!DOCTYPE html>
<html>

<head>

<!-- inclusion of styles for acceleration -->
<style type="text/css">
<pghtml-include>/css/common.css</pghtml-include>
</style>

<!-- inclusion of title with two variables "G_SITE" and "description" -->
<pghtml-include description="Example">/include/title.html</pghtml-include>

</head>
<body>


<!-- single value from database --> 
<p>Database uptime</p>

$$
select now() - pg_postmaster_start_time()
$$

<br><br>


<!-- dataset --> 
<p>Top 10 indexes by size, table</p>

<table>

<tr><th>Schema</th><th>Index</th><th>Size</th></tr>

$$
select '<tr><td>'||schemaname||'</td><td>'||indexname||'</td><td class="td-number">'||size||'</td></tr>' from (
  select schemaname,indexname,pg_total_relation_size((schemaname||'.'||tablename)::regclass) size from pg_indexes order by 3 desc limit 10
) t 
$$

</table>

<br><br>

<!-- dataset from json --> 
<p>Top 10 indexes by scans, json</p>

<div id="top_indexes_scans" style="width: 600px; height: 200px">

<button onclick="loadFile('top_indexes_scans', 'data/top_indexes_scans.json')">
Load file 'data/top_indexes_scans.json'
</button>


</div>


<script>

function loadFile(element_id, filename) {
  var xhr = new XMLHttpRequest();
  xhr.onreadystatechange = function() {
    if (xhr.readyState == 4) {
      if (xhr.status == 200) {
        document.getElementById(element_id).innerHTML = "<pre>"+xhr.responseText+"</pre>";
        document.getElementById(element_id).style.border = "1px solid black";
      } else {
        document.getElementById(element_id).innerHTML =
          "Page must be opened via web server,<br>view file '"+filename+"' manually";
      }
    }
  }
  xhr.open('GET', filename, true);
  xhr.send();
}

</script>

<br><br>

<!-- variables --> 
<p>Variables</p>

<table>

<tr><th>Name</th><th>Value</th></tr>

<tr> <td>${directory_root}</td>  <td>${directory_root}</td>  </tr>
<tr> <td>${directory}</td>       <td>${directory}</td>       </tr>
<tr> <td>${filename}</td>        <td>${filename}</td>        </tr>
<tr> <td>${filepath_source}</td> <td>${filepath_source}</td> </tr>
<tr> <td>${filepath_dest}</td>   <td>${filepath_dest}</td>   </tr>
<tr> <td>${G_SITE}</td>          <td>${G_SITE}</td>          </tr>

</table>

<br><br>


<pghtml-include>include/footer.html</pghtml-include>

</body>
</html>

→

→

→

→

<!DOCTYPE html>
<html>

<head>

<!-- inclusion of styles for acceleration -->
<style type="text/css">
html { font: 12px Verdana, Arial, Helvetica, sans-serif; }

p { font-size: 16px; font-weight: bold; }

table { border-collapse: collapse; }

td, th { padding: 3px; border: 1px solid black; }

th { background: silver; }

.td-number { text-align: right; }
</style>

<!-- inclusion of title with two variables "G_SITE" and "description" -->
<title>Example</title>
                     

</head>
<body>


<!-- single value from database --> 
<p>Database uptime</p>

00:01:03.749699

<br><br>


<!-- dataset --> 
<p>Top 10 indexes by size, table</p>

<table>

<tr><th>Schema</th><th>Index</th><th>Size</th></tr>

<tr><td>pg_catalog</td><td>pg_depend_reference_index</td><td class="td-number">1130496</td></tr>
<tr><td>pg_catalog</td><td>pg_depend_depender_index</td><td class="td-number">1130496</td></tr>
<tr><td>pg_catalog</td><td>pg_proc_proname_args_nsp_index</td><td class="td-number">1015808</td></tr>
<tr><td>pg_catalog</td><td>pg_proc_oid_index</td><td class="td-number">1015808</td></tr>
<tr><td>pg_catalog</td><td>pg_attribute_relid_attnum_index</td><td class="td-number">688128</td></tr>
<tr><td>pg_catalog</td><td>pg_attribute_relid_attnam_index</td><td class="td-number">688128</td></tr>
<tr><td>pg_catalog</td><td>pg_rewrite_oid_index</td><td class="td-number">679936</td></tr>
<tr><td>pg_catalog</td><td>pg_rewrite_rel_rulename_index</td><td class="td-number">679936</td></tr>
<tr><td>pg_catalog</td><td>pg_collation_name_enc_nsp_index</td><td class="td-number">581632</td></tr>
<tr><td>pg_catalog</td><td>pg_collation_oid_index</td><td class="td-number">581632</td></tr>

</table>

<br><br>

<!-- dataset from json --> 
<p>Top 10 indexes by scans, json</p>

<div id="top_indexes_scans" style="width: 600px; height: 200px">

<button onclick="loadFile('top_indexes_scans', 'data/top_indexes_scans.json')">
Load file 'data/top_indexes_scans.json'
</button>


</div>


<script>

function loadFile(element_id, filename) {
  var xhr = new XMLHttpRequest();
  xhr.onreadystatechange = function() {
    if (xhr.readyState == 4) {
      if (xhr.status == 200) {
        document.getElementById(element_id).innerHTML = "<pre>"+xhr.responseText+"</pre>";
        document.getElementById(element_id).style.border = "1px solid black";
      } else {
        document.getElementById(element_id).innerHTML =
          "Page must be opened via web server,<br>view file '"+filename+"' manually";
      }
    }
  }
  xhr.open('GET', filename, true);
  xhr.send();
}

</script>

<br><br>

<!-- variables --> 
<p>Variables</p>

<table>

<tr><th>Name</th><th>Value</th></tr>

<tr> <td>${directory_root}</td>  <td>${directory_root}</td>  </tr>
<tr> <td>${directory}</td>       <td></td>       </tr>
<tr> <td>${filename}</td>        <td>example-full.pghtml</td>        </tr>
<tr> <td>${filepath_source}</td> <td>/tmp/build/example/example-full.pghtml</td> </tr>
<tr> <td>${filepath_dest}</td>   <td>/tmp/build/example/example-full.html</td>   </tr>
<tr> <td>${G_SITE}</td>          <td>${G_SITE}</td>          </tr>

</table>

<br><br>


-----
<br>
<span style="font-size: 75%">
info updated at 2025-12-09 12:13:39.25283-05
</span>


</body>
</html>

Converting file top_indexes_scans.pgjson to top_indexes_scans.json

→

→

[{"schema":"pg_catalog","index":"pg_class_oid_index","scans":103}, 
 {"schema":"pg_catalog","index":"pg_attribute_relid_attnum_index","scans":99}, 
 {"schema":"pg_catalog","index":"pg_index_indexrelid_index","scans":63}, 
 {"schema":"pg_catalog","index":"pg_db_role_setting_databaseid_rol_index","scans":32}, 
 {"schema":"pg_catalog","index":"pg_database_oid_index","scans":19}, 
 {"schema":"pg_catalog","index":"pg_database_datname_index","scans":11}, 
 {"schema":"pg_catalog","index":"pg_opclass_oid_index","scans":7}, 
 {"schema":"pg_catalog","index":"pg_amproc_fam_proc_index","scans":7}, 
 {"schema":"pg_catalog","index":"pg_authid_oid_index","scans":3}, 
 {"schema":"pg_catalog","index":"pg_authid_rolname_index","scans":3}]

Example files are available along with the source code in archive, which can be downloaded from page Download

Review posted on YouTube

PGHtml
HTML template engine using PostgreSQL

Description

Replacements

Replacement example

Template example

Template

Source and result pages

Simple example

Important qualities

Check for updates

Import

Change list

Error handling

Selecting processed files

Variables

Unlimited nesting

Conditions and loops

Full example

Call from command line

Converting file example.pghtml to example.html

Converting file top_indexes_scans.pgjson to top_indexes_scans.json

Video

PGHtml HTML template engine using PostgreSQL

Description

Replacements

Replacement example

Template example

Template

Source and result pages

Simple example

Important qualities

Check for updates

Import

Change list

Error handling

Selecting processed files

Variables

Unlimited nesting

Conditions and loops

Full example

Call from command line

Converting file example.pghtml to example.html

Converting file top_indexes_scans.pgjson to top_indexes_scans.json

Video

PGHtml
HTML template engine using PostgreSQL