PostgreSQL pretty-print views for XML values

[Steven Grimm]

Other folks might find this useful. We're using PostgreSQL and I added some views to our database to make it nicer to examine serialized objects in the database, which we sometimes need to do when debugging.

-- Some views to make manual examination of objects in the Axon tables a bit easier.

--

-- These add a new column to each of the Axon tables that have XML blobs. The new column

-- uses Postgres's "xml" type, so in addition to looking at the pretty-printed rendition of

-- the XML, it's also possible to use Postgres's XML functions to search for specific tags, etc.

CREATE EXTENSION IF NOT EXISTS xml2;

CREATE OR REPLACE FUNCTION pretty_print_xml(bytea) RETURNS xml AS $$

  SELECT xslt_process(encode($1, 'escape'),

                      '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

                       <xsl:strip-space elements="*" />

                       <xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />

                       <xsl:template match="node() | @*">

                       <xsl:copy>

                       <xsl:apply-templates select="node() | @*" />

                       </xsl:copy>

                       </xsl:template>

                       </xsl:stylesheet>')::xml

$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE VIEW sagaentry_pretty AS

  SELECT *, pretty_print_xml(serializedsaga) AS saga_xml

  FROM sagaentry;

CREATE OR REPLACE VIEW domainevententry_pretty AS

  SELECT *, pretty_print_xml(payload) AS payload_xml

  FROM domainevententry;

CREATE OR REPLACE VIEW snapshotevententry_pretty AS

  SELECT *, pretty_print_xml(payload) AS payload_xml

  FROM snapshotevententry;