How can I declare a Postgres function on SQLAlchemy?

Question

I'm using Flask-SQLAlchemy and I have table/models structured like

class TableName (db.Model):
    columnname = db.Column(db.String)

How can I insert a Postgres custom function there so SQLAlchemy knows about it?

I mean a function like

create function canonicalize (text) returns text as $$
  select $1;
$$ language sql;

Answer 1

You can use text() from sqlalchemy to generate Postgresql Functions a.k.a Stored Procedure.

I tested with postgresql 14 and python library psycopg2==2.9.6 and psycopg2-binary==2.9.6

from sqlalchemy import create_engine, text

engine = create_engine('/path/to/db...')
create_procedure = text("""
CREATE OR REPLACE FUNCTION calculate_sum(a integer, b integer)
RETURNS integer AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;
""")

with engine.connect() as connection:
   connection.execute(create_procedure)
   connection.commit()

1. Use text() function to create SQL expressions and statements as text.
2. Creating a stored procedure using SQLAlchemy's text method doesn't automatically commit the transaction to the database. You need to explicitly commit it
3. For enhancement, you also can import the sql statement from other .sql extension file, so you can iterate and create multiple sql functions for postgresql.