CODEWITHSUNDEEP
postgresqlliquibase
Polos
Polos

Reputation: 85

Liquibase: PSQLException: Unterminated dollar using SQL Changelog

I have two functions that i wanted to add to my DB using liquibase. First one is:

--changeset polos:001_9
create or replace function xml_attribute_element(attribute_name varchar(255), source numeric)
    returns xml as $body$
declare

begin
    return xml_attribute_element(attribute_name, source::text);
end;
$body$
    language plpgsql immutable;

I got Unterminated dollar-quoted string at or near "$BODY$ error with it and used the way described here https://forum.liquibase.org/t/unterminated-dollar-quote-started/4553 to fix it. Now it works and looks like:

--changeset polos:001_9
create or replace function xml_attribute_element(attribute_name varchar(255), source numeric)
    returns xml as '
declare

begin
    return xml_attribute_element(attribute_name, source::text);
end;
'
    language plpgsql immutable;

The second function is:

--changeset polos:001_10
create or replace function xml_text_escape(source text)
    returns text as $body$
declare
    sa varchar[] := array['&', '<', '>', '"'];
    da varchar[] := array['&amp;', '&lt;', '&gt;', '&quot;'];
    t text := source;
begin
    FOR i IN 1..array_length(sa, 1) LOOP
            t := replace(t, sa[i], da[i]);
        END LOOP;
    return t;
end;
$body$
    language plpgsql immutable;

I cant fix it by replacing $body$ with ' because it has $ inside its body, so im getting same Unterminated dollar quote but in another place.

Are there any ways to fix it?

Here is a playground: https://www.db-fiddle.com/f/kpmP1y7U4UvUhbgahuQrQc/1

Upvotes: 2

Views: 1688

Answers (1)

Polos
Polos

Reputation: 85

Answer based on @a_horse_with_no_name comment: splitStatements=false can be used to solve this issue.

Declaration for SQL changelog: --changeset name:id splitStatements:false

Additionally:

  1. If you have several functions to declare you have to do it in separate changesets;
  2. You may do it without replacing '$body$' with ' when using splitStatements=false.

Upvotes: 5

Related Questions