Some (obvious) ideas can struck you when you are just sitting around at the airport or so. This is exactly what happened to me yesterday in Berlin.
Table of Contents
In some cases it can be quite handy to dump a (reasonably) small database, edit it with vi and use replay it. As a passionate (and fundamentalist) user of Vim I am always interested in finding new tweaks to make my daily work even more efficient.
Let us start with a simple table:
|
1 2 3 4 |
test=# CREATE TABLE t_test (a int, b int); CREATE TABLE test=# INSERT INTO t_test VALUES (1, 2), (3, 4); INSERT 0 2 |
We can start vim now and fetch the data directly from PostgreSQL. The ! command will add arbitrary shell output to your current document. It works like this:
To start vim we use: vim /tmp/output.txt
Inside vim we can now use a : to get started:
|
1 |
:.!psql -c "COPY t_test TO stdout;" test |
The easiest way to get output is to run COPY and send the data to standard output. To execute COPY we have to call psql and use -c to pass a shell command to it.
Your vim document will now contain those two rows we have added before.
Of course, typing this all over again is somewhat boring. To do this in a fancier way, we can come up with a vim function like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
function PSQLCopyTable( table, ... ) " The following is needed to obtain the possibility to " manipulate the querystring in the function let table = a:table " Conditional varaiables let database = '' let clear = 0 if ( len(a:000) >= 1 ) let database = ' -d '.a:1 endif if ( len(a:000) == 2 ) let clear = a:2 endif " Store the query result in a register let result = system('psql -c "COPY '.table.' TO stdout;" '. database) let pos = 0 " Clear file if clear == 1 :%d endif for row in split(result, "n") execute append(pos, row) let pos += 1 endfor endfunction |
Put this into .vimrc.
Calling the vim macro is also pretty simple:
|
1 |
:call PSQLCopyTable( 't_test', 'test', 1) |
The content of the table will appear in your document.
Writing those changes back to the DB is a little bit more complicated. If you want to read more, we have another blog for you:
PostgreSQL Vim integration: Finally …
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
