Why I have DOLLAR QUOTE error in console when i run tests and not when i run the function on the workbench?

Question

I have a problem with my postgres function. I' ve created a function to copy data from db to csv.

I' ve tried to run the function on the workbench and there aren't errors, but when i run tests i have dollar quoting error.

This is the function:

--------- the @DELIMITER / is necessary in order to sent sql statement 
with mybatis...
-- @DELIMITER /
/
CREATE OR REPLACE FUNCTION save_audit_deletions(IN days numeric, IN table_name text) 
RETURNS void AS
$body$
DECLARE
   interval INT;
   statement_copy text;
   statement_count text;
   copied_rows INTEGER; --number of rows copied by COPY
   backup_rows INTEGER; --number of rows that COPY needs to copy into csv
BEGIN

    UPDATE table_name SET backup = 1 WHERE backup = 0 AND creationdate >= now()::DATE - days AND creationdate < now()::DATE;

    statement_copy := 'COPY (SELECT * FROM table_name WHERE backup = 1) TO ''/var/audiobays/logs/audit/' || table_name || '_deletions_(' || date-days|| ').csv'' CSV DELIMITER '','' HEADER;';
    execute statement_copy 
    into copied_rows;

    statement_count := 'SELECT COUNT (*) FROM table_name WHERE backup = 1';
    execute statement_count 
    into backup_rows;

    IF copied_rows = backup_rows THEN
        DELETE FROM table_name WHERE backup = 1;
    ELSE
        UPDATE table_name SET backup = 0 WHERE backup = 1;
    END IF;
END
$body$
LANGUAGE plpgsql;
/
-- @DELIMITER ;     

And this is the error.

Error executing: CREATE OR REPLACE FUNCTION save_audit_deletions(IN days numeric, IN table_name text) 
RETURNS void AS
$body$
DECLARE
   interval INT;
   statement_copy text;
   statement_count text;
   copied_rows INTEGER; --number of rows copied by COPY
   backup_rows INTEGER; --number of rows that COPY needs to copy into csv
BEGIN
    UPDATE table_name SET backup = 1 WHERE backup = 0 AND creationdate >= now()::DATE - days AND creationdate < now()::DATE;
   statement_copy := 'COPY (SELECT * FROM table_name WHERE backup = 1) TO ''/var/audiobays/logs/audit
.  Cause: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 104 in SQL CREATE OR REPLACE FUNCTION 


. Expected terminating $$
Error executing:   execute statement_copy 


.  Cause: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 328 in SQL   execute statement_copy 

. Expected terminating $$

Answer 1

Whatever it is that interprets the @DELIMITER /, it probably gets confused by /var/audiobays/logs/audit/ which contains slashes.

Try with a delimiter like ` that does not appear in the function body.