createFunction

Note: This is a Liquibase Pro Change Type, so you need a Liquibase Pro License Key to use it.

Creates a stored function.

Uses

You can use this Change Type to deploy stored functions, which are reusable code blocks, to your database. Use-cases include encapsulating complex logic, enforcing data integrity, and promoting code reusability.

On most databases, you can use this Change Type to deploy SQL functions. If you use the Liquibase Pro extension for Databricks, you can use this Change Type to deploy both SQL and Python functions.

Run `createFunction`

To run this Change Type, follow these steps:

Add the Change Type to your changeset, as shown in the examples on this page.
Specify any required attributes. Use the table on this page to see which ones your database requires.
Deploy your changeset by running the update command:

liquibase update

Available attributes

Name	Description	Required for	Supports	Since
`catalogName`	Name of the catalog		asany, db2, db2z, derby, firebird, h2, hsqldb, informix, ingres, mariadb, mysql, oracle, postgresql, sqlite, sybase
`dbms`	Specifies which database type(s) a changeset is to be used for. See valid database type names on `dbms`. Separate multiple databases with commas. Specify that a changeset is not applicable to a particular database type by prefixing with `!`. The keywords `all` and `none` are also available.		all	3.1
`encoding`	Encoding used in the file specified in the `path` attribute. Default: `UTF-8`.		all
`functionBody`	The SQL creating the function. You need to define either this attribute or the `path` attribute. The `functionBody` attribute is not supported in the XML format; however, you can specify the function SQL in a nested format. See the XML example.	all	all
`functionName`	Name of the function	all	all
`path`	File containing the procedure text. Either this attribute or a nested procedure text is required.	all	all
`procedureText`	The SQL creating the procedure. You need to define either this attribute or the `path` attribute. The `procedureText` attribute is not supported in the XML format; however, you can specify the procedure SQL in a nested format. See the XML example.	all	all
`relativeTo Changelog File`	Specifies whether the file path is relative to the changelog file rather than looked up in the search path. Default: `false`.		all
`replaceIfExists`	Boolean. If the stored procedure defined by `procedureName` already exists, alter it instead of creating it. Default: `false`.		oracle, mssql, mysql, db2, db2z, postgres, snowflake
`schemaName`	Name of the schema		all

Rollback with `createFunction`

When performing rollbacks, Liquibase allows you to create functions with or without quotes in the function name.

Name Examples:

```
"FUNCTION1"()
```
```
FUNCTION1()
```

If you use double quotes like "FUNCTION1"(), the corresponding create syntax should be CREATE FUNCTION "FUNCTION1"() as well.

Examples

databaseChangeLog:
-  changeSet:
    id:  createFunction-example
    author:  liquibase-docs
    changes:
    -  createFunction:
        catalogName:  cat
        dbms:  h2, !oracle, mysql
        encoding:  UTF-8
        functionBody:  A String
        functionName:  A String
        path:  com/example/my-logic.sql
        relativeToChangelogFile:  true
        replaceIfExists:  false
        schemaName:  public

{
    "databaseChangeLog": [
        {
            "changeSet": {
                "id": "createFunction-example",
                "author": "liquibase-docs",
                "changes": [
                    {
                        "createFunction": {
                            "catalogName": "cat",
                            "dbms": "h2, !oracle, mysql",
                            "encoding": "UTF-8",
                            "functionBody": "A String",
                            "functionName": "A String",
                            "path": "com/example/my-logic.sql",
                            "relativeToChangelogFile": true,
                            "replaceIfExists": false,
                            "schemaName": "public"
                        }
                    }
                ]
            }
        }
    ]
}

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet  author="liquibase-docs"  id="createFunction-example">
        <pro:createFunction  catalogName="cat"
            dbms="h2, !oracle, mysql"
            encoding="UTF-8"
            functionName="A String"
            path="com/example/my-logic.sql"
            relativeToChangelogFile="true"
            replaceIfExists="false"
            schemaName="public">A String</pro:createFunction>
    </changeSet>

</databaseChangeLog>

Database support

Database	Notes	Auto Rollback
DB2/LUW	Supported	No
DB2/z	Supported	No
Derby	Supported	No
Firebird	Supported	No
Google BigQuery	Supported	No
H2	Supported	No
HyperSQL	Supported	No
INGRES	Supported	No
Informix	Supported	No
MariaDB	Supported	No
MySQL	Supported	No
Oracle	Supported	No
PostgreSQL	Supported	No
Snowflake	Supported	No
SQL Server	Supported	No
SQLite	Not Supported	No
Sybase	Supported	No
Sybase Anywhere	Supported	No