Functions

from sqlalchemy.orm import declarative_base
from sqlalchemy_declarative_extensions import declarative_database, Functions

# Import dialect-specific Function for full feature support
from sqlalchemy_declarative_extensions.dialects.postgresql import Function
# from sqlalchemy_declarative_extensions.dialects.mysql import Function

_Base = declarative_base()

@declarative_database
class Base(_Base):
    __abstract__ = True

    functions = Functions().are(
        Function("gimme", "SELECT 1;", returns="INTEGER"),
        Function(
            "fancy_trigger",
            """
            BEGIN
            INSERT INTO foo (id) select NEW.id + 1;
            RETURN NULL;
            END
            """,
            language="plpgsql",
            returns="trigger",
        ),
        Function(
            "gimme_rows",
            '''
            SELECT id, name
            FROM dem_rowz
            WHERE group_id = _group_id;
            ''',
            language="sql",
            parameters=["_group_id int"],
            returns="TABLE(id int, name text)",
            volatility='stable', # PostgreSQL specific characteristic
        )

        # Example MySQL function
        # Function(
        #    "gimme_concat",
        #    "RETURN CONCAT(label, ': ', CAST(val AS CHAR));",
        #    parameters=["val INT", "label VARCHAR(50)"],
        #    returns="VARCHAR(100)",
        #    deterministic=True, # MySQL specific
        #    data_access='NO SQL', # MySQL specific
        #    security='INVOKER', # MySQL specific
        # ),
    )

Note

Functions options are wildly different across dialects. As such, you should likely always use the dialect-specific Function object (e.g., sqlalchemy_declarative_extensions.dialects.postgresql.Function or sqlalchemy_declarative_extensions.dialects.mysql.Function) to access all available features. The base Function provides only the most common subset of options.

Note

Function comparison logic now supports parsing and comparing function parameters (including name and type) and various dialect-specific characteristics:

• PostgreSQL: LANGUAGE, VOLATILITY, SECURITY, RETURNS TABLE(...) syntax.

• MySQL: DETERMINISTIC, DATA ACCESS, SECURITY.

The comparison logic handles normalization (e.g., mapping integer to int4 in PostgreSQL) to ensure accurate idempotency checks during Alembic autogeneration.

class sqlalchemy_declarative_extensions.function.base.Function

Describes a user defined function.

Many function attributes are not currently supported. Support is currently minimal due to being a means to an end for defining triggers.

definition: str

classmethod from_unknown_function(f)

Parameters:

f (Function)

Return type:

Self

language: str = 'sql'

name: str

abstract normalize()

Return type:

Self

parameters: Sequence[str] | None = None

List of parameter definitions as strings, e.g., [‘param1 int’, ‘param2 varchar’]. The comparison logic parses these strings to compare parameter names and types.

property qualified_name

returns: str = 'void'

schema: str | None = None

abstract to_sql_create()

Return type:

list[str]

to_sql_drop()

Return type:

list[str]

to_sql_update()

Return type:

list[str]

with_language(language)

Parameters:

language (str)

with_name(name)

Parameters:

name (str)

with_return_type(return_type)

Parameters:

return_type (str)

class sqlalchemy_declarative_extensions.function.base.Functions

The collection of functions and associated options comparisons.

Note: ignore option accepts a sequence of strings. Each string is individually

interpreted as a “glob”. This means a string like “foo.*” would ignore all views contained within the schema “foo”.

append(function)

Parameters:

function (Function)

are(*functions)

Parameters:

functions (Function)

classmethod coerce_from_unknown(unknown)

Parameters:

unknown (None | Iterable[Function] | Functions)

Return type:

Functions | None

classmethod extract(metadata)

Parameters:

metadata (sqlalchemy.MetaData | list[sqlalchemy.MetaData | None] | None)

Return type:

Self | None

functions: list[Function] = []

ignore: list[str] = []

ignore_unspecified: bool = False

include: list[str] | None = None

sqlalchemy_declarative_extensions.function.base.register_function(base_or_metadata, function)

Register a function onto the given declarative base or Metadata.

This can be used instead of the static registration through Functions on a declarative base or MetaData, to imperitively register functions.

Parameters:

• base_or_metadata (sqlalchemy_declarative_extensions.sqlalchemy.HasMetaData | sqlalchemy.MetaData)

• function (Function)

Note, there also exist dialect-specific variants which you must use in order to make use of any dialect-specific options.

class sqlalchemy_declarative_extensions.dialects.postgresql.function.Function

Describes a PostgreSQL function.

Not all functionality is currently implemented, but can be evaluated/added on request.

normalize()

Return type:

Function

parameters: Sequence[FunctionParam | str] | None = None

List of parameter definitions as strings, e.g., [‘param1 int’, ‘param2 varchar’]. The comparison logic parses these strings to compare parameter names and types.

returns: FunctionReturn | str | None = None

security: FunctionSecurity

to_sql_create(replace=False)

Return type:

list[str]

to_sql_drop()

Return type:

list[str]

to_sql_update()

Return type:

list[str]

volatility: FunctionVolatility

with_security(security)

Parameters:

security (FunctionSecurity)

with_security_definer()

class sqlalchemy_declarative_extensions.dialects.mysql.function.Function

Describes a MySQL function.

data_access: FunctionDataAccess

deterministic: bool = False

classmethod from_unknown_function(f)

Parameters:

f (sqlalchemy_declarative_extensions.function.base.Function)

Return type:

Self

modifies_sql()

no_sql()

normalize()

Return type:

Function

reads_sql()

security: FunctionSecurity

to_sql_create()

Return type:

list[str]

to_sql_drop()

Return type:

list[str]

with_data_access(data_access)

Parameters:

data_access (FunctionDataAccess)

with_security(security)

Parameters:

security (FunctionSecurity)

with_security_definer()