Christ has risen from the dead! And so shall we all. I wish you the best for Easter! And of course, as the tradition dictates, I will pray a decade of Rosary for all readers (of both of my blogs).
Happy Easter, rejoice!
(Note: I published this a day later because I had some issue with my computer which I only managed to fix today – it was Easter, after all, I had more important things to do than to play around with a broken laptop! I decided to leave the yesterday’s date of the post, though.)
It is a fairly common opinion that a function should not be larger than your screen. The reality, though, is often different. And even if your functions are shorter, you may want to see more than one at a time. The problem is that our screen are usually not that high. (My laptop gives me 66 lines of text with normal font settings.) You can have an external monitor rotated vertically (I have that at work to see as much of the logs of the application I’m working on as possible), but Emacs gives us another solution – the Follow mode. If you split your window into two (or more) side-by-side windows (for example, using C-x 3) and say M-x follow-mode, the other window will start displaying the “next portion” of the current buffer – that is, its top line will be the line just below the bottom line of the current window. Whenever you scroll either window, the other window follows, so both windows always show two adjacent parts of the current buffer. What’s more, when you go to the bottom line of the current window and press C-n to get to the next line, Emacs automatically switches to the other window without scrolling. This means that the two windows combined will act as one big “virtual window”.
There is also the follow-delete-other-windows-and-split command, which does exactly whet it says on the tin.
It turns out that Follow mode is even better than that! It seems to automatically detect all windows showing the buffer with Follow mode enabled, so if you activate it first and split the window after that, it also works. And it is not restricted to two windows! Try hitting C-x 3 twice (to get three windows) and using Follow mode then – you’ll have three windows following each other. (By the way, if you start with a single window and press C-x 3 twice, you’ll end up with two windows occupying 25% of your Emacs frame’s width and one having 50%. You can remedy that with M-x balance windows. It is smart enough to work reasonably even if you have some windows split vertically and some horizontally. There is also M-x
balance-windows-area, which resizes all windows so that their area is the same – try e.g. C-x 1 C-x 2 C-x 3 M-x balance-windows-area to see how it works!).
The last thing I’d like to mention in my paean about Follow mode is that when you want to view the file instead of editing it, it may be a good idea to also turn on Scroll lock mode, where keys used to move the point vertically (like the up and down arrows) scroll the buffer instead.
And that’s it for today – see you next time!
As I mentioned many times, I am a PostgreSQL fan. I wrote several times about psql, the default PostgreSQL terminal client. A few days ago I gave a short talk in my company about the virtues of psql, and in preparation for that I looked at its manual (again). As is often the case, I discovered a few hidden gems I didn’t know about.
psql has quite a few “backslash commands” or “metacommands”. Many of them operate on the “current query buffer”, which means what you have typed so far but haven’t yet sent to the server for execution. For example, if you type this:
select * from my_table-!-
where -!- means the cursor, and then type \g followed by Enter, the metacommand \g will treat the select * from table as its input. However, if the current query buffer is empty, \g will use the previous query.
Since \g basically just sends its input to the server, you can use it instead of the semicolon to end your query. It is more capable than that, though. For example, you can follow it by a filename, and then it will output the result of your query to that file. Alternatively, you can use a pipe symbol followed by a shell command to pass the result to the given command. And if that were not enough, you can follow \g by a list of options and their values in parenthesis. This way, the query will be run with those options set to the given values, as if you invoked \pset before, but the options will only be valid for this single query. For instance, you can generate a file with a LaTeX table, without the annoying vertical lines, and see its first few lines with this invocation:
\g (format=latex border=0) |tee /tmp/table.tex | head -n2
Now, if you remember my troubles with \copy and multiline SQL statements, here is a much simpler way to generate a CSV file from the result of a (possibly multiline) select statement:
select * from my_table \g (format=csv) my-table.csv
And you can also invoke \g after you successfully ran the select and saw that its results are exactly what you want.
Another trick I learned (or relearned) is the \! metacommand. It allows you to run any shell command without leaving psql. For example, if you want to check the current directory, you can say \!
pwd. You can also type \! without any argument, which then runs an interactive shell; when you exit the shell, you get back to your psql prompt.
There is also the \set command, which – surprise! – sets (psql) variables. These variables are a very useful concept. For example, assume that you have some uuid you use all the time, say 9e7c2a24-42bb-4c1c-9479-933ec8d30fd1. Instead of copying and pasting it time and time again, you can say \set uuid_var
'9e7c2a24-42bb-4c1c-9479-933ec8d30fd1' and then use it like this:
select * from some_table where some_uuid_column = :'uuid_var';
to get the data about the row with the given uuid. (You can also have a column or table name in some variable and then say things like :"column_var".)
But it gets better! Oftentimes the value(s) you need can be read from some table. For example, let’s assume that you have a user table with columns like user_id, user_full_name or user_email. If you need to query some other tables using (for example) one user’s id and email, you can put them into variables using the \gset metacommand.
select user_id, user_email as email from "user" where user_full_name = 'John Doe' \gset
and you’ll set the variables user_id and email to the uuid and email of John Doe. (In order for this to work, the query must return exactly one row.) And if you do this a lot, you might want to know that the \gset metacommand takes one optional “prefix” parameter, so \gset p_ will prepend p_ to the names of the variables it creates.
The last thing I’d like to mention is one of the most complicated psql metacommands, \crosstabview. Assume that you have a query that displays e.g. some counts, broken down by two variables. Let’s take an example from Wikipedia:
select 'male' as sex, 'right' as handedness, 43 as count union select 'male' as sex, 'left' as handedness, 9 as count union select 'female' as sex, 'right' as handedness, 44 as count union select 'female' as sex, 'left' as handedness, 4 as count
If you just evaluate it using \g (or a semicolon), you get this table:
sex | handedness | count --------+------------+------- male | left | 9 female | left | 4 male | right | 43 female | right | 44 (4 rows)
But if you use \crosstabview instead of \g, you get the following contingency table:
sex | left | right --------+------+------- male | 9 | 43 female | 4 | 44 (2 rows)
How cool is that‽ Head on to the manual to learn the details (the argumentless invocation of \crosstabview works if the query result has exactly three columns – if it has more, you need to tell psql which column to use as the vertical and horizontal headers and the output).
That’s it for today. Happy querying!