我正在尝试将所有的postrgres表导出到单独的csv文件中,为此我正在使用以下函数
CREATE OR REPLACE FUNCTION db_to_csv(path text)
RETURNS void AS
$BODY$
declare
tables RECORD;
statement TEXT;
begin
FOR tables IN
SELECT (table_schema || '.' || table_name) AS schema_table
FROM information_schema.tables t INNER JOIN information_schema.schemata s
ON s.schema_name = t.table_schema
WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'configuration')
ORDER BY schema_table
LOOP
statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';
EXECUTE statement;
END LOOP;
return;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION db_to_csv(text)
OWNER TO postgres;
但是当我调用该函数时,出现了如下错误:无法打开文件“/home/user/Documents/public.tablename.csv”,进行写入操作:权限被拒绝。
我尝试使用复制单个表格的方式。
COPY activities TO '/home/user/Documents/foldername/conversions/tablename.csv' DELIMITER ',' CSV HEADER;
它给了我以下错误提示:
ERROR: could not open file "/home/user/Documents/foldername/conversions/tablename.csv" for writing: Permission denied
********** Error **********
ERROR: could not open file "/home/user/Documents/foldername/conversions/tablename.csv" for writing: Permission denied
SQL state: 42501
任何解决此问题的建议。