Hello all,
Thank you to all who replied for suggestions and help. Enclosed please find
code changes for the following items:
- Allow COPY to understand \x as a hex byte, and
- Add XML output to COPY
The changes include implementation of the features as well as modification
of the copy regression test.
After a careful consideration we decided to
- put XML implementation in the backend and
- use XML format described below, with justification of our decision.
The XML schema used by the COPY TO command was designed for ease of use and
to avoid the problem of column names appearing in XML element names.
XML doesn't allow spaces and punctuation in element names but Postgres does
allow these characters in column names; therefore, a direct mapping would be
problematic.
The solution selected places the column names into attribute fields where
any special characters they contain can be properly escaped using XML
entities. An additional attribute is used to distinguish null fields from
empty ones.
The example below is taken from the test suite. It demonstrates some basic
XML escaping in row 2. Row 3 demonstrates the difference between an empty
string (in col2) and a null string (in col3). If a field is null it will
always be empty but a field which is empty may or may not be null.
Always check the value of the 'null' attribute to be sure when a field is
truly null.
<?xml version='1.0'?>
<table>
<row>
<col name='col1' null='n'>Jackson, Sam</col>
<col name='col2' null='n'>\h</col>
</row>
<row>
<col name='col1' null='n'>It is "perfect".</col>
<col name='col2' null='n'> </col>
</row>
<row>
<col name='col1' null='n'></col>
<col name='col2' null='y'></col>
</row>
</table>
Please let us know if about any concerns, objections the proposed change may
cause.
Best regards,
Jason Lucas, Sergey Ten
SourceLabs
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Wednesday, May 11, 2005 7:11 PM
> To: Sergey Ten
> Cc: pgsql-hackers@postgresql.org; jason@sourcelabs.com
> Subject: Re: [HACKERS] patches for items from TODO list
>
> Sergey Ten wrote:
> > Hello all,
> >
> > We would like to contribute to the Postgresql community by implementing
> > the following items from the TODO list
> > (http://developer.postgresql.org/todo.php):
> > . Allow COPY to understand \x as a hex byte . Allow COPY to optionally
> > include column headings in the first line . Add XML output to COPY
> >
> > The changes are straightforward and include implementation of the
> > features as well as modification of the regression tests and
> documentation.
> >
> > Before sending a diff file with the changes, we would like to know if
> > these features have been already implemented.
>
> Please check the web site version. Someone has already implemented
> "Allow COPY to optionally include column headings in the first line".
>
> As far as XML, there has been discussion on where that should be done?
> In the backend, libpq, or psql. It will need discussion on hackers. I
> assume you have read the developer's FAQ too.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania
> 19073
