> ## Documentation Index
> Fetch the complete documentation index at: https://docs.peaka.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/sql/routines/function) block as [inline routines](#inline-routines) or [catalog
routines](#catalog-routines).

## Inline 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:

```sql theme={null}
WITH
  FUNCTION abc(x integer)
    RETURNS integer
    RETURN x * 2
SELECT abc(21);
```

Inline routine names must follow SQL identifier naming conventions, and cannot
contain `.` characters.

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](#catalog-routines).

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.

```sql theme={null}
WITH
  FUNCTION abc(x integer)
    RETURNS integer
    RETURN x * 2,
  FUNCTION xyz(x integer)
    RETURNS integer
    RETURN abc(x) + 1
SELECT xyz(21);
```

Note that inline routines can mask and override the meaning of a built-in function:

```sql theme={null}
WITH
  FUNCTION abs(x integer)
    RETURNS integer
    RETURN x * 2
SELECT abs(-10); -- -20, not 10!
```

## Catalog routines

Catalog routines must use a name that combines the catalog name and schema name
with the routine name, such as `example.default.power` for the `power` routine
in the `default` schema of the `example` catalog.

Invocation must use the fully qualified name, such as `example.default.power`.

## Routine declaration

Refer to the documentation for the [function](/sql/routines/function) keyword for more
details about declaring the routine overall. The routine body is composed with
statements from the following list:

* [begin](/sql/routines/begin)
* [case](/sql/routines/case)
* [declare](/sql/routines/declare)
* [if](/sql/routines/if)
* [iterate](/sql/routines/iterate)
* [leave](/sql/routines/leave)
* [loop](/sql/routines/loop)
* [repeat](/sql/routines/repeat)
* [return](/sql/routines/return)
* [set](/sql/routines/set)
* [while](/sql/routines/while)

Statements can also use [built-in functions and operators](/sql/functions/list) 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](/sql/routines/examples) for more complex use cases that combine
multiple statements.

{/* (routine-label)= */}

## Labels

Routines can contain labels as markers for a specific block in the declaration
before the following keywords:

* `CASE`
* `IF`
* `LOOP`
* `REPEAT`
* `WHILE`

The label is used to name the block to continue processing with the `ITERATE`
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 `REPEAT` to `END REPEAT`:

```sql theme={null}
top: REPEAT
  SET a = a + 1;
  IF a <= 3 THEN
    ITERATE top;
  END IF;
  SET b = b + 1;
  UNTIL a >= 10
END REPEAT;
```

Labels can be used with the `ITERATE` and `LEAVE` statements to continue
processing the block or leave the block. This flow control is also supported for
nested blocks and labels.

## Recommendations

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](/sql/functions/lambda) 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](/sql/functions/lambda) instead of the loop.
* Most routines should declare the `RETURNS NULL ON NULL INPUT` characteristics
  unless the code has some special handling for null values. You must declare
  this explicitly since `CALLED ON NULL INPUT` is the default characteristic.

## Limitations

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 `RETURN` statement.