SQL UDFs #

A SQL user-defined function (UDF) is a custom function authored by a Starburst Galaxy user and written in the SQL language. The syntax for SQL functions is described in the Galaxy SQL documentation, including some examples.

The galaxy catalog #

Both inline and catalog UDFs are supported, as described in the SQL UDF Introduction. In Starburst Galaxy, catalog UDFs are stored in a global catalog named galaxy that is automatically attached to each cluster in your account. Because galaxy is a reserved word in Galaxy, it is not possible for a customer to create a different catalog also named galaxy.

The galaxy catalog does not appear in the cluster explorer of the query editor, but does appear in the catalog explorer, showing only an internal information_schema. You cannot create schemas, tables, or views in the galaxy catalog. Notice that the galaxy catalog is not the same as the galaxy_telemetry catalog described elsewhere.

Required privileges #

To use the SQL UDF functionality, including to create, delete, and show a list of functions, your current role must have the account-level privilege Create SQL routines.

This privilege is not enabled by default for any role, including the accountadmin role, and must be explicitly added. Be sure to select the Allow role receiving privilege slider before selecting the Create SQL routines features checkbox, so that the accountadmin role can pass the privilege on to other roles as needed.

By default, the creator of a UDF is automatically granted permission to execute that routine, but no other role can run it by default. Each defined SQL UDF can be protected with a function-level privilege that allows or restricts its use to a particular role:

1. In the navigation menu, select Access > Roles and privileges.
2. Click the name of the role you want grant privileges to.
3. Click the Privileges tab.
4. Click Add privilege.
5. Select the Function tab.
6. In the Catalog name drop-down menu, select galaxy.
7. Select the checkbox for the function you want to approve.
8. Click Save privileges.

Define and view SQL functions #

To see the list of SQL functions defined for an account, run the SHOW FUNCTIONS SQL statement as shown here:

SHOW FUNCTIONS from galaxy.functions;

SHOW FUNCTIONS without arguments displays a list of all functions, including built-in functions and SQL UDFs.

To add a new SQL UDF to your account, use the CREATE FUNCTION statement. You can also create an ephemeral inline SQL function in a WITH block, as described in the SQL UDF introduction.

Notice that the FUNCTION statement includes a RETURNS clause that is not the same as the RETURN statement. Use RETURNS to specify the data type of the returned value.

If a SQL UDF specifies a return type, you must include that data type when using DROP FUNCTION.