Question 264360 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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>
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search