Skip site navigation (1) Skip section navigation (2)

XML ouput for psql

From: greg(at)turnstep(dot)com
To: pgsql-patches(at)postgresql(dot)org
Subject: XML ouput for psql
Date: 2003-02-26 20:46:15
Message-ID: b9daa33f80cc0a0c73d984696cb8fbac@biglumber.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Patch to add XML output to psql:

http://www.gtsm.com/xml.patch.txt

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 Latex modes should have better documentation - 
I'll submit a separate doc patch when/if this gets finalized.


- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200302261518

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

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



Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2003-02-26 20:54:05
Subject: Free-space-map management thoughts
Previous:From: Neil ConwayDate: 2003-02-26 17:35:22
Subject: Re: numeric datataypes as seperate library

pgsql-patches by date

Next:From: Hannu KrosingDate: 2003-02-26 21:17:11
Subject: Re: XML ouput for psql
Previous:From: Lennert BuytenhekDate: 2003-02-26 16:31:08
Subject: PGRES_POLLING_ACTIVE is unused...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group