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?

Off topic, but have you considered a database migration tool? Examples would be liquibase or flyway. — Mark O'Connor, May 02 '12 at 21:06

score 2 · Answer 1 · answered May 02 '12 at 16:02

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))
/

This didn't help. Error still occurs near $$ characters. – Andrew Sokolov May 03 '12 at 08:22 — Andrew Sokolov, May 03 '12 at 08:22

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

In addition to a_horse_with_no_name's answer, you will need to replace the $$ with the tag form $foo$ .

More on Dollar-Quoted Strings: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

edited May 23 '17 at 12:24

Community

1
1

answered Mar 04 '14 at 15:28

Danny Kirchmeier

1,134
6
14

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

2 Answers2

Linked