Re: [GENERAL] PSQL suggested enhancement
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PSQL suggested enhancement
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? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 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\nrow 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PSQL suggested enhancement
In article [EMAIL PROTECTED], Roger Hand [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PSQL suggested enhancement
Patrick TJ McPhee schrieb: In article [EMAIL PROTECTED], Roger Hand [EMAIL PROTECTED] 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... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PSQL suggested enhancement
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PSQL suggested enhancement
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PSQL suggested enhancement
On Oct 20, 2005, at 15:45 , Roger Hand wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PSQL suggested enhancement
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. pgphrCL6IDueB.pgp Description: PGP signature
Re: [GENERAL] PSQL suggested enhancement
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 DONT 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PSQL suggested enhancement
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:pLast 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. pgpiZYEvym5pO.pgp Description: PGP signature
Re: [GENERAL] PSQL suggested enhancement
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PSQL suggested enhancement
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
