apache / apache/datafusion

Add named notation for user defined function arguments

Open
#17,379 12 comments 2 reactions 1 assignee Claimed by @bubulalabu View on GitHub
enhancement sql
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Is your feature request related to a problem or challenge?

As a user I would like to pass a series of arguments to a function. I would like to name these arguments so that I can use default values when arguments are not passed OR to do some processing of the arguments in the user defined table function.

For example, suppose I write a table function that is going to generate a variety of random values. I wish to pass up to three parameters, `length`, `data_type`, and `min_value`. Now I want to support `data_type` to be a few different options like `float` and `string`. If the user passes `string` for data type and they also pass a `min_value` that should generate an error.

This is a very simple example, and it *can* be supported by the current implementation. But you can easily imagine making more complex parameter combinations where you could end up having many different parameters.

By limiting to current approach where you would need to follow positional notation you can end up with something very error prone line `SELECT * FROM my_table_func(10, NULL, NULL, NULL, NULL, NULL, "somestring", NULL, NULL 2.24)`. This is very easy to get the positions wrong.

Even better would be able to do something like `SELECT * FROM my_table_func(length => 10, default_string => "somestring", avg_deviation => 2.24)`

### Describe the solution you'd like

In general I believe we want to follow [PostgreSQL syntax](https://www.postgresql.org/docs/current/sql-syntax-calling-funcs.html) where we would follow something like

```
SELECT concat_lower_or_upper(a => 'Hello', b => 'World');
```

I am not confident if we support anything other than literal values for the expressions we pass via Table Functions. It **seems** like nothing else makes a lot of sense, but I don't have enough use cases to support this. If we do only stick with literal values, then I think this could be done with a non-breaking change where we pass the name of the named expression as metadata on the literal value.

### Describe alternatives you've considered

Stick with positional arguments.

### Additional context

To be a complete solution we should support:

- [x] Scalar UDFs - done via #18019
- [ ] Aggregate UDFs
- [ ] Window UDFs
- [ ] Table UDFs

Also it is important to understand which SQL dialects support these operations.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.