How can I create triggers for a postgreSQL db using liquibase?

Question

I'm using the dropwizard-migrations module for liquibase db refactoring. See the guide here: http://dropwizard.codahale.com/manual/migrations/

When I run java -jar my_project.jar db migrate my_project.yml

I get the following error:

ERROR [2013-09-11 20:53:43,089] liquibase: Change Set migrations.xml::11::me failed. Error: Error executing SQL CREATE OR REPLACE TRIGGER add_current_date_to_my_table BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE change_update_time();: ERROR: syntax error at or near "TRIGGER" Position: 19

Here are some relevant changesets from my migrations.xml file:

<changeSet id="1" author="me">
    <createProcedure>
        CREATE OR REPLACE FUNCTION change_update_time() RETURNS trigger
        LANGUAGE plpgsql
        AS $$
        BEGIN
        NEW.updated_at := CURRENT_TIMESTAMP;
        RETURN NEW;
        END;
        $$;
    </createProcedure>
    <rollback>
        DROP FUNCTION change_update_time();
    </rollback>
</changeSet>        
    
<changeSet id="2" author="me">
    <preConditions>
        <not>
            <tableExists tableName="my_table"/>
        </not>
    </preConditions>

    <createTable tableName="my_table">
        <column name="_id" type="integer" defaultValue="0">
            <constraints nullable="false"/>
        </column>
        <column name="updated_at" type="timestamp without time zone" defaultValue="now()">
            <constraints nullable="false"/>
        </column>
    </createTable>
</changeSet>

<changeSet id="3" author="me">
    <sql splitStatements="false">
        CREATE OR REPLACE TRIGGER add_current_date_to_my_table BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE change_update_time();
    </sql>
    <rollback>
        DROP TRIGGER add_current_date_to_my_table ON my_table;
    </rollback>
</changeSet>

Is there any way I can create the trigger add_current_date_to_my_table? Is this redundant with the "RETURNS trigger" from creating the function?

Barsky

I don't know Postgres at all but does the stored procedures work on it's own? The error message you get is from postgres. Does "create or replace trigger" work? Quick googleing shows that trigger might be dropped like DROP TRIGGER IF EXISTS.... Anyhow. Try to create the trigger manually first and make sure it works. Liquibase will just execute the same SQL. – Onus

Answer 1

The solution is:

<changeSet id="3" author="me">
    <sql>
        DROP TRIGGER IF EXISTS add_current_date_to_my_table ON my_table;
        CREATE TRIGGER add_current_date_to_my_table BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE change_update_time();
    </sql>
    <rollback>
        DROP TRIGGER add_current_date_to_my_table ON my_table;
    </rollback>
</changeSet>

H/T Jens.

Barsky

Answer 2

Ann Kilzer has provided the right answer as there is no CREATE OR REPLACE statement in Postgres, unlike PL/SQL.

So we need to split this statement into 2 atomic operations:

1. DROP TRIGGER
2. CREATE TRIGGER

Bousquet

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Serene