Support Out Arguments for callable procedures

[erichwang]

The SQL spec and JDBC API support callable procedures that take output arguments. The current Trino version only supports input arguments. Adding this capability will allow us to write custom Trino callable procedures that can return values.

[mdesmet]

For taking output arguments we would also need to support variable declaration:

DECLARE my_var type
SET my_var=value

[martint]

Why would that be needed? How is that related to OUT arguments?

[mdesmet]

My understanding was that before you can pass a variable as an argument you will have to declare the variable and its type. However I was not able to decypher this in the SQL spec.

Could you elaborate on the syntax required for supporting OUT parameters and how to define, call the procedure with an OUT parameter and finally read those parameters after procedure execution?

[martint]

There's currently no syntax for defining procedures in SQL. They need to be implemented in Java.

This issue is about adding the concept of OUT arguments for procedures (calling conventions in the Java code and other necessary plumbing), and all the necessary wiring to be able to present those OUT arguments via the client protocol and drivers.

[mdesmet]

Sorry I meant rather defining a variable, procedure definition is indeed not in scope of this issue.

When we execute for example CALL my_procecure(output_var), wouldn't we need to define this variable beforehand. I would also expect SELECT output_var to work and probably a way to delete the variable. My question is rather if the SQL spec describes how this should work exactly. For example if the procedure would (theoretically) return a cursor, you would have to use SQL statements to fetch results from it.