CODEWITHSUNDEEP
postgresqlstored-proceduresliquibase
orion_kid
orion_kid

Reputation: 455

liquibase postgres function not getting replaced

I am simply trying to make some modifications to my postgres function. The liquibase logs report "Stored Procedure created", and show the changeSet id as expected, but when I check the database objects the function still has the old code.

I've tried dropping it in a sql block first, but this does not work. It complains that I have to first drop all the tables dependent on it. Also tried playing with the runOnChange/ runAlways flags to no avail.

log snippet

2023-10-12 16:34:10.808 INFO 29604 --- [ main] liquibase.changelog : Stored procedure created 2023-10-12 16:34:10.811 INFO 29604 --- [ main] liquibase.changelog : ChangeSet db/changelog/service/triggers/TRIGGER_AUDIT.xml::AUDIT_TRIGGER_2::ran successfully in 10ms

redacted code

    <changeSet author="me" id="AUDIT_TRIGGER_1">
        <createProcedure dbms="postgresql">
            create or replace function audit_trigger() RETURNS trigger
            language plpgsql
                AS
            $audit_trigger$
            begin
                    // CODE
            end;
            $audit_trigger$
        </createProcedure>


    <changeSet author="me" id="AUDIT_TRIGGER_2">
        <createProcedure dbms="postgresql">
            create or replace function audit_trigger() RETURNS trigger
            language plpgsql
                AS
            $audit_trigger$
            begin
                    // NEW CODE
            end;
            $audit_trigger$
        </createProcedure>

Upvotes: 0

Views: 484

Answers (2)

orion_kid
orion_kid

Reputation: 455

My changesets are just fine, it was my client (intellij) not refreshing the function contents which threw me. Tried with another client and I could see the changes had gotten applied.

Upvotes: 0

Adarsh
Adarsh

Reputation: 726

You can create an SQL changelog for stored procedures and functions and include this in your main XML change log file. Please see the below for a sample procedure.

--liquibase formatted sql
--changeset auther:someid-1.0 splitStatements:false runOnChange:true
CREATE OR REPLACE PROCEDURE myproc_of_function_name( <Your args if any> )
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    <....your stuff here...>
END;
$BODY$
--rollback empty

Please note splitStatements and runOnChange tags are important. rollback tag in the bottom you can either add empty or a valid rollback statement.

To include in the main file, use the below tag

<include file="yourpath/sql/yourSqlChangeLogFileName.sql"/>

It works for me all the time! Hope this helps.

Upvotes: 0

Related Questions