Support functions with parameters

[enobayram]

As far as I can tell, there's currently no way to define Functions with parameters. Would be great to be able to pass in a list of parameter_name and parameter_type pairs.

[DanCardin]

indeed. the current support is very oriented around support for triggers.

i dont personally have a need for function arguments, but if i can extrapolate from your need for some minimal intitial support (e.g. if you have a specific example function as a gauge for success), that'd be useful

[enobayram]

Thank you very much for your response. Side note: I've just discovered your library and it's awesome!

I have two concrete use cases for functions with parameters (and the reason why I'm looking for a library for it):

1. I want to abuse stored functions so that I can run a subquery in the DEFAULT value of a column, so it will be something like:

CREATE FUNCTION get_company_name_by_id(company_id UUID)
RETURNS TEXT AS $$
    SELECT name FROM company WHERE id = company_id;
$$ LANGUAGE sql STABLE;

So the key requirements here are that a) I can pass in a parameter, b) I can mark it as STABLE.

2. I want to utilize stored procedures written in plpqsql in order to cache the query plans of complex parameterized queries.

CREATE FUNCTION get_active_users_by_groups(
    start_date DATE,
    end_date DATE,
    group_ids UUID[]
)
RETURNS TABLE (id UUID, name TEXT, joined_at DATE, group_id UUID)
AS $$
BEGIN
    RETURN QUERY
    -- Imagine some very complex query here involving CTEs and many UNIONS etc.
    SELECT u.id, u.name, u.joined_at, ug.group_id
    FROM "user" u
    JOIN ...;
END;
$$ LANGUAGE plpgsql;

The requirements for this use case would be that a) I can pass in multiple parameters b) I can use non-trivial types like UUID[] c) have a complex return type like TABLE (id UUID, name TEXT, joined_at DATE, group_id UUID).

Having said all that, I'd be happy to try and open a PR for this feature if you think this is useful functionality to have.

[DanCardin]

I decided to just merge it since no one responded and as far as my tests show, there's no effect on o.g. functions so 🤷.

Feel free to submit follow up issues if there's unknown flaws in the current impl!

[enobayram]

@DanCardin thank you very much for pushing this PR to completion. I feel very bad about having disappeared from this PR. We've been going through a major life event as a family and I had very little time and focus to spare on anything non-survival.

I should let you know that I'll be using the changes from this PR and will open tickets here if I encounter any issues with the implementation. I hope I can make it up to you with more timely engagement in the future.