Introduction to SQL routines#
A SQL routine is a custom, user-defined function authored by a user of Trino and written in the SQL routine language. You can declare the routine body within a FUNCTION block as inline routines or catalog routines.
An inline routine declares and uses the routine within a query processing
context. The routine is declared in a
WITH block before the query:
FUNCTION abc(x integer)
RETURN x * 2
Inline routine names must follow SQL identifier naming conventions, and cannot
The routine declaration is only valid within the context of the query. A separate later invocation of the routine is not possible. If this is desired, use a catalog routine.
Multiple inline routine declarations are comma-separated, and can include routines calling each other, as long as a called routine is declared before the first invocation.
FUNCTION abc(x integer)
RETURN x * 2,
FUNCTION xyz(x integer)
RETURN abc(x) + 1
Note that inline routines can mask and override the meaning of a built-in function:
FUNCTION abs(x integer)
RETURN x * 2
SELECT abs(-10); -- -20, not 10!
Catalog routines in Starburst Galaxy are stored in a global catalog named
galaxy, which is automatically attached to each cluster.
Refer to the documentation for the FUNCTION keyword for more details about declaring the routine overall. The routine body is composed with statements from the following list:
Statements can also use built-in functions and operators as well as other routines, although recursion is not supported for routines.
Find simple examples in each statement documentation, and refer to the example documentation for more complex use cases that combine multiple statements.
Routines can contain labels as markers for a specific block in the declaration before the following keywords:
The label is used to name the block to continue processing with the
statement or exit the block with the
LEAVE statement. This flow control is
supported for nested blocks, allowing to continue or exit an outer block, not
just the innermost block. For example, the following snippet uses the label
top to name the complete block from
SET a = a + 1;
IF a <= 3 THEN
SET b = b + 1;
UNTIL a >= 10
Labels can be used with the
LEAVE statements to continue
processing the block or leave the block. This flow control is also supported for
nested blocks and labels.
Processing routines can potentially be resource intensive on the cluster in terms of memory and processing. Take the following considerations into account when writing and running SQL routines:
Some checks for the runtime behavior of routines are in place. For example, routines that take longer to process than a hardcoded threshold are automatically terminated.
Avoid creation of arrays in a looping construct. Each iteration creates a separate new array with all items and copies the data for each modification, leaving the prior array in memory for automated clean up later. Use a lambda expression instead of the loop.
Avoid concatenating strings in a looping construct. Each iteration creates a separate new string and copying the old string for each modification, leaving the prior string in memory for automated clean up later. Use a lambda expression instead of the loop.
Most routines should declare the
RETURNS NULL ON NULL INPUTcharacteristics unless the code has some special handling for null values. You must declare this explicitly since
CALLED ON NULL INPUTis the default characteristic.
The following limitations apply to SQL routines.
Routines must be declared before they are referenced.
Recursion cannot be declared or processed.
Mutual recursion can not be declared or processed.
Queries cannot be processed in a routine.
Specifically this means that routines can not use
SELECT queries to retrieve
data or any other queries to process data within the routine. Instead queries
can use routines to process data. Routines only work on data provided as input
values and only provide output data from the