Liquibase Rollback Error for Multi-Line Rollback Script

Liquibase Development
1

Hi,
I’m getting an error when I attempt to roll back this sample script (below).  Is this the correct way to format multi-line rollbacks?  We’ve played with splitStatements, but no luck.  Next we’ll try to use XML, but would love to stick with this format.

Thanks in advance for any help!
Rich

Here is the error:
[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.2.0:rollback (default-cli) on project database: Error setting up or running Liquibase: liquibase.exception.RollbackFailedException: Error executing custom SQL [CREATE OR REPLACE FUNCTION a_number() RETURNS integer AS $$
[ERROR] BEGIN
[ERROR] RETURN 1]: Error executing SQL CREATE OR REPLACE FUNCTION a_number() RETURNS integer AS $$
[ERROR] BEGIN
[ERROR] RETURN 1: ERROR: unterminated dollar-quoted string at or near “$$
[ERROR] BEGIN
[ERROR] RETURN 1”
[ERROR] Position: 58
[ERROR] -> [Help 1]
[ERROR]

[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]

[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

– Start Liquibase Script
–liquibase formatted sql
–changeset rhanbidge :foo splitStatements:false

CREATE OR REPLACE FUNCTION a_number() RETURNS integer AS $$
BEGIN
    RETURN 2;
END
$$ LANGUAGE plpgsql;

–rollback CREATE OR REPLACE FUNCTION a_number() RETURNS integer AS $$
–rollback BEGIN
–rollback     RETURN 1;
–rollback END
–rollback $$ LANGUAGE plpgsql;

2

We figured out a workaround, which is to put the entire create function code on one line, in one “rollback” statement.  Ugly, but it works.  :slight_smile:

–rollback CREATE OR REPLACE FUNCTION a_number() RETURNS integer AS $$ BEGIN RETURN 1; END  $$ LANGUAGE plpgsql;

Any other suggestions welcome!
Rich

3

I don’t think you can avoid the statement-splitting in formatted sql rollback currently.  I created https://liquibase.jira.com/browse/CORE-1984 to track the fix.

Nathan

4

Thanks Nathan!