Feature Request: DEFAULT option on Tabular UDF

1

When creating a FUNCTION I wanted to be able to set some of the argument with a DEFAULT value, something like:

CREATE FUNCTION foo(id NUMERIC DEFAULT bar);

To better illustrate my problem is that I wanted to make a FUNCTION that takes 2 values as arguments and uses them in the WHERE clause, but I wanted to be able to give two or only one of the values, not necessarily both at the same time.

2

Hi @teoxugo

The tabular UDF doc is: User-Defined Functions | Dremio Documentation

Here is an example that returns the red-colored entries from a table named fruits:

CREATE TABLE $scratch.fruits (name, color)
    AS VALUES ('strawberry', 'red'), ('banana', 'yellow'), ('raspberry', 'red');
CREATE FUNCTION red_fruits()
    RETURNS TABLE (name VARCHAR, color VARCHAR)
    RETURN SELECT * FROM $scratch.fruits WHERE color = 'red';
SELECT name FROM TABLE (red_fruits());

Please let me know if that helps, or alter it to look as you want to achieve.

Thanks, Bogdan

3

hello @bogdan.coman

I did read the doc for an example and found this ‘fruit color’ one, but it doesn’t work for what I needed since it doesn’t allow for a default value. Meaning I wouldn’t be able to pass a parameter to the red_fruits() function.

But in the mean time I did found a way to use something similar to a default value in a tabular UDF. It’s something like this:

CREATE FUNCTION foo(id NUMERIC)
RETURN TABLE(username VARCHAR)
RETURN (
    SELECT  username
    FROM user
    WHERE (id IS NULL OR user.id = id)
)

to use this tabular UDF just call it like:
FROM TABLE(foo('bar')) if you want to search for the id = bar or
FROM TABLE(foo(NULL)) if you want to search all the rows without the WHERE clause removing anything

If anyone has any tips or suggestion for my example feel free to comment.

This is useful in my case because I wanted to create a query that takes multiple inputs and creates multiple inner joins. Each inner join take some arguments, but not necessarily every argument will be passed so I needed some DEFAULT-like values or a way to just use the WHERE clause if the value is different from NULL.