Re: XML ouput for psql

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: greg(at)turnstep(dot)com
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: XML ouput for psql
Date: 2003-02-26 21:17:11
Message-ID: 1046294231.4096.5.camel@fuji.krosing.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

greg(at)turnstep(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-02-26 22:09:49 Re: Hey! I thought this was fixed in 7.2.4
Previous Message Josh Berkus 2003-02-26 21:13:31 Hey! I thought this was fixed in 7.2.4

Browse pgsql-patches by date

  From Date Subject
Next Message Christopher Kings-Lynne 2003-02-27 05:07:38 Dump ALTER TABLE/SET STORAGE in pg_dump
Previous Message greg 2003-02-26 20:46:15 XML ouput for psql