Share your best Trino SQL routine

We want to see the best SQL routines you can write, feature them as examples in the documentation, and send you some goodies as a reward!

With the recent Trino 431 release we shipped a feature that has been awaited by many Trino users for a long, long time. SQL routines are an easy way to define our own procedural, custom functions. All users on your Trino instance can then use that function in their queries and enjoy the new feature to simplify their queries.

The new process of writing a routine in your client tool in SQL can be used as alternative to the old way of having to create a custom plugin in Java, compiling it, and getting the binary deployed in your cluster. The time it takes to use a function has gone from hours to minutes and a few commands!

Our documentation includes details for all the supported statements:

• BEGIN
• CASE
• DECLARE
• FUNCTION
• IF
• ITERATE
• LEAVE
• LOOP
• REPEAT
• RETURN
• SET
• WHILE

With the memory connector and the Hive connector supporting routine storage, you can use CREATE FUNCTION and DROP FUNCTION, so that everyone using the cluster has access to your routines.

The unit tests and our examples documentation contain a number of routines that scratch the surface of what is possible. Now, we are looking for you to help us improve the documentation and maybe even find some bugs. So here is what we are asking from you:

• Upgrade your Trino cluster, CLI, and other clients to 431 or newer. Support in client tools may vary.
• Learn from the documentation and write your own routines.
• Send us your best SQL routine.
  • Create a pull request to add to the examples in the documentation with a new section, and request a review from Manfred (mosabua)
  • Alternatively, email the details and submit a CLA separately.
• Explain the use case, what the routine does, and maybe also how it works.
• Include the full statement for the CREATE FUNCTION definition and an example invocation.
• Add any necessary tables or data so we can test the function.
• Reach out to us on the Trino community Slack, if you need any help.

We plan to present submissions at Trino Summit 2023, write a blog post, add them to the documentation, and Starburst will send a cool reward for the ten best entries.

Also, if you have more great Trino usage to talk about and share, we would love to see your speaker proposal for Trino Summit.

We look forward to seeing many great submissions from you all.

See you at Trino Summit 2023, and don’t forget to register.

Martin, Dain, David, and Manfred