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,start l,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.t xt' DELIMITER ' ';
CONTEXT: PL/pgSQL function "printentri es" line 11 at EXECUTE statement
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,start l,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.t xt' DELIMITER ' ';
CONTEXT: PL/pgSQL function "printentri es" line 11 at EXECUTE statement