SQL UDFs

[BohuTANG]

• Scalar SQL UDFs (already done)

Databend already have the scalar UDFs:

CREATE FUNCTION mean2number AS (x, y) -> (x + y) / 2;
SELECT mean2number(150, 250);
+-------------------+
| ((150 + 250) / 2) |
+-------------------+
|               200 |
+-------------------+

• Tabular SQL UDFs (UDTFs)(TODO)

A user-defined table functions can apply to a sql query and returns 0, 1 or multi-rows, each result contains 1 or more columns.
A snow style UDTFs:

create function sum_range_func(l int, r int)
  returns table (x integer)
  as 'select sum(a) from test where a > l and a < r'
  ;
  
select * from table(sum_range_func(1,9));

Reference:
https://docs.snowflake.com/en/developer-guide/udf/sql/udf-sql-tabular-functions.html

[GrapeBaBa]

/assignme

[wangzhen11aaa]

https://github.com/ClickHouse/ClickHouse/pull/23978/commits
We can refer to this.

[BohuTANG]

https://github.com/ClickHouse/ClickHouse/pull/23978/commits We can refer to this.

It looks like Scalar UDF in databend, we have implemented it:
https://github.com/datafuselabs/databend/blob/8452db7e49afbcef3e9b21539bb22b9ab0c3e697/tests/suites/0_stateless/05_ddl/05_0010_ddl_create_udf.sql

The purpose of this issue is to implement Tabular SQL UDFs.

[GrapeBaBa]

@BohuTANG @wangzhen11aaa Any other database open sourced support tabular function?

[wangzhen11aaa]

@BohuTANG @wangzhen11aaa Any other database open sourced support tabular function?

I think this article will give you some idea, This is a very good example.
https://docs.snowflake.com/en/sql-reference/functions-table.html

[BohuTANG]

@BohuTANG @wangzhen11aaa Any other database open sourced support tabular function?

I haven't seen yet, maybe have but it's not important for this issue :)

[BohuTANG]

BTW, databend already used tabular function: select * from numbers(10), it accepts an argument and returning blocks.

[GrapeBaBa]

BTW, databend already used tabular function: select * from numbers(10), it accepts an argument and returning blocks.

Cool. Let me check it.

[wangzhen11aaa]

BTW, databend already used tabular function: select * from numbers(10), it accepts an argument and returning blocks.
Is the following expression ture?
Tabular function = Table Function?

[BohuTANG]

Mostly the same.

[wangzhen11aaa]

Mostly the same.

Thanks

[BohuTANG]

Update:
Tabular SQL UDFs looks like a deep version of view #4610

[GrapeBaBa]

Update: Tabular SQL UDFs looks like a deep version of view #4610

@BohuTANG I prepare starting this task, what is your latest suggestion here after view feature merged.

[BohuTANG]

@GrapeBaBa
Cool, Thanks for taking, my suggestion is: just do it :D

[BohuTANG]

Hi, @GrapeBaBa

How is this going? :)
Spill a new issue for the tabular UDF: #5731, and welcome to take.
This issue will be closed soon.

[GrapeBaBa]

Hi, @GrapeBaBa

How is this going? :) Spill a new issue for the tabular UDF: #5731, and welcome to take. This issue will be closed soon.

will do it.