News & Blog back

Subscribe

Global shortcuts and PostgreSQL queries.

Using your favorite hotkeys on queries in Linux

One of my colleagues often talks about using hot keys for his favourite SQL queries and commands in iterm2 (e.g. for checking current activity or to view lists of largest tables). 

Usually, I listen to this with only half an ear, because iterm2 is available only for MacOS and I am a strong Linux user. Once this topic came up again I thought perhaps this function could be realised not only through iterm2 but through an alternative tool or settings in desktop environment.

Due to me being an old KDE user, I opened “System settings” and began to check all settings related to keyboard, input, hotkeys and so on. What I have found is number of settings that allow to emulate text input from the keyboard. Using this feature I configured bindings for my favourite queries. So now, for executing needed query I don’t need to search it in my queries collection, copy and paste… I just press hotkey and got it in active window, it can be psql console, work chat, text editor or something else.

Here is how these bindings are configured:
Open “System settings” application and go to “Shortcuts”. There is “Custom Shortcuts” menu. Here, optionally, we should create a dedicated group for our shortcuts. I named my group “PostgreSQL hot queries”. When creating a shortcut, select “Global shortcut” and next “Send Keyboard Input”.

Now, we need to setup a new shortcut, give it a name and description. And here is the most interesting part. From the different Linux users, sometimes I’ve heard that KDE must have been written by aliens, and that statement has not been completely clear for me, until this moment since I never had serious issues with KDE. Now, after configuring the shortcuts, I tend to agree with this statement more and more.

Ok, here we go, next we should type text of a query which should appear when hotkey will be pressed. So, instead of plain query text you have to input alien sequences of symbols.

Check out the screenshot with example of query that should show current activity from pg_stat_activity.

Ok, when I typed in the first query, I understood that I might have to spent a whole day inputting other queries. I took sed and made one-liner that reads query text and replaces most often occurences of symbols’ sequences. But even this sed one-liner is not perfect and I had to fix some errors in the translated query.

sed -e "s/([a-zA-Z0-9',./=-]{1})/1:/g" -e 's/::/Shift+;:Shift+;:/g' -e 's/ /Space:/g' -e 's/(/Shift+9:/g' -e 's/~/Shift+`:/g' -e 's/)/Shift+0:/g' -e 's/_/Shift+-:/g' -e 's/^/Shift+6:/g' -e 's/$/Enter:/g' -e 's/||/Shift+\:Shift+\:/g' -e 's/*/Shift+8:/g' -e 's/</Shift+,:/g' -e 's/>/Shift+.:/g' filename

Huh, it looks like avada kedavra but it works in most cases and with sed and seldom tiny edits I managed to configure hotkeys for my favourite queries.

Of course, we can discuss the convenience of shortcuts’ configuration, but, unfortunately, that’s what we have available at the moment. Perhaps in future versions of KDE it will be possible to specify source file with query instead of typing mutant sequences of symbols.

What I thought may be useful is an example with configured shortcuts that you can use for importing instead of typing (you should activate them after import). But I’m pretty sure you have your own favourite queries so you may want to add them, so be ready to spent some time to configure your own hotkeys. In case of using my settings you may need to change hotkeys combinations for your preferences (I used Win+1..0 combinations).

I hope this KDE feature will be useful and convenient for you.

You may also like: