returning PGresult as xml

hello,

this note is intended to describe my work on beginning to further
integrate xml into postgresql.  first, i'd like to thank the
contributers of contrib/xml as their work was instrumental in helping
me understand what support exists and where i wanted to go. thanks.

my first requirement is to export data from the database into a format
which can be read not only by existing (postgresql) clients but by
people and programs that don't know how to use a PGresult. xml is very
verbose, but its popularity makes it closer to universal than anything
else of which i could think. in addition, ideas like XSL/XSLT make an
export of xml very attractive to me.

it's been said that converting a PGresult into xml is "trivial" and
that's why it hasn't been done in the codebase as of yet. i have seen
much code that writes xml, and many mistakes are made. most often
improper escaping, or writing to a schema/DTD that has not been
well-thought out. the transformation into xml is not difficult, but it
does require attention to detail.

i feel badly that i have not been able to use any existing
standards. xmlrpc, i found, was not type-rich enough, and that made it
difficult or impossible to use. in particular, the only way to
represent a matrix is as a struct of structs. this makes it very
verbose for one to encode a PGresult. i found SOAP too difficult for
compliance. so my result was to create a schema, which results in a  DTD.

an example of what my code generates can be found below. the following
xml is the result of the query "SELECT 1 as foo 2 as bar":

<?xml version='1.0' encoding='ISO-8859-1'?>
<!DOCTYPE PGresult [ <!ELEMENT PGresult (col_desc*, row*)> <!ATTLIST PGresult    num_rows CDATA #REQUIRED    num_cols
CDATA#REQUIRED> <!ELEMENT col_desc EMPTY> <!ATTLIST col_desc     num CDATA #REQUIRED     format (text | binary)
#REQUIRED    type CDATA #REQUIRED     name CDATA #REQUIRED> <!ELEMENT row (col*)> <!ATTLIST row    num CDATA #REQUIRED>
<!ELEMENTcol (#PCDATA)> <!ATTLIST col    num CDATA #REQUIRED> <!ENTITY NULL ''>
 
]>
<PGresult num_rows='1' num_cols='2'> <col_desc num='0' type='int4' format='text' name='foo' /> <col_desc num='1'
type='int4'format='text' name='bar' /> <row num='0'>   <col num='0'>1</col>   <col num='1'>2</col> </row>
 
</PGresult>

a slightly more complicated example:
template1=# select oid,typname,typlen,typtype from pg_type where 
oid<20;oid | typname | typlen | typtype
-----+---------+--------+--------- 16 | bool    |      1 | b 17 | bytea   |     -1 | b 18 | char    |      1 | b 19 |
name   |     32 | b
 
(4 rows)

<!DOCTYPE PGresult [ <!ELEMENT PGresult (col_desc*, row*)> <!ATTLIST PGresult    num_rows CDATA #REQUIRED    num_cols
CDATA#REQUIRED> <!ELEMENT col_desc EMPTY> <!ATTLIST col_desc    num CDATA #REQUIRED    format (text | binary) #REQUIRED
  type CDATA #REQUIRED    name CDATA #REQUIRED> <!ELEMENT row (col*)> <!ATTLIST row    num CDATA #REQUIRED> <!ELEMENT
col(#PCDATA)> <!ATTLIST col    num CDATA #REQUIRED> <!ENTITY NULL ''>
 
]>
<PGresult num_rows='4' num_cols='4'> <col_desc num='0' type='oid' format='text' name='oid' /> <col_desc num='1'
type='name'format='text' name='typname' /> <col_desc num='2' type='int2' format='text' name='typlen' /> <col_desc
num='3'type='char' format='text' name='typtype' /> <row num='0'>   <col num='0'>16</col>   <col num='1'>bool</col>
<colnum='2'>1</col>   <col num='3'>b</col> </row> <row num='1'>   <col num='0'>17</col>   <col num='1'>bytea</col>
<colnum='2'>-1</col>   <col num='3'>b</col> </row> <row num='2'>   <col num='0'>18</col>   <col num='1'>char</col>
<colnum='2'>1</col>   <col num='3'>b</col> </row> <row num='3'>   <col num='0'>19</col>   <col num='1'>name</col>
<colnum='2'>32</col>   <col num='3'>b</col> </row>
 
</PGresult>

i have done this work for myself and my own needs, so i fully
understand if this work is not interesting to the postgresql group in
general. however, if there is some chance that the changes could be
incorporated into the tree, i would be interested in contributing, as
integration into a proper version of postgresql will make my build
easier. ;)

i would expect that integration would look something like exposing
from libpq a function that looks something like: const char *PGresult_as_xml(PGresult *result, int include_dtd);

i would also expect that psql would be modified to take a \X
and to call the above function. there is some strangeness now,
as psql doesn't call methods defined in libpq to print; it has
its own printer. i, of course, would do this work. :) i just
need to know that people are interested.

also, if integration is going to happen, i will need to replace
calls to my hashtables with calls to postgresql's hashtables.
i saw dynamic hashtables in the backend, but not in the interfaces.
i wasn't exactly sure how i should go about introducing another
module to the frontend; there could be problems of which i
remain blissfully unaware.

i look forward to feedback, and i hope this note finds you well,

b

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/