### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [August 23, 2024, 12:11am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/1 "2024-08-23T00:11:59Z")
As far as I can tell there’s no UDF in Julia for DuckDB. Gemini says it’s easy to add but a google search turns up this [Github issue](https://github.com/duckdb/duckdb/discussions/4874) which seems to suggest that it is available and need some work.
Does anyone know what’s the actual story here? Trying to see if it’s possible for Julia
---
### Author:  [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)
#### Post date: [August 23, 2024, 2:23am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/2 "2024-08-23T02:23:45Z")
As is see - table\_function already supported.
- [duckdb/tools/juliapkg/test/test\_table\_function.jl at 862852fa395b99735e5713cb55d0cea1d9320659 · duckdb/duckdb · GitHub](https://github.com/duckdb/duckdb/blob/862852fa395b99735e5713cb55d0cea1d9320659/tools/juliapkg/test/test_table_function.jl)
```julia
DuckDB.create_table_function(
con,
"forty_two_nulls",
[Int64],
my_bind_function,
my_init_function,
my_main_function_nulls
)
results = DBInterface.execute(con,
"SELECT COUNT(*) total_cnt, COUNT(forty_two) cnt FROM forty_two_nulls(10000)")
```
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [August 23, 2024, 8:00am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/3 "2024-08-23T08:00:41Z")
I can’t recall understand this code at all. So I am guessing, you are allowed to define functions that create tables. Not but a function you can use within the body of the SQL to compute columns.
---
### Author:  [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)
#### Post date: [August 23, 2024, 6:04pm UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/4 "2024-08-23T18:04:33Z")
It appears that the DuckDB C API now includes support for scalar functions, which was added about four months ago.
>
>
> This PR adds initial support for scalar functions to the C API. This allows simp…le scalar functions to be defined and registered using only the C API, which in turn will enable these functions to be added using other APIs that build on top of the C API (e.g. Rust or Go).
>
> The main function signature is as follows:
>
> \`\`\`cpp
> typedef void (\*duckdb\_scalar\_function\_t)(duckdb\_function\_info info, duckdb\_data\_chunk input, duckdb\_vector output);
> \`\`\`
>
> These are the added callbacks that allow for creating and registering a function:
>
> \`\`\`cpp
> duckdb\_scalar\_function duckdb\_create\_scalar\_function();
> void duckdb\_destroy\_scalar\_function(duckdb\_scalar\_function \*scalar\_function);
> void duckdb\_scalar\_function\_set\_name(duckdb\_scalar\_function scalar\_function, const char \*name);
> void duckdb\_scalar\_function\_add\_parameter(duckdb\_scalar\_function scalar\_function, duckdb\_logical\_type type);
> void duckdb\_scalar\_function\_set\_return\_type(duckdb\_scalar\_function scalar\_function, duckdb\_logical\_type type);
> void duckdb\_scalar\_function\_set\_extra\_info(duckdb\_scalar\_function scalar\_function, void \*extra\_info,
> duckdb\_delete\_callback\_t destroy);
> void duckdb\_scalar\_function\_set\_function(duckdb\_scalar\_function scalar\_function,
> duckdb\_scalar\_function\_t function);
> duckdb\_state duckdb\_register\_scalar\_function(duckdb\_connection con, duckdb\_scalar\_function scalar\_function);
> \`\`\`
>
> Here is an example program that registers a custom addition function
>
> \`\`\`cpp
>
> void MyAddition(duckdb\_function\_info info, duckdb\_data\_chunk input, duckdb\_vector output) {
> // get the total number of rows in this chunk
> idx\_t input\_size = duckdb\_data\_chunk\_get\_size(input);
> // extract the two input vectors
> duckdb\_vector a = duckdb\_data\_chunk\_get\_vector(input, 0);
> duckdb\_vector b = duckdb\_data\_chunk\_get\_vector(input, 1);
> // get the data pointers for the input vectors (both int64 as specified by the parameter types)
> auto a\_data = (int64\_t \*) duckdb\_vector\_get\_data(a);
> auto b\_data = (int64\_t \*) duckdb\_vector\_get\_data(b);
> auto result\_data = (int64\_t \*) duckdb\_vector\_get\_data(output);
> // get the validity vectors
> auto a\_validity = duckdb\_vector\_get\_validity(a);
> auto b\_validity = duckdb\_vector\_get\_validity(b);
> // if either a\_validity or b\_validity is defined there might be NULL values
> duckdb\_vector\_ensure\_validity\_writable(output);
> auto result\_validity = duckdb\_vector\_get\_validity(output);
> for(idx\_t row = 0; row \< input\_size; row++) {
> if (duckdb\_validity\_row\_is\_valid(a\_validity, row) && duckdb\_validity\_row\_is\_valid(b\_validity, row)) {
> // not null - do the addition
> result\_data\[row\] = a\_data\[row\] + b\_data\[row\];
> } else {
> // either a or b is NULL - set the result row to NULL
> duckdb\_validity\_set\_row\_invalid(result\_validity, row);
> }
> }
> }
>
> static void CAPIRegisterAddition(duckdb\_connection connection) {
> duckdb\_state status;
>
> // create a scalar function
> auto function = duckdb\_create\_scalar\_function();
> duckdb\_scalar\_function\_set\_name(function, "my\_addition");
>
> // add a two bigint parameters
> duckdb\_logical\_type type = duckdb\_create\_logical\_type(DUCKDB\_TYPE\_BIGINT);
> duckdb\_table\_function\_add\_parameter(function, type);
> duckdb\_table\_function\_add\_parameter(function, type);
>
> // set the return type to bigint
> duckdb\_scalar\_function\_set\_return\_type(function, type);
> duckdb\_destroy\_logical\_type(&type);
>
> // set up the function
> duckdb\_scalar\_function\_set\_function(function, MyAddition);
>
> // register and cleanup
> duckdb\_register\_scalar\_function(connection, function);
>
> duckdb\_destroy\_scalar\_function(&function);
> }
>
> \`\`\`
>
> \##### Performance
>
> As this uses the efficient DataChunk and Vector API, the performance is identical scalar functions defined in C++. Running \`my\_addition\` versus our regular addition (with some added optimization for no NULL values - see the included test file) results in the same performance. e.g. running the function over 100M values:
>
> \`\`\`cpp
> CREATE TABLE big\_table AS SELECT (random() \* 100)::BIGINT i FROM range(100000000) t(i);
> SELECT MIN(my\_addition(i, i)) FROM big\_table;
> \-- 22ms
> SELECT MIN(i + i) FROM big\_table;
> \-- 21ms
>
> \`\`\`
> “This PR adds initial support for scalar functions to the C API. This allows simple scalar functions to be defined and registered using only the C API, which in turn will enable these functions to be added using other APIs that build on top of the C API (e.g. Rust or Go).”
(my guess)
To integrate this functionality with the Julia package, the `duckdb_scalar_function*` should be added to the `api.jl` file in the Julia package repository:
- [duckdb/tools/juliapkg/src/api.jl at main · duckdb/duckdb · GitHub](https://github.com/duckdb/duckdb/blob/main/tools/juliapkg/src/api.jl)
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [September 17, 2024, 12:31am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/5 "2024-09-17T00:31:40Z")
@ImreSamu were you able successfully add and use a UDF?
---
### Author:  [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)
#### Post date: [September 17, 2024, 12:50am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/6 "2024-09-17T00:50:40Z")
> [@drizk1](#):
>
> @ImreSamu were you able successfully add and use a UDF?
Unfortunately, I haven’t had time to work on this.
Feel free to handle it if you can.
---
### Author:  [@era127](https://discourse.julialang.org/u/era127)
#### Post date: [September 17, 2024, 4:40am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/7 "2024-09-17T04:40:56Z")
This should work if you clone and modify the package to add the c api support.
> **add scalar api to DuckDB.jl package**
>
> ```julia
> # ctypes.jl
> const duckdb_scalar_function = Ptr{Cvoid}
>
> # api.jl scalar functions
> function duckdb_create_scalar_function()
> return ccall((:duckdb_create_scalar_function, libduckdb), duckdb_scalar_function, ())
> end
>
> function duckdb_destroy_scalar_function(func)
> return ccall((:duckdb_destroy_scalar_function, libduckdb), Cvoid, (Ref{duckdb_scalar_function},), func)
> end
>
> function duckdb_scalar_function_set_name(func, name)
> return ccall((:duckdb_scalar_function_set_name, libduckdb), Cvoid, (duckdb_scalar_function, Ptr{UInt8}), func, name)
> end
>
> function duckdb_scalar_function_add_parameter(func, type)
> return ccall(
> (:duckdb_scalar_function_add_parameter, libduckdb),
> Cvoid,
> (duckdb_scalar_function, duckdb_logical_type),
> func,
> type
> )
> end
>
> function duckdb_scalar_function_set_return_type(func, type)
> return ccall(
> (:duckdb_scalar_function_set_return_type, libduckdb),
> Cvoid,
> (duckdb_scalar_function, duckdb_logical_type),
> func,
> type
> )
> end
>
> function duckdb_scalar_function_set_function(scalar_func, func)
> return ccall(
> (:duckdb_scalar_function_set_function, libduckdb),
> Cvoid,
> (duckdb_scalar_function, Ptr{Cvoid}),
> scalar_func,
> func
> )
> end
>
> function duckdb_register_scalar_function(con, func)
> return ccall(
> (:duckdb_register_scalar_function, libduckdb),
> Int32,
> (duckdb_connection, duckdb_scalar_function),
> con,
> func
> )
> end
>
> ```
> **MyAddition example**
>
> ```Julia
> using DuckDB
> function MyAddition(info::DuckDB.duckdb_function_info, input::DuckDB.duckdb_data_chunk, output::DuckDB.duckdb_vector)
> input = DuckDB.DataChunk(input, false)
> n = DuckDB.get_size(input)
> a_data = DuckDB.get_array(DuckDB.get_vector(input, 1), Int64, n)
> b_data = DuckDB.get_array(DuckDB.get_vector(input, 2), Int64, n)
>
> output_data = DuckDB.get_array(DuckDB.Vec(output), Int64, n)
> for row in 1:n
> output_data[row] = a_data[row] + b_data[row]
> end
> end
>
> db = DuckDB.DB()
> con = DuckDB.connect(db)
> # create a scalar function
> f = DuckDB.duckdb_create_scalar_function()
> DuckDB.duckdb_scalar_function_set_name(f, "my_addition")
> # add two bigint parameters
> type = DuckDB.duckdb_create_logical_type(DuckDB.DUCKDB_TYPE_BIGINT)
> DuckDB.duckdb_table_function_add_parameter(f, type)
> DuckDB.duckdb_table_function_add_parameter(f, type)
> # set the return type to bigint
> DuckDB.duckdb_scalar_function_set_return_type(f, type)
> DuckDB.duckdb_destroy_logical_type(type)
> # set up the function
> CMyAddition = @cfunction(MyAddition, Cvoid, (DuckDB.duckdb_function_info, DuckDB.duckdb_data_chunk, DuckDB.duckdb_vector))
> DuckDB.duckdb_scalar_function_set_function(f, CMyAddition)
> # register and cleanup
> DuckDB.duckdb_register_scalar_function(con.handle, f)
> DuckDB.duckdb_destroy_scalar_function(f)
>
> DuckDB.query(con, "CREATE TABLE big_table AS SELECT i FROM range(9) t(i)")
> DuckDB.query(con, "SELECT my_addition(i, i) FROM big_table")
>
> ```
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [September 17, 2024, 10:12am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/8 "2024-09-17T10:12:17Z")
wow @era127 thank you so much for this example. I got it and others to work.
My understanding is that the Julia DuckDB package is more or less community maintained… Is there a reason you know of that this has functionality has not been added to official package?
Because I think it would be nice to have this without having to update the api locally
---
### Author:  [@era127](https://discourse.julialang.org/u/era127)
#### Post date: [September 17, 2024, 11:21am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/9 "2024-09-17T11:21:22Z")
The client api is community maintained, but they have typically accepted PR within a couple of days. I think you could make a PR that would be accepted; using the table\_function c api functions as a template to write the scalar\_function api calls.
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [September 17, 2024, 1:42pm UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/10 "2024-09-17T13:42:05Z")
this is amazing! I want to have a crack at this
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [September 18, 2024, 1:24am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/11 "2024-09-18T01:24:15Z")
ok, so I have put this API to a cloned DuckDB repo. I have passing tests. Before I put in a PR, are there any thoughts folks have around this? I am not sure how capable I am to make changes to the existing api that @era127 kindly provided.
I might toy around and try to make a function call such as the below that wraps up all of the different calls above into a single more convenient version.
```julia
create_scalar_function(
con::DuckDB.Connection,
name::AbstractString,
parameter_types::Vector{Union{DuckDB.DuckDBType, Type}},
return_type::Union{DuckDB.DuckDBType, Type},
julia_function::Function
)
```
cc @xiaodai @ImreSamu
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [September 18, 2024, 2:18am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/12 "2024-09-18T02:18:04Z")
This is awesome. Maybe just my ignorance, it says scalar function here but it’s also usable as a summarisation? right e.g. `select grp, my_func(vars1) from xyz group by abc`.
Is the ability implement vectorised arrays also the same? can seem to find the answer on my cursory search
---
### Author:  [@era127](https://discourse.julialang.org/u/era127)
#### Post date: [September 18, 2024, 4:22am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/13 "2024-09-18T04:22:43Z")
I think that would require all of the “aggregate\_function” c api calls to be exposed in the Julia client api. They are similarly named like `duckdb_create_aggregate_function`.
You can see the c api example in the [test case](https://github.com/duckdb/duckdb/blob/main/test/api/capi/capi_aggregate_functions.cpp).
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [September 18, 2024, 5:10am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/14 "2024-09-18T05:10:19Z")
I guess that means it’s possible and just need some work
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [September 18, 2024, 8:25pm UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/15 "2024-09-18T20:25:53Z")
I took a shot pulling together the custom aggregate functions api, but havent quite made it work.
I also tried multiple iterations of an easier function call but was unable to get it.
I think for now I may submit the PR and see if it becomes clearer in the mean time
> **here is the api i put together so far**
>
> ```julia
> # ctypes.jl
> const duckdb_aggregate_function = Ptr{Cvoid}
>
> # api.jl aggregate functions
> function duckdb_create_aggregate_function()
> return ccall((:duckdb_create_aggregate_function, libduckdb), duckdb_aggregate_function, ())
> end
>
> function duckdb_destroy_aggregate_function(func)
> return ccall((:duckdb_destroy_aggregate_function, libduckdb), Cvoid, (Ref{duckdb_aggregate_function},), func)
> end
>
> function duckdb_aggregate_function_set_name(func, name)
> return ccall((:duckdb_aggregate_function_set_name, libduckdb), Cvoid, (duckdb_aggregate_function, Ptr{UInt8}), func, name)
> end
>
> function duckdb_aggregate_function_add_parameter(func, type)
> return ccall(
> (:duckdb_aggregate_function_add_parameter, libduckdb),
> Cvoid,
> (duckdb_aggregate_function, duckdb_logical_type),
> func,
> type
> )
> end
>
> function duckdb_aggregate_function_set_return_type(func, type)
> return ccall(
> (:duckdb_aggregate_function_set_return_type, libduckdb),
> Cvoid,
> (duckdb_aggregate_function, duckdb_logical_type),
> func,
> type
> )
> end
>
> function duckdb_aggregate_function_set_state_size(func, size::UInt64)
> return ccall(
> (:duckdb_aggregate_function_set_state_size, libduckdb),
> Cvoid,
> (duckdb_aggregate_function, UInt64),
> func,
> size
> )
> end
>
> function duckdb_aggregate_function_set_init(func, init_func)
> return ccall(
> (:duckdb_aggregate_function_set_init, libduckdb),
> Cvoid,
> (duckdb_aggregate_function, Ptr{Cvoid}),
> func,
> init_func
> )
> end
>
> function duckdb_aggregate_function_set_update(func, update_func)
> return ccall(
> (:duckdb_aggregate_function_set_update, libduckdb),
> Cvoid,
> (duckdb_aggregate_function, Ptr{Cvoid}),
> func,
> update_func
> )
> end
>
> function duckdb_aggregate_function_set_combine(func, combine_func)
> return ccall(
> (:duckdb_aggregate_function_set_combine, libduckdb),
> Cvoid,
> (duckdb_aggregate_function, Ptr{Cvoid}),
> func,
> combine_func
> )
> end
>
> function duckdb_aggregate_function_set_finalize(func, finalize_func)
> return ccall(
> (:duckdb_aggregate_function_set_finalize, libduckdb),
> Cvoid,
> (duckdb_aggregate_function, Ptr{Cvoid}),
> func,
> finalize_func
> )
> end
>
> function duckdb_register_aggregate_function(con, func)
> return ccall(
> (:duckdb_register_aggregate_function, libduckdb),
> Int32,
> (duckdb_connection, duckdb_aggregate_function),
> con,
> func
> )
> end
>
> ```
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [September 19, 2024, 8:07pm UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/16 "2024-09-19T20:07:01Z")
[the pr with scalar udf support has been merged, so should be available on next release](https://github.com/duckdb/duckdb/pull/14024)
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [September 19, 2024, 10:14pm UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/17 "2024-09-19T22:14:20Z")
Legend
---
### Author:  [@asbisen](https://discourse.julialang.org/u/asbisen)
#### Post date: [December 6, 2024, 2:04pm UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/18 "2024-12-06T14:04:22Z")
@drizk1 I see that the commit’s merged since [v1.1.1](https://github.com/duckdb/duckdb/tree/v1.1.1/tools/juliapkg/test)
Any idea why this is not reflected in the DuckDB installation on my machine which shows `[d2f5444f] DuckDB v1.1.0`. Is there a separate release process to make the Julia library available in the registry?
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [December 7, 2024, 12:06am UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/19 "2024-12-07T00:06:15Z")
@asbisen I’m not totally sure. My understanding is that the duckdb Julia package is essentially community managed, and I don’t think there was a new duckdb.jl release even tho there was a new a duckdb general release but I haven’t had a chance to investigate.
There was also a fellow working on a really convenient macro for udfs similar to the SQLite one, but I don’t think he ever merged it in either
---
### Author:  [@asbisen](https://discourse.julialang.org/u/asbisen)
#### Post date: [December 8, 2024, 5:07pm UTC](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/20 "2024-12-08T17:07:40Z")
@drizk1 where can i find the implementation of this function? I would love to check it out
[Next page](https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509.md?page=2)