From PostgreSQL directly to Vim

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.

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.

Importing data from PostgreSQL to vim

Let us start with a simple table:

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:

:.!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:

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:

:call PSQLCopyTable( 't_test', 'test', 1)

The content of the table will appear in your document.

Writing back to PostgreSQL

Writing those changes back to the DB is a little bit more complicated. Have a look at another blogpost for more details.