Add support for Postgresql's function and trigger

[HigherOrderLogic]

Motivation

Give users the ablity to create functions and triggers on database by using sea-query instead of raw SQL.

Proposed Solutions

N/A

Additional Information

https://www.postgresql.org/docs/current/plpgsql-trigger.html

[billy1624]

Hey @HigherOrderLogic, thanks for the feature request!

I think creating, dropping and altering of function and trigger can be offered under sea_query::extension::postgres module.

• CREATE FUNCTION
• DROP FUNCTION
• ALTER FUNCTION
• CREATE TRIGGER
• DROP TRIGGER
• ALTER TRIGGER

[billy1624]

I'll open this for contributions! :)

[ikrivosheev]

@billy1624 @tyt2y3 do we really want to add support for this functionality?

[anshulxyz]

If you are open to contribution for this issue, then I'd like to work on it

[billy1624]

Hey @anshulxyz, this is still open for contribution! Please go ahead :D

[stevenhansel]

@billy1624 I'm not sure whether @anshulxyz is still working on this issue or not but I'm going to start working to add support for the trigger first

[anshulxyz]

@stevenhansel I'm not, you can take over the issue.

[billy1624]

Thanks!! @stevenhansel Let me know if you need any help :)

[HigherOrderLogic]

@billy1624 I'm not sure whether @anshulxyz is still working on this issue or not but I'm going to start working to add support for the trigger first

Do you have any idea about the API design in mind?

[billy1624]

Hey @HigherOrderLogic, you can take this as a reference TypeCreateStatement. We need to create a struct let say TriggerCreateStatement under extension/postgres module since this is Postgres specific. Looking at the docs CREATE TRIGGER.

I imagine the API be like...

enum Event {
    Insert,
    Update(Vec<ColumnRef>),
    Delete,
    Truncate,
}

Trigger::create()
    .or_replace()
    .name(NameIden)
    .table(TableIden)
    // One of the event that fire the trigger
    .before_event(Event::Insert)
    .after_event(Event::Update(vec![ ... ]))
    .instead_of_event(Event::Delete)
    // How the trigger function should be fired
    .for_each_row()
    .for_each_statement()
    // Function to execute
    .execute_function(Func)
    .to_string(PostgresQueryBuilder);

[stevenhansel]

Do you have any idea about the API design in mind?

@HigherOrderLogic I'm mostly thinking the same thing with what @billy1624 wrote, for creating a trigger we can just follow the existing TypeCreateStatement pattern which I believe fundamentally follows the Builder Pattern. For the other one, such as ALTER and DROP, well we also have the same counterparts e.g. TableAlterStatement, TableDropStatement (for the tables).

e.g. this is how you would drop a table using seaquery (from the docs)

let table = Table::alter()
    .table(Font::Table)
    .add_column(
        ColumnDef::new(Alias::new("new_col"))
            .integer()
            .not_null()
            .default(100),
    )
    .to_owned();

Well, this is probably a rough counterpart for the Trigger::drop, let me know if you have any comments

Trigger::drop()
    .if_exists()
    .name(NameIden)
    .table(TableIden);