Spring ScriptUtils - Unterminated dollar quote in PostgreSQL

Question

I am using the following Java code to run SQL script:

@SpringBootApplication
public class Application implements CommandLineRunner {
  @Autowired
  DataSource ds;

  @Value("classpath:test.sql")
  Resource resource;

  public static void main(String[] args) {
    SpringApplication.run(Application.class, args);
  }

  @Override
  public void run(String... args) throws Exception {
    try {
      @Cleanup
      Connection c = ds.getConnection();
      ScriptUtils.executeSqlScript(c, resource);

    } catch (Exception e) {
      e.printStackTrace();
    }

  }

}

The Spring boot version I am using is 2.1.7.RELEASE and PostgreSQL is running in local docker image via:

docker run --rm   --name pg-docker -e POSTGRES_PASSWORD=PASSWORD -d -p 5432:5432 -v postgresql:/var/lib/postgresql/data  postgres:11.3

The SQL Script I am using is:

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
  total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql; 

Which in my opinion is perfectly valid and runs OK in PgAdmin.

Unfortunatelly I am receiving the following exception from Java:

Caused by: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 62 in SQL CREATE OR REPLACE FUNCTION totalRecords () RETURNS integer AS $total$ declare total integer. Expected terminating $$
    at org.postgresql.core.Parser.checkParsePosition(Parser.java:1274)
    at org.postgresql.core.Parser.parseSql(Parser.java:1173)
    at org.postgresql.core.Parser.replaceProcessing(Parser.java:1125)
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:41)
    at org.postgresql.core.QueryExecutorBase.createQueryByKey(QueryExecutorBase.java:314)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:289)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:274)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:269)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:488)
    ... 9 more

What is wrong ? How can I fix it please ? Or how to reliably run PostgreSQL script from Java if ScriptUtils is unable to do so ?

Pierce

Answer 1

Finally I was able to solve myself. Not sure if my solution is the best thought. The problem was in ScriptUtils - it determines end of statement by ScriptUtils.DEFAULT_STATEMENT_SEPARATOR which is ";". Unfortunatelly the FUNCTION command contains various ";" which are part of the statement itself. So the solution is to declare and use another separator which does not interfere with PostgreSQL syntax. In my case I used two semicolons - ";;". So my statement looks like:

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
  total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;; 

And Java code to call it is changed to:

ScriptUtils.executeSqlScript(c, new EncodedResource(resource, "UTF-8"), false, false, ScriptUtils.DEFAULT_COMMENT_PREFIX, ";;",
      ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER, ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER);

Pierce

Answer 2

As stated here, hibernate seems to be the problem when it parses. Replace $total$ with ' and it should work:

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS '
declare
  total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
' LANGUAGE plpgsql; 

Toot