feature request: stored procedures

[YaraslauZhylko]

Add posibility to manage PostgreSQL procedures in addition to functions.

Something like this:

refresh_my_view_procedure = Procedure(
    "refresh_my_view",
    """
    BEGIN
        REFRESH MATERIALIZED VIEW my_view WITH DATA;
    END
    """,
    language="plpgsql",
)
Base.procedures.append(refresh_my_view_procedure)

That will result in:

op.execute(
    """
    CREATE PROCEDURE refresh_my_view()
    LANGUAGE plpgsql AS $$
    BEGIN
        REFRESH MATERIALIZED VIEW my_view WITH DATA;
    END
    $$;
    """
)

The difference is purely aesthetic:

CALL refresh_my_view();

vs.

SELECT refresh_my_view();

[YaraslauZhylko]

@DanCardin Is there any progress on this feature request?

[DanCardin]

woops! I had the initial PR done up, but it wasn't passing CI and I honestly just forgot about this. sorry! I might have some time this weekend to see if i can't get it into a working state.

[DanCardin]

In fact, I believe I fixed it just now. if you wanted to test out https://github.com/DanCardin/sqlalchemy-declarative-extensions/pull/65 before I go and merge it. otherwise i'll probably release later this weekend.

[YaraslauZhylko]

@DanCardin Left some comments in the PR itself: https://github.com/DanCardin/sqlalchemy-declarative-extensions/pull/65#issuecomment-2211710249

[YaraslauZhylko]

Seems to work in our project code. Thanks!