UDF support

[idomic]

Currently the package doesn't support it.
We should add a feature to accept it and execute it via the engine.

[idomic]

cc: @jonnickerson
If you have a specific UDF you've tried please tag it here!

[edublancas]

This is already possible since 0.5.1! I opened a PR with an example

[idomic]

Reopening this as an easier solution can be made here

[edublancas]

I think this should be a separate issue with more details (the proposed API)

[idomic]

Proposed API:

# Load the extension and connect to the database
%load_ext sql
%sql postgresql://username:password@localhost:5432/mydatabase

# Define a function that takes in a value and returns its square
def my_square_function(x):
    return x * x

# Use the func module to define the UDF as a SQL expression
from sqlalchemy import func
square_udf = func.my_square_function(column)

# Use the UDF in a SELECT statement
result = %sql SELECT *, :square_udf AS squared_value FROM mytable

# Print the result set
print(result)

We might want to even create a tighter integration to the func part, maybe something like:

# Define the UDF as a SQL expression
%sql --udf square_udf=my_square_function

# And then consume it
%sql SELECT *, :square_udf AS squared_value FROM mytable

In the second snippet we take the extra step to wrap func.user_function for the user, when given the --udf flag. This allows a complete abstraction of sqlalchemy like it should be.

[edublancas]

this is highly db dependent, I think we should close it

[idomic]

The question I have is how many users actually have UDFs, I know it's pretty common with DWHs, and is supported by most if not all of them (At least snowflake, bigquery, redshift).

[edublancas]

ok, then let's start with a single one, the one that has the most users.

[edublancas]

each db has its own mechanism for defining UDFs so it'll take a lot of effort to unify them in a single API