This is a follow-up question from this answer for "Save PL/pgSQL output from PostgreSQL to a CSV file".
I need to write a client-side CSV file using psql's \copy command. A one liner works:
db=> \copy (select 1 AS foo) to 'bar.csv' csv header
COPY 1
However, I have long queries that span several lines. I don't need to show the query, as I can't seem to extend this past one line without a parse error:
db=> \copy (
\copy: parse error at end of line
db=> \copy ( \\
\copy: parse error at end of line
db=> \copy ("
\copy: parse error at end of line
db=> \copy "(
\copy: parse error at end of line
db=> \copy \\
\copy: parse error at end of line
Is it possible to use \copy with a query that spans multiple lines? I'm using psql on Windows.
The working solution I have right now is to create a temporary view, which can be declared over multiple lines, then select from it in the \copy command, which fits comfortably on one line.
db=> CREATE TEMP VIEW v1 AS
db-> SELECT i
db-> FROM generate_series(1, 2) AS i;
CREATE VIEW
db=> \cd /path/to/a/really/deep/directory/structure/on/client
db=> \copy (SELECT * FROM v1) TO 'out.csv' csv header
COPY 2
db=> DROP VIEW v1;
DROP VIEW