java - Using Apache ANT to programmatically execute sql script with plpgsql function fails

Question

I need to execute a SQL script programmatically. The script defines a function which upgrades the postgreSQL database depending on its version. It looks like this:

create or replace function update_auto_increment(version varchar(20)) returns integer
as $$
declare 
 v integer;
begin
 v := cast(version as int);
 if v < 590 then
 -- some upgrade code here
 v := 590;
 raise notice 'Updated base to version %', v;
 end if;
 return v;
end;
$$ language plpgsql;

select update_auto_increment(value) from info where name = 'version';

drop function update_auto_increment(varchar(20));

Then I prepare an ANT task and execute it:

SQLExec task = new SQLExec();
Project project = new Project();
project.init();
task.setProject(project);
task.setTaskType("sql");
task.setTaskName("sql");
task.setSrc(new File("updateScript.sql"));
task.setDriver(driver);
task.setPassword(password);
task.setUserid(username);
task.setUrl(connectionURL);
task.execute();

And the execution process fails when it encounters $$ :

org.postgresql.util.PSQLException: ERROR: syntax error (near: "$")
position: 91
at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:672)

(the error message was localized and I tried to translate it into English). So, the question is: how can I programmatically execute a SQL script with function definition in it?

Answer 1

Most probably SQLExec is splitting the statement by ; which is the default statement termination character in SQL.

In your case, that splitting should not happen.

You might try to use something like this:

task.setDelimiterType(SQLExec.DelimiterType.ROW);
task.setDelimiter("/");

and then separate your SQL statements in the file with a / on a single line:

create or replace function update_auto_increment(version varchar(20)) returns integer
as $$
declare 
 v integer;
begin
 v := cast(version as int);
 if v < 590 then
 -- some upgrade code here
 v := 590;
 raise notice 'Updated base to version %', v;
 end if;
 return v;
end;
$$ language plpgsql;
/

select update_auto_increment(value) from info where name = 'version'
/

drop function update_auto_increment(varchar(20))
/