CODEWITHSUNDEEP
javapostgresqlplpgsqlliquibase
Vibin
Vibin

Reputation: 619

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

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

Upvotes: 47

Views: 27645

Answers (10)

Prav_Singh
Prav_Singh

Reputation: 104

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!!!

Upvotes: 1

volkov_kv
volkov_kv

Reputation: 41

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

Upvotes: 1

spielbug
spielbug

Reputation: 1371

You can set splitStatements attribute to false to avoid this error

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

Upvotes: 4

Yuvaraj G
Yuvaraj G

Reputation: 1237

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>

Upvotes: 1

Mister1Burger
Mister1Burger

Reputation: 191

Add splitStatements:false to changeset

Like this --changeset splitStatements:false

Upvotes: 19

cool
cool

Reputation: 3515

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

Upvotes: 37

Milton Isaya
Milton Isaya

Reputation: 13

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

Upvotes: -3

Sudhagar i
Sudhagar i

Reputation: 1

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

Upvotes: 0

Jiri
Jiri

Reputation: 191

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

Upvotes: 19

Shaohua Huang
Shaohua Huang

Reputation: 788

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.

Upvotes: 46

Related Questions