Обсуждение: psql Week 3
For external reasons I didn't get as much done as I wanted. Originally, I planned for 4 weeks and I think I can keep that. The source is posted at http://www.pathwaynet.com/~peter/psql3.tar.gz CHANGELOG * Accommodated object descriptions in \d* commands. * Re-wrote \dd * Re-wrote \d "table" * \pset command as generic way for setting printing options (e.g., \pset format html, \pset null "(null)") * Proliferated use of const char * and enums * Rewrote \di, \dt, \ds, \dS. Say hello to \dv for views, which are now recognized correctly. You can also call, e.g., \dtvifor a list of indices, tables, and views. The possibilities are endless ... (where "endless" = 325) Also I wrote new printing routines which are better abstracted so that one could easily throw in new formats. Here are some examples. Let me know if you hate them. (Sorry that this is a little long.) peter@localhost:5432 play=> \pset format aligned peter@localhost:5432 play=> \pset border 0 peter@localhost:5432 play=> select * from foo; first second -------- ------------ 2 0 ----- 0 hi 34 -1 -2 yo &&& <> ho 99999999 this is text (7 rows) peter@localhost:5432 play=> \pset border 1 peter@localhost:5432 play=> select * from foo; first | second ----------+-------------- 2 | 0 | ----- 0 | hi 34 | -1 | -2 | yo &&& <> ho99999999| this is text (7 rows) peter@localhost:5432 play=> \pset border 2 peter@localhost:5432 play=> select * from foo; +----------+--------------+ | first | second | +----------+--------------+ | 2 | | | 0 | ----- | | 0 | hi | | 34 | | | -1 | -2 | | | yo &&& <> ho | | 99999999 | this is text | +----------+--------------+ (7 rows) peter@localhost:5432 play=> \pset format unaligned peter@localhost:5432 play=> \pset fieldsep ",," peter@localhost:5432 play=> select * from foo; first,,second 2,, 0,,----- 0,,hi 34,, -1,,-2 ,,yo &&& <> ho 99999999,,this is text (7 rows) peter@localhost:5432 play=> \t Turned on only tuples peter@localhost:5432 play=> \x Turned on expanded table representation peter@localhost:5432 play=> select * from foo; first,,2 second,, first,,0 second,,----- first,,0 second,,hi first,,34 second,, first,,-1 second,,-2 first,, second,,yo &&& <> ho first,,99999999 second,,this is text peter@localhost:5432 play=> \pset border 1 peter@localhost:5432 play=> \pset format html peter@localhost:5432 play=> \pset expanded Turned off expanded display peter@localhost:5432 play=> select * from foo; <table border=1> <tr valign=top> <td align=right>2</td> <td align=left> </td> </tr> <tr valign=top> <td align=right>0</td> <td align=left>-----</td> </tr> <tr valign=top> <td align=right>0</td> <td align=left>hi</td> </tr><tr valign=top> <td align=right>34</td> <td align=left> </td> </tr> <tr valign=top> <td align=right>-1</td> <td align=left>-2</td> </tr> <tr valign=top> <td align=right> </td> <td align=left>yo &&&<> ho</td> </tr> <tr valign=top> <td align=right>99999999</td> <td align=left>this is text</td></tr> </table> peter@localhost:5432 play=> \t Turned off only tuples peter@localhost:5432 play=> \pset border 2 peter@localhost:5432 play=> \pset null "(null)" peter@localhost:5432 play=> \pset format latex peter@localhost:5432 play=> select * from foo; \begin{tabular}{|r|l|} \hline first & second \\ \hline 2 & (null) \\ 0 & ----- \\ 0 & hi \\ 34 & (null) \\ -1 & -2 \\ (null) & yo &&& <> ho \\ % Yes, this needs to be escaped. 99999999 & this is text \\ \hline \end{tabular} (7 rows) \\ peter@localhost:5432 play=> \pset null Current null display is "(null)". peter@localhost:5432 play=> \pset fieldsep Current field separator is ",,". (Oh, you actually read all the way down to here? Good.) -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> For external reasons I didn't get as much done as I wanted. Originally, I > planned for 4 weeks and I think I can keep that. > > The source is posted at http://www.pathwaynet.com/~peter/psql3.tar.gz > > CHANGELOG > * Accommodated object descriptions in \d* commands. > * Re-wrote \dd > * Re-wrote \d "table" > * \pset command as generic way for setting printing options (e.g., > \pset format html, \pset null "(null)") > * Proliferated use of const char * and enums > * Rewrote \di, \dt, \ds, \dS. Say hello to \dv for views, which are > now recognized correctly. You can also call, e.g., \dtvi for a list > of indices, tables, and views. The possibilities are endless ... > (where "endless" = 325) > Very cool. Nice new formats. Man, I will have to add them to this book. I am going to have to have pre-7.0 psql backslash command listings, and 7.0 backslash listings. This improvement is long overdue. psql has always been one of our nifty features. It just got niftier. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: >> For external reasons I didn't get as much done as I wanted. Originally, I >> planned for 4 weeksand I think I can keep that. >> >> The source is posted at http://www.pathwaynet.com/~peter/psql3.tar.gz >> >> CHANGELOG>> * Accommodated object descriptions in \d* commands. >> * Re-wrote \dd >> * Re-wrote \d "table" >> * \pset commandas generic way for setting printing options (e.g., >> \pset format html, \pset null "(null)") >> * Proliferateduse of const char * and enums >> * Rewrote \di, \dt, \ds, \dS. Say hello to \dv for views, which are >> nowrecognized correctly. You can also call, e.g., \dtvi for a list >> of indices, tables, and views. The possibilitiesare endless ... >> (where "endless" = 325) >> > >Very cool. Nice new formats. Man, I will have to add themto this >book. I am going to have to have pre-7.0 psql backslash command >listings, and 7.0 backslash listings. Thisimprovement is long overdue. >psql has always been one of our nifty features. It just got niftier. My previous database experience was with PICK, where the data dictionaries include an output format and the command language allows for column formatting 'on the fly'. This is something that I have missed with PostgreSQL, though I believe the SQL standard does not cover it. What I would like would be the ability to attach a format to a column, so that text could be truncated at 25 characters or floats lined up with a specified number of decimal places. (May be we can already and I've missed it?) I would particularly like to be able to do text wrapping within a column and totalling of numeric columns: -----+---------------------+-------- id | description | qty -----+---------------------+-------- abc1 | text that rambles | 35.43 | on and on about | | something or other | def2 | more useless text | 2.00 hgf3 | and yet more text | 355.10 | to read | -----+---------------------+-------- | | 392.53 -----+---------------------+-------- (3 rows) Do you think there's any place for this in PostgreSQL? Perhaps it needs a separate front-end tool. -- Vote against SPAM: http://www.politik-digital.de/spam/ ======================================== Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "Commit thy way unto the LORD; trust also in him and he shall bring it to pass." Psalms 37:5
hi... > What I would like would be the ability to attach a format to a column, so > that text could be truncated at 25 characters or floats lined up with a > specified number of decimal places. (May be we can already and I've missed > it?) I would particularly like to be able to do text wrapping within a > column and totalling of numeric columns: i miss this from oracle as well... > > Do you think there's any place for this in PostgreSQL? Perhaps it needs a > separate front-end tool. psql would seem the proper place for this? -- Aaron J. Seigo Sys Admin
> >Very cool. Nice new formats. Man, I will have to add them to this > >book. I am going to have to have pre-7.0 psql backslash command > >listings, and 7.0 backslash listings. This improvement is long overdue. > >psql has always been one of our nifty features. It just got niftier. > > My previous database experience was with PICK, where the data dictionaries > include an output format and the command language allows for column > formatting 'on the fly'. This is something that I have missed with > PostgreSQL, though I believe the SQL standard does not cover it. I had that with Progress. Yes, it was handy. > > What I would like would be the ability to attach a format to a column, so > that text could be truncated at 25 characters or floats lined up with a char(25)? > specified number of decimal places. (May be we can already and I've missed numeric(16,2)? > it?) I would particularly like to be able to do text wrapping within a > column and totalling of numeric columns: > > -----+---------------------+-------- > id | description | qty > -----+---------------------+-------- > abc1 | text that rambles | 35.43 > | on and on about | > | something or other | > def2 | more useless text | 2.00 > hgf3 | and yet more text | 355.10 > | to read | > -----+---------------------+-------- > | | 392.53 > -----+---------------------+-------- > (3 rows) > > Do you think there's any place for this in PostgreSQL? Perhaps it needs a > separate front-end tool. That's a job for pgaccess. I thought it did stuff like that. If you want printing like that, we need a report-writer, which is an important application. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Tue, 19 Oct 1999, Oliver Elphick wrote: > My previous database experience was with PICK, where the data dictionaries > include an output format and the command language allows for column > formatting 'on the fly'. This is something that I have missed with > PostgreSQL, though I believe the SQL standard does not cover it. Hmm. Well, the idea of keeping formatting data in the backend doesn't sound too exciting to me. The data type already contains a fair amount of formatting information in itself. But psql is not supposed to be a report generator. It's also not a typesetting engine. The idea was to sort of write a shell, but one that doesn't use the OS kernel but a database server. > > What I would like would be the ability to attach a format to a column, so > that text could be truncated at 25 characters or floats lined up with a > specified number of decimal places. (May be we can already and I've missed Okay, lining up floats is sort of on the wish list (as distinct from todo list). I'll keep that in mind. > it?) I would particularly like to be able to do text wrapping within a > column and totalling of numeric columns: Wrapping text also seemed like a nice idea to me, but psql is supposed to be a query interpreter. Wrapping text is a whole different animal and there are specialty programs out there for that. And totalling numeric columns is the job of a report generator. All psql does is send a query and output the results, and it wants to make that job as fun as possible along the way. It doesn't know anything about what's in the query results. That is probably an important line to keep. > > -----+---------------------+-------- > id | description | qty > -----+---------------------+-------- > abc1 | text that rambles | 35.43 > | on and on about | > | something or other | > def2 | more useless text | 2.00 > hgf3 | and yet more text | 355.10 > | to read | > -----+---------------------+-------- > | | 392.53 > -----+---------------------+-------- > (3 rows) > > Do you think there's any place for this in PostgreSQL? Perhaps it needs a > separate front-end tool. I hear pg_access has a report generator. If you want a text-based report generator, then you're seemingly on your own. -Peter -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden