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

Possible duplicate of [ERROR: unterminated quoted string at or near](https://stackoverflow.com/questions/3499483/error-unterminated-quoted-string-at-or-near) — Andremoniy, May 22 '17 at 16:07

score 38 · Answer 1 · answered Mar 10 '16 at 04:34

38

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.

answered Mar 10 '16 at 04:34

Shaohua Huang

698
5
19

1

In case you don't want to define an absolute path to your sql script file, you can use attribute: `relativeToChangelogFile="true"` – Andrey Sarul Apr 16 '18 at 10:01
3

So the key point is having splitStatements="false", why do I even need to split statements. False should be default. – X.Y. Dec 07 '21 at 01:05
"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. – Shender Ramos Feb 22 '23 at 22:30

score 22 · Answer 2 · edited Jul 01 '21 at 09:24

22

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:

$ will be '
$BODY$ will be '
' 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;'

edited Jul 01 '21 at 09:24

Dmitriy Popov

2,150
3
25
34

answered Apr 19 '21 at 11:22

cool

3,225
3
33
58

4

This is the correct answer. See https://forum.liquibase.org/t/unterminated-dollar-quote-started/4553/3 – James Jun 18 '21 at 20:57
What is the reason why this works? – danbtl Nov 18 '21 at 21:21
@danbtl 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) – cool Nov 19 '21 at 13:45
1

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 ;) – Markus Feb 16 '23 at 10:30

score 17 · Answer 3 · edited Jun 15 '19 at 04:34

17

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

edited Jun 15 '19 at 04:34

yegor256

102,010
123
446
597

answered Nov 06 '17 at 13:54

Jiri

171
1
2

4

Can explain why your solution works in order raise the value of your contribution? – Noel Widmer Nov 06 '17 at 14:11

Mister1Burger · Answer 4 · 2022-06-07T04:49:28.640

12

Add splitStatements:false to changeset

Like this --changeset splitStatements:false

edited Jun 07 '22 at 04:49

answered Jun 07 '22 at 04:47

Mister1Burger

121
1
5

score 1 · Answer 5 · answered Oct 05 '22 at 11:34

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>

score 0 · Answer 6 · answered Apr 08 '21 at 10:20

0

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

answered Apr 08 '21 at 10:20

Sudhagar i

1

score 0 · Answer 7 · answered Jun 21 '23 at 13:48

0

You can set splitStatements attribute to false to avoid this error

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

answered Jun 21 '23 at 13:48

Yacine MEDDAH

1,211
13
17

score -3 · Answer 8 · edited Jul 01 '21 at 01:25

-3

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

edited Jul 01 '21 at 01:25

Dmitriy Popov

2,150
3
25
34

answered Aug 10 '20 at 10:27

Milton Isaya

3
3

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

8 Answers8

Linked