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 remove_linebreaks.pl -----#!/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 stdinwhile (<>) { # 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 remove_linebreaks.pl -----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'WITH CSV
HEADER
DELIMITER AS ';'
QUOTE AS '"'-- / 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 | remove_linebreaks.pl | psql <options>That's all, programmer happy, psql happy.
Trackbacks
No Trackbacks
Comments
Display comments as Linear | Threaded