Very Computer

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Patch to add XML output to psql:

http://www.veryComputer.com/

Notes and questions:

The basic output looks something like this:

<?xml version="1.0" encoding="SQL_ASCII"?>
<resultset psql_version="7.4devel" query="select * from foo;">

<columns>
  <col num="1">a</col>
  <col num="2">b</col>
  <col num="3">c</col>
  <col num="4">mucho nacho  </col>
</columns>
<row num="1">
  <a>1</a>
  <b>pizza</b>
  <c>2003-02-25 15:19:22.169797</c>
  <"mucho nacho  "></"mucho nacho  ">
</row>
<row num="2">
  <a>2</a>
  <b>mushroom</b>
  <c>2003-02-25 15:19:26.969415</c>
  <"mucho nacho  "></"mucho nacho  ">
</row>
<footer>(2 rows)</footer>
</resultset>

and with the \x option:

<?xml version="1.0" encoding="SQL_ASCII"?>
<resultset psql_version="7.4devel" query="select * from foo;">

<columns>
  <col num="1">a</col>
  <col num="2">b</col>
  <col num="3">c</col>
  <col num="4">mucho nacho  </col>
</columns>
<row num="1">
  <cell name="a">1</cell>
  <cell name="b">pizza</cell>
  <cell name="c">2003-02-25 15:19:22.169797</cell>
  <cell name="mucho nacho  "></cell>
</row>
<row num="2">
  <cell name="a">2</cell>
  <cell name="b">mushroom</cell>
  <cell name="c">2003-02-25 15:19:26.969415</cell>
  <cell name="mucho nacho  "></cell>
</row>
</resultset>

The default encoding "SQL-ASCII" is not valid for XML.
Should it be automatically changed to something else?

The flag "-X" is already taken, unfortunately, although \X is not.
I used "-L" and "\L" but they are not as memorable as "X". Anyone
see a way around this? Can we still use \X inside of psql?

It would be nice to include the string representation of the column
types in the xml output:
<col type="int8">foo</col>
....but I could not find an easy way to do this: PQftype returns the
OID only (which is close but not quite there). Is there an
existing way to get the name of the type of a column from a
PQresult item?

The HTML, XML, and*modes should have better documentation -
I'll submit a separate doc patch when/if this gets finalized.

- --

PGP Key: 0x14964AC8 200302261518

-----BEGIN PGP SIGNATURE-----
Comment: http://www.veryComputer.com/

iD8DBQE+XSR/vJuQZxSWSsgRAi2jAJ9IAKnMBmNcVEEI8TXQBBd/rtm4XQCg0Vjq
IO9OsCSkdnNJqnrYYutM3jw=
=9kwY
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.veryComputer.com/

Quote:

> and with the \x option:

> <?xml version="1.0" encoding="SQL_ASCII"?>
> <resultset psql_version="7.4devel" query="select * from foo;">

> <columns>
>   <col num="1">a</col>
>   <col num="2">b</col>
>   <col num="3">c</col>
>   <col num="4">mucho nacho  </col>
> </columns>
> <row num="1">
>   <cell name="a">1</cell>
>   <cell name="b">pizza</cell>
>   <cell name="c">2003-02-25 15:19:22.169797</cell>
>   <cell name="mucho nacho  "></cell>
> </row>
> <row num="2">
>   <cell name="a">2</cell>
>   <cell name="b">mushroom</cell>
>   <cell name="c">2003-02-25 15:19:26.969415</cell>
>   <cell name="mucho nacho  "></cell>
> </row>
> </resultset>

> The default encoding "SQL-ASCII" is not valid for XML.
> Should it be automatically changed to something else?

btw, "UNICODE" is also not any known encoding in XML, but PostgreSQL
uses it to mean utf-8

Quote:> The flag "-X" is already taken, unfortunately, although \X is not.
> I used "-L" and "\L" but they are not as memorable as "X". Anyone
> see a way around this? Can we still use \X inside of psql?

> It would be nice to include the string representation of the column
> types in the xml output:
> <col type="int8">foo</col>
> ....but I could not find an easy way to do this: PQftype returns the
> OID only (which is close but not quite there). Is there an
> existing way to get the name of the type of a column from a
> PQresult item?

you could also store the result in ~/.psql for faster access later on
and manually clear it if new types are defined

----------------
Hannu

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

1. Look into the SQL/XML standard draft (ftp.sqlstandards.org) to find out
whether the standard addresses this sort of thing.

2. Use an established/standardized XML (or SGML) table model rather than
rolling your own.

Incidentally, the HTML table model is such an established and standardized
XML and SGML table model, so the easiest way to get the task "add XML
output to psql" done is to update the HTML output to conform to XHTML.
That way you get both the strict XML and you can look at the formatted
result with any old (er, new) browser.

--

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

http://www.wiscorp.com/sql/SQLX_Bringing_SQL_and_XML_Together.pdf

The article mentions a lot of links on the sqlstandards.org and iso.org sites, none
of which work or are restricted. If anyone knows of some good links, please
let me know. (especially ISO 9075). From what I've read of the SQLX stuff, the
format in my patch should be mostly standard:

<row>
 <name>Joe Sixpack</name>
 <age>35</age>
 <state>Alabama</state>
</row>

One problem is that the recommended way to handle non-standard characters
(including spaces) is to escape them like this:

foobar baz => <foobar_x0020_baz>

This also includes escaping things like "_x*" and "xml*". We don't have
anything like that in the code yet (?), but we should probably think about
heading that way. I think escaping whitespace in quotes is good enough
for now for:

foobar baz => <"foobar baz">

The xsd and xsi standards are also interesting, but needlessly complicated
for psql output, IMO.

Quote:> Incidentally, the HTML table model is such an established and standardized
> XML and SGML table model, so the easiest way to get the task "add XML
> output to psql" done is to update the HTML output to conform to XHTML.
> That way you get both the strict XML and you can look at the formatted
> result with any old (er, new) browser.

<columnname>data here</columnname>

..which is not valid XHTML and won't be viewable by any browser. The other
suggested XML formats are even further from XHTML than the above. The HTML
format should be "html table/layout" specific and the XML should be
"schema/data" specific.

- --

PGP Key: 0x14964AC8 200302280938

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+X3k5vJuQZxSWSsgRAuXFAKDGO1IsjB9Lwtkcws1xJy47PibcLQCg3dx5
fsy27qguZv841lPvCjzdUic=
=4f9B
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------