Create function from SQL script by Liquibase
I think you need to add the endDelimiter
clause in the Liquibase tag, based on the documentation found here
Below is an example
<changeSet author="newbie" id="function_rad2deg" dbms="mysql,h2">
<sqlFile encoding="utf8"
path="sql/function_rad2deg.sql"
relativeToChangelogFile="true"
splitStatements="false"
stripComments="false"
endDelimiter="\nGO"
/>
</changeSet>
Your SQL file with the above delimiter would then look like
DROP FUNCTION IF EXISTS rad2deg;
GO
CREATE FUNCTION rad2deg(rad DOUBLE)
RETURNS DOUBLE
BEGIN
RETURN (rad * 180 / PI());
END
GO
Hope this helps
With above example there are two problems one is it will not work on h2 and 2 is splitStatements should be true:
<changeSet author="me" id="01_functions_mysql" dbms="mysql">
<sqlFile encoding="utf8" path="sql/01_functions.mysql.sql"
relativeToChangelogFile="true"
splitStatements="true"
stripComments="false"
endDelimiter="\nGO" />
</changeSet>
and than something like this:
DROP FUNCTION IF EXISTS FIRST_DAY_THIS_MONTH;
GO
CREATE FUNCTION FIRST_DAY_THIS_MONTH (day date)
RETURNS date
DETERMINISTIC
BEGIN
RETURN STR_TO_DATE ( DATE_FORMAT ( day,'%Y%m01' ),'%Y%m%d');
END
GO
If you are using yaml file then here is the configuration
changeSet:
id: sqlFile-function
author: sandeep
logicalFilePath: baseFunctionScript
changes:
- sqlFile:
dbms: mysql
encoding: utf8
endDelimiter: \nGO
path: my/path/baseScripts.sql
splitStatements: true
stripComments: false
Make sure stripComments should be false else it will throw an error. I spent 4 hours because of it.