vsql - pset options unavailable
Navin_C
Vertica Customer ✭
Hi,
I am trying to see my output of all columns in one row, when using vsql prompt.
Normally when we have more columns then the width of screen the records go in multiple lines.
With postgreSQL psql prompt, there is an option of setting the pager to wrapped style
\pset format wrapped
But when tried with vsql, it shows this :
nnani=>\pset format wrapped
\pset: allowed formats are unaligned, aligned, html, latex.
Is there any other way I can achieve this in vsql, I don't want the outpout in horizontal format, I want it in vertical format and all the columns should we fitted to width of screen.
Can we put a feature request if this option in not available
0
Comments
Hi Navin_C,
Unfortunately, vsql doesn't support this option.
However, there are workarounds. You can set the program vsql uses to display its output by using the PAGER environment variable. You can also change the output format around by using the \pset commands. Using these two features in conjunction can let you get some of the functionality you want.
Disclaimer: this isn't an official suggestion... this is just a hacky workaround I found. Use at your own risk.
For example, you can have vsql format its query output as HTML tables using \pset format html. There are some tools under Linux that can format HTML to be displayed as text on the terminal. One such program available for CentOS is html2text. If you have root access, you can install it using this command as root:
It seems pretty standard, so it should be available for other Linux flavors as well.
So, without tweaking, we have output like this:
Yuck.
The first step is setting the PAGER environment variable in the Linux shell to the html2text program:
(Personally, I wouldn't hard-code this environment variable anyplace. I believe PAGER is used by other Linux commands as well, such as man. Instead I would just manually set it before running vsql, or set it in a script that calls vsql.)
Then run vsql and set the output format to HTML. I also set the pager setting to always, to force vsql to always pipe the output through the pager, even if it thinks it won't scroll off the screen.
[dbadmin@docd01 ~]$ vsql
Password:
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
dbadmin=> \pset format html
Output format is html.
dbadmin=> \pset pager always
Pager is always used.
dbadmin=> select * from messages limit 10;
_____________________________________________________________________________
|messageid|userid|___________________________text_________|_______timestamp___|
| 1| 78542|sociis orci Curae nulla congue Curae |2015-04-02 00:00:00|
| | |Lorem orci vitae Proin eros a | |
|_________|______|consectetuer_torquent_felis_____________|___________________|
| 2| 10364|pede vel Sed nec adipiscing ad Fusce boo|2015-04-02 00:00:00|
| | |tincidunt amet nascetur senectus nostra | |
|_________|______|neque_dolor_____________________________|___________________|
| 3| 76263|orci Vivamus lacinia Ut montes diam Duis|2015-04-02 00:00:00|
| | |nulla lorem elementum facilisi Aliquam | |
|_________|______|elit_sociis_venenatis_elit_libero_______|___________________|
| 4| 66561|quam risus mollis vestibulum rutrum |2015-04-02 00:00:00|
| | |neque Proin tortor mollis placerat | |
|_________|______|vulputate_amet__________________________|___________________|
| 5| 98308|facilisi erat cubilia commodo amet |2015-04-02 00:00:00|
| | |senectus felis justo Nullam pretium at | |
|_________|______|semper_diam_nulla_est_Sed_viverra_______|___________________|
| 6| 38230|sapien Phasellus ligula mattis ad libero|2015-04-02 00:00:00|
|_________|______|euismod_tellus_at_cubilia_congue________|___________________|
| 7| 93004|per dis luctus iaculis Praesent |2015-04-02 00:00:00|
|_________|______|Pellentesque_Aliquam____________________|___________________|
| 8| 77991|elit sociis ultrices pede Vivamus velit |2015-04-02 00:00:01|
| | |lobortis quam molestie porttitor non | |
|_________|______|elementum_natoque_______________________|___________________|
| 9| 86680|a nunc venenatis semper fermentum eros |2015-04-02 00:00:01|
| | |dis imperdiet nibh mattis massa pulvinar| |
|_________|______|ante_Donec_lectus_ad_mi_________________|___________________|
| 10| 75612|ut mi massa primis tincidunt tempus |2015-04-02 00:00:01|
|_________|______|Curae_ut_dui_in_Sed_____________________|___________________|
(10 rows)
(Edit: the code box ironically messes up code output... grrr.)
Much nicer output.
I've only tried this on Linux. I'm not sure whether it works with the vsql client on Windows, for example.
Oh, and also note that you can pipe html2text's output through a pager program such as more, so it pauses every screenful, like so:
$export PAGER='html2text | more'