Unable to execute Script with SQL functions

[ghost]

I am using Jdbi 3.10.1 (and by the way: really loving it so far — so a big thank you).

When trying to load and execute a script from a file I found out that the parsing currently can't handle functions. Executing it directly via handle.execute("[…]"); works fine, though.

Loading and executing the script:

this.jdbi.useHandle(handle -> {
    handle.createScript(classpathSqlLocator.getResource(classLoader, "sql/functions.sql"))
          .execute();
});

Content of functions.sql:

CREATE OR REPLACE FUNCTION trigger_set_timestamp()
    RETURNS TRIGGER AS
$$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Will be parsed to:

1. CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
2. RETURN NEW
3. END
4. $$ LANGUAGE plpgsql

And of course fails with a org.postgresql.util.PSQLException:

Unterminated dollar quote started at position 74 in SQL CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'. Expected terminating $$

My current workaround is to not use files for adding functions.

[stevenschlansker]

Yeah, the Script parser is not very smart and tries to line-split out separate statements. But as a general piece of code, it does not understand Postgres $$ quoting spanning multiple lines. This would be a nice improvement to have, but as a workaround, just execute the creates as a statement on their own rather than part of a script. Or remove the line breaks from your function definition.