Use SQL user defined functions in Managed ClickHouse®

Use SQL user defined functions (UDFs) in Managed ClickHouse® to name repeated expressions once and keep your queries readable.

You can define your own SQL UDFs, which are automatically replicated to all nodes in the cluster and contained in backups.

Create a UDF in SQL

To create an SQL UDF in Managed ClickHouse, run the CREATE FUNCTION expression including function parameters, constants, operators, or other function calls.

CREATE FUNCTION name AS (parameter0, ...) -> expression

Limitations

• The name of your UDF needs to be unique among other functions.
• Recursive functions are not supported.
• All variables used by your UDF need to be defined in its parameter list.

Example of using SQL UDFs

1. Create an SQL UDF.

   CREATE FUNCTION is_weekend AS (date) -> toDayOfWeek(date) IN (6, 7);

2. Use your is_weekend SQL UDF in a SELECT query.

   This example counts the week-end rides in the trips table loaded in the quick start:

   SELECT count()
   FROM trips
   WHERE is_weekend(pickup_date)