XML schemas and PG column names

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: XML schemas and PG column names
Date: 2009-12-12 15:42:59
Message-ID: 4B23BA03.9050304@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm doing some work with the output of query_to_xml_and_xmlschema(). The
output is a bit unfortunate in my opinion when the column names in the
query are not legal XML names. We quite reasonably translate the names
so that legal XML names result, but we don't actually put the original
name anywhere in the output, meaning that the end processor has some
work to do to recover the original. Here are two snippets from the
output when the column names are "z" and "25 %ile":

<xsd:complexType name="RowType">
<xsd:sequence>
<xsd:element name="z" type="INTEGER" nillable="true"></xsd:element>
<xsd:element name="_x0032_5_x0020__x0025_ile" type="INTEGER"
nillable="true"></xsd:element>
</xsd:sequence>
</xsd:complexType>

<row>
<z>1</z>
<_x0032_5_x0020__x0025_ile>2</_x0032_5_x0020__x0025_ile>
</row>

Of course, we can recover the original name by using something like perl
to do operations like this:

$column_name =~ s/_x([[:xdigit:]]{4})_/pack("U",hex($1))/ge;

but that's ugly and not as simply available in many XSL processors (I am
using XSL to transform the XML.)

I propose that we annotate the schema section RowType elements with the
original names, so we would have something like this in the schema section:

<xsd:complexType name="RowType"
xmlns:pg="http://www.postgresql.org/schemas/column-names">
<xsd:sequence>
<xsd:element name="z" type="INTEGER" nillable="true">
<xsd:annotation>
<xsd:appinfo>
<pg:column-name>z</pg:column-name>
</xsd:appinfo>
</xsd:annotation>*
* </xsd:element>
<xsd:element name="_x0032_5_x0020__x0025_ile" type="INTEGER"
nillable="true">
<xsd:annotation>
<xsd:appinfo>
<pg:column-name>25 %ile</pg:column-name>
</xsd:appinfo>
* *</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>

While it might be a bit longwinded, it's not going to add to the per-row
output, just the schema section.

Thoughts?

cheers

andrew

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-12-12 15:53:40 Re: XML schemas and PG column names
Previous Message Tom Lane 2009-12-12 15:19:53 Re: Streaming replication and non-blocking I/O