0

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 $$
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Michele Verriello
  • 123
  • 2
  • 15
  • 1
    `-- @DELIMITER /` does nothing in `psql` and which "workbench" are you talking about? –  Aug 26 '19 at 13:08
  • I'm talking about sql workbench/J. I' ve my code in a db_test.sql file. The project is in java – Michele Verriello Aug 26 '19 at 13:10
  • `-- @DELIMITER /` also does nothing in SQL Workbench/J (or for JDBC) - how do you run that statement from within your Java code? –  Aug 26 '19 at 13:12
  • I know. If you delete -- @DELIMITER / in the code. What is the error? Because I' ve a dollar quote error – Michele Verriello Aug 26 '19 at 13:13
  • I run the function with running the tests. When I run a test with jUnit I have the error – Michele Verriello Aug 26 '19 at 13:15
  • So how exactly do you "run the test with jUnit"? Please **[edit]** your question and show us the Java code that runs that script. –  Aug 26 '19 at 13:41
  • There isn' t a code that run the script, When I run a not specified test (a test between all the test) the script will run – Michele Verriello Aug 26 '19 at 14:23
  • There must be code to do that, otherwise you wouldn't get the error –  Aug 26 '19 at 14:24

1 Answers1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263