Liquibase PSQL Create Function Fails with Trivial Conditional

1

Reposting my question from stack overflow for visibility.

This statement works without issue when I run liquibase update (notice that the conditional is commented out).

--liquibase formatted sql
--changeset user.me:1
--rollback DROP FUNCTION IF EXISTS cmp_app.simple_test_conditional;
CREATE
OR REPLACE FUNCTION cmp_app.simple_test_conditional () RETURNS TRIGGER AS $
BEGIN
  -- IF 1 > 0 THEN
    RAISE EXCEPTION 'ERROR';
  -- END IF;
RETURN OLD;
END
$ LANGUAGE plpgsql;

However, when I rollback and uncomment those two lines and attempt to run the update again as follows:

--liquibase formatted sql
--changeset user.me:1
--rollback DROP FUNCTION IF EXISTS cmp_app.simple_test_conditional;
CREATE
OR REPLACE FUNCTION cmp_app.simple_test_conditional () RETURNS TRIGGER AS $
BEGIN
  IF 1 > 0 THEN
    RAISE EXCEPTION 'ERROR';
  END IF;
RETURN OLD;
END
$ LANGUAGE plpgsql;

I am met with this error:

liquibase  | Unexpected error running Liquibase: Migration failed for changeset changelogs/20231130-004-widget-pre-delete-check.sql::1::user.me:
liquibase  |      Reason: liquibase.exception.DatabaseException: Unterminated dollar quote started at position 81 in SQL CREATE
liquibase  | OR REPLACE FUNCTION cmp_app.simple_test_conditional () RETURNS TRIGGER AS $
liquibase  | BEGIN
liquibase  |   IF 1 > 0 THEN
liquibase  |     RAISE EXCEPTION 'ERROR';
liquibase  |   END IF. Expected terminating $ [Failed SQL: (0) CREATE
liquibase  | OR REPLACE FUNCTION cmp_app.simple_test_conditional () RETURNS TRIGGER AS $
liquibase  | BEGIN
liquibase  |   IF 1 > 0 THEN
liquibase  |     RAISE EXCEPTION 'ERROR';
liquibase  |   END IF]
liquibase  | 
liquibase  | For more information, please use the --log-level flag
liquibase  | 
liquibase exited with code 1

You may have noticed that the ; is omitted after the END and it actually worked, even though that would ; would be required when applied directly to a PostgreSQL database.

I am not sure why but I am assuming that it something to do with the way Liquibase parses the SQL.

Since I noticed this semicolon issue from previous function declarations, I experimented with many combinations of semi-colons to try and see if that makes a difference. Different semi colon locations produce slightly different but essentially the same error messages, but I believe I have tried nearly all if not all of the 32 possible combinations of the 5 semicolons and to no avail.

This is my best attempt to frame the issue as simply as possible.

Version Info

Both my database and my liquibase are running in docker containers.

This was the result of running SELECT VERSION(); in my pgAdmin PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

This was the result of running liquibase --version in my docker compose file. liquibase | Liquibase Version: 4.23.0 liquibase | Liquibase Open Source 4.23.0 by Liquibase