Spring Data JPA with PostgreSQL - Loading schema.sql that Includes a Function

Question

I am working on a Spring Boot JPA application that initializes the database using a schema.sql function. However, I am receiving an error when it encounters a function defintion:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #13 of URL [file:/C:/Users/Kelly%20Marchewa/workspace/Auth/auth/bin/main/schema.sql]: CREATE OR REPLACE FUNCTION admin.add_user(new_username VARCHAR, new_password VARCHAR, role_name VARCHAR) RETURNS int AS $$ DECLARE ret_role_id INTEGER; nested exception is org.postgresql.util.PSQLException: Unterminated dollar quote started at position 120 in SQL CREATE OR REPLACE FUNCTION admin.add_user(new_username VARCHAR, new_password VARCHAR, role_name VARCHAR) RETURNS int AS $$ DECLARE ret_role_id INTEGER. Expected terminating $$

The function definition:

CREATE OR REPLACE FUNCTION admin.add_user(new_username VARCHAR, new_password VARCHAR, role_name VARCHAR) RETURNS int AS $$
       DECLARE 
        ret_role_id INTEGER;
        ret_user_id INTEGER;
       BEGIN
       ret_role_id = (SELECT id FROM admin.role WHERE role_title = role_name);
       INSERT INTO admin.app_user (username, password) VALUES
        (new_username, crypt(new_password, gen_salt('bf', 8)));
    ret_user_id = (SELECT id FROM admin.app_user WHERE username = new_username);
    INSERT into admin.role(user_id, role_id) VALUES (ret_user_id, ret_role_id);
       RETURN ret_user_id;
        END;
$$ LANGUAGE plpgsql;

I can run execute this script just fine from the 'psql' CLI client. What do I have to change to make it work with a Spring Boot JPA application/the JDBC driver?

Thanks.

Simply remove $$ LANGUAGE plpgsql PostgreSQL will recognize the language. — Simon Martinelli, Dec 31 '18 at 17:40
@SimonMartinelli Removing $$ LANGUAGE plpgsql did not work I am afraid; I received the same error. Neither did removing that along with the $$ pair; I received `ERROR: syntax error at or near "DECLARE"`. Thanks. — KellyM, Dec 31 '18 at 17:50

score 1 · Answer 1 · answered Oct 20 '20 at 14:23

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

CREATE OR REPLACE FUNCTION admin.add_user(new_username VARCHAR, new_password VARCHAR, role_name VARCHAR) RETURNS int AS '
       DECLARE 
        ret_role_id INTEGER;
        ret_user_id INTEGER;
       BEGIN
       ret_role_id = (SELECT id FROM admin.role WHERE role_title = role_name);
       INSERT INTO admin.app_user (username, password) VALUES
        (new_username, crypt(new_password, gen_salt('bf', 8)));
    ret_user_id = (SELECT id FROM admin.app_user WHERE username = new_username);
    INSERT into admin.role(user_id, role_id) VALUES (ret_user_id, ret_role_id);
       RETURN ret_user_id;
        END;
' LANGUAGE plpgsql;

Spring Data JPA with PostgreSQL - Loading schema.sql that Includes a Function

1 Answers1