Liquibase error [Postgresql]: unterminated dollar-quoted string at or near "$BODY$

Question

Liquibase error: unterminated dollar-quoted string at or near "$BODY$`

Chaneg log xml has one entry: see below include file="/home/dev/....../admin_script.sql"

content of the file:

...............
CREATE OR REPLACE FUNCTION my_schema.function-name()
RETURNS smallint AS
$BODY$
   DECLARE
      v_next_gen_id smallint := 0;
   BEGIN
..........

Exception:

liquibase.exception.DatabaseException: Error executing SQL CREATE OR REPLACE FUNCTION function name()
ETURNS smallint AS
$BODY$
   DECLARE
      v_next_gen_id smallint := 0: ERROR: unterminated dollar-quoted string at or near "$BODY$

Appreciate any help to resolve this

Answer 1

I just encountered the same issue days ago.

It does not work if we add the changeset into changelog.xml file using the format below:
<include file="path/to/sqlfile" />

To work around, I use another format:

<changeSet author="authour_name" id="your_id">
    <sqlFile path="path/to/sqlfile" splitStatements="false"/>
</changeSet>

Here is the link which gives a brief explanation to Problem with dollar-quoted-in-postgresql.

Answer 2

Use <createProcedure> tag instead of <sql> in your <changeSet> definition

Answer 3

The solution below is from official Liquibase forum.

If you want to use SQL changelog fully, this solution works fine (tested and confirmed):

If you are using SQL Changelog file then you can replace $ Sign with Single Quote and Single Quote with double Single Quote ''

So to elaborate:

1. $ will be '
2. $BODY$ will be '
3. ' will be ''

Example:

CREATE OR REPLACE FUNCTION public.testingfunctions()
RETURNS TABLE("DistributorCode" character varying)
LANGUAGE plpgsql
AS '
begin
    RETURN QUERY
    select * from testtable;
    
    -- .
    -- .
    -- . somewhere in the function
    RAISE NOTICE ''OPEN deleted customer_avatar'';
END;'

Answer 4

Add splitStatements:false to changeset

Like this --changeset splitStatements:false