Home:ALL Converter>Liquibase with PostgreSQL : it does not create the function

Liquibase with PostgreSQL : it does not create the function

Ask Time:2019-10-02T15:07:31         Author:Bestemmiolino

Json Formatter

Liquibase on Postgresql doesn't create functio/procedure at all; It logs correctly on databasechangelog the current changeset as "executed" but function doesn't exists.

I'm searching to create and then call a procedure to support mandatory foreign key on new field with null value;

This my included xml file with dedicated changeset:

<changeSet id="20190930_update_relation_001" author="bernardo">
    <sqlFile path="../script/20190930_update_script.sql" splitStatements="true"
             stripComments="true" dbms="PostgreSQL" encoding="utf8" relativeToChangelogFile="true" />
</changeSet>
<changeSet id="20190930_update_relation_002" author="bernardo">
        <sql dbms="PostgreSQL" splitStatements="true" stripComments="true">
            SELECT update_relation();
        </sql>
</changeSet>

and this is my plpgsql code for:

CREATE OR REPLACE FUNCTION public.update_relation()
    RETURNS void
    LANGUAGE 'plpgsql>'
AS $BODY$
 declare
 prCrs [...]%rowtype;
 ngCrs [...]%rowtype;
 begin
    for prCrs in select [...] loop
    with row as (insert [...] RETURNING * )
    update [...] where  id = prCrs.id; --CURRENT OF prCrs;
    end loop;

    for ngCrs in select [...] loop
    with row as (insert [...] RETURNING * )
    update [...] where id = prCrs.id; --CURRENT OF ngCrs;
    end loop;
   end;$BODY$;

It's simple. Liquibase logs correctly on databasechangelog table, but function doesn't exists actually.

WHY ?

EDIT

even tag -ext:createFunction- doesn't works; I have just tried :

<changeSet id="20190930_update_indirizzo_relation_001" author="bernardo">
                <ext:createFunction dbms="PostgreSQL"
                                 encoding="utf8"
                                 relativeToChangelogFile="true"
                                 replaceIfExists="true"
                                 path="../script/20190930_update_indirizzo_script.sql"
                                 functionName="update_relation" />
                <!--<sqlFile path="../script/20190930_update_indirizzo_script.sql" splitStatements="false"-->
                         <!--stripComments="false" dbms="PostgreSQL" encoding="utf8" relativeToChangelogFile="true" />-->
            </changeSet>

No ERROR LOG, NO FUNCTION... SOB! :-(

Author:Bestemmiolino,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/58196702/liquibase-with-postgresql-it-does-not-create-the-function
yy