java - Exception in JPA when using seed file for PostgreSQL

Question

I am using Spring Boot 2 with JPA, and I leave it to Hibernate to create my database from my entities, which works fine. Now I want to add a data.sql file which will seed my database. I configured JPA as follows:

spring.jpa.properties.hibernate.hbm2ddl.import_files=data.sql

However I have a problem when executing the seed SQL. In the file I have defined a couple of functions, and at the end I am executing them:

CREATE OR REPLACE FUNCTION insert_timeout_configuration() RETURNS bigint AS $$
  DECLARE created_id bigint;

  BEGIN
    INSERT INTO timeout_configuration (id, version, timeout)
    VALUES (nextval('my_sequence'), 0, 300)
    RETURNING id INTO created_id;
    return created_id;
  END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION insert_url_configuration() RETURNS bigint AS $$
  DECLARE created_id bigint;

  BEGIN
    INSERT INTO url_configuration (id, version, my_url)
    VALUES (nextval('my_sequence'), 0,'http://localhost:8080/')
    RETURNING id INTO created_id;
    return created_id;
  END;
$$ language plpgsql;

DO $$
      INSERT INTO global_configuration(id, version, name, timeout_configuration_id, url_configuration_id)
VALUES (nextval('my_sequence'), 0, 'My global config', insert_timeout_configuration(), insert_url_configuration());

-- do some other code 
END
$$;
drop function insert_timeout_configuration();
drop function insert_url_configuration();

If I execute the same code in a PostgreSQL console to read from the file it works fine. But if I run it via Spring, I keep getting the following:

org.postgresql.util.PSQLException: Unterminated dollar quote started at position 0 in SQL $$ language plpgsql. Expected terminating $$
    at org.postgresql.core.Parser.checkParsePosition(Parser.java:1273) ~    [postgresql-42.2.4.jar:42.2.4]
    at org.postgresql.core.Parser.parseSql(Parser.java:1172) ~[postgresql-    42.2.4.jar:42.2.4]
    at org.postgresql.core.Parser.replaceProcessing(Parser.java:1124) ~    [postgresql-42.2.4.jar:42.2.4]
at     org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:41) ~[postgresql-42.2.4.jar:42.2.4]
at org.postgresql.core.QueryExecutorBase.createQueryByKey(QueryExecutorBase.java:314) ~[postgresql-42.2.4.jar:42.2.4]
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:285) ~[postgresql-42.2.4.jar:42.2.4]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270) ~    [postgresql-42.2.4.jar:42.2.4]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266) ~    [postgresql-42.2.4.jar:42.2.4]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~    [HikariCP-2.7.9.jar:?]
at         com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~    [HikariCP-2.7.9.jar:?]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(Generat    ionTargetToDatabase.java:54) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    ... 33 more
[DEBUG] 2018-09-07 21:09:43.325 [main] SQL - CREATE OR REPLACE FUNCTION insert_url_configuration() RETURNS bigint AS $$
Hibernate: CREATE OR REPLACE FUNCTION insert_url_configuration() RETURNS bigint AS $$
[WARN ] 2018-09-07 21:09:43.325 [main] ExceptionHandlerLoggedImpl - GenerationTarget encountered exception accepting command : Error executing DDL 
via JDBC Statement

I am using PostgreSQL 9.5 and Spring Boot 2.0.3. I read that the delimiter $$ in the function definition cannot be parsed correctly, but I cannot find how to solve this. I tried instead of $$ to have it with simple '' and escape the single-quote everywhere, but that didn't work either.

Answer 1

The problem was not the syntax, because the syntax was perfectly working with flyway or directly in PostgreSQL CLI. The problem was with Hibernate, specifically with parsing the import file. The way Hibernate works is that it executes each expression from the files individually, not the whole content as a single expression. I tried to put all function definitions in one line and it worked, but it was not readable. So I found that there is a configuration for Hibernate to tell it that expressions can be multi-lined, but the $$ delimiter was still unrecognized when used in multi-line.

So the solution was to define the command with ' delimiter and then escape the single quotes where needed with an additional '.

The solution is to set the spring.jpa.properties.hibernate.hbm2ddl.import_files_sql_extractor to use org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor. MultipleLinesSqlCommandExtractor extracts the SQL expression from multiple lines, and stops when a semicolon is present. That is the end of the expression. By wrapping the body of the function in single-quote string, Hibernate will treat that wrapping as a single line.

data.sql

CREATE OR REPLACE FUNCTION insert_timeout_configuration() RETURNS bigint AS '
  DECLARE created_id bigint;

  BEGIN
    INSERT INTO timeout_configuration (id, version, timeout)
    VALUES (nextval(''my_sequence''), 0, 300)
    RETURNING id INTO created_id;
    return created_id;
  END;
' language plpgsql;

CREATE OR REPLACE FUNCTION insert_url_configuration() RETURNS bigint AS '
  DECLARE created_id bigint;

  BEGIN
    INSERT INTO url_configuration (id, version, my_url)
    VALUES (nextval(''my_sequence''), 0,''http://localhost:8080/'')
    RETURNING id INTO created_id;
    return created_id;
  END;
' language plpgsql;

DO '
      INSERT INTO global_configuration(id, version, name, timeout_configuration_id, url_configuration_id)
      VALUES (nextval(''my_sequence''), 0, ''My global config'', insert_timeout_configuration(), insert_url_configuration());

-- do some other code 
END
';
drop function insert_timeout_configuration();
drop function insert_url_configuration();

I have to always keep in mind to escape the single-quotes in the expressions, but now I can have a more human-readable seed file.