Re: PSQL suggested enhancement

On 2005-10-20 15:46, Roger Hand wrote:
> On Thursday, October 20, 2005 1:01 PM, Martijn van Oosterhout wrote:
>
>> On Thu, Oct 20, 2005 at 09:28:25AM -0700, Dean Gibson (DB Administrator) wrote:
>>
>>> I just find it surprising that XML is not one of the formats provided, considering that XML is considered a data interchange format (much more than HTML, which is a representation format).
>>>
>> All jokes aside, saying "output to XML" is like saying "all our documentation will use words from the english dictionary". Yes, you made a constraint but until you decide grammer, syntax and style, you havn't decided anything yet.
>>
>> Realistically, psql is only ever going to support one XML format, we can't add more styles every time someone asks for one. Hence, we should pick the one that is going to be most easily loaded into other programs (the entire point of XML, right?). To that end, we should probably aim for something like the OpenDocument table format, which looks a bit like below. At least that way you stand a chance of being able to import it and/or display it.
>>
>
> If pg outputs a simple xml format, it can easily be transformed via xslt into OpenDoc table format, alternate html formats, or the alternate xml format of your choice.
>
> I would argue against outputting this one specific OpenDoc format, even though it is the "flavor of the month" right now.
>
> -Roger
>

Exactly. The simpler, the better. It's far easier to add structure
than to remove it.

Actually, using PSQL's "expanded" output ("\x" command), I see that it
is absolutely trivial using SED to post-format the output into XML:

sed -r 's:^-\[ RECORD (.*) \]-+$:</row>\n<row number="\1">:;s:([^ ]*)
+\| (.*): <\1>\2</\1>:;s:^$:</row>:;1s:</row>\n::'

There's probably a cleaner way with AWK, but the above is simple enough
(your eMail reader may break the above into separate lines at the
positions where I had a space). The last two "s" commands just handle
the first/last line cases.

The only thing the above does not handle is NULL values as distinct from
zero-length strings. If someone is interested, I'll do up a version
that handles NULLs in a distinct manner (requires using the PSQL "\pset
null" command).

-- Dean