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:

  1. Add the Change Type to your changeset, as shown in the examples on this page.

  2. Specify any required attributes. Use the table on this page to see which ones your database requires.

  3. 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 will 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.

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

createFunction 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