copy语法 postgre,Postgres：.sql文件中的\copy语法错误

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?

解决方案

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;