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

Baptistry

Possible duplicate of ERROR: unterminated quoted string at or near – Verbiage

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.

Eulogia

In case you don't want to define an absolute path to your sql script file, you can use attribute: relativeToChangelogFile="true" – Alonaalone

So the key point is having splitStatements="false", why do I even need to split statements. False should be default. – Polypary

"splitStatements": false in Liquibase's changelog can help avoid issues with unterminated dollar quotes or other syntax errors in SQL statements, by allowing Liquibase to read the entire statement as a single unit instead of splitting it into separate statements based on semicolons. This can be particularly useful for complex SQL statements or stored procedures that use advanced features like dollar-quoted strings or nested statements. – Circadian

Answer 2

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;'

Geniegenii

This is the correct answer. See forum.liquibase.org/t/unterminated-dollar-quote-started/4553/3 – Milla

What is the reason why this works? – Spittoon

@Spittoon It seems its related with dollar-quoting when we are talking about postgresql. For some kind of reason using $$/$BODY$ does not work properly with liquibase (im not sure are they preprocessing anything before feeding sql into the database) – Geniegenii

Thanks for that solution works fine! One word of caution if you just do search & replace LANGUAGE 'plpgsql' can also have single quotes and e.g. DEFAULT ''::character in the header. so the 3. above only applies to the PLPGSQL/SQL block between the BEGIN & END ;) – Pentheam

Answer 3

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

Uniform

Can explain why your solution works in order raise the value of your contribution? – Hydroscope

Answer 4

Add splitStatements:false to changeset

Like this --changeset splitStatements:false

Langur

This works for the change but not for the rollback. – Auricular

Answer 5

You can set splitStatements attribute to false to avoid this error

<changeSet author="authour_name" id="your_id">
    <sql splitStatements="false">
    ...
    </sql>
</changeSet>

Harmonic

Answer 6

I've encountered similar error while using sql-maven-plugin. Adding below attributes fixed the issue

<execution>
    ...
    <configuration>
        ...
        <delimiter>/</delimiter>
        <delimiterType>normal</delimiterType>
    </configuration>
</execution>

Apical

Answer 7

You should understand why this happened. The typical anonynous plsql-block contains many semicolons. Each semicolon by default is recognized by liquibase as a delimiter of a different SQL-statement in one transaction.

So, the database received 1-st query:

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

This statement has no closing $BODY$ token. And we have an exception from DB.

The right way is setup changeset do not split statement by any delimiter. Just set splitStatements flag in chageset declaration. For example:

--liquibase formatted sql
--changeset Author-BB:20231101-001 failOnError:true splitStatements:false

Decameter

Answer 8

I have postgresSQL15 in my system and running trigger with liquibase sql script

Table name: test

Table name history: test_history

1:- Create you history table

--changeset praveen.singh:1

   CREATE TABLE IF NOT EXISTS test_history
(
    id serial primary key,
    original_test_id BIGINT NOT NULL,
    operation VARCHAR(10),
    change_timestamp TIMESTAMP without time zone DEFAULT CURRENT_TIMESTAMP,
  
    dummy_data VARCHAR(15) NOT NULL
);

--rollback drop table test_history;

--changeset praveen.singh:2 failOnError:true splitStatements:false

CREATE OR REPLACE FUNCTION test_history_trigger()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO test_history (original_test_id, operation, change_timestamp, dummy_data)
        VALUES (OLD.id, 'UPDATE', NOW(), OLD. dummy_data);
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO test_history (original_test_id, operation, change_timestamp, dummy_data)
        VALUES (OLD.id, 'DELETE', NOW(), OLD. dummy_data);
    END IF;
    IF (TG_OP = 'UPDATE') THEN
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        RETURN OLD;
    END IF;
END;
$$;


CREATE TRIGGER test_history_trigger
AFTER UPDATE OR DELETE
ON test
FOR EACH ROW
EXECUTE FUNCTION test_history_trigger();

2:- Run command liquibase

mvn liquibase:update -Dusername=<USER_NAME> -Dpassword= -Durl='jdbc:postgresql://localhost:5432/<DB_NAME>?currentSchema=<SCHEMA_NAME>'

Hope it will work for you all!!!

Natishanative

The --changeset [...] splitStatements:false is the key here which will allow $$ or $BODY$ to be used as delimiters. This answer already mentioned that though. – Iliad

Answer 9

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

Czarevitch

Answer 10

I had the same error. Replacing the $function$ and $body$ with $$ solved the problem.

Barouche