Error at "OLD <> NEW" when creating function in Liquibase

Question

I am trying to create a trigger function using Liqubase. Below is my change set:

<changeSet id="1" author="valen" dbms ="postgresql" >
    <createProcedure>
        CREATE OR REPLACE FUNCTION public.onupdate_update_mod_ver()
        RETURNS trigger
        LANGUAGE plpgsql
        AS $function$
        BEGIN
        IF NEW <> OLD THEN
        UPDATE mod_ver SET rec_change_date = current_timestamp where id = new.id;
        END IF;
        RETURN NEW;
        END;
        $function$;
    </createProcedure>
    <rollback>
        DROP FUNCTION onupdate_update_mod_ver();
    </rollback>
</changeSet>

It throws a syntax error on the line IF NEW <> OLD THEN.
It says tag name expected. Basically it reads <> as a tag instead of "not equal".

I tried using != but didn't work.

Note: the function is correct when executed directly in the database, but with a change log it doesn't work. I also tried to use <sql> tags to simply write it as statement but still same error.

Any idea how to bypass this issue?

In general I would **not** use `` to begin with in Liquibase. Put the `create` statement into a SQL script, then use `` to "run" that file. That makes your XML smaller (as you don't need to repeat the whole code when something changes) and easier to debug as you can also use that script from within a SQL client — , May 21 '21 at 05:56

Erwin Brandstetter · Accepted Answer · 2021-05-21T01:39:48.463

Since Liquibase uses XML format, wrap the whole CREATE FUNCTION statement in a CDATA section to escape all contained characters which would otherwise be recognized as markup:

<changeSet id="1" author="valen" dbms ="postgresql">
    <createProcedure>
       <![CDATA[ 
        CREATE OR REPLACE FUNCTION public.onupdate_update_mod_ver()
          RETURNS trigger
          LANGUAGE plpgsql AS
        $func$
        BEGIN
           IF NEW IS DISTINCT FROM OLD THEN
              UPDATE public.mod_ver
              SET    rec_change_date = CURRENT_TIMESTAMP
              WHERE  id = NEW.id;
           END IF;
           RETURN NEW;
        END
        $func$
       ]]> 
    </createProcedure>
    <rollback>
        DROP FUNCTION public.onupdate_update_mod_ver()
    </rollback>
</changeSet>

Besides <, $ (as used for dollar-quoting the function body) also has special meaning.

See:

While being at it I added a couple other suggestions:

If it makes sense to schema-qualify the function on creation, it makes sense to do the same when dropping. (And schema-qualify the updated table in the same fashion.)
NEW <> OLD will fail if any row contains a NULL field. If all involved columns are defined NOT NULL that's ok. Otherwise use NEW IS DISTINCT FROM OLD instead.
Indentation, trim noise.

Error at "OLD <> NEW" when creating function in Liquibase

1 Answers1