Postgres: \copy syntax error in .sql file

Question

I'm trying to write a script that copies data from a crosstab query to a .csv file in Postgres 8.4. I am able to run the command in the psql command line but when I put the command in a file and run it using the -f option, I get a syntax error.

Here's an example of what I'm looking at (from this great answer):

CREATE TEMP TABLE t (
  section   text
 ,status    text
 ,ct        integer 
);

INSERT INTO t VALUES
 ('A', 'Active', 1), ('A', 'Inactive', 2)
,('B', 'Active', 4), ('B', 'Inactive', 5)
                   , ('C', 'Inactive', 7);

\copy (
SELECT * FROM crosstab(
       'SELECT section, status, ct
        FROM   t
        ORDER  BY 1,2' 
       ,$$VALUES ('Active'::text), ('Inactive')$$)
AS ct ("Section" text, "Active" int, "Inactive" int)
) TO 'test.csv' HEADER CSV

I then run this and get the following syntax error:

$ psql [system specific] -f copy_test.sql
CREATE TABLE
INSERT 0 5
psql:copy_test.sql:12: \copy: parse error at end of line
psql:copy_test.sql:19: ERROR:  syntax error at or near ")"
LINE 7: ) TO 'test.csv' HEADER CSV
        ^

A similar exercise doing just a simple query without crosstab works without incident.

What is causing the syntax error and how can I copy this table to a csv file using script file?

Answer 1

As with this answer, create a multi-line VIEW with a single-line \copy command, e.g.:

CREATE TEMP TABLE t (
  section   text
 ,status    text
 ,ct        integer 
);

INSERT INTO t VALUES
 ('A', 'Active', 1), ('A', 'Inactive', 2)
,('B', 'Active', 4), ('B', 'Inactive', 5)
                   , ('C', 'Inactive', 7);
CREATE TEMP VIEW v1 AS
  SELECT * FROM crosstab(
         'SELECT section, status, ct
          FROM   t
          ORDER  BY 1,2' 
         ,$$VALUES ('Active'::text), ('Inactive')$$)
  AS ct ("Section" text, "Active" int, "Inactive" int);

\copy (SELECT * FROM v1) TO 'test.csv' HEADER CSV

-- optional
DROP VIEW v1;