User defined scalar-valued functions in SQLAlchemy-CodePudding

Say I have defined an SQL Server function say myfunc which takes a integer as input and returns an integer like this

CREATE FUNCTION myfunc
(@val as INTEGER)
RETURNS INTEGER
AS
BEGIN
    RETURN
        @val   10
END

Is there a way to call this function in SQLAlchemy? I know I can't do as below, as it is not a recognized built-in function name ('myfunc' is not a recognized built-in function name.)

select(func.myfunc(x.c.mycolumn).label("my_column_plus_10"))

CodePudding user response：

Scalar-valued UDFs must be called using schema-qualified name in SQL Server.

CREATE FUNCTION myfunc
(@val as INTEGER)
RETURNS INTEGER
AS
BEGIN
    RETURN
        @val   10
END
go
select myfunc(1) --fails
go
select dbo.myfunc(1) --succeeds

So try:

select(func.dbo.myfunc(x.c.mycolumn).label("my_column_plus_10"))

per:

To call functions which are present in dot-separated packages, specify them in the same manner:

>>> print(func.stats.yield_curve(5, 10))

stats.yield_curve(:yield_curve_1, :yield_curve_2)

sqlalchemy.sql.expression.func

Here it's a schema-qualified UDF not a "dot-separated package", but the generated SQL looks the same.