olirice / alembic_utils

An alembic/sqlalchemy extension for migrating sql views, functions, triggers, and policies
https://olirice.github.io/alembic_utils
MIT License
211 stars 43 forks source link

Unexpected white space inclusion in signature (Pg error: function ... does not exist) #129

Closed DiegoAlpizar closed 4 months ago

DiegoAlpizar commented 4 months ago

I tried to create a function like the following, with a space between the name and the opening paren:

delete_post =   PGFunction( schema  = 'public'
                  , signature   = "delete_post (_id INT , _user_id INT)"
                  , definition  = """
RETURNS posts AS
$BODY$
DECLARE
    deleted_post    posts%ROWTYPE ;
BEGIN
    DELETE
    FROM        posts
    WHERE       id = _id
    RETURNING   *
    INTO STRICT deleted_post
    ;

    IF deleted_post.user_id != _user_id
    THEN
        RAISE 'User ID --> % is not the owner of post ID --> %' , _user_id , _id ;
    END IF ;

    RETURN  deleted_post ;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RAISE NO_DATA_FOUND USING MESSAGE = 'Nonexistent post ID --> %' || _id ;
END ;
$BODY$
LANGUAGE plpgsql ;
"""
)

As you can see in alembic upgrade --sql head output, it is actually taking the space as part of the name:

(...)
CREATE FUNCTION "public"."delete_post "(_id INT , _user_id INT) RETURNS posts AS
(...)

So naturally, Postgre was giving me "error it doesn't" exist when trying to invoke the function with the original name

olirice commented 4 months ago

thanks, resolved in https://github.com/olirice/alembic_utils/pull/130

its available on pypi under version 0.8.3