Single line input for psql copy command

I have the following problem:

A database server and another client machine. I have to execute several \copy commands in psql on the client machine to retrieve data and store the output in CSV files. The PostgreSQL COPY command is not an option because it stores the results on the database server, not the client machine. All \copy commands are executed from several files containing SQL commands, which are concated together by a Makefile and feeded into psql. So far so good.

My SELECT commands used in the \copy are a bit more complicated and \copy requires to have the entire command on a single line. That can be done in two ways:

• Write the entire command in a single line. But that’s hard to debug :-(
• Write the command as I like it and remove the linebreaks before feeding the command into psql

Since the files are processed by make anyway, it’s not a problem to do some preprocessing in the Makefile and remove the linkebreaks. Some lines of Perl and using the flip-flop operator is all you need for making \copy and psql happy:

 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
32

#!/usr/bin/perl -w
#
# remove linebreaks between markers to make \copy happy
#
# written by Andreas 'ads' Scherbaum
#
# description:
# remove linebreaks between start and stop markers
# the markers are:
# -- no linebreaks
# -- / no linebreaks
#
#
# history: v1.0 2008-10-08
# initial version

use strict;

# loop through stdin
while (<>) {
    # use the flip-flop operator to search for marker
    if (/\-\- no linebreaks/ .. /\-\- \/ no linebreaks/) {
        if (substr($_, 0, 2) eq '--') {
            # remove SQL coments
            $_ = '';
        } else {
            # remove linebreaks
            $_ =~ s/[\r\n]//gs;
        }
    }
    print "$_";
}

The Perl flip-flop operator .. searches for the first marker and if found for the second one. Every line between the first and second marker is processed in the inner code block. The script removes every SQL style comment (just single line comments) and any linebreak character. The result is a single line of text consisting of all lines between the start and stop marker. One example:

1
2
3
4
5
6

-- no linebreaks
\copy (SELECT 1 AS column1, 2 AS column2, 3 AS column3) TO '/tmp/output-file.csv' WITH CSV
 HEADER
 DELIMITER AS ';'
 QUOTE AS '"'
-- / no linebreaks

will result in the following (valid) psql input:

1

\copy (SELECT 1 AS column1, 2 AS column2, 3 AS column3) TO '/tmp/output-file.csv' WITH CSV HEADER DELIMITER AS ';' QUOTE AS '"'

Note: I removed all unnecessary spaces from the example output.

Last step is integrating the script into my Makefile:

cat file1.sql file2.sql file3.sql | remove_linebreaks.pl | psql <options> That’s all, developer happy, psql happy.