\COPY in plpgsql function

[Ellen P]

Hi there,

I've been trying to execute \COPY from within a plpsql function and keep getting an error. Any hints at where I'm going wrong would be great.

I'm using \COPY with a backslash because the database is owned by a different user to the file I'm writing to and COPY on its own gives a 'Permission denied' error

Thanks!

CREATE or REPLACE FUNCTION printentries() RETURNS VOID AS $$
DECLARE STATEMENT TEXT;
BEGIN
FOR Y IN 7..11 LOOP
DROP TABLE M1;
CREATE TEMP TABLE M1 AS
SELECT daynumber,startl,endl,numb
FROM batchentries
WHERE (EXTRACT('year' FROM date)=Y);
STATEMENT := E'\\COPY M1 TO \'/Users/ellen/my_data/batchentries' || Y || E'.txt\' DELIMITER \' \'; ';
EXECUTE STATEMENT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;


SELECT printentries();
ERROR: syntax error at or near "\"
LINE 1: \COPY M1 TO '/Users/ellen/my_data/batchent...
^
QUERY: \COPY M1 TO '/Users/ellen/my_data/batchentries7.txt' DELIMITER ' ';
CONTEXT: PL/pgSQL function "printentries" line 11 at EXECUTE statement