Re: XML ouput for psql

greg@turnstep.com kirjutas K, 26.02.2003 kell 22:46:

>
> 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?

I think you should force conversion to something standard, try using
automatic conversion to some known client encoding.

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

> 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?

Run "select  oid,typname from pg_type;" first if run in xml mode and
store the oid/columnname pairs.

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

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