How to create a path for copy function in postgres?

Question

I have a problem with my COPY on sqlWorkbench. I need to do a copy function from db TO a path that I create at the moment because is different for each table. I have this error ' ERROR: syntax error at or near "||"'

I have created the path with the concat operator but i have the error at ||

CREATE OR REPLACE FUNCTION copy_func(IN table_name text, IN days numeric) 
RETURNS void AS 
$body$ 
DECLARE    

BEGIN        

 COPY (SELECT * FROM table_name WHERE backup = 1) TO '/var/audiobays/logs/audit/' || table_name || '_deletions_(' || date-days|| ').csv' CSV DELIMITER ',' HEADER; 
END 
$body$ 
LANGUAGE plpgsql;

sticky bit · Accepted Answer · 2019-08-05 10:28:54Z

2

COPY can only take string literals for the path. If you need a dynamic path you can use dynamic SQL to build and execute the statement.

DECLARE    
  statement text;
BEGIN        
  statement := 'COPY (SELECT * FROM table_name WHERE backup = 1) TO ''/var/audiobays/logs/audit/' || table_name || '_deletions_(' || date-days|| ').csv'' CSV DELIMITER '','' HEADER;';
  EXCUTE statement;  
END;

answered Aug 5, 2019 at 10:28

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a path for copy function in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related