CODEWITHSUNDEEP
sqldatabasepostgresqlfunctionliquibase
funder7
funder7

Reputation: 1830

How to use the result of a query as liquibase defaultValueComputed?

Working with liquibase I've found that it's possible to define properties, and use them as defaultValue for columns.

Everything is ok if you use pre-made functions, but how to do when you have some custom SQL that produces some data, to be used in the same manner?

I tried to do something like this:

<property name="myFunc" value="SELECT CURRENT_DATE + time '08:00:00' + (random() * interval '3 hours')::time" dbms="postgresql"/>

...

<column name="selected_pickup_date" type="datetime" defaultValueComputed="${myFunc}" remarks="Selected from customer during order creation">
         <constraints nullable="false"/>
</column>

But it didn't work! The error code showed that liquibase was just placing my SQL string as the column's default value.

SQL wasn't parsed as I expected.

How to proceed to get the query result used as default value?

Upvotes: 0

Views: 4089

Answers (1)

funder7
funder7

Reputation: 1830

After many attempts, I understood that Liquibase doesn't parse SQL in properties, probably it only executes functions and places their result in the property value, and the placeholder ${xxx} is replaced with the function's output.

Once a new row is added to the table, and the column with defaultValue is empty, the property value is read and used in place of NULL.

I tried many solutions and luckily the last one worked:

  1. Take the SQL and create a new function in a sql file, in my case it was simple:
DROP FUNCTION IF EXISTS fixed_date_rand_time;

CREATE FUNCTION fixed_date_rand_time() RETURNS timestamp
AS $$ SELECT CURRENT_DATE + time '08:00:00' + (random() * interval '3 hours')::time $$
          LANGUAGE SQL;
  1. Import the sql before other changelogs
 <include relativeToChangelogFile="true" file="func.sql" />
  1. Now..you can create the property! This time calling the function instead of the SQL string

    <property name="myFunc" value="fixed_date_rand_time()" dbms="postgresql"/>

...

    <changeSet id="20201026174848-1" author="jhipster">
        <createTable tableName="my_table">

            <column name="selected_pickup_date" type="datetime" defaultValueComputed="${myFunc}" remarks="Selected from customer during order creation">
                <constraints nullable="true"/>
            </column>
        </createTable>
    </changeSet>

...

Et Voilà! It works now!

The very-nice thing is that properties can be used with context too, allowing you to bypass the new function when outside of local test environment:


 <property name="myFunc" value="fixed_date_rand_time()" dbms="postgresql" context="test"/>

 <property name="myFunc" value="NULL" dbms="postgresql" context="!test"/>

Not perfect, it works only with nullable columns, but there must a solution.

Finally the database is loaded with correct values automatically, taking away the hassle of updating dates everyday, to have everything work as expected.

I hope that this solution can help somebody, who maybe has the will to refine it a bit :-)

My Liquibase version is 3.10, I cannot update to the latest due to other dependencies... maybe the problem has been addressed there.

I don't exclude that the feature has been disabled intentionally in favour of the pro version.

Have a nice day

Upvotes: 3

Related Questions