Thread: PSQL suggested enhancement
PSQL has the option to output the result of queries in several different formats, including HTML. Suggestion: have an option to output query results in XML format. Suggested format: <row> <field-1-name>field-1 value</field-1-name> <field-2-name>field-2 value</field-2-name> </row> etc. The user would be responsible for adding the enclosing XML. NULL values could be output as <field-n-name /> Optionally, tags could contain an attribute describing the field type. Or is there a way to do this I don't know about? -- Dean
On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote: > PSQL has the option to output the result of queries in several > different formats, including HTML. Suggestion: have an option to > output query results in XML format. Suggested format: My personal opinion on this is that there are a lot of different ideas about how the XML should/could be written, and a the current output can be piped to a script in <insert favorite scripting language here> to format to match anyone's wish. Rather than have psql decree the one and true format or include so many as to make maintainability burdensome, further formatting is best left to the end user. My 2 yen. Michael Glaesemann grzm myrealbox com
> -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Michael > Glaesemann > Sent: Wednesday, October 19, 2005 11:24 PM > On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote: >> PSQL has the option to output the result of queries in several >> different formats, including HTML. Suggestion: have an option to >> output query results in XML format. > > My personal opinion on this is that there are a lot of different > ideas about how the XML should/could be written, and a the current > output can be piped to a script in <insert favorite scripting > language here> to format to match anyone's wish. Rather than have > psql decree the one and true format or include so many as to make > maintainability burdensome, further formatting is best left to the > end user. By that reasoning there shouldn't be html format either. The beauty of xml output is that it can easily be processed via xslt to create whatever format the user wants. So there need not be "one true format" but simply one root format that could be easily transformed. -Roger > Michael Glaesemann
On Oct 20, 2005, at 15:45 , Roger Hand wrote: >> -----Original Message----- >> From: pgsql-general-owner@postgresql.org >> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Michael >> Glaesemann >> Sent: Wednesday, October 19, 2005 11:24 PM >> > > >> On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote: >> >>> PSQL has the option to output the result of queries in several >>> different formats, including HTML. Suggestion: have an option to >>> output query results in XML format. >>> >> >> My personal opinion on this is that there are a lot of different >> ideas about how the XML should/could be written, and a the current >> output can be piped to a script in <insert favorite scripting >> language here> to format to match anyone's wish. Rather than have >> psql decree the one and true format or include so many as to make >> maintainability burdensome, further formatting is best left to the >> end user. >> > > By that reasoning there shouldn't be html format either. Agreed :) I'd submit a patch to remove it if I thought it'd be accepted--and more importantly, if I thought it were worth the time it'd take me (I'm still learning C, and the little bit of PostgreSQL code I'm familiar with is not the psql client) (excuses excuses :). Though justification for removing a feature is different from that of adding or extending one: removing one breaks backwards compatibility. Michael Glaesemann grzm myrealbox com
On Thu, Oct 20, 2005 at 03:23:51PM +0900, Michael Glaesemann wrote: > My personal opinion on this is that there are a lot of different > ideas about how the XML should/could be written, and a the current > output can be piped to a script in <insert favorite scripting > language here> to format to match anyone's wish. Rather than have > psql decree the one and true format or include so many as to make > maintainability burdensome, further formatting is best left to the > end user. IMHO, we should look for an OpenDocument like standard for spreadsheets and/or tables. If you pick something like that then you have a higher chance it can be imported directly into applications, which at the end of the day is what people want, right? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On 2005-10-19 23:52, Michael Glaesemann wrote: > > On Oct 20, 2005, at 15:45 , Roger Hand wrote: > >> >>> On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote: >>> >>>> PSQL has the option to output the result of queries in several >>>> different formats, including HTML. Suggestion: have an option to >>>> output query results in XML format. >>>> >>> >>> My personal opinion on this is that there are a lot of different >>> ideas about how the XML should/could be written, and a the current >>> output can be piped to a script in <insert favorite scripting >>> language here> to format to match anyone's wish. Rather than have >>> psql decree the one and true format or include so many as to make >>> maintainability burdensome, further formatting is best left to the >>> end user. >>> >> >> By that reasoning there shouldn't be html format either. > > Agreed :) I'd submit a patch to remove it if I thought it'd be > accepted--and more importantly, if I thought it were worth the time > it'd take me (I'm still learning C, and the little bit of PostgreSQL > code I'm familiar with is not the psql client) (excuses excuses :). > Though justification for removing a feature is different from that of > adding or extending one: removing one breaks backwards compatibility. > > Michael Glaesemann > grzm myrealbox com So, which other features THAT YOU DON"T PERSONALLY WANT OR SEE THE NEED FOR, would you remove from PostgreSQL ??? Do you personally not use the expanded, or unaligned outputs ??? By all means rip them out !!! (sarcasm off) While not every suggested feature needs to be in software, the idea that you'd remove a useful feature that someone else found valuable enough to spend the time coding, testing, etc, is anathema to me. Remind me to NEVER let you touch the open-source projects I control. I'm looking for contributors, not hackers or saboteurs. Not to mention people that are open to new ideas. Second, surrounding field values with XML tags having the name of the field, would be BY FAR the most natural way of representing the data. The only "variable" in what I suggested was the name of the row tag, which could either be a formatting parameter, or (my preference) easily changed by the user in the script which added the surrounding XML (which I left off because THAT PORTION is highly variable). 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). -- Dean
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.
At least, unless someone has some better idea? Outputting complete
gnumeric spreadsheets is going a bit far, no?
Have a nice day,
<office:body>
<office:spreadsheet>
<table:table table:name="TableName">
<table:table-header-rows>
<table:table-row>
<table:table-cell table:style-name="heading">
<text:p>Last Name</text:p>
</table:table-cell>
...for each column...
</table:table-row>
</table:table-header-rows>
<table:table-row>
<table:table-cell office:value-type="float" office:value="$value">
<text:p>$value</text:p>
</table:table-cell>
</table:table-row>
...for each row and column...
</table:table>
</office:spreadsheet>
</office:body>
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment
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. > >At least, unless someone has some better idea? Outputting complete >gnumeric spreadsheets is going a bit far, no? > > OTOH, this idea might be really kinda cool. Maybe a good idea for client applications.... Or are there other programs out there that can save db queries to static (stand-alone) Gnumeric spreadsheets? This would actually be a killer feature for one of my customers.... Best Wishes, Chris Travers
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
Am Donnerstag, den 20.10.2005, 14:56 -0700 schrieb Chris Travers: > Martijn van Oosterhout wrote: .. > > > OTOH, this idea might be really kinda cool. Maybe a good idea for > client applications.... Or are there other programs out there that can > save db queries to static (stand-alone) Gnumeric spreadsheets? > > This would actually be a killer feature for one of my customers.... While for easy access (e.g. w/o column types) the \H SELECT ... ; \g table.xls works very well. Open Document spreadsheet would be a nice option I guess.
Am Donnerstag, den 20.10.2005, 15:46 -0700 schrieb Roger Hand: > On Thursday, October 20, 2005 1:01 PM, Martijn van Oosterhout wrote: > ... > I would argue against outputting this one specific OpenDoc format, > even though it is the "flavor of the month" right now. Actually its near standardized. And we love standards, dont we?
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
interchangeformat (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
forone. 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
likebelow. 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
In article <DB28E9B548192448A4E8C8A3C1B1E475611D89@sj1-exch-01.us.corp.kailea.com>, Roger Hand <RHand@kailea.com> wrote: % 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. Well, pg does output a simple xml format, which can be transformed via xslt, and yet here's a suggestion for an enhancement to do it differently. To be more explicit, if you turn on html output and turn off the footer \H \pset footer off what you get isn't always valid HTML, but it does seem to be well-formed XML, which can be easily transformed to the XML you really want. -- Patrick TJ McPhee North York Canada ptjm@interlog.com
Patrick TJ McPhee schrieb: > In article <DB28E9B548192448A4E8C8A3C1B1E475611D89@sj1-exch-01.us.corp.kailea.com>, > Roger Hand <RHand@kailea.com> wrote: > > % 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. > > Well, pg does output a simple xml format, which can be transformed via > xslt, and yet here's a suggestion for an enhancement to do it differently. > To be more explicit, if you turn on html output and turn off the footer > > \H > \pset footer off > > what you get isn't always valid HTML, but it does seem to be well-formed XML, > which can be easily transformed to the XML you really want. not 100% true because the type information is missing here. Otoh, if one has a spreadsheet program one can just fetch the data directly from database into it...