While building some catalogs queries I always forgot what some column is. At this point I need to access the
documentation or (if I don't have Internet access) build the documentation in my laptop and check it out. Sometimes it is an annoying task. Thinking about this scenario I imagined why we don't have comments on catalogs? I started to research a
easy lazy way to achieve this. I checked the source (
catalogs.sgml) to investigate how I could extract such information. I noticed that each section (that starts with
catalog-pg-) contains a catalog and the description is stored in a table (the first one). I decided to use the same infrastructure PostgreSQL uses to build the docs (
DSSSL or
XSL). Easy task? Not that easy.
DSSSL x XSL. I had never done any formatting in DSSSL; it is greek for me so I headed to XSL. I have already played with XSL some time ago; it wouldn't be that hard to extract those descriptions, would it?
First step was converting the SGML file to XML (XSL only works with XML). I used the
osx (that is part of
openjade). The
catalogs.sgml file doesn't have a header (because it is part of the PostgreSQL documentation that provides its header in a separate file
postgres.sgml); I had to add the following 3 lines as header.
$ cat /path/to/header.sgml
<!doctype chapter PUBLIC "-//OASIS//DTD DocBook V4.2//EN" [
<!entity % standalone-ignore "IGNORE">
<!entity % standalone-include "INCLUDE"> ]>
Next step was run the osx tool to obtain the xml output. The verbose messages generated are just broken links for other documentation parts; it was safe to ignore them.
$ cat /path/to/header.sgml /path/to/catalogs.sgml | osx -x lower > /path/to/catalogs.tmp
At this point, I had the catalogs file in XML format. Looking at the generated file (
catalogs.tmp), I noticed that some special characters (i.e. &, >, <) were not preserved. Fortunately, looking at the
doc/src/sgml/Makefile I found this command:
$ perl -p -e 's/\[(amp|copy|egrave|gt|lt|mdash|nbsp|ouml|pi|quot|uuml) *\]/\&\1;/g;' \
> -e '$_ .= qq{<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN" "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd">\n} if $. == 1;' \
> < /path/to/catalogs.tmp > /path/to/catalogs.xml
It converts some entities like
[foo ] to
&foo; and adds the DOCTYPE header at the second line.
Once I had the file in XML format, I started to investigate how to extract descriptions for COMMENT ON TABLE. I noticed that the first section (
sect1 id="catalogs-overview") has a table with the information I was looking for. The following XSL script is capable of extracting the catalog table descriptions. (The only difficulty in this script was to build the template
escape-string-sql to escape sql strings. This was necessary because the replace function is only available at XSL version 2.0; I was using version 1.0).
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
xmlns="http://www.w3.org/TR/xhtml1/transitional"
exclude-result-prefixes="#default">
<xsl:output method="text" encoding="utf-8"/>
<!-- escape SQL string -->
<xsl:template name="escape-string-sql">
<xsl:param name="mystr"/>
<xsl:choose>
<!-- "'" = "'" -->
<xsl:when test="contains($mystr, "'")">
<xsl:value-of select="substring-before($mystr, "'")"/>
<!-- replacing ' (') with '' -->
<xsl:text>''</xsl:text>
<xsl:call-template name="escape-string-sql">
<xsl:with-param name="mystr" select="substring-after($mystr, "'")"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$mystr"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template match="/chapter[@id='catalogs']">
<xsl:text>BEGIN;
</xsl:text>
<xsl:text>
-- comment on catalog tables</xsl:text>
<xsl:for-each select="sect1">
<xsl:choose>
<!-- comment on catalog tables -->
<xsl:when test="self::sect1[@id='catalogs-overview']">
<xsl:for-each select="table[@id='catalog-table']/tgroup/tbody/row">
COMMENT ON TABLE pg_catalog.<xsl:value-of select="entry[1]"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space(entry[2])"/></xsl:call-template>';</xsl:for-each>
<xsl:text>
</xsl:text>
</xsl:when>
</xsl:choose>
</xsl:for-each>
<xsl:text>
END;
</xsl:text>
</xsl:template>
</xsl:stylesheet>
Let's check the output:
euler@harman:~$ xsltproc /path/to/only-tables.xsl /path/to/catalogs.xml
BEGIN;
-- comment on catalog tables
COMMENT ON TABLE pg_catalog.pg_aggregate IS 'aggregate functions';
COMMENT ON TABLE pg_catalog.pg_am IS 'index access methods';
COMMENT ON TABLE pg_catalog.pg_amop IS 'access method operators';
COMMENT ON TABLE pg_catalog.pg_amproc IS 'access method support procedures';
.
.
.
COMMENT ON TABLE pg_catalog.pg_ts_parser IS 'text search parsers';
COMMENT ON TABLE pg_catalog.pg_ts_template IS 'text search templates';
COMMENT ON TABLE pg_catalog.pg_type IS 'data types';
COMMENT ON TABLE pg_catalog.pg_user_mapping IS 'mappings of users to foreign servers';
END;
The next step was extracting descriptions from catalog tables (COMMENT ON COLUMNS). I observed that each section contains a catalog and this section is identified with
catalog-pg-*. Inside each section the
first table contains the information we want to extract. Those tables can have
three or four columns. The following XSL script is capable of extracting the catalog column descriptions. It uses the template
escape-string-sql to escape sql strings too. Also, the template
build-comment-col centralizes the COMMENT ON COLUMN output.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
xmlns="http://www.w3.org/TR/xhtml1/transitional"
exclude-result-prefixes="#default">
<xsl:output method="text" encoding="utf-8"/>
<!-- escape SQL string -->
<xsl:template name="escape-string-sql">
<xsl:param name="mystr"/>
<xsl:choose>
<!-- "'" = "'" -->
<xsl:when test="contains($mystr, "'")">
<xsl:value-of select="substring-before($mystr, "'")"/>
<!-- replacing ' (') with '' -->
<xsl:text>''</xsl:text>
<xsl:call-template name="escape-string-sql">
<xsl:with-param name="mystr" select="substring-after($mystr, "'")"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$mystr"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- build catalog column comment -->
<xsl:template name="build-comment-col">
<xsl:param name="mytab"/>
<xsl:param name="mycol"/>
<xsl:param name="mystr"/>
COMMENT ON COLUMN pg_catalog.<xsl:value-of select="$mytab"/>.<xsl:value-of select="$mycol"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space($mystr)"/></xsl:call-template>';
</xsl:template>
<xsl:template match="/chapter[@id='catalogs']">
<xsl:text>BEGIN;
</xsl:text>
<xsl:for-each select="sect1">
<xsl:choose>
<!-- comment on catalog columns -->
<xsl:when test="contains(self::sect1/@id,'catalog-pg-')">
<xsl:text>
-- comment on </xsl:text><xsl:value-of select="table/title/structname"/><xsl:text> columns</xsl:text>
<xsl:variable name="tab" select="table/title/structname"/>
<xsl:variable name="numcol" select="table/tgroup/@cols"/>
<!-- consider only the first table of each section -->
<xsl:for-each select="table[1]/tgroup/tbody/row">
<xsl:choose>
<!-- there are tables with 3 or 4 columns -->
<xsl:when test="$numcol = 3">
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[3]"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="$numcol = 4">
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</xsl:when>
</xsl:choose>
</xsl:for-each>
<xsl:text>
END;</xsl:text>
</xsl:template>
</xsl:stylesheet>
Let's check the output:
euler@harman:~$ xsltproc /path/to/only-columns.xsl /path/to/catalogs.xml
BEGIN;
-- comment on catalog tables
-- comment on pg_aggregate columns
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggfnoid IS 'pg_proc OID of the aggregate function';
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggtransfn IS 'Transition function';
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggfinalfn IS 'Final function (zero if none)';
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggsortop IS 'Associated sort operator (zero if none)';
COMMENT ON COLUMN pg_catalog.pg_aggregate.aggtranstype IS 'Data type of the aggregate function''s internal transition (state) data';
COMMENT ON COLUMN pg_catalog.pg_aggregate.agginitval IS 'The initial value of the transition state. This is a text field containing the initial value in its external string representation. If this field is null, the transition state value starts out null.';
.
.
.
COMMENT ON COLUMN pg_catalog.pg_statistic.stawidth IS 'The average stored width, in bytes, of nonnull entries';
COMMENT ON COLUMN pg_catalog.pg_statistic.stadistinct IS 'The number of distinct nonnull data values in the column. A value greater than zero is the actual number of distinct values. A value less than zero is the negative of a multiplier for the number of rows in the table; for example, a column in which values appear about twice on the average could be represented by stadistinct = -0.5. A zero value means the number of distinct values is unknown.';
COMMENT ON COLUMN pg_catalog.pg_statistic.stakindN IS 'A code number indicating the kind of statistics stored in the Nth slot of the pg_statistic row.';
COMMENT ON COLUMN pg_catalog.pg_statistic.staopN IS 'An operator used to derive the statistics stored in the Nth slot. For example, a histogram slot would show the < operator that defines the sort order of the data.';
.
.
.
-- comment on pg_user_mapping columns
COMMENT ON COLUMN pg_catalog.pg_user_mapping.umuser IS 'OID of the local role being mapped, 0 if the user mapping is public';
COMMENT ON COLUMN pg_catalog.pg_user_mapping.umserver IS 'The OID of the foreign server that contains this mapping';
COMMENT ON COLUMN pg_catalog.pg_user_mapping.umoptions IS 'User mapping specific options, as keyword=value strings';
END;
Checking the generated file, everything seems to be ok except a small detail: some pg_statistic columns presented the form
fooN while there were 4 columns to its representation (
foo1,
foo2,
foo3, and
foo4). This is true for 4 columns of
pg_statistic:
stakind,
staop,
stanumbers, and
stavalues. A hack transforms one "virtual" comment into 4 comments.
Two templates were designed to expand the statistic columns (
expand-stat-col and
build-comment-stat-col) and another one to convert the string "Nth" to "1st", "2nd", "3rd", or "4th" (
convert-ord-number). Also, some tests for those special statistic columns were added in the main template.
The new version of only-cols.xsl is above.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
xmlns="http://www.w3.org/TR/xhtml1/transitional"
exclude-result-prefixes="#default">
<xsl:output method="text" encoding="utf-8"/>
<!-- escape SQL string -->
<xsl:template name="escape-string-sql">
<xsl:param name="mystr"/>
<xsl:choose>
<!-- "'" = "'" -->
<xsl:when test="contains($mystr, "'")">
<xsl:value-of select="substring-before($mystr, "'")"/>
<!-- replacing ' (') with '' -->
<xsl:text>''</xsl:text>
<xsl:call-template name="escape-string-sql">
<xsl:with-param name="mystr" select="substring-after($mystr, "'")"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$mystr"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- expand fooN to foo1, foo2, foo3, and foo4 (pg_statistic has columns like fooN) -->
<xsl:template name="expand-stat-col">
<xsl:param name="mycol"/>
<xsl:param name="mynum"/>
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:value-of select="$mynum"/>
</xsl:template>
<!-- convert Nth to 1st, 2nd, 3rd, or 4th -->
<xsl:template name="convert-ord-number">
<xsl:param name="mystr"/>
<xsl:param name="myord"/>
<xsl:choose>
<xsl:when test="contains($mystr, 'Nth')">
<xsl:value-of select="substring-before($mystr, 'Nth')"/>
<xsl:choose>
<xsl:when test="$myord = 1">1st</xsl:when>
<xsl:when test="$myord = 2">2nd</xsl:when>
<xsl:when test="$myord = 3">3rd</xsl:when>
<xsl:when test="$myord = 4">4th</xsl:when>
</xsl:choose>
<xsl:value-of select="substring-after($mystr, 'Nth')"/>
</xsl:when>
</xsl:choose>
</xsl:template>
<!-- build catalog column comment -->
<xsl:template name="build-comment-col">
<xsl:param name="mytab"/>
<xsl:param name="mycol"/>
<xsl:param name="mystr"/>
COMMENT ON COLUMN pg_catalog.<xsl:value-of select="$mytab"/>.<xsl:value-of select="$mycol"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space($mystr)"/></xsl:call-template>';
</xsl:template>
<!-- build special catalog stat column comment -->
<xsl:template name="build-comment-stat-col">
<xsl:param name="mytab"/>
<xsl:param name="mycol"/>
<xsl:param name="mystr"/>
<!-- first column -->
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$mytab"/>
<xsl:with-param name="mycol">
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:text>1</xsl:text>
</xsl:with-param>
<xsl:with-param name="mystr">
<xsl:call-template name="convert-ord-number">
<xsl:with-param name="mystr" select="$mystr"/>
<xsl:with-param name="myord">1</xsl:with-param>
</xsl:call-template>
</xsl:with-param>
</xsl:call-template>
<!-- second column -->
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$mytab"/>
<xsl:with-param name="mycol">
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:text>2</xsl:text>
</xsl:with-param>
<xsl:with-param name="mystr">
<xsl:call-template name="convert-ord-number">
<xsl:with-param name="mystr" select="$mystr"/>
<xsl:with-param name="myord">2</xsl:with-param>
</xsl:call-template>
</xsl:with-param>
</xsl:call-template>
<!-- third column -->
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$mytab"/>
<xsl:with-param name="mycol">
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:text>3</xsl:text>
</xsl:with-param>
<xsl:with-param name="mystr">
<xsl:call-template name="convert-ord-number">
<xsl:with-param name="mystr" select="$mystr"/>
<xsl:with-param name="myord">3</xsl:with-param>
</xsl:call-template>
</xsl:with-param>
</xsl:call-template>
<!-- fourth column -->
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$mytab"/>
<xsl:with-param name="mycol">
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:text>4</xsl:text>
</xsl:with-param>
<xsl:with-param name="mystr">
<xsl:call-template name="convert-ord-number">
<xsl:with-param name="mystr" select="$mystr"/>
<xsl:with-param name="myord">4</xsl:with-param>
</xsl:call-template>
</xsl:with-param>
</xsl:call-template>
</xsl:template>
<xsl:template match="/chapter[@id='catalogs']">
<xsl:text>BEGIN;
</xsl:text>
<xsl:for-each select="sect1">
<xsl:choose>
<!-- comment on catalog columns -->
<xsl:when test="contains(self::sect1/@id,'catalog-pg-')">
<xsl:text>
-- comment on </xsl:text><xsl:value-of select="table/title/structname"/><xsl:text> columns</xsl:text>
<xsl:variable name="tab" select="table/title/structname"/>
<xsl:variable name="numcol" select="table/tgroup/@cols"/>
<!-- consider only the first table of each section -->
<xsl:for-each select="table[1]/tgroup/tbody/row">
<xsl:choose>
<!-- comment on special stat columns -->
<xsl:when test="contains(entry[1]/structfield, 'stakindN')">
<xsl:call-template name="build-comment-stat-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="contains(entry[1]/structfield, 'staopN')">
<xsl:call-template name="build-comment-stat-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="contains(entry[1]/structfield, 'stanumbersN')">
<xsl:call-template name="build-comment-stat-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="contains(entry[1]/structfield, 'stavaluesN')">
<xsl:call-template name="build-comment-stat-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
<!-- there are tables with 3 or 4 columns -->
<xsl:when test="$numcol = 3">
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[3]"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="$numcol = 4">
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</xsl:when>
</xsl:choose>
</xsl:for-each>
<xsl:text>
END;</xsl:text>
</xsl:template>
</xsl:stylesheet>
Let's check the output:
euler@harman:~$ xsltproc /path/to/only-columns-2.xsl /path/to/catalogs.xml
BEGIN;
.
.
.
COMMENT ON COLUMN pg_catalog.pg_statistic.stadistinct IS 'The number of distinct nonnull data values in the column. A value greater than zero is the actual number of distinct values. A value less than zero is the negative of a multiplier for the number of rows in the table; for example, a column in which values appear about twice on the average could be represented by stadistinct = -0.5. A zero value means the number of distinct values is unknown.';
COMMENT ON COLUMN pg_catalog.pg_statistic.stakind1 IS 'A code number indicating the kind of statistics stored in the 1st slot of the pg_statistic row.';
COMMENT ON COLUMN pg_catalog.pg_statistic.stakind2 IS 'A code number indicating the kind of statistics stored in the 2nd slot of the pg_statistic row.';
COMMENT ON COLUMN pg_catalog.pg_statistic.stakind3 IS 'A code number indicating the kind of statistics stored in the 3rd slot of the pg_statistic row.';
COMMENT ON COLUMN pg_catalog.pg_statistic.stakind4 IS 'A code number indicating the kind of statistics stored in the 4th slot of the pg_statistic row.';
.
.
.
END;
The next step was integrating
only-tables.xsl and
only-columns-2.xsl. The
escape-string-sql template is used in both scripts and the path to extract descriptions are the same. The resulting script
extract-catalog.xsl is above.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
xmlns="http://www.w3.org/TR/xhtml1/transitional"
exclude-result-prefixes="#default">
<xsl:output method="text" encoding="utf-8"/>
<!-- escape SQL string -->
<xsl:template name="escape-string-sql">
<xsl:param name="mystr"/>
<xsl:choose>
<!-- "'" = "'" -->
<xsl:when test="contains($mystr, "'")">
<xsl:value-of select="substring-before($mystr, "'")"/>
<!-- replacing ' (') with '' -->
<xsl:text>''</xsl:text>
<xsl:call-template name="escape-string-sql">
<xsl:with-param name="mystr" select="substring-after($mystr, "'")"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$mystr"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- expand fooN to foo1, foo2, foo3, and foo4 (pg_statistic has columns like fooN) -->
<xsl:template name="expand-stat-col">
<xsl:param name="mycol"/>
<xsl:param name="mynum"/>
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:value-of select="$mynum"/>
</xsl:template>
<!-- convert Nth to 1st, 2nd, 3rd, or 4th -->
<xsl:template name="convert-ord-number">
<xsl:param name="mystr"/>
<xsl:param name="myord"/>
<xsl:choose>
<xsl:when test="contains($mystr, 'Nth')">
<xsl:value-of select="substring-before($mystr, 'Nth')"/>
<xsl:choose>
<xsl:when test="$myord = 1">1st</xsl:when>
<xsl:when test="$myord = 2">2nd</xsl:when>
<xsl:when test="$myord = 3">3rd</xsl:when>
<xsl:when test="$myord = 4">4th</xsl:when>
</xsl:choose>
<xsl:value-of select="substring-after($mystr, 'Nth')"/>
</xsl:when>
</xsl:choose>
</xsl:template>
<!-- build catalog column comment -->
<xsl:template name="build-comment-col">
<xsl:param name="mytab"/>
<xsl:param name="mycol"/>
<xsl:param name="mystr"/>
COMMENT ON COLUMN pg_catalog.<xsl:value-of select="$mytab"/>.<xsl:value-of select="$mycol"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space($mystr)"/></xsl:call-template>';
</xsl:template>
<!-- build special catalog stat column comment -->
<xsl:template name="build-comment-stat-col">
<xsl:param name="mytab"/>
<xsl:param name="mycol"/>
<xsl:param name="mystr"/>
<!-- first column -->
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$mytab"/>
<xsl:with-param name="mycol">
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:text>1</xsl:text>
</xsl:with-param>
<xsl:with-param name="mystr">
<xsl:call-template name="convert-ord-number">
<xsl:with-param name="mystr" select="$mystr"/>
<xsl:with-param name="myord">1</xsl:with-param>
</xsl:call-template>
</xsl:with-param>
</xsl:call-template>
<!-- second column -->
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$mytab"/>
<xsl:with-param name="mycol">
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:text>2</xsl:text>
</xsl:with-param>
<xsl:with-param name="mystr">
<xsl:call-template name="convert-ord-number">
<xsl:with-param name="mystr" select="$mystr"/>
<xsl:with-param name="myord">2</xsl:with-param>
</xsl:call-template>
</xsl:with-param>
</xsl:call-template>
<!-- third column -->
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$mytab"/>
<xsl:with-param name="mycol">
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:text>3</xsl:text>
</xsl:with-param>
<xsl:with-param name="mystr">
<xsl:call-template name="convert-ord-number">
<xsl:with-param name="mystr" select="$mystr"/>
<xsl:with-param name="myord">3</xsl:with-param>
</xsl:call-template>
</xsl:with-param>
</xsl:call-template>
<!-- fourth column -->
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$mytab"/>
<xsl:with-param name="mycol">
<xsl:value-of select="substring-before($mycol, 'N')"/>
<xsl:text>4</xsl:text>
</xsl:with-param>
<xsl:with-param name="mystr">
<xsl:call-template name="convert-ord-number">
<xsl:with-param name="mystr" select="$mystr"/>
<xsl:with-param name="myord">4</xsl:with-param>
</xsl:call-template>
</xsl:with-param>
</xsl:call-template>
</xsl:template>
<xsl:template match="/chapter[@id='catalogs']">
<xsl:text>BEGIN;
</xsl:text>
<xsl:text>
-- comment on catalog tables</xsl:text>
<xsl:for-each select="sect1">
<xsl:choose>
<!-- comment on catalog tables -->
<xsl:when test="self::sect1[@id='catalogs-overview']">
<xsl:for-each select="table[@id='catalog-table']/tgroup/tbody/row">
COMMENT ON TABLE pg_catalog.<xsl:value-of select="entry[1]"/> IS '<xsl:call-template name="escape-string-sql"><xsl:with-param name="mystr" select="normalize-space(entry[2])"/></xsl:call-template>';</xsl:for-each>
<xsl:text>
</xsl:text>
</xsl:when>
<!-- comment on catalog columns -->
<xsl:when test="contains(self::sect1/@id,'catalog-pg-')">
<xsl:text>
-- comment on </xsl:text><xsl:value-of select="table/title/structname"/><xsl:text> columns</xsl:text>
<xsl:variable name="tab" select="table/title/structname"/>
<xsl:variable name="numcol" select="table/tgroup/@cols"/>
<!-- consider only the first table of each section -->
<xsl:for-each select="table[1]/tgroup/tbody/row">
<xsl:choose>
<!-- comment on special stat columns -->
<xsl:when test="contains(entry[1]/structfield, 'stakindN')">
<xsl:call-template name="build-comment-stat-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="contains(entry[1]/structfield, 'staopN')">
<xsl:call-template name="build-comment-stat-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="contains(entry[1]/structfield, 'stanumbersN')">
<xsl:call-template name="build-comment-stat-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="contains(entry[1]/structfield, 'stavaluesN')">
<xsl:call-template name="build-comment-stat-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
<!-- there are tables with 3 or 4 columns -->
<xsl:when test="$numcol = 3">
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[3]"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="$numcol = 4">
<xsl:call-template name="build-comment-col">
<xsl:with-param name="mytab" select="$tab"/>
<xsl:with-param name="mycol" select="entry[1]/structfield"/>
<xsl:with-param name="mystr" select="entry[4]"/>
</xsl:call-template>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</xsl:when>
</xsl:choose>
</xsl:for-each>
<xsl:text>
END;</xsl:text>
</xsl:template>
</xsl:stylesheet>
The SQL file can be built with the following command:
euler@harman:~$ xsltproc /path/to/extract-catalog.xsl /path/to/catalogs.xml > /path/to/catalogs.sql
The final step was to load the sql file into PostgreSQL. You need to have in mind that comments are not shared across databases; if you load the script in database
foo, those comments will only be visible to users connected to database
foo. You can load it to your template database (
template1, for example) and any new database created using that template will inherit those comments.
euler@harman:~$ psql -f /path/to/catalogs.sql template1
