sql - The Liquibase Exception: unterminated dollar-quoted string at or near

Question

I can't get my function to work in the liquibase changeset. This function is good working on the IntelliJ IDEA:

CREATE OR REPLACE FUNCTION get_value() RETURNS varchar AS
$$
SELECT varchar '170d76f83d90ea1427' AS result;
$$ LANGUAGE SQL;

But it fails when trying to run it in the liquibase changeset like this:

<changeSet author="akulik" id="test-014-002">
        <sql>
            CREATE OR REPLACE FUNCTION get_value() RETURNS varchar AS
            $$
                SELECT varchar '170d76f83d90ea1427' AS result;
            $$ LANGUAGE SQL;
        </sql>
        <rollback>
            <sql>
                DROP FUNCTION get_secret();
            </sql>
        </rollback>
</changeSet>

I'm catching this exception:

[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.2.2:update (default-cli) on project my_liquibase_project: Error setting up or running Liquibase: Migration failed for change set db/com/akulik/test/db/changesets/db.changelog-014.xml::test-014-002::akulik:
[ERROR]      Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE OR REPLACE FUNCTION get_secret() RETURNS varchar AS
[ERROR]      $$
[ERROR]             SELECT varchar '170d76f83d90ea1427' AS result: ERROR: unterminated dollar-quoted string at or near "$$
[ERROR]             SELECT varchar '170d76f83d90ea1427' AS result"
[ERROR]   Position: 68
[ERROR] -> 

I think that I should correctly shield my function. I tried this way but it didn't work and I received the same error message. I continue to find a solution in this way.

<changeSet author="akulik" id="test-014-002">
        <sql><![CDATA[
            CREATE OR REPLACE FUNCTION get_value() RETURNS varchar AS
            $$
                SELECT varchar '170d76f83d90ea1427' AS result;
            $$ LANGUAGE SQL;
        ]]></sql>
        <rollback>
            <sql>
                DROP FUNCTION get_secret();
            </sql>
        </rollback>
</changeSet>

question from:https://stackoverflow.com/questions/65924314/the-liquibase-exception-unterminated-dollar-quoted-string-at-or-near

Answer 1

I found out about <createProcedure> tag and used it instead of <sql> tag in my changeset definition. That's resolved my problem. Now it's working fine.