By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 804 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

\COPY in plpgsql function

P: 1
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
Aug 16 '11 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.