I was going over the Mapbender database structure, to write an exporter, and I found postgres' psql a little limiting when editing long queries or displaying large result sets. I am using a Netbook which can only display about 80 columns before wrapping, which makes it difficult to match cells with their column header on long lines.
The two other ways that came to my mind that could make editing and displaying large SQL statements and results a little easier where pgadmin and phpPgAdmin.
I found pgadmin to be slow though, and phppgadmin is webbased so also not exactly what I want. They both require me to do alot of the clicking and the moving of the mouse - annoying.
Since I love Vim I looked a little and found a solution that I am happy with.
Open vim without giving a filename and type :set ft=sql . This set's the filetype to sql which gives you syntax highlighting.
Vim can not only write to a file but to stdout as well:
1: SELECT * FROM mb_user LIMIT 1; :%w !psql mapbender_trunk mapbender
% specifies that all lines (you could also replace % with . for just the current line) should be written to he stdin of the psql command. Note the space in front of the !.
The result is written to stdout and is not very readable yet.
Elinks is a command line webbrowser, which will display the result of the SQL command. To do this we modify the vim command like this:
:%w !psql mapbender_trunk mapbender | elinks
basically piping the output of psql to elinks. This looks a little nicer:
mb_user_id | mb_user_name | mb_user_password | mb_user_owner | mb_user_description | mb_user_log
------------+--------------+----------------------------------+---------------+---------------------+------------
10 | test5 | 8e64d17ac104113a5dd53233a764aebb | 1 | lala |
(1 Row)
Scroll left and right with [ and ]
For some added comfort you can try these little tricks.
When in elinks pressing q will pop up a dialog asking you if you really want to quit. If this annoys you, you can add the following line to your elinks.conf to quit immediately:
bind "main" "q" = "really-quit"
After you quit elinks, you won't immediately return to vim, instead it will display Press ENTER or type command to continue . To get rid of this message modify the vim command like so
:silent%w !psql mapbender_trunk mapbender | elinks
To get be able to also see errors in Elinks modify the command line like this:
:silent%w !psql mapbender_trunk mapbender 2>&1| elinks
Comments? Questions? Tell me: karim+blog@malhas.de