Skip to content

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:

----- begin -----
#!/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 "$_";
----- end -----

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:

----- begin example -----
-- no linebreaks
\copy (
SELECT 1 AS column1,
2 AS column2,
3 AS column3
TO '/tmp/output-file.csv'
-- / no linebreaks
----- end example -----

will result in the following (valid) psql input:

----- begin psql input -----

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

----- end psql input ------

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 | | psql <options>

That's all, programmer happy, psql happy.


No Trackbacks


Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options