Spring Support $$ quotes in JDBC and R2DBC ScriptUtils

PostgreSQL (and maybe others?) support multi-line strings using $$ delimiters.

ScriptUtils.executeSqlScript does not support it, and will break at the next ;.

This makes it very difficult to get database initialization to work with function definitions like the following.

CREATE FUNCTION example() RETURNS text AS $$
DECLARE
    value text := 'Hello World!';
BEGIN
    RETURN value;
END;
$$ LANGUAGE plpgsql;

https://www.postgresql.org/docs/12/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

Comment From: sbrannen

Does the script you are executing contain anything other than that single CREATE FUNCTION statement?

Comment From: OrangeDog

@sbrannen yes, lots of other statements. You can do it with single quotes and escapes, but it can get very messy in more complex examples.

Comment From: snicoll

Shouldn't you configure a custom delimiter for this?

Comment From: OrangeDog

@snicoll how do you mean?

The script is standard valid PostgreSQL and shouldn't need to be changed. Ideally this should Just Work when used as schema.sql.

Comment From: mp911de

So far, Postgres (and derivatives), Vertica and Snowflake support double-dollar quoted strings. Database engines can afford a proper SQL parser engine as that is their purpose to some extent. Our script utils are here to help with standard use-cases across all databases and within a certain margin of that you need to tailor to your database.

The suggestion is to find a character you do not use otherwise and use it as a separator. Say you decided to go with | (pipe). Then rewrite your SQL file into the following form:

CREATE FUNCTION example() RETURNS text AS $$
DECLARE
    value text := 'Hello World!';
BEGIN
    RETURN value;
END;
$$ LANGUAGE plpgsql|

// next statement 

CREATE FUNCTION example() RETURNS text AS $$
…
$$ LANGUAGE plpgsql|

Note the statement blocks are delimited with a pipe. On ResourceDatabasePopulator, you configure setSeparator("|") to split the individual statements.

SQL databases come with too many variances in operators, escape chars, comment delimiters, and so on that we could reliably support all of them.

Comment From: snicoll

Thanks for the additional explanation Mark. I am going to close it for the reason above, I understand this would be ideal if that worked out-of-the-box but we can't justify adding the extra complexity.