PSQLException unterminated dollar-quoted string at or near "$$ DECLARE while executing sql file from spring app

Question

I am getting error while executing a sql file name sample_script.sql from my java code. It is a spring application from where I am trying to run the script through java class ScriptRunner because I wanted to run the script automatically.

sample_script.sql:

CREATE OR REPLACE FUNCTION core.getValue (id bigint, field varchar) 
RETURNS text
AS $$
  DECLARE json_object json;
  DECLARE item json;
  DECLARE val text;
  BEGIN
    SELECT E.json::json into json_object from customer E where E.id = id;
    FOR item IN SELECT * FROM json_array_elements((json_object->>'name')::json)
    LOOP
        IF (item->>'data') = field 
        THEN
        val = (item->>'values')::json->>0;
        END IF;
    END LOOP;
    return val;
  END;
$$ LANGUAGE 'plpgsql';

It is running ok from PGAdmin but when I run this file from my spring project like following it is giving error.

Java Code segment:

ScriptRunner sr = new ScriptRunner(con, false, false);
Reader reader = new BufferedReader(new FileReader("sample_script.sql"));
sr.runScript(reader);

Error Log:

Error executing: CREATE OR REPLACE FUNCTION core.getValue (id bigint, field varchar)  RETURNS text AS $$   DECLARE json_object json 
org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string at or near "$$   DECLARE json_object json "
  Position: 91

Also while executing the script it is removing ; from the script like following:

CREATE OR REPLACE FUNCTION core.getValue (id bigint, field varchar)  RETURNS text AS $$   DECLARE json_object json 
  DECLARE item json 
  DECLARE val text 
  BEGIN     SELECT E.json::json into json_object from customer E where E.id = id 
    FOR item IN SELECT * FROM json_array_elements((json_object->>'name')::json)     LOOP         IF (item->>'data') = field          THEN         val = (item->>'values')::json->>0 
        END IF 
    END LOOP 
    return val 
  END 
$$ LANGUAGE 'plpgsql'

Can anyone explain why this is happening and how to resolve it?

Note: I have seen this but it does not help on this issue.

ScriptRunner probably doesn't know dollar quoting, and tries to split the script at each `;` resulting in invalid partial SQL statements. But you don't really need a "script runner" to begin with. Read the file into memory, then use `Statement.execute()` with the contents of the file. Or configure ScriptRunner to **not** split the statements in the file — , Oct 09 '18 at 09:04
Unrelated, but: the language name is an identifier. Do not put it in single quotes — , Oct 09 '18 at 09:05

score 0 · Accepted Answer · answered Oct 09 '18 at 09:55

Used Statement.execute() instead of ScriptRunner.

updated Java Code segment:

Connection con = sessionFactory.getSessionFactoryOptions().getServiceRegistry().getService(ConnectionProvider.class).getConnection();
BufferedReader in = new BufferedReader(new FileReader("sample_script.sql"));
LineNumberReader fileReader = new LineNumberReader(in);
String query = ScriptUtils.readScript(fileReader, ScriptUtils.DEFAULT_COMMENT_PREFIX, ScriptUtils.DEFAULT_STATEMENT_SEPARATOR);
Statement stmt = null;
stmt = con.createStatement();
stmt.execute(query);

PSQLException unterminated dollar-quoted string at or near "$$ DECLARE while executing sql file from spring app

1 Answers1